Jak používat funkci Excel Match

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.

Tento obrázek má prázdný atribut alt; jeho název souboru je allthings.how-how-to-use-excel-match-function-image-1.png

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.