Ako používať funkcie INDEX a MATCH v Exceli

2024 Autor: Abigail Brown | [email protected]. Naposledy zmenené: 2023-12-17 07:00

Čo by ste mali vedieť

Funkciu INDEX možno použiť samostatne, ale vnorením funkcie MATCH do nej sa vytvorí pokročilé vyhľadávanie.
Táto vnorená funkcia je flexibilnejšia ako funkcia VLOOKUP a môže priniesť výsledky rýchlejšie.

Tento článok vysvetľuje, ako používať funkcie INDEX a MATCH spolu vo všetkých verziách Excelu vrátane Excelu 2019 a Microsoftu 365.

Aké sú funkcie INDEX a MATCH?

INDEX a MATCH sú funkcie vyhľadávania Excelu. Aj keď ide o dve úplne samostatné funkcie, ktoré možno používať samostatne, možno ich tiež kombinovať a vytvárať pokročilé vzorce.

Funkcia INDEX vracia hodnotu alebo odkaz na hodnotu z konkrétneho výberu. Môže sa napríklad použiť na nájdenie hodnoty v druhom riadku množiny údajov alebo v piatom riadku a treťom stĺpci.

Zatiaľ čo INDEX by sa dal veľmi dobre použiť aj samostatne, vnorením MATCH do vzorca je o niečo užitočnejší. Funkcia MATCH vyhľadá zadanú položku v rozsahu buniek a potom vráti relatívnu pozíciu položky v rozsahu. Môže sa napríklad použiť na určenie, že konkrétne meno je treťou položkou v zozname mien.

INDEX a MATCH Syntax & Argumenty

Takto musia byť obe funkcie napísané, aby im Excel porozumel:

=INDEX(pole, číslo_riadka, [číslo_stĺpca])

array je rozsah buniek, ktoré bude vzorec používať. Môže to byť jeden alebo viac riadkov a stĺpcov, napríklad A1:D5. Je to povinné.
row_num je riadok v poli, z ktorého sa má vrátiť hodnota, napríklad 2 alebo 18. Je povinný, pokiaľ nie je uvedený stĺpec_num.
column_num je stĺpec v poli, z ktorého sa má vrátiť hodnota, napríklad 1 alebo 9. Je to voliteľné.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value je hodnota, ktorú chcete porovnať v lookup_array. Môže to byť číslo, text alebo logická hodnota, ktorá sa zadáva manuálne alebo sa na ňu odkazuje prostredníctvom odkazu na bunku. Toto je povinné.
lookup_array je rozsah buniek, ktoré sa majú prezerať. Môže to byť jeden riadok alebo jeden stĺpec, napríklad A2:D2 alebo G1:G45. Toto je povinné.
match_type môže byť -1, 0 alebo 1. Špecifikuje, ako sa lookup_value spája s hodnotami v lookup_array (pozri nižšie). 1 je predvolená hodnota, ak je tento argument vynechaný.

