Jak používat funkce INDEX a MATCH v Excelu

2024 Autor: Abigail Brown | [email protected]. Naposledy změněno: 2023-12-17 06:42

Co je dobré vědět

Funkci INDEX lze použít samostatně, ale vnoření funkce MATCH do ní vytvoří pokročilé vyhledávání.
Tato vnořená funkce je flexibilnější než SVYHLEDAT a může poskytovat výsledky rychleji.

Tento článek vysvětluje, jak používat funkce INDEX a MATCH společně ve všech verzích Excelu, včetně Excel 2019 a Microsoft 365.

Co jsou funkce INDEX a MATCH?

INDEX a MATCH jsou vyhledávací funkce Excelu. I když se jedná o dvě zcela samostatné funkce, které lze používat samostatně, lze je také kombinovat a vytvářet pokročilé vzorce.

Funkce INDEX vrací hodnotu nebo odkaz na hodnotu z konkrétního výběru. Může být například použit k nalezení hodnoty ve druhém řádku souboru dat nebo v pátém řádku a třetím sloupci.

I když lze INDEX velmi dobře použít samostatně, vnořením MATCH do vzorce je o něco užitečnější. Funkce MATCH hledá zadanou položku v rozsahu buněk a poté vrátí relativní pozici položky v rozsahu. Může být například použit k určení, že konkrétní jméno je třetí položkou v seznamu jmen.

INDEX a MATCH Syntaxe a argumenty

Takto je třeba napsat obě funkce, aby jim Excel porozuměl:

=INDEX(pole, číslo_řádku, [číslo_sloupce])

array je rozsah buněk, které bude vzorec používat. Může to být jeden nebo více řádků a sloupců, například A1:D5. Je to povinné.
row_num je řádek v poli, ze kterého se má vrátit hodnota, například 2 nebo 18. Je povinný, pokud není uveden column_num.
column_num je sloupec v poli, ze kterého se má vrátit hodnota, například 1 nebo 9. Je to nepovinné.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value je hodnota, kterou chcete porovnat v lookup_array. Může to být číslo, text nebo logická hodnota, která se zadává ručně nebo se na ni odkazuje odkazem na buňku. Toto je povinné.
lookup_array je rozsah buněk, které se mají prohlížet. Může to být jeden řádek nebo jeden sloupec, například A2:D2 nebo G1:G45. Toto je povinné.
match_type může být -1, 0 nebo 1. Určuje, jak je hodnota lookup_value spárována s hodnotami v poli lookup_array (viz níže). 1 je výchozí hodnota, pokud je tento argument vynechán.

Jaký typ shody použít
Typ shody	Co to dělá	Pravidlo	Příklad
1	Najde největší hodnotu, která je menší nebo rovna lookup_value.	Hodnoty pole lookup_array musí být umístěny ve vzestupném pořadí (např. -2, -1, 0, 1, 2; nebo A-Z; nebo FALSE, TRUE.	lookup_value je 25, ale chybí v lookup_array, takže místo toho je vrácena pozice dalšího nejmenšího čísla, například 22.
0	Najde první hodnotu, která se přesně rovná lookup_value.	Hodnoty lookup_array mohou být v libovolném pořadí.	lookup_value je 25, takže vrací pozici 25.
-1	Najde nejmenší hodnotu, která je větší nebo rovna lookup_value.	Hodnoty lookup_array musí být umístěny v sestupném pořadí (např. 2, 1, 0, -1, -2).	lookup_value je 25, ale chybí v lookup_array, takže místo toho je vrácena pozice dalšího největšího čísla, například 34.

Použijte 1 nebo -1 pro případy, kdy potřebujete provést přibližné vyhledávání na stupnici, například když pracujete s čísly a když jsou aproximace v pořádku. Ale pamatujte, že pokud nezadáte match_type, bude 1 výchozí, což může zkreslit výsledky, pokud opravdu chcete přesnou shodu.

Ukázkové vzorce INDEX a MATCH

Než se podíváme na to, jak zkombinovat INDEX a MATCH do jednoho vzorce, musíme pochopit, jak tyto funkce fungují samy o sobě.

Příklady INDEXU

=INDEX(A1:B2; 2; 2)

=INDEX(A1:B1; 1)

=INDEX(2:2; 1)=INDEX(B1:B2; 1)

V tomto prvním příkladu existují čtyři vzorce INDEX, které můžeme použít k získání různých hodnot:

=INDEX(A1:B2, 2, 2) prohledá A1:B2 a najde hodnotu ve druhém sloupci a druhém řádku, což je Stacy.
=INDEX(A1:B1, 1) prohledá A1:B1 a najde hodnotu v prvním sloupci, což je Jon.
=INDEX(2:2, 1) prohledá vše ve druhém řádku, aby našel hodnotu v prvním sloupci, což je Tim.
=INDEX(B1:B2, 1) prohledá B1:B2 a najde hodnotu v prvním řádku, což je Amy.

Příklady SHODY

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13; A1:D1; 0)

