INDEX-MATCH se stal populárnějším nástrojem pro Excel, protože řeší omezení funkce SVYHLEDAT a jeho použití je jednodušší. Funkce INDEX-MATCH v Excelu má oproti funkci VLOOKUP řadu výhod:

1. INDEX a MATCH jsou flexibilnější a rychlejší než Vlookup
2. Je možné provádět horizontální vyhledávání, vertikální vyhledávání, 2-cestné vyhledávání, vyhledávání vlevo, vyhledávání rozlišující malá a velká písmena a dokonce vyhledávání na základě více kritérií.
3. V seřazených datech je INDEX-MATCH o 30 % rychlejší než VLOOKUP. To znamená, že ve větším souboru dat dává větší smysl 30% rychlejší.

Začněme podrobnými koncepty každého INDEXU a MATCH.

Funkce INDEX

Funkce INDEX v Excelu je velmi výkonný a zároveň flexibilní nástroj, který načte hodnotu na daném místě v rozsahu. Jinými slovy, vrací obsah buňky, určený posunem řádků a sloupců.

Syntax:

=INDEX(reference, [row], [column])>

Parametry:

reference: Pole buněk, do kterých má být posunuto. Může to být jeden rozsah nebo celá datová sada v tabulce dat. řádek [nepovinné]: Počet odsazených řádků. To znamená, že pokud zvolíme referenční rozsah tabulky jako A1:A5, pak je buňka/obsah, který chceme extrahovat, v jak velké vertikální vzdálenosti. Zde pro řádek A1 bude 1, pro řádek A2 = 2 a tak dále. Pokud dáme řádek = 4, vytáhne A4. Protože řádek je volitelný, pokud neuvedeme žádné číslo řádku, extrahuje celé řádky v referenčním rozsahu. To je v tomto případě A1 až A5. sloupec [volitelné]: Počet odsazených sloupců. To znamená, že pokud zvolíme referenční rozsah tabulky jako A1:B5, pak je buňka/obsah, který chceme extrahovat, v jaké horizontální vzdálenosti. Zde pro A1 řádek bude 1 a sloupec bude 1, pro B1 řádek bude 1, ale sloupec bude 2 podobně pro A2 řádek = 2 sloupec = 1, pro B2 řádek = 2 sloupec = 2 a tak dále. Pokud dáme řádek = 5 a sloupec 2, vyjme B5. Protože sloupec je volitelný, takže pokud neuvedeme žádný řádek č. pak vyjme celý sloupec v referenčním rozsahu. Pokud například dáme řádek = 2 a sloupec jako prázdný, pak se to extrahuje (A2:B2). Pokud neurčíme řádek i sloupec, extrahuje se celá referenční tabulka, která je (A1:B5).

Referenční tabulka: Následující tabulka bude použita jako referenční tabulka pro všechny příklady funkce INDEX. První buňka je na B3 (FOOD) a poslední diagonální buňka je na F10 (180).

Příklady: Níže jsou uvedeny některé příklady funkcí indexu.

Případ 1: Nejsou uvedeny žádné řádky a sloupce.

Vstupní příkaz: =INDEX(B3:C10)

Případ 2: Jsou uvedeny pouze řádky.

Vstupní příkaz: =INDEX(B3:C10;2)

Případ 3: Jsou zmíněny řádky i sloupce.

Vstupní příkaz: =INDEX(B3:D10;4;2)

Případ 4: Jsou zmíněny pouze sloupce.

Vstupní příkaz: =INDEX(B3 : D10 , , 2)

Problém s funkcí INDEX: Problém funkce INDEX je v tom, že je potřeba specifikovat řádky a sloupce pro data, která hledáme. Předpokládejme, že máme co do činění s datovou sadou strojového učení o 10 000 řádcích a sloupcích, pak bude velmi obtížné vyhledávat a extrahovat data, která hledáme. Zde přichází koncept funkce Match, která identifikuje řádky a sloupce na základě nějaké podmínky.

Funkce MATCH

Načte pozici položky/hodnoty v rozsahu. Jedná se o méně propracovanou verzi SVYHLEDAT nebo VVYHLEDAT, která vrací pouze informace o poloze, nikoli skutečná data. MATCH nerozlišuje velká a malá písmena a nezáleží na tom, zda je rozsah horizontální nebo vertikální.

Syntax:

=MATCH(search_key, range, [search_type])>

Parametry:

