Zakaj sta INDEX in MATCH boljša od VLOOKUP v Excelu

Začetnikom smo že prej razložili, kako uporabljati osnovne funkcije VLOOKUP-a (angleško VLOOKUP, okrajšava pomeni “vertical lookup function”). In izkušenim uporabnikom je bilo prikazanih več zapletenih formul.

In v tem članku bomo poskušali dati informacije o drugem načinu dela z vertikalnim iskanjem.

Morda se sprašujete: "Zakaj je to potrebno?". In to je potrebno za prikaz vseh možnih načinov iskanja. Poleg tega številne omejitve VLOOKUP pogosto preprečujejo doseganje želenega rezultata. V tem pogledu so INDEX( ) MATCH( ) veliko bolj funkcionalni in raznoliki, imajo pa tudi manj omejitev.

Osnove INDEX MATCH

Ker je namen tega vodnika pokazati, kako dobra je ta funkcija, smo Oglejmo si osnovne informacije o načelih njegovega delovanja. Pokazali bomo primere in tudi razmislili, zakaj je boljši od VLOOKUP ().

Sintaksa in uporaba funkcije INDEX

Ta funkcija pomaga pri iskanju želene vrednosti med podanimi iskalnimi območji na podlagi številke stolpca ali vrstice. Skladnja:

=INDEX(matrika, številka vrstice, številka stolpca):

• niz – območje, v katerem bo potekalo iskanje;
• številka vrstice – številka vrstice, ki jo je treba iskati v podanem nizu. Če številka vrstice ni znana, je treba navesti številko stolpca;
• številka stolpca – številka stolpca, ki ga je treba najti v podani matriki. Če vrednost ni znana, je zahtevana številka vrstice.

Primer preproste formule:

=KAZALO(A1:S10,2,3;XNUMX;XNUMX)

Funkcija bo iskala v območju od A1 do C10. Številke kažejo, iz katere vrstice (2) in stolpca (3) naj se prikaže želena vrednost. Rezultat bo celica C2.

Precej preprosto, kajne? Toda ko delate s pravimi dokumenti, je malo verjetno, da boste imeli informacije o številkah stolpcev ali celicah. Temu je namenjena funkcija MATCH().

Sintaksa in uporaba funkcije MATCH

Funkcija MATCH() poišče želeno vrednost in prikaže njeno približno število v podanem območju iskanja.

Sintaksa searchpos() izgleda takole:

=MATCH(vrednost za iskanje, polje za iskanje, vrsta ujemanja)

• iskalna vrednost – številka ali besedilo, ki ga je treba najti;
• iskani niz – območje, kjer bo potekalo iskanje;
• vrsta ujemanja – določa, ali naj se išče točno vrednost ali vrednosti, ki so ji najbližje:
  • 1 (ali vrednost ni podana) – vrne največjo vrednost, ki je enaka ali manjša od podane vrednosti;
  • 0 – prikazuje natančno ujemanje z iskano vrednostjo. V kombinaciji INDEX() MATCH() boste skoraj vedno potrebovali natančno ujemanje, zato pišemo 0;
  • -1 – Prikazuje najmanjšo vrednost, ki je večja ali enaka vrednosti, navedeni v formuli. Razvrščanje poteka v padajočem vrstnem redu.

Na primer, v območju B1:B3 so registrirani New York, Pariz, London. Spodnja formula bo pokazala številko 3, ker je London tretji na seznamu:

=EXPOSE(London,B1:B3,0)

Kako delati s funkcijo INDEX MATCH 

Verjetno ste že začeli razumeti načelo, po katerem je zgrajeno skupno delo teh funkcij. Skratka torej INDEX() išče želeno vrednost med podanimi vrsticami in stolpci. In MATCH() prikazuje številke teh vrednosti:

=INDEX(stolpec, iz katerega je vrnjena vrednost, MATCH(vrednost za iskanje, stolpec za iskanje, 0))

Še vedno težko razumete, kako deluje? Morda bo primer bolje pojasnil. Recimo, da imate seznam svetovnih prestolnic in njihovega prebivalstva:

Da bi ugotovili število prebivalcev določene prestolnice, na primer glavnega mesta Japonske, uporabimo naslednjo formulo:

