Excel

Ako používať INDEX a MATCH

How Use Index Match

INDEX a MATCH je najobľúbenejší nástroj v Exceli na vykonávanie pokročilejších vyhľadávaní. Dôvodom je, že INDEX a MATCH sú neuveriteľne flexibilné-môžete vykonávať horizontálne a vertikálne vyhľadávania, dvojsmerné vyhľadávania, ľavostranné vyhľadávania, malé a veľké písmená a dokonca aj vyhľadávania na základe viacerých kritérií. Ak sa chcete zlepšiť v Exceli, INDEX a MATCH by mali byť vo vašom zozname.



Tento článok zjednodušene vysvetľuje, ako používať INDEX a MATCH spoločne na vyhľadávanie. Vyžaduje si to krok za krokom, najskôr vysvetlite INDEX, potom MATCH a potom vám ukážem, ako skombinovať tieto dve funkcie dohromady a vytvoriť dynamické obojsmerné vyhľadávanie. Ďalej na stránke sú pokročilejšie príklady.

Funkcia INDEX | Funkcia MATCH | INDEX a ZHODA | 2-smerné vyhľadávanie | Vyhľadávanie vľavo | Rozlišujú sa malé a veľké písmená | Najbližší zápas | Viac kritérií | Viac príkladov





Funkcia INDEX

Funkcia INDEX v programe Excel je fantasticky flexibilná a výkonná a nájdete ju v obrovskom počte vzorcov programu Excel, najmä v pokročilých vzorcoch. Čo však INDEX vlastne robí? Stručne povedané, INDEX získava hodnotu na danom mieste v rozsahu. Povedzme napríklad, že máte tabuľku planét v našej slnečnej sústave (pozri nižšie) a pomocou vzorca chcete získať názov 4. planéty Mars. INDEX môžete použiť takto:

 
= INDEX (B3:B11,4)

Na získanie názvu 4. planéty použite INDEX
INDEX vráti hodnotu v 4. riadku rozsahu.



Video: Ako hľadať veci s INDEX

Čo keď chcete získať priemer Marsu pomocou INDEX? V takom prípade môžeme zadať číslo riadku aj číslo stĺpca a poskytnúť väčší rozsah. Nasledujúci vzorec INDEX používa celý rozsah údajov v B3: D11 s číslom riadka 4 a stĺpcom 2:

 
= INDEX (B3:D11,4,2)

Použitie INDEX na získanie priemeru 4. planéty
INDEX získa hodnotu v riadku 4, stĺpci 2.

Ak to zhrnieme, INDEX získa hodnotu na danom mieste v rozsahu buniek na základe číselnej polohy. Ak je rozsah jednorozmerný, stačí zadať iba číslo riadka. Keď je rozsah dvojrozmerný, budete musieť zadať číslo riadka aj stĺpca.

V tomto mieste si možno myslíte: „Čo teda? Ako často vlastne poznáte polohu niečoho v tabuľke? '

Uplne spravne. Potrebujeme spôsob, ako nájsť polohu vecí, ktoré hľadáme.

Zadajte funkciu MATCH.

Funkcia MATCH

Funkcia MATCH je navrhnutá na jeden účel: nájsť polohu položky v rozsahu. Napríklad pomocou MATCH môžeme získať pozíciu slova „broskyňa“ v tomto zozname druhov ovocia takto:

 
= MATCH ('peach',B3:B9,0)

Použitie MATCH na nájdenie polohy vo vertikálnom rozsahu
MATCH vráti 3, pretože 'Peach' je treťou položkou. V MATCH sa nerozlišujú malé a veľké písmená.

MATCH nezaujíma, či je rozsah horizontálny alebo vertikálny, ako vidíte nižšie:

 
= MATCH ('peach',C4:I4,0)

Použitie MATCH na nájdenie polohy v horizontálnom rozsahu
Rovnaký výsledok s horizontálnym rozsahom, MATCH vráti 3.

Video: Ako používať MATCH na presné zhody

Dôležité: Posledný argument vo funkcii MATCH je typ zhody. Typ zhody je dôležitý a určuje, či je zhoda presná alebo približná. V mnohých prípadoch budete chcieť na vynútenie správania sa presnej zhody použiť nulu (0). Predvolený typ zhody je 1, čo znamená približnú zhodu, preto je dôležité zadať hodnotu. Pozrite si Stránka MATCH pre viac detailov.

INDEX a MATCH spoločne

Teraz, keď sme sa zaoberali základmi INDEX a MATCH, ako skombinujeme tieto dve funkcie do jedného vzorca? Vezmite do úvahy nižšie uvedené údaje, tabuľku zobrazujúcu zoznam predajcov a mesačné čísla predajov za tri mesiace: január, február a marec.

Predaj predajcom podľa mesiaca

Povedzme, že chceme pre daného predajcu napísať vzorec, ktorý vráti predajné číslo za február. Z diskusie vyššie vieme, že INDEXU môžeme dať číslo riadka a stĺpca na získanie hodnoty. Na vrátenie februárového predajného čísla pre Frantz napríklad poskytneme rad C3: E11 s riadkom 5 a stĺpcom 2:

 
= INDEX (C3:E11,5,2) // returns 94

Očividne však nechceme čísla kódovať natvrdo. Namiesto toho chceme a dynamický vyhľadať.

Ako to urobíme? Funkcia MATCH samozrejme. MATCH bude perfektne fungovať pri hľadaní pozícií, ktoré potrebujeme. Postupne po krokoch ponechajme stĺpec napevno kódovaný ako 2 a urobme číslo riadka dynamickým. Tu je revidovaný vzorec s funkciou MATCH vnorenou do INDEXu namiesto 5:

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

