Funkciu Excel MATCH môžete použiť na nájdenie relatívnej polohy konkrétnej hodnoty v rozsahu buniek alebo poľa.
Funkcia MATCH je podobná funkcii VLOOKUP, pretože obe sú kategorizované v rámci Excel Lookup/Reference Functions. VLOOKUP hľadá konkrétnu hodnotu v stĺpci a vracia hodnotu v rovnakom riadku, zatiaľ čo funkcia MATCH hľadá určitú hodnotu v rozsahu a vracia pozíciu tejto hodnoty.
Funkcia Excel MATCH hľadá zadanú hodnotu v rozsahu buniek alebo poli a vracia relatívnu pozíciu prvého výskytu tejto hodnoty v rozsahu. Funkciu MATCH je možné použiť aj na vyhľadanie určitej hodnoty a vrátenie jej zodpovedajúcej hodnoty pomocou funkcie INDEX (rovnako ako Vlookup). Pozrime sa, ako použiť funkciu Excel MATCH na nájdenie pozície hľadanej hodnoty v rozsahu buniek.
Funkcia Excel MATCH
Funkcia MATCH je vstavaná funkcia v Exceli a používa sa predovšetkým na nájdenie relatívnej pozície vyhľadávanej hodnoty v stĺpci alebo riadku.
Syntax funkcie MATCH:
=MATCH(vyhľadávacia_hodnota,vyhľadávacie_pole,[typ_zhody})Kde:
hľadaná_hodnota – Hodnota, ktorú chcete vyhľadať v určenom rozsahu buniek alebo v poli. Môže to byť číselná hodnota, textová hodnota, logická hodnota alebo odkaz na bunku s hodnotou.
lookup_array – Polia buniek, v ktorých hľadáte hodnotu. Musí to byť jeden stĺpec alebo jeden riadok.
typ_zhody – Je to voliteľný parameter, ktorý možno nastaviť na 0,1 alebo -1 a predvolená hodnota je 1.
- 0 hľadá presnú zhodu, keď sa nenájde, vráti chybu.
- -1 hľadá najmenšiu hodnotu, ktorá je väčšia alebo rovná hodnote lookup_value, keď je pole vyhľadávania vo vzostupnom poradí.
- 1 hľadá najväčšiu hodnotu, ktorá je menšia alebo rovná hodnote look_up, keď je pole vyhľadávania v zostupnom poradí.
Nájdite pozíciu presnej zhody
Predpokladajme, že máme nasledujúci súbor údajov, kde chceme nájsť polohu určitej hodnoty.
V tejto tabuľke chceme nájsť pozíciu názvu mesta (Memphis) v stĺpci (A2:A23), preto použijeme tento vzorec:
=MATCH("memphis",A2:A23;0)Tretí argument je nastavený na „0“, pretože chceme nájsť presnú zhodu názvu mesta. Ako vidíte, názov mesta „memphis“ vo vzorci je napísaný malým písmenom, zatiaľ čo v tabuľke je prvé písmeno názvu mesta veľké (Memphis). Napriek tomu vzorec dokáže nájsť polohu zadanej hodnoty v danom rozsahu. Je to preto, že funkcia MATCH nerozlišuje veľké a malé písmená.
Poznámka: Ak sa hodnota lookup_value nenájde v rozsahu vyhľadávania alebo ak zadáte nesprávny rozsah vyhľadávania, funkcia vráti chybu #N/A.
Namiesto priamej hodnoty môžete v prvom argumente funkcie použiť odkaz na bunku. Vzorec nižšie nájde polohu hodnoty v bunke F2 a vráti výsledok v bunke F3.
Nájdite pozíciu približnej zhody
Existujú dva spôsoby, ako môžete vyhľadať približnú alebo presnú zhodu hľadanej hodnoty a vrátiť jej polohu.
- Jedným zo spôsobov je nájsť najmenšiu hodnotu, ktorá je väčšia alebo rovná (ďalšia najväčšia zhoda) zadanej hodnote. Dá sa to dosiahnuť nastavením posledného argumentu (typ_zhody) funkcie na „-1“
- Ďalším spôsobom je najväčšia hodnota, ktorá je menšia alebo rovná (najbližšia najmenšia zhoda) danej hodnote. Dá sa to dosiahnuť nastavením match_type funkcie na „1“
Ďalší najmenší zápas
Ak funkcia nemôže nájsť presnú zhodu so zadanou hodnotou, keď je typ zhody nastavený na „1“, nájde najväčšiu hodnotu, ktorá je o niečo menšia ako zadaná hodnota (čo znamená najbližšiu najmenšiu hodnotu) a vráti svoju pozíciu . Aby to fungovalo, musíte zoradiť pole vo vzostupnom poradí, ak nie, bude to mať za následok chybu.
V príklade používame nižšie uvedený vzorec na nájdenie najbližšej najmenšej zhody:
=MATCH(F2;D2:D23;1)Keď tento vzorec nedokáže nájsť presnú zhodu s hodnotou v bunke F2, ukazuje na pozíciu (16) najbližšej najmenšej hodnoty, t. j. 98.
Ďalší Najväčší zápas
Keď je typ zhody nastavený na „-1“ a funkcia MATCH nemôže nájsť presnú zhodu, nájde najmenšiu hodnotu, ktorá je väčšia ako zadaná hodnota (čo znamená ďalšiu najväčšiu hodnotu) a vráti svoju pozíciu. Pre túto metódu musí byť vyhľadávacie pole zoradené v zostupnom poradí, inak vráti chybu.
Zadajte napríklad nasledujúci vzorec, aby ste našli ďalšiu najväčšiu zhodu s vyhľadávanou hodnotou:
=MATCH(F2;D2:D23;-1)Táto funkcia MATCH hľadá hodnotu v F2 (55) vo vyhľadávacom rozsahu D2:D23, a keď nemôže nájsť presnú zhodu, vráti pozíciu (16) ďalšej najväčšej hodnoty, t. j. 58.
Zápas so zástupnými znakmi
Zástupné znaky možno použiť vo funkcii MATCH iba vtedy, keď je typ_zhody nastavený na „0“ a vyhľadávaná hodnota je textový reťazec. Vo funkcii MATCH môžete použiť zástupné znaky: hviezdičku (*) a otáznik (?).
- Otáznik (?) sa používa na priradenie ľubovoľného jednotlivého znaku alebo písmena k textovému reťazcu.
- hviezdička (*) sa používa na priradenie ľubovoľného počtu znakov k reťazcu.
Napríklad sme použili dva zástupné znaky '?' v lookup_value (Lo??n) funkcie MATCH, aby sme našli hodnotu, ktorá sa zhoduje s textovým reťazcom s akýmikoľvek dvoma znakmi (na miestach so zástupnými znakmi). A funkcia vráti relatívnu polohu zodpovedajúcej hodnoty v bunke E5.
=MATCH("Lo??n",A2:A22;0)
Zástupný znak (*) môžete použiť rovnakým spôsobom ako (?), ale hviezdička sa používa na priradenie ľubovoľného počtu znakov, zatiaľ čo otáznik sa používa na priradenie ľubovoľného jednotlivého znaku.
Ak napríklad použijete „sp*“, funkcia sa môže zhodovať s reproduktorom, rýchlosťou alebo spielbergom atď. Ak však funkcia nájde viacero/duplicitných hodnôt zodpovedajúcich hodnote vyhľadávania, vráti iba polohu prvej hodnoty.
V príklade sme do argumentu lookup_value zadali „Kil*o“. Funkcia MATCH() teda vyhľadá text, ktorý obsahuje „Kil“ na začiatku, „o“ na konci a ľubovoľný počet znakov medzi nimi. „Kil*o“ sa zhoduje s Kilimandžárom v poli, a preto funkcia vráti relatívnu pozíciu Kilimandžára, ktorá je 16.
INDEX a MATCH
Funkcie MATCH sa zriedka používajú samostatne. Často sa spárovali s inými funkciami, aby vytvorili výkonné vzorce. Keď je funkcia MATCH kombinovaná s funkciou INDEX, môže vykonávať pokročilé vyhľadávanie. Mnoho ľudí stále uprednostňuje použitie funkcie VLOOKUP na vyhľadanie hodnoty, pretože je to jednoduchšie, ale INDEX MATCH je flexibilnejšie a rýchlejšie ako VLOOKUP.
VLOOKUP dokáže vyhľadať hodnotu iba vertikálne, tj stĺpce, zatiaľ čo kombinácia INDEX MATCH môže vykonávať vertikálne aj horizontálne vyhľadávanie.
Funkcia INDEX používaná na získanie hodnoty na konkrétnom mieste v tabuľke alebo rozsahu. Funkcia MATCH vráti relatívnu pozíciu hodnoty v stĺpci alebo riadku. Keď sa skombinuje, MATCH nájde číslo riadka alebo stĺpca (umiestnenie) konkrétnej hodnoty a funkcia INDEX načíta hodnotu na základe čísla tohto riadka a stĺpca.
Syntax funkcie INDEX:
=INDEX(pole,číslo_riadku,[číslo_stĺpca],)V každom prípade sa pozrime na príklade, ako funguje INDEX MATCH.
V nižšie uvedenom príklade chceme získať skóre „Kvíz2“ pre študentku „Anne“. Na to použijeme nasledujúci vzorec:
=INDEX(B2:F20,ZHODA(H2;A2:A20;0);3)INDEX potrebuje na získanie hodnoty číslo riadka a stĺpca. Vo vyššie uvedenom vzorci vnorená funkcia MATCH nájde číslo riadku (pozíciu) hodnoty „Anne“ (H2). Potom dodáme toto číslo riadku do funkcie INDEX s rozsahom B2:F20 a číslom stĺpca (3), ktoré určíme. A funkcia INDEX vráti skóre „91“.
Obojsmerné vyhľadávanie pomocou INDEX a MATCH
Na vyhľadanie hodnoty v dvojrozmernom rozsahu môžete použiť aj funkcie INDEX a MATCH (obojsmerné vyhľadávanie). Vo vyššie uvedenom príklade sme použili funkciu MATCH na nájdenie čísla riadka hodnoty, ale číslo stĺpca sme zadali manuálne. Ale môžeme nájsť riadok aj stĺpec vnorením dvoch funkcií MATCH, jednu v argumente číslo_riadku a druhú v argumente číslo_stĺpca funkcie INDEX.
Použite tento vzorec na obojsmerné vyhľadávanie pomocou INDEX a MATCH:
=INDEX(A1:F20,ZHODA(H2;A2:A20;0);ZHODA(H3;A1:F1;0))Ako vieme, funkcia MATCH dokáže hľadať hodnotu horizontálne aj vertikálne. V tomto vzorci druhá funkcia MATCH v argumente počet_stĺpcov nájde pozíciu Quiz2 (4) a dodá ju do funkcie INDEX. A INDEX získa skóre.
Teraz viete, ako používať funkciu Match v Exceli.