=INDEX(C2:C10, MATCH(Japonska, A2:A10,0;XNUMX))

Razlaga:

• Funkcija MATCH() išče vrednost – »Japonska« v matriki A2:A10 in vrne številko 3, ker je Japonska tretja vrednost na seznamu. 
• Ta številka gre zaštevilka vrstice” v formuli INDEX() in pove funkciji, naj natisne vrednost iz te vrstice.

Tako zgornja formula postane standardna formula INDEKS(C2:C10,3;XNUMX). Formula išče od celic C2 do C10 in vrne podatke iz tretje celice v tem obsegu, to je C4, ker se odštevanje začne od druge vrstice.

Ne želite v formuli predpisati imena mesta? Nato ga zapišite v poljubno celico, recimo F1, in ga uporabite kot referenco v formuli MATCH(). In na koncu dobite formulo za dinamično iskanje:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Pomembno! Število vrstic v matrika INDEX() mora biti enako številu vrstic v obravnavani niz v MATCH(), sicer boste dobili napačen rezultat.

Počakajte malo, zakaj ne bi preprosto uporabili formule VLOOKUP()?

=VLOOKUP(F1, A2:C10, 3, False)

 Kakšen smisel ima zapravljati čas, ko poskušamo ugotoviti vse te zapletenosti INDEX MATCH?

V tem primeru ni pomembno, katero funkcijo uporabiti. To je le primer za razumevanje delovanja funkcij INDEX() in MATCH(). Drugi primeri bodo pokazali, kaj so te funkcije zmožne v situacijah, ko je VLOOKUP brez moči. 

INDEX MATCH ali VLOOKUP

Pri odločanju, katero iskalno formulo uporabiti, se mnogi strinjajo, da sta INDEX() in MATCH() bistveno boljša od VLOOKUP. Vendar veliko ljudi še vedno uporablja VLOOKUP(). Prvič, VLOOKUP() je preprostejši, in drugič, uporabniki ne razumejo popolnoma vseh prednosti dela z INDEX() in MATCH(). Brez tega znanja se nihče ne bo strinjal, da bi porabil svoj čas za preučevanje zapletenega sistema.

Tukaj so ključne prednosti INDEX() in MATCH() pred VLOOKUP():

• Išči od desne proti levi. VLOOKUP() ne more iskati od desne proti levi, zato morajo biti vrednosti, ki jih iščete, vedno v skrajno levih stolpcih tabele. Toda INDEX() in MATCH() lahko to obravnavata brez težav. Ta članek vam bo povedal, kako izgleda v praksi: kako najti želeno vrednost na levi strani.

1. Varno dodajanje ali odstranjevanje stolpcev. Formula VLOOKUP() prikazuje nepravilne rezultate pri odstranjevanju ali dodajanju stolpcev, ker VLOOKUP() za uspešno delovanje potrebuje natančno številko stolpca. Seveda se ob dodajanju ali odstranjevanju stolpcev spreminja tudi njihovo število. 

In v formulah INDEX() in MATCH() je določen obseg stolpcev, ne posameznih stolpcev. Posledično lahko varno dodajate in odstranjujete stolpce, ne da bi morali vsakič posodobiti formulo.

1. Ni omejitev glede obsega iskanja. Pri uporabi funkcije VLOOKUP() skupno število iskalnih kriterijev ne sme preseči 255 znakov, sicer boste prejeli #VREDNOST! Torej, če vaši podatki vsebujejo veliko število znakov, sta najboljša možnost INDEX() in MATCH().
2. Visoka hitrost obdelave. Če so vaše mize relativno majhne, ​​je malo verjetno, da boste opazili kakršno koli razliko. Toda, če tabela vsebuje na stotine ali tisoče vrstic in zato obstaja na stotine in tisoče formul, se bosta INDEX () in MATCH () spopadla veliko hitreje kot VLOOKUP (). Dejstvo je, da bo Excel obdelal samo stolpce, določene v formuli, namesto da bi obdelal celotno tabelo. 

