Inhalter
Mir hu virdru fir Ufänger erkläert wéi d'Basisfunktioune vu VLOOKUP benotzen (Englesch VLOOKUP, d'Ofkierzung steet fir "vertikal Lookup Funktioun"). An erfuerene Benotzer goufen e puer méi komplizéiert Formulen gewisen.
An an dësem Artikel wäerte mir probéieren Informatiounen iwwert eng aner Method vun Aarbecht mat vertikalen Sich ze ginn.
Dir kënnt Iech froen: "Firwat ass dat néideg?". An dat ass néideg fir all méiglech Sichmethoden ze weisen. Zousätzlech verhënnert vill VLOOKUP Restriktiounen dacks dat gewënschte Resultat ze kréien. An dëser Hisiicht ass INDEX ( ) MATCH ( ) vill méi funktionell an divers, a si hunn och manner Restriktiounen.
Grondlage INDEX MATCH
Well den Zweck vun dësem Guide ass ze weisen wéi gutt dës Feature ass, mir Loosst eis d'Basisinformatioun iwwer d'Prinzipien vu senger Operatioun kucken. A mir wäerte Beispiller weisen, an och betruecht firwat, et ass besser wéi VLOOKUP ().
INDEX Funktioun Syntax a Benotzung
Dës Funktioun hëlleft de gewënschte Wäert tëscht de spezifizéierte Sichberäicher ze fannen baséiert op der Kolonn oder der Linnnummer. Syntax:
=INDEX(Array, Zeilnummer, Kolonnnummer):
- Array - de Beräich an deem d'Sich stattfënnt;
- Zeilnummer - d'Zuel vun der Linn déi an der spezifizéierter Array gesicht gëtt. Wann d'Zeilnummer onbekannt ass, muss d'Kolonnnummer uginn;
- Kolonnnummer - d'Zuel vun der Kolonn déi an der spezifizéierter Array fonnt gëtt. Wann de Wäert onbekannt ass, ass eng Linnnummer néideg.
E Beispill vun enger einfacher Formel:
=INDEX(A1:S10,2,3;XNUMX;XNUMX)
D'Funktioun sicht am Beräich vun A1 bis C10. D'Zuelen weisen wéi eng Rei (2) a Kolonn (3) de gewënschte Wäert ze weisen. D'Resultat wäert Zell C2 sinn.
Zimmlech einfach, richteg? Awer wann Dir mat echte Dokumenter schafft, sidd Dir onwahrscheinlech Informatiounen iwwer Kolonnnummeren oder Zellen ze hunn. Dat ass wat d'MATCH () Funktioun ass fir.
MATCH Funktioun Syntax a Benotzung
D'MATCH () Funktioun sicht no de gewënschten Wäert a weist seng ongeféier Zuel am spezifizéierte Sichberäich.
De searchpos () Syntax gesäit esou aus:
=MATCH(Wäert fir ze sichen, Array fir ze sichen, Matchtyp)
- Sichwäert - d'Zuel oder den Text ze fannen;
- gesichte Array - de Beräich wou d'Sich stattfënnt;
- Match Typ - spezifizéiert ob fir de genaue Wäert ze sichen oder d'Wäerter am nootste derbäi:
- 1 (oder kee Wäert uginn) - bréngt de gréisste Wäert zréck, deen gläich oder manner ass wéi de Wäert dee spezifizéiert gouf;
- 0 - weist en exakte Match mam gesichte Wäert. An der Kombinatioun INDEX () MATCH () Dir braucht bal ëmmer e genee Match, also mir schreiwen 0;
- -1 - Weist de klengste Wäert dee méi grouss ass wéi oder gläich wéi de Wäert, deen an der Formel spezifizéiert ass. D'Sortéierung gëtt an ofstigend Uerdnung duerchgefouert.
Zum Beispill, am Beräich B1: B3 New York, Paräis, London sinn registréiert. D'Formel hei drënner weist d'Nummer 3 well London ass drëtt op der Lëscht:
=EXPOSE(London,B1:B3,0)
Wéi mat der INDEX MATCH Funktioun ze schaffen
Dir hutt wahrscheinlech schonn ugefaang de Prinzip ze verstoen, duerch deen d'gemeinsame Aarbecht vun dëse Funktiounen gebaut gëtt. Kuerz, dann INDEX () sicht no de gewënschten Wäert tëscht de spezifizéierte Reihen a Kolonnen. A MATCH () weist d'Zuelen vun dëse Wäerter:
=INDEX(Kolonn aus deem de Wäert zréckgeet, MATCH(Wäert fir ze sichen, Kolonn fir ze sichen, 0))
Hutt Dir nach ëmmer schwéier ze verstoen wéi et funktionnéiert? Vläicht wäert e Beispill besser erklären. Ugeholl, Dir hutt eng Lëscht vun de Welthaaptstied an hir Bevëlkerung:
Fir d'Gréisst vun der Bevëlkerung vun enger bestëmmter Haaptstad erauszefannen, zum Beispill der Haaptstad vu Japan, benotze mir déi folgend Formel:
=INDEX(C2:C10, MATCH(Japan, A2:A10,0))
Erklärung:
- D'MATCH () Funktioun sicht de Wäert - "Japan" an der Array A2:A10 a gëtt d'Nummer 3 zréck, well Japan den drëtte Wäert an der Lëscht ass.
- Dës Figur geet unLinn Nummer" an der Formel INDEX () a seet d'Funktioun fir e Wäert aus dëser Zeil ze drécken.
Also déi uewe genannte Formel gëtt d'Standardformel INDEX(C2:C10,3). D'Formel sicht aus den Zellen C2 bis C10 a gitt Daten aus der drëtter Zell an dësem Beräich zréck, dat heescht C4, well de Countdown vun der zweeter Zeil ufänkt.
Wëllt Dir net den Numm vun der Stad an der Formel virschreiwen? Da schreift et an all Zell, sot F1, a benotzt et als Referenz an der MATCH () Formel. An Dir sidd mat enger dynamescher Sichformel um Enn:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Wichteg! Zuel vun Linnen an vill INDEX () muss d'selwecht sinn wéi d'Zuel vun de Reihen an ugesinn Array am MATCH (), soss kritt Dir dat falscht Resultat.
Waart eng Minutt, firwat net just d'VLOOKUP () Formel benotzen?
=VLOOKUP(F1, A2:C10, 3, Falsch)
Wat ass de Punkt Zäit ze verschwenden fir all dës Komplexitéite vum INDEX MATCH erauszefannen?
An dësem Fall ass et egal wéi eng Funktioun ze benotzen. Dëst ass just e Beispill fir ze verstoen wéi d'INDEX () an MATCH () Funktiounen zesumme schaffen. Aner Beispiller wäerte weisen wat dës Funktiounen fäeg sinn a Situatiounen wou de VLOOKUP mächteg ass.
INDEX MATCH oder VLOOKUP
Wann Dir décidéiert wéi eng Sichformel ze benotzen, si vill d'accord datt INDEX () an MATCH () vill iwwer VLOOKUP sinn. Wéi och ëmmer, vill Leit benotzen nach ëmmer VLOOKUP (). Éischtens, VLOOKUP () méi einfach, an zweetens, Benotzer verstinn net all d'Virdeeler vun enger Aarbecht mat INDEX () an MATCH (). Ouni dëst Wëssen wäert keen averstane sinn hir Zäit ze verbréngen fir e komplexe System ze studéieren.
Hei sinn d'Haaptvirdeeler vun INDEX () a MATCH () iwwer VLOOKUP ():
- Sich vu riets op lénks. VLOOKUP () kann net vu riets op lénks sichen, sou datt d'Wäerter déi Dir sicht muss ëmmer an de lénkste Kolonnen vun der Tabell sinn. Mee INDEX () an MATCH () kann dëst ouni Problem verschaffen. Dësen Artikel wäert Iech soen wéi et an der Praxis ausgesäit: wéi Dir de gewënschte Wäert op der lénker Säit fannt.
- Sécher Zousatz oder Ewechhuele vu Sailen. D'VLOOKUP () Formel weist falsch Resultater wann Spalten ewechhuelen oder dobäi ginn, well VLOOKUP () déi exakt Kolonnnummer brauch fir erfollegräich ze sinn. Natierlech, wann Spalten dobäi oder ewechgeholl ginn, änneren hir Zuelen och.
An an den INDEX () an MATCH () Formelen gëtt eng Rei vu Kolonnen uginn, net eenzel Kolonnen. Als Resultat kënnt Dir sécher Spalten derbäisetzen an ewechhuelen ouni d'Formel all Kéier ze aktualiséieren.
- Keng Limiten op Sichvolumen. Wann Dir VLOOKUP () benotzt, däerf d'Gesamtzuel vun de Sichkriterien net méi wéi 255 Zeeche sinn oder Dir kritt e #VALUE! Also wann Är Donnéeën eng grouss Zuel vun Zeechen enthält, INDEX () an MATCH () sinn déi bescht Optioun.
- Héich Veraarbechtungsgeschwindegkeet. Wann Är Dëscher relativ kleng sinn, da sidd Dir onwahrscheinlech en Ënnerscheed ze bemierken. Mä, wann den Dësch enthält honnerte oder dausende vun Zeile, an, deementspriechend, ginn et honnerte an dausende vun Formelen, INDEX () an MATCH () vill méi séier wéi VLOOKUP eens (). D'Tatsaach ass datt Excel nëmmen d'Spalten veraarbecht, déi an der Formel spezifizéiert sinn, anstatt déi ganz Tabell ze veraarbecht.
D'Performance Impakt vun VLOOKUP () wäert besonnesch bemierkenswäert sinn wann Är Aarbechtsblat eng grouss Zuel vu Formelen enthält wéi VLOOKUP () an SUM (). Separat Kontrollen vun de VLOOKUP () Funktiounen sinn néideg fir all Wäert an engem Array ze analyséieren. Also Excel muss eng rieseg Quantitéit un Informatioun veraarbecht, an dëst verlangsamt d'Aarbecht wesentlech.
Formel Beispiller
Mir hu schonn d'Nëtzlechkeet vun dëse Funktiounen erausfonnt, sou kënne mir op déi interessantst Deel réckelen: d'Applikatioun vum Wëssen an der Praxis.
Formel fir vu riets op lénks ze sichen
Wéi scho gesot, VLOOKUP kann dës Form vu Sich net ausféieren. Also, wann déi gewënschte Wäerter net an der lénkser Kolonn sinn, wäert VLOOKUP () kee Resultat produzéieren. D'INDEX () a MATCH () Funktiounen si méi villsäiteg, an d'Location vun de Wäerter spillt keng grouss Roll fir datt se funktionnéieren.
Zum Beispill wäerte mir eng Rankkolonne op der lénker Säit vun eiser Tabelle addéieren a probéieren erauszefannen wat fir eng Bevëlkerungsrang d'Haaptstad vun Eist Land besetzt.
An Zell G1 schreiwen mir de Wäert ze fannen, a benotzt dann déi folgend Formel fir am Beräich C1: C10 ze sichen an de entspriechende Wäert vun A2: A10 zréckzekommen:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Prompt. Wann Dir plangt dës Formel fir verschidde Zellen ze benotzen, vergewëssert Iech datt Dir d'Beräicher mat absoluter Adresséierung fixéiert (zum Beispill, $A$2: $A$10 an $C$2: 4C$10).
INDEX MÉI ausgesat MÉI ausgesat a Kolonnen a Reihen ze sichen
An den uewe genannte Beispiller hu mir dës Funktiounen als Ersatz fir VLOOKUP () benotzt fir Wäerter aus engem virdefinéierte Reihenbereich zréckzekommen. Awer wat wann Dir eng Matrix oder zweesäiteg Sich maache musst?
Et kléngt komplizéiert, awer d'Formel fir sou Berechnungen ass ähnlech wéi d'Standard INDEX () MATCH () Formel, mat nëmmen engem Ënnerscheed: d'MATCH () Formel muss zweemol benotzt ginn. Déi éischt Kéier fir d'Zeilnummer ze kréien, an déi zweet Kéier fir d'Kolonnnummer ze kréien:
=INDEX(Array, MATCH(vertikal Sichwäert, Sichkolonne, 0), MATCH(horizontale Sichwäert, Sichrei, 0))
Loosst eis d'Tabell hei ënnen kucken a probéieren eng Formel ze maachen INDEX() EXPRESS() EXPRESS() fir Demographie an engem spezifesche Land fir e ausgewielt Joer ze weisen.
D'Zilland ass an der Zell G1 (vertikal Lookup) an d'Ziljoer ass an der Zell G2 (horizontal Lookup). D'Formel wäert esou ausgesinn:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Wéi dës Formel funktionnéiert
Wéi mat all aner komplex Formelen, si si méi einfach ze verstoen andeems se se an eenzel Equatiounen ofbriechen. An da kënnt Dir verstoen wat all eenzel Funktioun mécht:
- MATCH(G1,A2:A11,0) - sicht no engem Wäert (G1) am Beräich A2:A11 a weist d'Zuel vun dësem Wäert, an eisem Fall ass et 2;
- SEARCH(G2,B1:D1,0) - sicht no engem Wäert (G2) am Beräich B1:D1. An dësem Fall war d'Resultat 3.
Déi fonnt Zeilen a Spaltnummeren ginn op de entspriechende Wäert an der INDEX () Formel geschéckt:
=INDEX(B2:D11,2,3;XNUMX;XNUMX)
Als Resultat hu mir e Wäert deen an enger Zell op der Kräizung vun 2 Reihen an 3 Kolonnen am Beräich B2:D11 ass. An d'Formel weist de gewënschten Wäert, deen an der Zelle D3 ass.
Sich no multiple Konditioune mat INDEX a MATCH
Wann Dir eise Guide fir VLOOKUP gelies hutt (), Dir hutt wahrscheinlech e puer Sichformelen probéiert. Awer dës Sichmethod huet eng bedeitend Begrenzung - de Besoin fir eng Hëllefskolonn ze addéieren.
Awer déi gutt Noriicht ass dat Mat INDEX () a MATCH () kënnt Dir no verschidde Konditioune sichen ouni Äert Aarbechtsblat z'änneren oder z'änneren.
Hei ass déi allgemeng Multi-Conditioun Sich Formel fir INDEX () MATCH ():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
D'Notiz: dës Formel muss zesumme mat der Tastatur Ofkiirzung benotzt ginn CTRL+SHIFT+ENTER.
Ugeholl Dir musst de Wäert fannen deen Dir sicht baséiert op 2 Bedéngungen: Keefer и Produit.
Dëst erfuerdert déi folgend Formel:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
An dëser Formel ass C2:C10 de Beräich an deem d'Sich stattfënnt, F1 - dës Konditioun, A2:A10 — ass d'Gamme fir den Zoustand ze vergläichen, F2 - Konditioun 2, V2: V10 - Gamme fir de Verglach vum Zoustand 2.
Vergiesst net d'Kombinatioun um Enn vun der Aarbecht mat der Formel ze drécken CTRL+SHIFT+ENTER - Excel schléisst automatesch d'Formel mat Curly Klameren zou, wéi am Beispill gewisen:
Wann Dir keng Arrayformel fir Är Aarbecht benotze wëllt, da füügt en aneren INDEX () un d'Formel a dréckt ENTER, et gesäit aus wéi am Beispill:
Wéi dës Formulen Aarbecht
Dës Formel funktionnéiert op déiselwecht Manéier wéi d'Standard INDEX () MATCH () Formel. Fir no verschidde Konditiounen ze sichen, erstellt Dir einfach verschidde falsch a richteg Konditiounen, déi déi richteg a falsch individuell Konditioune representéieren. An dann gëllen dës Konditioune fir all entspriechend Elementer vun der Array. D'Formel konvertéiert déi falsch a richteg Argumenter op 0 respektiv 1, a gëtt eng Array eraus wou 1 déi passend Wäerter ass, déi an der String fonnt goufen. MATCH () fënnt den éischte Wäert deen entsprécht 1 a passt et un d'INDEX () Formel. An et, am Tour, wäert de scho gewënschten Wäert an der spezifizéierter Linn vun der gewënschter Kolonn zréckginn.
Eng Formel ouni Array hänkt vun der Fäegkeet vum INDEX () fir se eleng ze handhaben. Den zweeten INDEX () an der Formel entsprécht falsch (0), sou datt et de ganze Array mat dëse Wäerter un d'MATCH () Formel passéiert.
Dëst ass eng zimlech laang Erklärung vun der Logik hannert dëser Formel. Fir méi Informatioun liest den Artikel "INDEX MATCH mat multiple Konditiounen".
AVERAGE, MAX a MIN am INDEX a MATCH
Excel huet seng eege speziell Funktiounen fir Duerchschnëtt, Maximum a Minimum ze fannen. Awer wat wann Dir Daten aus der Zell wëllt kréien, déi mat dëse Wäerter assoziéiert ass? An dësem Fall AVERAGE, MAX a MIN mussen a Verbindung mat INDEX a MATCH benotzt ginn.
INDEX MATCH an MAX
Fir de gréisste Wäert an der Kolonn D ze fannen an an der Kolonn C ze weisen, benotzt d'Formel:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH a MIN
Fir de klengste Wäert an der Kolonn D ze fannen an an der Kolonn C ze weisen, benotzt déi folgend Formel:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
SEARCH INDEX an SERPENT
Fir den Duerchschnëttswäert an der Kolonn D ze fannen an dëse Wäert an C ze weisen:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Ofhängeg wéi Är Donnéeën geschriwwe sinn, ass dat drëtt Argument fir MATCH () entweder 1, 0 oder -1:
- wann d'Säulen an opsteigend Uerdnung zortéiert sinn, setze 1 (da berechent d'Formel de maximale Wäert, dee manner wéi oder gläich ass mam Duerchschnëttswäert);
- wann d'Zort erofgeet, dann -1 (d'Formel gëtt de Mindestwäert erausginn, dee méi grouss ass wéi oder gläich mam Duerchschnëtt);
- Wann de Lookup-Array e Wäert enthält dee genee dem Duerchschnëtt gläich ass, da setzt se op 0.
An eisem Beispill gëtt d'Bevëlkerung an ofstigend Uerdnung zortéiert, sou datt mir -1 setzen. An d'Resultat ass Tokyo, well de Bevëlkerungswäert (13,189) am nootste beim Duerchschnëttswäert (000) ass.
VLOOKUP() kann och esou Berechnunge maachen, awer nëmmen als Arrayformel: VLOOKUP mat AVERAGE, MIN a MAX.
INDEX MATCH an ESND / IFERROR
Dir hutt wahrscheinlech scho gemierkt datt wann d'Formel de gewënschten Wäert net fanne kann, e Feeler werft # N / A. Dir kënnt d'Standardfehlermeldung duerch eppes méi informativ ersetzen. Zum Beispill, setzen d'Argument an der Formel Am XNUMX:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Mat dëser Formel, wann Dir Daten gitt, déi net an der Tabell sinn, gëtt de Formulaire Iech de spezifizéierte Message.
Wann Dir wëllt all Feeler ze fänken, dann ausser fir Am XNUMX kënne benotzt ginn IFERROR:
=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Eppes ass falsch gaang!")
Awer denkt drun datt d'Maskefehler op dës Manéier keng gutt Iddi ass, well Standardfehler Verstouss an der Formel mellen.
Mir hoffen Dir hutt eise Guide fir d'Benotzung vun der INDEX MATCH () Funktioun hëllefräich fonnt.