search_key: Hodnota, kterou chcete vyhledat. Například 42, Cats nebo I24. rozsah: Jednorozměrné pole, které se má prohledávat. Může to být buď jeden řádek nebo jeden sloupec. např.->A1:A10 , A2:D2 atd. search_type [nepovinné]: Metoda vyhledávání. = 1 (výchozí) najde největší hodnotu menší nebo rovnou search_key, když je rozsah seřazen vzestupně.
• = 0 najde přesnou hodnotu, když je rozsah neseřazený.
• = -1 najde nejmenší hodnotu větší nebo rovnou vyhledávacímu_klíči, když je rozsah seřazen sestupně.

Číslo řádku nebo číslo sloupce lze najít pomocí funkce shody a lze je použít uvnitř funkce indexu, takže pokud existují nějaké podrobnosti o položce, pak lze všechny informace o položce extrahovat vyhledáním řádku/sloupce položky pomocí shody pak jej vnořit do funkce indexu.

Referenční tabulka: Následující tabulka bude použita jako referenční tabulka pro všechny příklady funkce MATCH. První buňka je na B3 (FOOD) a poslední diagonální buňka je na F10 (180)

Příklady: Níže jsou uvedeny některé příklady funkce MATCH –

Případ 1: Typ vyhledávání 0, To znamená Přesná shoda.

Vstupní příkaz: =MATCH(Jižní Indie,C3:C10;0)

Případ 2: Typ vyhledávání 1 (výchozí).

Vstupní příkaz: =MATCH(Jižní Indie,C3:C10)

10 ml v oz

Případ 3: Typ vyhledávání -1.

Vstupní příkaz: =MATCH(Jižní Indie,C3:C10,-1)

INDEX-MATCH Společně

V předchozích příkladech byly statické hodnoty řádků a sloupců poskytnuty ve funkci INDEX Předpokládejme, že neexistují žádné předchozí znalosti o pozicích řádků a sloupců, pak lze pozici řádků a sloupců poskytnout pomocí funkce MATCH. Toto je dynamický způsob vyhledávání a získávání hodnoty.

Syntax:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Referenční tabulka: Použije se následující referenční tabulka. První buňka je na B3 (FOOD) a poslední diagonální buňka je na F10 (180)

Příklad: Řekněme, že úkolem je zjistit cenu Masala Dosa. Je známo, že sloupec 3 představuje náklady na položky, ale pozice řádku Masala Dosa není známa. Problém lze rozdělit do dvou kroků –

Krok 1: Najděte pozici Masala Dosa pomocí vzorce:

 =MATCH('Masala Dosa',B3:B10,0)>

Zde B3:B10 představuje sloupec jídlo a 0 znamená přesnou shodu. Vrátí číslo řádku Masala Dosa.

Krok 2: Zjistěte cenu Masala Dosa. Pomocí funkce INDEX zjistíte cenu Masala Dosa. Nahrazením výše uvedeného dotazu funkce MATCH uvnitř funkce INDEX v místě, kde je vyžadována přesná poloha Masala Dosa a číslo sloupce nákladů je 3, což je již známo.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Dva způsoby vyhledávání s INDEX-MATCH společně

V předchozím příkladu byla pozice sloupce atributu Cena pevně zakódována. Takže to nebylo úplně dynamické.

Případ 1: Předpokládejme, že neexistují žádné znalosti o čísle sloupce nákladů, pak jej lze získat pomocí vzorce:

 =MATCH('Cost',B3:F3,0)>

Zde B3:F3 představuje sloupec záhlaví.

Případ 2: Pokud je řádek i hodnota sloupce poskytnuta pomocí funkce MATCH (bez zadání statické hodnoty), nazývá se to dvousměrné vyhledávání. Toho lze dosáhnout pomocí vzorce:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Vyhledávání vlevo

Jednou z klíčových výhod INDEX a MATCH oproti funkci VLOOKUP je schopnost provádět vyhledávání vlevo. To znamená, že je možné extrahovat pozici řádku položky pomocí libovolného atributu vpravo a hodnotu jiného atributu vlevo lze extrahovat.

Řekněme například, že kupte jídlo, jehož cena by měla být 140 Rs. Nepřímo říkáme koupit Biryani. V tomto příkladu je známa cena Rs 140/-, je potřeba extrahovat jídlo. Protože sloupec Cena je umístěn napravo od sloupce Jídlo. Pokud je použita funkce VLOOKUP, nebude možné prohledávat levou stranu sloupce Cena. To je důvod, proč pomocí funkce VLOOKUP není možné získat název jídla.