Aký typ zhody použiť
Typ zhody	Na čo slúži	Pravidlo	Príklad
1	Nájde najväčšiu hodnotu, ktorá je menšia alebo rovná lookup_value.	Hodnoty lookup_array musia byť umiestnené vo vzostupnom poradí (napr. -2, -1, 0, 1, 2; alebo A-Z; alebo FALSE, TRUE.	lookup_value je 25, ale chýba v lookup_array, takže namiesto toho sa vráti pozícia najbližšieho najmenšieho čísla, napríklad 22.
0	Nájde prvú hodnotu, ktorá sa presne zhoduje s hodnotou lookup_value.	Hodnoty lookup_array môžu byť v akomkoľvek poradí.	lookup_value je 25, takže vráti pozíciu 25.
-1	Nájde najmenšiu hodnotu, ktorá je väčšia alebo rovná lookup_value.	Hodnoty lookup_array musia byť umiestnené v zostupnom poradí (napr. 2, 1, 0, -1, -2).	lookup_value je 25, ale chýba v lookup_array, takže namiesto toho sa vráti pozícia ďalšieho najväčšieho čísla, napríklad 34.

Použite 1 alebo -1 pre časy, keď potrebujete spustiť približné vyhľadávanie na stupnici, napríklad pri práci s číslami a keď sú aproximácie v poriadku. Pamätajte však, že ak nezadáte typ_zhody, predvolená hodnota bude 1, čo môže skresliť výsledky, ak skutočne chcete presnú zhodu.

Príklad vzorcov INDEX a MATCH

Skôr ako sa pozrieme na to, ako skombinovať INDEX a MATCH do jedného vzorca, musíme pochopiť, ako tieto funkcie fungujú samostatne.

Príklady INDEXU

=INDEX(A1:B2; 2; 2)

=INDEX(A1:B1; 1)

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

V tomto prvom príklade sú štyri vzorce INDEX, ktoré môžeme použiť na získanie rôznych hodnôt:

=INDEX(A1:B2, 2, 2) hľadá cez A1:B2 hodnotu v druhom stĺpci a druhom riadku, čo je Stacy.
=INDEX(A1:B1, 1) hľadá cez A1:B1 hodnotu v prvom stĺpci, čo je Jon.
=INDEX(2:2; 1) prezerá všetko v druhom riadku, aby našiel hodnotu v prvom stĺpci, čo je Tim.
=INDEX(B1:B2, 1) hľadá cez B1:B2 hodnotu v prvom riadku, ktorou je Amy.

Príklady ZHODY

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

=MATCH(14; D1:D2)

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

Tu sú štyri jednoduché príklady funkcie MATCH:

=MATCH("Stacy", A2:D2, 0) hľadá Stacy v rozsahu A2:D2 a ako výsledok vráti 3.
=MATCH(14, D1:D2) hľadá 14 v rozsahu D1:D2, ale keďže sa nenachádza v tabuľke, MATCH nájde ďalšiu najväčšiu hodnotu to je menšie alebo rovné 14, čo je v tomto prípade 13, čo je na pozícii 1 lookup_array.
=MATCH(14, D1:D2, -1) je identické so vzorcom nad ním, ale keďže pole nie je v zostupnom poradí, ako vyžaduje -1, dostaneme chybu.
=MATCH(13, A1:D1, 0) hľadá 13 v prvom riadku hárka, ktorý vráti 4, pretože je to štvrtá položka v tomto poli.

Príklady INDEX-MATCH

Tu sú dva príklady, kde môžeme kombinovať INDEX a MATCH do jedného vzorca:

Nájdi referenciu bunky v tabuľke

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

Tento príklad je vnorením vzorca MATCH do vzorca INDEX. Cieľom je identifikovať farbu položky pomocou čísla položky.

Ak sa pozriete na obrázok, v riadkoch „Oddelené“môžete vidieť, ako by sa vzorce písali samostatne, ale keďže ich vnorujeme, deje sa toto:

MATCH(F1, A2:A5) hľadá hodnotu F1 (8795) v množine údajov A2:A5. Ak odpočítame stĺpec, vidíme, že je to 2, takže to práve zistila funkcia MATCH.
Pole INDEX je B2:B5, pretože nakoniec hľadáme hodnotu v tomto stĺpci.
Funkciu INDEX je teraz možné prepísať takto, pretože MATCH našiel 2: INDEX(B2:B5, 2, [číslo_stĺpca]).
Keďže column_num je voliteľný, môžeme ho odstrániť a ponechať toto: INDEX(B2:B5, 2).
Takže toto je ako normálny INDEXOVÝ vzorec, kde nachádzame hodnotu druhej položky v B2:B5, ktorá je červená.

Vyhľadávanie podľa nadpisov riadkov a stĺpcov

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

V tomto príklade MATCH a INDEX robíme obojsmerné vyhľadávanie. Cieľom je zistiť, koľko peňazí sme zarobili na zelených položkách v máji. Toto je naozaj podobné vyššie uvedenému príkladu, ale v INDEXE je vnorený ďalší vzorec MATCH.

MATCH(G1, A2:A13, 0) je prvá položka vyriešená v tomto vzorci. Hľadá G1 (slovo „máj“) v A2:A13, aby získal konkrétnu hodnotu. Nevidíme to tu, ale je to 5.
MATCH(G2, B1:E1, 0) je druhý vzorec MATCH a je naozaj podobný prvému, ale namiesto toho hľadá G2 (slovo „Zelená“) v nadpisoch stĺpcov v B1:E1. Toto je 3.
Teraz môžeme prepísať vzorec INDEX takto, aby sme vizualizovali, čo sa deje: =INDEX(B2:E13, 5, 3). Toto hľadá v celej tabuľke B2:E13 piaty riadok a tretí stĺpec, ktorý vracia 180 $.

Pravidlá ZHODY a INDEXU

Pri písaní vzorcov s týmito funkciami je potrebné mať na pamäti niekoľko vecí:

MATCH nerozlišuje veľké a malé písmená, takže pri zhode textových hodnôt sa s veľkými a malými písmenami zaobchádza rovnako.
MATCH vráti hodnotu N/A z viacerých dôvodov: ak typ_zhody je 0 a hodnota_hľadania sa nenašla, ak typ_zhody je -1 a pole vyhľadávania nie je v zostupnom poradí, ak typ_zhody je 1 a pole vyhľadávania nie je vzostupne poradie a ak lookup_array nie je jeden riadok alebo stĺpec.
Môžete použiť zástupný znak v argumente lookup_value, ak match_type je 0 a lookup_value je textový reťazec. Otáznik zodpovedá ľubovoľnému jednotlivému znaku a hviezdička ľubovoľnej sekvencii znakov (napr.napr. =MATCH("Jo", 1:1; 0)). Ak chcete pomocou funkcie MATCH nájsť skutočný otáznik alebo hviezdičku, najprv napíšte ~.
INDEX vráti REF! ak row_num a column_num neukazujú na bunku v poli.

Súvisiace funkcie Excelu

Funkcia MATCH je podobná funkcii LOOKUP, ale MATCH vracia pozíciu položky namiesto samotnej položky.

VLOOKUP je ďalšia funkcia vyhľadávania, ktorú môžete použiť v Exceli, ale na rozdiel od funkcie MATCH, ktorá vyžaduje INDEX na pokročilé vyhľadávanie, vzorce VLOOKUP potrebujú iba túto jednu funkciu.