Zde jsou čtyři snadné příklady funkce MATCH:

=MATCH("Stacy", A2:D2, 0) hledá Stacy v rozsahu A2:D2 a jako výsledek vrací 3.
=MATCH(14, D1:D2) hledá 14 v rozsahu D1:D2, ale protože to není v tabulce nalezeno, MATCH najde další největší hodnotu to je menší nebo rovno 14, což je v tomto případě 13, což je na pozici 1 pole lookup_array.
=MATCH(14, D1:D2, -1) je identický se vzorcem nad ním, ale protože pole není v sestupném pořadí, jak vyžaduje -1, dostaneme chybu.
=MATCH(13, A1:D1, 0) hledá 13 v prvním řádku listu, který vrátí 4, protože je to čtvrtá položka v tomto poli.

Příklady INDEX-MATCH

Zde jsou dva příklady, kdy můžeme kombinovat INDEX a MATCH do jednoho vzorce:

Najděte odkaz na buňku v tabulce

=INDEX(B2:B5, MATCH(F1, A2:A5))

Tento příklad je vnořením vzorce MATCH do vzorce INDEX. Cílem je identifikovat barvu položky pomocí čísla položky.

Pokud se podíváte na obrázek, můžete v řádcích "Oddělené" vidět, jak by se vzorce zapisovaly samostatně, ale protože je vnořujeme, děje se toto:

MATCH(F1, A2:A5) hledá hodnotu F1 (8795) v sadě dat A2:A5. Pokud sloupec odpočítáme, uvidíme, že je to 2, takže to funkce MATCH právě zjistila.
Pole INDEX je B2:B5, protože nakonec hledáme hodnotu v tomto sloupci.
Funkci INDEX lze nyní přepsat takto, protože MATCH našel 2: INDEX(B2:B5, 2, [číslo_sloupce]).
Vzhledem k tomu, že column_num je nepovinné, můžeme jej odstranit a ponechat toto: INDEX(B2:B5, 2).
Takže toto je jako normální vzorec INDEXU, kde zjišťujeme hodnotu druhé položky v B2:B5, která je červená.

Vyhledat podle nadpisů řádků a sloupců

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

V tomto příkladu MATCH a INDEX provádíme obousměrné vyhledávání. Cílem je zjistit, kolik peněz jsme vydělali na zelených položkách v květnu. Toto je opravdu podobné příkladu výše, ale v INDEXU je vnořen zvláštní vzorec MATCH.

MATCH(G1, A2:A13, 0) je první položka vyřešená v tomto vzorci. Hledá G1 (slovo "květen") v A2:A13, aby získal konkrétní hodnotu. Tady to nevidíme, ale je to 5.
MATCH(G2, B1:E1, 0) je druhý vzorec MATCH a je opravdu podobný prvnímu, ale místo toho hledá G2 (slovo „zelená“) v záhlaví sloupců v B1:E1. Toto se vyřeší na 3.
Nyní můžeme přepsat vzorec INDEX takto, abychom si vizualizovali, co se děje: =INDEX(B2:E13, 5, 3). Toto hledá v celé tabulce B2:E13 pátý řádek a třetí sloupec, který vrací 180 $.

Pravidla MATCH and INDEX

Při psaní vzorců s těmito funkcemi je třeba mít na paměti několik věcí:

MATCH nerozlišuje velká a malá písmena, takže velká a malá písmena jsou při shodě textových hodnot považována za stejná.
MATCH vrací N/A z několika důvodů: pokud typ_shody je 0 a hodnota_hledané_hodnoty nebyla nalezena, pokud je typ_shody -1 a pole lookup_array není v sestupném pořadí, pokud je typ_shody 1 a pole vyhledávacího pole není vzestupně pořadí, a pokud lookup_array není jeden řádek nebo sloupec.
Můžete použít zástupný znak v argumentu lookup_value, pokud je match_type 0 a lookup_value je textový řetězec. Otazník odpovídá libovolnému jednotlivému znaku a hvězdička libovolné sekvenci znaků (např.např. =MATCH("Jo", 1:1; 0)). Chcete-li použít MATCH k nalezení skutečného otazníku nebo hvězdičky, zadejte nejprve ~.
INDEX vrací REF! pokud row_num a column_num neukazují na buňku v poli.

Související funkce Excelu

Funkce MATCH je podobná funkci LOOKUP, ale MATCH vrací pozici položky místo položky samotné.

VLOOKUP je další vyhledávací funkce, kterou můžete použít v Excelu, ale na rozdíl od MATCH, který vyžaduje INDEX pro pokročilé vyhledávání, vzorce VLOOKUP potřebují pouze tuto jednu funkci.