Tento tutoriál vás naučí používať kombináciu INDEX & MATCH na vyhľadávanie v Exceli a Tabuľkách Google.
INDEX & MATCH, perfektný pár
Pozrime sa podrobnejšie na niektoré zo spôsobov, ako môžete kombinovať funkcie INDEX a MATCH. Funkcia MATCH je navrhnutá tak, aby vracala relatívnu polohu položky v poli, zatiaľ čo funkcia INDEX môže načítať položku z poľa s konkrétnou polohou. Táto synergia medzi nimi im umožňuje vykonávať takmer akýkoľvek typ vyhľadávania, ktoré by ste mohli potrebovať.
Kombinácia INDEX / MATCH sa v minulosti používala ako náhrada funkcie VLOOKUP. Jedným z primárnych dôvodov je schopnosť urobiť ľavo vyzerajúce vyhľadávanie (pozri nasledujúcu časť).
Poznámka: nová funkcia XLOOKUP teraz môže vykonávať hľadanie vľavo.
Vyhľadajte vľavo
Použime túto tabuľku basketbalových štatistík:
Chceme nájsť Bobovho hráča #. Pretože Číslo hráča je vľavo od stĺpca s menom, nemôžeme použiť VLOOKUP.
Namiesto toho by sme mohli urobiť základnú požiadavku MATCH na výpočet Bobovho radu
= ZÁPAS (H2, B2: B5, 0)
Toto vyhľadá presnú zhodu slova „Bob“, a tak naša funkcia vráti číslo 2, pretože „Bob“ je v čísle 2.nd pozíciu.
Ďalej môžeme použiť funkciu INDEX na vrátenie hráča #, zodpovedajúceho riadku. Teraz do funkcie zadáme ručne „2“:
= INDEX (A2: A5, 2)
Tu INDEX bude odkazovať na A3, pretože to je 2nd bunka v rozsahu A2: A5 a vráti výsledok 42. Pre náš celkový cieľ potom môžeme tieto dve skombinovať do:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Výhodou je, že sme mohli vrátiť výsledok zo stĺpca naľavo od miesta, kde sme hľadali.
Dvojrozmerné vyhľadávanie
Pozrime sa na našu tabuľku predtým:
Tentokrát však chceme získať konkrétnu štatistiku. Volali sme, že chceme hľadať Reboundy v bunke H1. Namiesto toho, aby ste museli písať niekoľko príkazov IF na určenie, z ktorého stĺpca dostanete výsledok, môžete znova použiť funkciu MATCH. Funkcia INDEX vám umožňuje zadať hodnotu riadka a hodnota stĺpca. Tu pridáme ďalšiu funkciu MATCH, aby sme určili, ktorý stĺpec chceme. To bude vyzerať
= ZÁPAS (H1, A1: E1, 0)
Naša bunka v H1 je rozbaľovacia ponuka, ktorá nám umožní vybrať si kategóriu, ktorú chceme hľadať, a potom náš MATCH určí, do ktorého stĺpca v tabuľke patrí. Pripojme tento nový bit do nášho predchádzajúceho vzorca. Všimnite si toho, že musíme vyladiť prvý argument tak, aby bol dvojrozmerný, pretože už nechceme iba výsledok zo stĺpca A.
= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
V našom prípade chceme nájsť Rebounds pre Charlieho. Náš vzorec to vyhodnotí takto:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("doskoky", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Teraz sme vytvorili flexibilné nastavenie, ktoré umožňuje používateľovi načítať z našej tabuľky ľubovoľnú hodnotu, ktorú chce, bez toho, aby musel písať viac vzorcov alebo vetviť príkazy IF.
Viac sekcií
Nie je to často používané, ale INDEX má piaty argument, ktorý je možné určiť, ktorý z nich oblasť v rámci argumentu jeden použiť. To znamená, že potrebujeme spôsob, ako do prvého argumentu preniesť viac oblastí. Môžete to urobiť pomocou ďalšej sady zátvoriek. Tento príklad ilustruje, ako by ste mohli načítať výsledky z rôznych tabuliek na hárku pomocou INDEX.
Tu je rozloženie, ktoré použijeme. Máme štatistiky za tri rôzne štvrťroky hry.
V bunkách H1: H3 sme vytvorili rozbaľovacie zoznamy na overenie údajov pre naše rôzne možnosti. Rozbaľovací zoznam pre štvrťrok pochádza z J2: J4. Použijeme to v inom vyhlásení MATCH, aby sme určili, ktorú oblasť použiť. Náš vzorec v H4 bude vyzerať takto:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Už sme diskutovali o tom, ako fungujú dve vnútorné funkcie MATCH, takže sa zamerajme na prvý a posledný argument:
= INDEX ((A3: E6, A10: E13, A17: E20), …, MATCH (H3, J2: J4, 0))
Funkcii INDEX sme v prvom argumente poskytli viac polí tým, že sme ich všetky uzavreli do zátvoriek. Druhý spôsob, ako to môžete urobiť, je použiť vzorce - definovať meno. Môžete definovať názov s názvom „Moje tabuľky“ s definíciou
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Vráťme sa k celému tvrdeniu. Naše rôzne funkcie MATCH povedia funkcii INDEX presne, kde sa máte pozrieť. Najprv určíme, že „Charlie“ je 3rd riadok. Ďalej chceme „Rebounds“, čo je 4th stĺpci. Nakoniec sme sa rozhodli, že chceme výsledok z 2nd stôl. Vzorec sa vyhodnotí takto:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Ako sme už uviedli na začiatku tohto príkladu, obmedzuje sa na to, aby boli tabuľky na rovnakom pracovnom hárku. Ak dokážete napísať správne spôsoby, ako svojmu INDEXU povedať, z ktorého riadka, stĺpca a/alebo oblasti chcete získavať údaje, INDEX vám veľmi dobre poslúži.
Tabuľky Google -INDEX A ZHODA
Všetky vyššie uvedené príklady fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
