Prečo sú INDEX a MATCH lepšie ako VLOOKUP v Exceli

Začiatočníkom sme už predtým vysvetlili, ako používať základné funkcie VLOOKUP (anglicky VLOOKUP, skratka znamená „funkcia vertikálneho vyhľadávania“). A skúseným používateľom sa ukázalo niekoľko komplikovanejších vzorcov.

A v tomto článku sa pokúsime poskytnúť informácie o inej metóde práce s vertikálnym vyhľadávaním.

Možno sa pýtate: "Prečo je to potrebné?". A to je potrebné na zobrazenie všetkých možných metód vyhľadávania. Okrem toho početné obmedzenia funkcie VLOOKUP často bránia dosiahnutiu požadovaného výsledku. V tomto ohľade je INDEX() MATCH() oveľa funkčnejší a rozmanitejší a má tiež menej obmedzení.

Základy INDEX MATCH

Keďže účelom tejto príručky je ukázať, aká dobrá je táto funkcia, my Pozrime sa na základné informácie týkajúce sa princípov jeho fungovania. A ukážeme príklady a tiež zvážime prečo, je to lepšie ako VLOOKUP ().

Syntax a použitie funkcie INDEX

Táto funkcia pomáha nájsť požadovanú hodnotu medzi špecifikovanými oblasťami vyhľadávania na základe čísla stĺpca alebo riadku. syntaxe:

=INDEX(pole, číslo riadka, číslo stĺpca):

• pole – oblasť, v ktorej bude vyhľadávanie prebiehať;
• číslo riadku – číslo riadku, ktorý sa má vyhľadať v zadanom poli. Ak číslo riadka nie je známe, musí sa uviesť číslo stĺpca;
• číslo stĺpca – číslo stĺpca, ktorý sa má nájsť v zadanom poli. Ak je hodnota neznáma, vyžaduje sa číslo riadku.

Príklad jednoduchého vzorca:

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

Funkcia bude vyhľadávať v rozsahu od A1 do C10. Čísla ukazujú, z ktorého riadku (2) a stĺpca (3) sa má zobraziť požadovaná hodnota. Výsledkom bude bunka C2.

Celkom jednoduché, však? Ale keď pracujete so skutočnými dokumentmi, je nepravdepodobné, že budete mať informácie týkajúce sa čísel stĺpcov alebo buniek. Na to slúži funkcia MATCH().

Syntax a použitie funkcie MATCH

Funkcia MATCH() vyhľadá požadovanú hodnotu a zobrazí jej približné číslo v určenej oblasti vyhľadávania.

Syntax searchpos() vyzerá takto:

=MATCH(hodnota na vyhľadanie, pole na vyhľadanie, typ zhody)

• hľadaná hodnota – číslo alebo text, ktorý sa má nájsť;
• vyhľadávané pole – oblasť, v ktorej bude vyhľadávanie prebiehať;
• typ zhody – určuje, či sa má hľadať presná hodnota alebo hodnoty, ktoré sú jej najbližšie:
  • 1 (alebo nie je zadaná žiadna hodnota) – vráti najväčšiu hodnotu, ktorá sa rovná alebo je menšia ako zadaná hodnota;
  • 0 – zobrazuje presnú zhodu s hľadanou hodnotou. V kombinácii INDEX() MATCH() budete takmer vždy potrebovať presnú zhodu, preto píšeme 0;
  • -1 – Zobrazuje najmenšiu hodnotu, ktorá je väčšia alebo rovná hodnote zadanej vo vzorci. Triedenie sa vykonáva v zostupnom poradí.

Napríklad v rozsahu B1:B3 sú registrované New York, Paríž, Londýn. Vzorec nižšie zobrazí číslo 3, pretože Londýn je tretí v zozname:

=EXPOSE(Londýn,B1:B3,0;XNUMX)

Ako pracovať s funkciou INDEX MATCH 

Pravdepodobne ste už začali chápať princíp, na ktorom je postavená spoločná práca týchto funkcií. Skrátka teda INDEX() hľadá požadovanú hodnotu medzi určenými riadkami a stĺpcami. A MATCH() zobrazuje čísla týchto hodnôt:

=INDEX(stĺpec, z ktorého sa vracia hodnota, MATCH(hodnota na vyhľadávanie, stĺpec na vyhľadávanie, 0))

Stále máte problém pochopiť, ako to funguje? Možno príklad lepšie vysvetlí. Predpokladajme, že máte zoznam svetových hlavných miest a ich obyvateľov:

Aby sme zistili veľkosť populácie určitého hlavného mesta, napríklad hlavného mesta Japonska, používame nasledujúci vzorec:

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