Keď ideme o krok ďalej, použijeme hodnotu z H2 v MATCH:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX a MATCH nájdete februárový predaj pre akékoľvek meno
MATCH nájde 'Frantz' a vráti 5 do INDEX pre riadok.

Zhrnúť:

  1. INDEX potrebuje číselné polohy.
  2. MATCH nájde tieto polohy.
  3. ZÁPAS je vnorené vo vnútri INDEX.

Teraz sa budeme zaoberať číslom stĺpca.

Obojsmerné vyhľadávanie s INDEX a MATCH

Hore sme použili funkciu MATCH na dynamické nájdenie čísla riadku, ale číslo stĺpca napevno. Ako môžeme dosiahnuť, aby bol vzorec plne dynamický, aby sme mohli vrátiť tržby pre akéhokoľvek daného predajcu v danom mesiaci? Ide o to, použiť MATCH dvakrát - raz na získanie pozície v riadku a raz na získanie umiestnenia v stĺpci.

Z vyššie uvedených príkladov vieme, že MATCH funguje dobre s horizontálnymi aj vertikálnymi poľami. To znamená, že pomocou MATCH môžeme ľahko nájsť pozíciu daného mesiaca. Tento vzorec napríklad vráti pozíciu z marca, ktorá je 3:

 
= MATCH ('Mar',C2:E2,0) // returns 3

Ale samozrejme nechceme tvrdo kódovať akýkoľvek hodnoty, aktualizujme teda pracovný hárok, aby umožnil zadanie názvu mesiaca, a pomocou MATCH nájdeme číslo stĺpca, ktoré potrebujeme. Nasledujúca obrazovka zobrazuje výsledok:

Dynamické vyhľadávanie s INDEX a MATCH
Plne dynamické, obojsmerné vyhľadávanie s INDEX a MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

Prvý vzorec MATCH vracia 5 do INDEX ako číslo riadka, druhý vzorec MATCH vracia 3 do INDEX ako číslo stĺpca. Po spustení MATCH sa vzorec zjednoduší na:

 
= INDEX (C3:E11,5,3)

a INDEX správne vráti 10 525 dolárov, predajné číslo pre Frantza v marci.

Poznámka: môžete použiť Overovanie dát na vytvorenie rozbaľovacích ponúk na výber predajcu a mesiaca.

Video: Ako vykonať obojsmerné vyhľadávanie s INDEX a MATCH

Video: Ako ladiť vzorec pomocou F9 (zobraziť návratové hodnoty MATCH)

Vyhľadávanie vľavo

Jednou z kľúčových výhod funkcií INDEX a MATCH oproti funkcii VLOOKUP je možnosť vykonať „ľavé vyhľadávanie“. Jednoducho povedané, znamená to len vyhľadávanie, kde je stĺpec ID v správny hodnôt, ktoré chcete získať, ako je vidieť na nižšie uvedenom príklade:

Vyhľadávanie vľavo s INDEX a MATCH

Prečítajte si podrobné vysvetlenie tu .

Vyhľadávanie rozlišujúce malé a veľké písmená

Samotná funkcia MATCH nerozlišuje malé a veľké písmená. Vy však používate PRESNÁ funkcia s INDEX a MATCH na vykonanie vyhľadávania, ktoré rešpektuje veľké a malé písmená, ako je uvedené nižšie:

Vyhľadávanie rozlišujúce malé a veľké písmena s INDEX a MATCH

Prečítajte si podrobné vysvetlenie tu .

Poznámka: toto je an maticový vzorec a musí sa zadať pomocou klávesovej skratky + shift + enter, s výnimkou Excel 365 .

Najbližší zápas

Ďalším príkladom, ktorý ukazuje flexibilitu INDEX a MATCH, je problém nájsť najbližší zápas . V nižšie uvedenom príklade používame príponu Funkcia MIN spolu s Funkcia ABS do vytvoriť vyhľadávacia hodnota a vyhľadávacie pole vo vnútri funkciu MATCH. V zásade používame MATCH na nájdenie najmenšieho rozdielu. Potom použijeme INDEX na načítanie pridruženého vypnutia zo stĺpca B.

Nájdite najbližšiu zhodu s INDEX a MATCH

Prečítajte si podrobné vysvetlenie tu .

Poznámka: toto je an maticový vzorec a musí sa zadať pomocou klávesovej skratky + shift + enter, s výnimkou Excel 365 .

Vyhľadávanie podľa viacerých kritérií

Jedným z najzložitejších problémov v programe Excel je vyhľadávanie založené na viacerých kritériách. Inými slovami, vyhľadávanie, ktoré sa zhoduje vo viac ako jednom stĺpci súčasne. V nižšie uvedenom príklade používame INDEX a MATCH a booleovská logika zodpovedajúce 3 stĺpcom: položka, farba a veľkosť:

INDEX a MATCH s viacerými kritériami

Prečítajte si podrobné vysvetlenie tu .

Poznámka: toto je an maticový vzorec a musí sa zadať pomocou klávesovej skratky + shift + enter, s výnimkou Excel 365 .

viacnásobné príkazy if v jednej bunke

Ďalšie príklady INDEX + MATCH

Tu je niekoľko ďalších základných príkladov INDEX a MATCH v prevádzke, každý s podrobným vysvetlením:

Autor Dave Bruns Prílohy Súbor index exceljet a match.xlsx


^