Vpliv funkcije VLOOKUP() na zmogljivost bo še posebej opazen, če vaš delovni list vsebuje veliko formul, kot sta VLOOKUP() in SUM(). Za razčlenitev vsake vrednosti v matriki so potrebna ločena preverjanja funkcij VLOOKUP(). Tako mora Excel obdelati ogromno informacij, kar močno upočasni delo.

Primeri formule 

Uporabnost teh funkcij smo že ugotovili, zato lahko nadaljujemo z najbolj zanimivim delom: uporabo znanja v praksi.

Formula za iskanje od desne proti levi

Kot že omenjeno, VLOOKUP ne more izvesti te oblike iskanja. Torej, če želene vrednosti niso v skrajnem levem stolpcu, VLOOKUP() ne bo dal rezultata. Funkciji INDEX() in MATCH() sta bolj vsestranski, lokacija vrednosti pa za njuno delovanje ne igra velike vloge.

Na primer, na levo stran naše tabele bomo dodali stolpec za rang in poskušali ugotoviti, kakšen rang glede na število prebivalcev zavzema glavno mesto naše države.

V celico G1 zapišemo vrednost, ki jo želimo najti, nato pa uporabimo naslednjo formulo za iskanje v obsegu C1:C10 in vrnemo ustrezno vrednost iz A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Poziv. Če nameravate to formulo uporabiti za več celic, se prepričajte, da ste popravili obsege z absolutnim naslavljanjem (npr. $A$2: $A$10 in $C$2: 4C$10).

KAZALO BOLJ IZPOSTAVLJENO BOLJ IZPOSTAVLJENO  za iskanje v stolpcih in vrsticah

V zgornjih primerih smo te funkcije uporabili kot zamenjavo za VLOOKUP() za vrnitev vrednosti iz vnaprej določenega obsega vrstic. Kaj pa, če morate opraviti matrično ali dvostransko iskanje?

Sliši se zapleteno, vendar je formula za takšne izračune podobna standardni formuli INDEX() MATCH(), le z eno razliko: formulo MATCH() je treba uporabiti dvakrat. Prvič za pridobitev številke vrstice in drugič za pridobitev številke stolpca:

=INDEX(niz, MATCH(navpična iskalna vrednost, iskalni stolpec, 0), MATCH(vodoravna iskalna vrednost, iskalna vrstica, 0))

Poglejmo spodnjo tabelo in poskusimo sestaviti formulo INDEX() EXPRESS() EXPRESS() za prikaz demografskih podatkov v določeni državi za izbrano leto.

Ciljna država je v celici G1 (navpično iskanje), ciljno leto pa v celici G2 (vodoravno iskanje). Formula bo videti takole:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Kako deluje ta formula

Tako kot vse druge zapletene formule jih je lažje razumeti, če jih razdelimo na posamezne enačbe. In potem lahko razumete, kaj počne vsaka posamezna funkcija:

• UJEMANJE(G1,A2:A11,0) – poišče vrednost (G1) v območju A2:A11 in prikaže številko te vrednosti, v našem primeru je to 2;
• ISKANJE(G2;B1:D1,0;XNUMX) – poišče vrednost (G2) v območju B1:D1. V tem primeru je bil rezultat 3.

Najdene številke vrstic in stolpcev se pošljejo ustrezni vrednosti v formuli INDEX():

=INDEX(B2:D11,2,3;XNUMX;XNUMX)

Posledično imamo vrednost, ki je v celici na presečišču 2 vrstic in 3 stolpcev v obsegu B2:D11. In formula prikazuje želeno vrednost, ki je v celici D3.

Išči po več pogojih z INDEX in MATCH

Če ste prebrali naš vodnik za VLOOKUP(), ste verjetno preizkusili več iskalnih formul. Toda ta metoda iskanja ima eno pomembno omejitev - potrebo po dodajanju pomožnega stolpca.

Ampak dobra novica je ta Z INDEX() in MATCH() lahko iščete več pogojev, ne da bi morali urejati ali spreminjati svoj delovni list.

Tukaj je splošna formula za iskanje z več pogoji za INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Opomba: to formulo je treba uporabiti skupaj z bližnjico na tipkovnici CTRL+SHIFT+ENTER.

Recimo, da morate najti vrednost, ki jo iščete, na podlagi dveh pogojev: Kupec и Izdelek.

To zahteva naslednjo formulo:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