Vysvetlenie:

• Funkcia MATCH() hľadá hodnotu – „Japonsko“ v poli A2:A10 a vráti číslo 3, pretože Japonsko je tretia hodnota v zozname. 
• Tento údaj ide doporadové číslo” vo vzorci INDEX() a povie funkcii, aby vypísala hodnotu z tohto riadka.

Takže vyššie uvedený vzorec sa stáva štandardným vzorcom INDEX(C2:C10,3;XNUMX). Vzorec hľadá v bunkách C2 až C10 a vracia údaje z tretej bunky v tomto rozsahu, teda C4, pretože odpočítavanie začína od druhého riadku.

Nechcete predpísať názov mesta vo vzorci? Potom ho napíšte do ľubovoľnej bunky, povedzme F1, a použite ho ako referenciu vo vzorci MATCH(). A skončíte s dynamickým vyhľadávacím vzorcom:

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

Dôležité! Počet riadkov v rad INDEX() musí byť rovnaký ako počet riadkov v uvažované pole v MATCH(), inak dostanete nesprávny výsledok.

Počkajte chvíľu, prečo nepoužiť vzorec VLOOKUP()?

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

 Aký má zmysel strácať čas pokúšaním sa zistiť všetky tieto zložitosti INDEX MATCH?

V tomto prípade je úplne jedno, ktorú funkciu použijete. Toto je len príklad na pochopenie toho, ako funkcie INDEX() a MATCH() spolupracujú. Ďalšie príklady ukážu, čoho sú tieto funkcie schopné v situáciách, keď je funkcia VLOOKUP bezmocná. 

INDEX MATCH alebo VLOOKUP

Pri rozhodovaní o tom, ktorý vzorec vyhľadávania použiť, mnohí súhlasia s tým, že INDEX() a MATCH() sú oveľa lepšie ako VLOOKUP. Mnoho ľudí však stále používa funkciu VLOOKUP(). Po prvé, VLOOKUP() je jednoduchšie a po druhé, používatelia úplne nechápu všetky výhody práce s INDEX() a MATCH(). Bez týchto znalostí nikto nebude súhlasiť s tým, že bude tráviť čas štúdiom zložitého systému.

Tu sú kľúčové výhody INDEX() a MATCH() oproti VLOOKUP():

• Hľadajte sprava doľava. VLOOKUP() nemôže vyhľadávať sprava doľava, takže hodnoty, ktoré hľadáte, musia byť vždy v stĺpcoch tabuľky úplne vľavo. Ale INDEX() a MATCH() to zvládnu bez problémov. Tento článok vám povie, ako to vyzerá v praxi: ako nájsť požadovanú hodnotu na ľavej strane.

1. Bezpečné pridávanie alebo odoberanie stĺpcov. Vzorec VLOOKUP() zobrazuje nesprávne výsledky pri odstraňovaní alebo pridávaní stĺpcov, pretože funkcia VLOOKUP() potrebuje presné číslo stĺpca, aby bola úspešná. Pri pridávaní alebo odstraňovaní stĺpcov sa samozrejme zmenia aj ich čísla. 

A vo vzorcoch INDEX() a MATCH() je špecifikovaný rozsah stĺpcov, nie jednotlivé stĺpce. Vďaka tomu môžete bezpečne pridávať a odstraňovať stĺpce bez toho, aby ste museli zakaždým aktualizovať vzorec.

1. Žiadne obmedzenia objemu vyhľadávania. Pri použití funkcie VLOOKUP() nesmie celkový počet kritérií vyhľadávania presiahnuť 255 znakov, inak dostanete #HODNOTA! Ak teda vaše údaje obsahujú veľký počet znakov, najlepšou možnosťou sú INDEX() a MATCH().
2. Vysoká rýchlosť spracovania. Ak sú vaše stoly relatívne malé, je nepravdepodobné, že si všimnete nejaký rozdiel. Ak však tabuľka obsahuje stovky alebo tisíce riadkov, a teda existujú stovky a tisíce vzorcov, INDEX () a MATCH () sa vyrovnajú oveľa rýchlejšie ako VLOOKUP (). Faktom je, že Excel spracuje iba stĺpce uvedené vo vzorci namiesto spracovania celej tabuľky. 

Vplyv funkcie VLOOKUP() na výkon bude obzvlášť viditeľný, ak váš pracovný hárok obsahuje veľké množstvo vzorcov ako VLOOKUP() a SUM(). Na analýzu každej hodnoty v poli sú potrebné samostatné kontroly funkcií VLOOKUP(). Excel teda musí spracovať obrovské množstvo informácií a to výrazne spomaľuje prácu.