K překonání této nevýhody lze použít funkci INDEX-MATCH Levé vyhledávání.
Krok 1: První extrahujte pozici řádku nákladů 140 Rs pomocí vzorce:

 =MATCH(140, D3:D10,0)>

Zde D3: D10 představuje sloupec Náklady, kde se provádí hledání čísla řádku Cena 140 Rs.

Krok 2: Po získání čísla řádku je dalším krokem použití funkce INDEX k extrahování názvu jídla pomocí vzorce:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Zde B3:B10 představuje sloupec potravin a 140 jsou náklady na potravinovou položku.

Vyhledávání rozlišující malá a velká písmena

Funkce MATCH sama o sobě nerozlišuje velká a malá písmena. To znamená, že pokud existuje název jídla DHOKLA a funkce MATCH je použita s následujícím vyhledávacím slovem:

1. Dhokla
2. dhokla
3. DhOkLA

Vše vrátí pozici řádku DHOKLA. Funkci EXACT však lze použít s INDEX a MATCH k provedení vyhledávání, které respektuje velká a malá písmena.

Přesná funkce: Funkce Excel EXACT porovná dva textové řetězce, přičemž bere v úvahu velká a malá písmena, a vrátí hodnotu TRUE, pokud jsou stejné, a FALSE, pokud ne. EXACT rozlišuje velká a malá písmena.

Příklady:

EXACT(DHOKLA,DHOKLA): Toto vrátí True. PŘESNĚ (DHOKLA,Dhokla): Toto vrátí False. PŘESNĚ (DHOKLA,dhokla): Toto vrátí False. PŘESNĚ (DHOKLA,DhOkLA): Toto vrátí False.

Příklad: Řekněme, že úkolem je hledat typ jídla Dhokla, ale rozlišujte malá a velká písmena. To lze provést pomocí vzorce -

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Zde funkce EXACT vrátí True, pokud se hodnota ve sloupci B3:B10 shoduje s Dhokla se stejným případem, jinak vrátí False. Nyní se funkce MATCH použije ve sloupci B3:B10 a vyhledá řádek s přesnou hodnotou TRUE. Poté funkce INDEX načte hodnotu sloupce C3:C10 (sloupec typu potraviny) na řádku vráceném funkcí MATCH.

Vyhledávání podle více kritérií

Jedním z nejzáludnějších problémů v Excelu je vyhledávání na základě více kritérií. Jinými slovy, vyhledávání, které se shoduje ve více než jednom sloupci současně. V níže uvedeném příkladu jsou funkce INDEX a MATCH a booleovská logika použity ke shodě na 3 sloupcích-

1. Jídlo.
2. Náklady.
3. Množství.

Chcete-li získat celkové náklady.

Příklad: Řekněme, že úkolem je vypočítat celkové náklady na těstoviny kde

Jídlo: Těstoviny. Cena: 60. Množství: 1.

V tomto příkladu tedy existují tři kritéria pro provedení shody. Níže jsou uvedeny kroky pro vyhledávání na základě více kritérií –

Krok 1: Nejprve porovnejte Food Column (B3:B10) s těstovinami pomocí vzorce:

 'PASTA' = B3:B10>

Tím se převedou hodnoty B3:B10 (sloupec potravin) jako booleovské. To je pravda, kde jídlo je těstoviny jinde nepravda.

Krok 2: Poté přiřaďte kritéria nákladů následujícím způsobem:

 60 = D3:D10>

To nahradí hodnoty D3:D10 (Cost Column) jako logické hodnoty. To je pravda, kde cena=60, jinak nepravda.

Krok 3: Dalším krokem je splnění třetího kritéria, kterým je Množství = 1, následujícím způsobem:

 1 = E3:E10>

Tím se nahradí sloupec E3:E10 (sloupec množství) jako True, kde množství = 1, jinak bude False.

Krok 4: Vynásobte výsledek prvního, druhého a třetího kritéria. Toto bude průsečík všech podmínek a převede Boolean True / False jako 1/0.

Krok 5: Nyní bude výsledkem sloupec s 0 a 1. Zde použijte funkci MATCH k nalezení počtu řádků sloupců, které obsahují 1. Protože pokud má sloupec hodnotu 1, znamená to, že splňuje všechna tři kritéria.

Krok 6: Po získání čísla řádku použijte funkci INDEX, abyste získali celkové náklady na tento řádek.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Zde F3:F10 představuje sloupec celkových nákladů.