V tej formuli je C2:C10 obseg, v katerem bo potekalo iskanje, F1 – ta pogoj, A2:A10 — je razpon za primerjavo stanja, F2 – pogoj 2, V2: V10 – obseg za primerjavo pogoja 2.

Ne pozabite pritisniti kombinacije na koncu dela s formulo CTRL+SHIFT+ENTER – Excel bo samodejno zaprl formulo z zavitimi oklepaji, kot je prikazano v primeru:

Če za svoje delo ne želite uporabljati matrične formule, formuli dodajte še en INDEX() in pritisnite ENTER, izgledalo bo kot v primeru:

Kako te formule delujejo

Ta formula deluje na enak način kot standardna formula INDEX() MATCH(). Če želite poiskati več pogojev, preprosto ustvarite več pogojev False in True, ki predstavljajo pravilne in nepravilne posamezne pogoje. In potem ti pogoji veljajo za vse ustrezne elemente matrike. Formula pretvori argumenta False in True v 0 oziroma 1 in izpiše matriko, kjer je 1 ujemajoče se vrednosti, ki so bile najdene v nizu. MATCH() bo našel prvo vrednost, ki se ujema z 1, in jo posredoval formuli INDEX(). In po drugi strani bo vrnil že želeno vrednost v določeni vrstici iz želenega stolpca.

Formula brez matrike je odvisna od zmožnosti INDEX(), da jih sam obravnava. Drugi INDEX() v formuli se ujema z false (0), tako da posreduje celotno matriko s temi vrednostmi formuli MATCH(). 

To je precej dolga razlaga logike za to formulo. Za več informacij preberite članek “INDEX MATCH z več pogoji".

AVERAGE, MAX in MIN v INDEX in MATCH

Excel ima svoje posebne funkcije za iskanje povprečij, maksimumov in minimumov. Kaj pa, če želite pridobiti podatke iz celice, povezane s temi vrednostmi? V tem primeru AVERAGE, MAX in MIN je treba uporabiti v povezavi z INDEX in MATCH.

INDEX MATCH in MAX

Če želite najti največjo vrednost v stolpcu D in jo prikazati v stolpcu C, uporabite formulo: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEKS UJEMANJE in MIN

Če želite najti najmanjšo vrednost v stolpcu D in jo prikazati v stolpcu C, uporabite naslednjo formulo:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

ISKALNI INDEX in SERPENT

Če želite najti povprečno vrednost v stolpcu D in prikazati to vrednost v C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Odvisno od tega, kako so vaši podatki zapisani, je tretji argument za MATCH() 1, 0 ali -1:

• če so stolpci razvrščeni v naraščajočem vrstnem redu, nastavite 1 (potem bo formula izračunala največjo vrednost, ki je manjša ali enaka povprečni vrednosti);
• če je razvrščanje padajoče, potem -1 (formula bo izpisala najmanjšo vrednost, ki je večja ali enaka povprečju);
• če iskalna matrika vsebuje vrednost, ki je popolnoma enaka povprečju, jo nastavite na 0. 

 V našem primeru je populacija razvrščena v padajočem vrstnem redu, zato smo dali -1. In rezultat je Tokio, saj je vrednost prebivalstva (13,189) najbližja povprečni vrednosti (000).

VLOOKUP() lahko izvaja tudi takšne izračune, vendar le kot matrično formulo: VLOOKUP s AVERAGE, MIN in MAX.

INDEX MATCH in ESND/IFERROR

Verjetno ste že opazili, da če formula ne najde želene vrednosti, vrže napako # N / A. Standardno sporočilo o napaki lahko zamenjate z nečim bolj informativnim. Na primer, nastavite argument v formuli V XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

S to formulo, če vnesete podatke, ki niso v tabeli, vam bo obrazec dal navedeno sporočilo.

Če želite ujeti vse napake, razen V XNUMX je lahko uporabljen NAPAKA:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Nekaj ​​je šlo narobe!")

Vendar ne pozabite, da maskiranje napak na ta način ni dobra ideja, ker standardne napake poročajo o kršitvah v formuli.

Upamo, da vam je bil naš vodnik za uporabo funkcije INDEX MATCH() v pomoč.