Príklady vzorcov 

Už sme prišli na užitočnosť týchto funkcií, takže môžeme prejsť k najzaujímavejšej časti: k aplikácii poznatkov v praxi.

Vzorec na vyhľadávanie sprava doľava

Ako už bolo spomenuté, VLOOKUP nemôže vykonávať túto formu vyhľadávania. Ak teda požadované hodnoty nie sú v stĺpci úplne vľavo, funkcia VLOOKUP() neprinesie výsledok. Funkcie INDEX() a MATCH() sú všestrannejšie a umiestnenie hodnôt nehrá veľkú úlohu, aby fungovali.

Napríklad na ľavú stranu našej tabuľky pridáme stĺpec poradia a pokúsime sa zistiť, akú hodnosť z hľadiska počtu obyvateľov zaujíma hlavné mesto Naša krajina.

Do bunky G1 zapíšeme hodnotu, ktorá sa má nájsť, a potom pomocou nasledujúceho vzorca hľadáme v rozsahu C1:C10 a vrátime zodpovedajúcu hodnotu z A2:A10:

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

Prompt. Ak plánujete použiť tento vzorec pre viacero buniek, uistite sa, že ste opravili rozsahy pomocou absolútneho adresovania (napr. $A$2: $A$10 a $C$2: 4C$10).

INDEX VIAC EXPOSED VIAC EXPOSED  na vyhľadávanie v stĺpcoch a riadkoch

Vo vyššie uvedených príkladoch sme tieto funkcie použili ako náhradu za VLOOKUP() na vrátenie hodnôt z preddefinovaného rozsahu riadkov. Čo ak však potrebujete vykonať maticové alebo obojstranné vyhľadávanie?

Znie to komplikovane, ale vzorec pre takéto výpočty je podobný štandardnému vzorcu INDEX() MATCH() s jediným rozdielom: vzorec MATCH() sa musí použiť dvakrát. Prvýkrát na získanie čísla riadku a druhýkrát na získanie čísla stĺpca:

=INDEX(pole, ZHODA(zvislá hľadaná hodnota, hľadaný stĺpec, 0), ZHODA(horizontálna hľadaná hodnota, hľadaný riadok, 0))

Pozrime sa na tabuľku nižšie a pokúsime sa vytvoriť vzorec INDEX() EXPRESS() EXPRESS() s cieľom zobraziť demografické údaje v konkrétnej krajine za vybraný rok.

Cieľová krajina je v bunke G1 (vertikálne vyhľadávanie) a cieľový rok je v bunke G2 (horizontálne vyhľadávanie). Vzorec bude vyzerať takto:

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

Ako tento vzorec funguje

Rovnako ako pri iných zložitých vzorcoch sú ľahšie pochopiteľné, ak ich rozdelíte na jednotlivé rovnice. A potom môžete pochopiť, čo každá jednotlivá funkcia robí:

• MATCH(G1;A2:A11,0;XNUMX) – hľadá hodnotu (G1) v rozsahu A2:A11 a zobrazuje číslo tejto hodnoty, v našom prípade je to 2;
• HĽADAŤ(G2;B1:D1,0;XNUMX) – hľadá hodnotu (G2) v rozsahu B1:D1. V tomto prípade bol výsledok 3.

Nájdené čísla riadkov a stĺpcov sa odošlú na zodpovedajúcu hodnotu vo vzorci INDEX():

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

Výsledkom je, že máme hodnotu, ktorá je v bunke na priesečníku 2 riadkov a 3 stĺpcov v rozsahu B2:D11. A vzorec ukazuje požadovanú hodnotu, ktorá je v bunke D3.

Vyhľadávajte podľa viacerých podmienok pomocou INDEX a MATCH

Ak ste si prečítali nášho sprievodcu funkciou VLOOKUP(), pravdepodobne ste vyskúšali viacero vzorcov vyhľadávania. Tento spôsob vyhľadávania má však jedno významné obmedzenie – potrebu pridať pomocný stĺpec.

Ale dobrá správa je, že Pomocou INDEX() a MATCH() môžete vyhľadávať viaceré podmienky bez toho, aby ste museli upravovať alebo meniť svoj pracovný hárok.

Tu je všeobecný vzorec vyhľadávania viacerých podmienok pre INDEX() MATCH():

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

Poznámka: tento vzorec je potrebné použiť spolu s klávesovou skratkou CTRL+SHIFT+ENTER.

Predpokladajme, že potrebujete nájsť hodnotu, ktorú hľadáte, na základe 2 podmienok: Kupujúci и Produktu.

