Funkci Excel MATCH můžete použít k nalezení relativní pozice konkrétní hodnoty v rozsahu buněk nebo pole.
Funkce MATCH je podobná funkci SVYHLEDAT, protože obě jsou kategorizovány pod funkcemi Excel Lookup/Reference Functions. Funkce VLOOKUP hledá konkrétní hodnotu ve sloupci a vrací hodnotu ve stejném řádku, zatímco funkce MATCH hledá určitou hodnotu v rozsahu a vrací pozici této hodnoty.
Funkce MATCH aplikace Excel hledá zadanou hodnotu v rozsahu buněk nebo poli a vrací relativní pozici prvního výskytu této hodnoty v rozsahu. Funkci MATCH lze také použít k vyhledání určité hodnoty a vrácení její odpovídající hodnoty pomocí funkce INDEX (stejně jako Vlookup). Podívejme se, jak použít funkci Excel MATCH k nalezení pozice hledané hodnoty v rozsahu buněk.
Funkce Excel MATCH
Funkce MATCH je vestavěná funkce v Excelu a primárně se používá k vyhledání relativní pozice hledané hodnoty ve sloupci nebo řádku.
Syntaxe funkce MATCH:
=MATCH(hledaná_hodnota,lookup_pole,[typ_shody})
Kde:
vyhledávací_hodnota – Hodnota, kterou chcete vyhledat v zadaném rozsahu buněk nebo v poli. Může to být číselná hodnota, textová hodnota, logická hodnota nebo odkaz na buňku s hodnotou.
vyhledávací_pole – Pole buněk, ve kterých hledáte hodnotu. Musí to být jeden sloupec nebo jeden řádek.
match_type – Je to volitelný parametr, který lze nastavit na 0,1 nebo -1 a výchozí je 1.
- 0 hledá přesnou shodu, když ji nenajde, vrátí chybu.
- -1 hledá nejmenší hodnotu, která je větší nebo rovna hodnotě lookup_value, když je pole vyhledávání ve vzestupném pořadí.
- 1 hledá největší hodnotu, která je menší nebo rovna hodnotě look_up, když je pole vyhledávání v sestupném pořadí.
Najděte pozici přesné shody
Předpokládejme, že máme následující datovou sadu, kde chceme najít pozici určité hodnoty.
V této tabulce chceme najít pozici názvu města (Memphis) ve sloupci (A2:A23), takže použijeme tento vzorec:
=MATCH("memphis",A2:A23;0)
Třetí argument je nastaven na „0“, protože chceme najít přesnou shodu názvu města. Jak můžete vidět, jméno města „memphis“ ve vzorci je malé, zatímco v tabulce je první písmeno názvu města velké (Memphis). Vzorec je přesto schopen najít polohu zadané hodnoty v daném rozsahu. Je to proto, že funkce MATCH nerozlišuje velká a malá písmena.
Poznámka: Pokud se hodnota lookup_value nenajde v rozsahu vyhledávání nebo pokud zadáte nesprávný rozsah vyhledávání, funkce vrátí chybu #N/A.
Místo přímé hodnoty můžete v prvním argumentu funkce použít odkaz na buňku. Níže uvedený vzorec najde pozici hodnoty v buňce F2 a vrátí výsledek v buňce F3.
Najděte pozici přibližné shody
Existují dva způsoby, jak můžete vyhledat přibližnou nebo přesnou shodu s vyhledávanou hodnotou a vrátit její pozici.
- Jedním ze způsobů je najít nejmenší hodnotu, která je větší nebo rovna (další největší shoda) zadané hodnotě. Toho lze dosáhnout nastavením posledního argumentu (typ_shody) funkce jako „-1“
- Dalším způsobem je největší hodnota, která je menší nebo rovna (další nejmenší shoda) dané hodnotě. Toho lze dosáhnout nastavením typu match_type funkce na „1“
Další nejmenší zápas
Pokud funkce nemůže najít přesnou shodu se zadanou hodnotou, když je typ shody nastaven na „1“, vyhledá největší hodnotu, která je o něco menší než zadaná hodnota (což znamená další nejmenší hodnotu) a vrátí svou pozici . Aby to fungovalo, musíte seřadit pole ve vzestupném pořadí, pokud ne, bude to mít za následek chybu.
V příkladu používáme níže uvedený vzorec k nalezení další nejmenší shody:
=MATCH(F2;D2:D23;1)
Když tento vzorec nemůže najít přesnou shodu s hodnotou v buňce F2, ukazuje na pozici (16) další nejmenší hodnoty, tj. 98.
Další Největší zápas
Když je typ shody nastaven na „-1“ a funkce MATCH nemůže najít přesnou shodu, najde nejmenší hodnotu, která je větší než zadaná hodnota (což znamená další největší hodnotu) a vrátí svou pozici. Vyhledávací pole musí být pro tuto metodu seřazeno v sestupném pořadí, jinak vrátí chybu.
Zadejte například následující vzorec, abyste našli další největší shodu s hledanou hodnotou:
=MATCH(F2;D2:D23;-1)
Tato funkce MATCH hledá hodnotu v F2 (55) ve vyhledávacím rozsahu D2:D23, a když nemůže najít přesnou shodu, vrátí pozici (16) další největší hodnoty, tj. 58.
Shoda zástupných znaků
Zástupné znaky lze ve funkci MATCH použít pouze v případě, že typ_shody je nastaven na „0“ a vyhledávací hodnotou je textový řetězec. Ve funkci MATCH můžete použít zástupné znaky: hvězdičku (*) a otazník (?).
- Otazník (?) se používá k přiřazení libovolného jednotlivého znaku nebo písmene k textovému řetězci.
- hvězdička (*) se používá k přiřazení libovolného počtu znaků k řetězci.
Například jsme použili dva zástupné znaky '?' v lookup_value (Lo??n) funkce MATCH, abychom našli hodnotu, která odpovídá textovému řetězci s libovolnými dvěma znaky (na místech se zástupnými znaky). A funkce vrátí relativní polohu odpovídající hodnoty v buňce E5.
=MATCH("Lo??n",A2:A22;0)
Zástupný znak (*) můžete použít stejným způsobem jako (?), ale hvězdička se používá pro shodu libovolného počtu znaků, zatímco otazník se používá pro shodu libovolného jednotlivého znaku.
Pokud například použijete ‚sp*‘, funkce se může shodovat s reproduktorem, rychlostí nebo spielbergem atd. Pokud však funkce najde více/duplicitní hodnoty odpovídající vyhledávací hodnotě, vrátí pouze pozici první hodnoty.
V příkladu jsme do argumentu lookup_value zadali „Kil*o“. Funkce MATCH() tedy hledá text, který obsahuje „Kil“ na začátku, „o“ na konci a libovolný počet znaků mezi nimi. „Kil*o“ odpovídá Kilimandžáru v poli, a proto funkce vrací relativní pozici Kilimandžára, což je 16.
INDEX a MATCH
Funkce MATCH se zřídka používají samostatně. Často se spárovaly s dalšími funkcemi a vytvořily výkonné vzorce. Když je funkce MATCH kombinována s funkcí INDEX, může provádět pokročilé vyhledávání. Mnoho lidí stále preferuje použití funkce SVYHLEDAT k vyhledání hodnoty, protože je jednodušší, ale INDEX MATCH je flexibilnější a rychlejší než funkce VLOOKUP.
Funkce VLOOKUP může vyhledávat hodnotu pouze vertikálně, tj. sloupce, zatímco combo INDEX MATCH může provádět vertikální i horizontální vyhledávání.
Funkce INDEX používaná k načtení hodnoty na určitém místě v tabulce nebo rozsahu. Funkce MATCH vrací relativní pozici hodnoty ve sloupci nebo řádku. Když se to zkombinuje, MATCH najde číslo řádku nebo sloupce (umístění) konkrétní hodnoty a funkce INDEX načte hodnotu na základě tohoto čísla řádku a sloupce.
Syntaxe funkce INDEX:
=INDEX(pole,číslo_řádku,[číslo_sloupce],)
Každopádně se podívejme, jak funguje INDEX MATCH na příkladu.
V níže uvedeném příkladu chceme získat skóre ‚Quiz2‘ pro studentku ‚Anne‘. K tomu použijeme níže uvedený vzorec:
=INDEX(B2:F20,SHODA(H2;A2:A20;0);3)
INDEX potřebuje k načtení hodnoty číslo řádku a sloupce. Ve výše uvedeném vzorci vnořená funkce MATCH najde číslo řádku (pozici) hodnoty ‚Anne‘ (H2). Poté dodáme toto číslo řádku do funkce INDEX s rozsahem B2:F20 a číslem sloupce (3), které určíme. A funkce INDEX vrátí skóre „91“.
Obousměrné vyhledávání pomocí INDEX a MATCH
K vyhledání hodnoty ve dvourozměrném rozsahu můžete také použít funkce INDEX a MATCH (obousměrné vyhledávání). Ve výše uvedeném příkladu jsme použili funkci MATCH k nalezení čísla řádku hodnoty, ale číslo sloupce jsme zadali ručně. Ale můžeme najít řádek i sloupec vnořením dvou funkcí MATCH, jednu v argumentu číslo_řádku a druhou v argumentu číslo_sloupce funkce INDEX.
Použijte tento vzorec pro obousměrné vyhledávání pomocí INDEX a MATCH:
=INDEX(A1:F20,SHODA(H2,A2:A20,0),SHODA(H3,A1:F1,0))
Jak víme, funkce MATCH dokáže hledat hodnotu jak horizontálně, tak vertikálně. V tomto vzorci druhá funkce MATCH v argumentu colum_num najde pozici Quiz2 (4) a dodá ji funkci INDEX. A INDEX získá skóre.
Nyní víte, jak používat funkci Match v Excelu.