Vyžaduje si to nasledujúci vzorec:

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

V tomto vzorci je C2:C10 rozsah, v ktorom bude vyhľadávanie prebiehať, F1 - táto podmienka, A2:A10 — je rozsah na porovnanie stavu, F2 - podmienka 2, V2:V10 – rozsah na porovnanie stavu 2.

Nezabudnite stlačiť kombináciu na konci práce so vzorcom CTRL+SHIFT+ENTER – Excel automaticky uzavrie vzorec so zloženými zátvorkami, ako je znázornené v príklade:

Ak pre svoju prácu nechcete použiť vzorec poľa, pridajte do vzorca ďalší INDEX() a stlačte ENTER, bude to vyzerať ako v príklade:

Ako fungujú tieto vzorce

Tento vzorec funguje rovnako ako štandardný vzorec INDEX() MATCH(). Ak chcete vyhľadať viacero podmienok, jednoducho vytvoríte viacero podmienok False a True, ktoré predstavujú správne a nesprávne jednotlivé podmienky. A potom tieto podmienky platia pre všetky zodpovedajúce prvky poľa. Vzorec skonvertuje argumenty False a True na 0 a 1 a vytvorí pole, kde 1 sú zhodné hodnoty, ktoré sa našli v reťazci. MATCH() nájde prvú hodnotu, ktorá sa zhoduje s 1 a odovzdá ju do vzorca INDEX(). A to zase vráti už požadovanú hodnotu v zadanom riadku z požadovaného stĺpca.

Vzorec bez poľa závisí od schopnosti funkcie INDEX() spracovať ich samostatne. Druhý INDEX() vo vzorci sa zhoduje s falsy (0), takže celé pole s týmito hodnotami odovzdá do vzorca MATCH(). 

Toto je pomerne zdĺhavé vysvetlenie logiky tohto vzorca. Pre viac informácií si prečítajte článok „INDEX MATCH s viacerými podmienkami".

AVERAGE, MAX a MIN v INDEX a MATCH

Excel má svoje špeciálne funkcie na nájdenie priemerov, maxím a minim. Čo ak však chcete získať údaje z bunky spojenej s týmito hodnotami? V tomto prípade AVERAGE, MAX a MIN musia byť použité v spojení s INDEX a MATCH.

INDEX MATCH a MAX

Ak chcete nájsť najväčšiu hodnotu v stĺpci D a zobraziť ju v stĺpci C, použite vzorec: 

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

INDEX MATCH a MIN

Ak chcete nájsť najmenšiu hodnotu v stĺpci D a zobraziť ju v stĺpci C, použite nasledujúci vzorec:

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

HĽADAŤ INDEX a HAD

Ak chcete nájsť priemernú hodnotu v stĺpci D a zobraziť túto hodnotu v C:

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

V závislosti od toho, ako sú vaše údaje zapísané, je tretí argument funkcie MATCH() buď 1, 0 alebo -1:

• ak sú stĺpce zoradené vzostupne, nastavte 1 (vtedy vzorec vypočíta maximálnu hodnotu, ktorá je menšia alebo rovná priemernej hodnote);
• ak je zoradenie zostupné, potom -1 (vzorec zobrazí minimálnu hodnotu, ktorá je väčšia alebo rovná priemeru);
• ak vyhľadávacie pole obsahuje hodnotu, ktorá sa presne rovná priemeru, nastavte ju na 0. 

 V našom príklade je populácia zoradená v zostupnom poradí, takže dáme -1. A výsledkom je Tokio, keďže hodnota populácie (13,189 000) je najbližšie k priemernej hodnote (XNUMX).

VLOOKUP() môže tiež vykonávať takéto výpočty, ale iba ako vzorec poľa: VLOOKUP s AVERAGE, MIN a MAX.

INDEX MATCH a ESND/IFERROR

Pravdepodobne ste si už všimli, že ak vzorec nemôže nájsť požadovanú hodnotu, vyvolá chybu # N / A. Štandardné chybové hlásenie môžete nahradiť niečím informatívnejším. Napríklad nastavte argument vo vzorci V XNUMX:

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

Ak pri tomto vzorci zadáte údaje, ktoré nie sú v tabuľke, formulár vám poskytne zadanú správu.

Ak chcete zachytiť všetky chyby, tak okrem V XNUMX môže byť použité IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Vyskytol sa problém!”)

Pamätajte však, že maskovanie chýb týmto spôsobom nie je dobrý nápad, pretože štandardné chyby hlásia porušenia vo vzorci.

Dúfame, že vám náš návod na používanie funkcie INDEX MATCH() pomohol.