Tento tutoriál vás naučí používat kombinaci INDEX & MATCH k vyhledávání v Excelu a Tabulkách Google.
INDEX & MATCH, perfektní pár
Podívejme se blíže na některé způsoby, jak můžete kombinovat funkce INDEX a MATCH. Funkce MATCH je navržena tak, aby vrátila relativní polohu položky v poli, zatímco funkce INDEX může načíst položku z pole s konkrétní pozicí. Tato synergie mezi nimi jim umožňuje provádět téměř jakýkoli typ vyhledávání, který byste mohli potřebovat.
Kombinace INDEX / MATCH byla historicky používána jako náhrada funkce VLOOKUP. Jedním z hlavních důvodů je možnost levého vyhledávání (viz další část).
Poznámka: nová funkce XLOOKUP nyní může provádět vyhledávání vlevo.
Vyhledejte vlevo
Pojďme použít tuto tabulku basketbalových statistik:
Chceme najít Bobova hráče #. Protože Player # je nalevo od sloupce se jménem, nemůžeme použít VLOOKUP.
Místo toho bychom mohli provést základní požadavek MATCH pro výpočet Bobovy řady
= MATCH (H2, B2: B5, 0)
To bude hledat přesnou shodu slova „Bob“, a tak naše funkce vrátí číslo 2, protože „Bob“ je ve 2nd pozice.
Dále můžeme pomocí funkce INDEX vrátit Player #, odpovídající řádku. Prozatím zadáme do funkce ručně „2“:
= INDEX (A2: A5, 2)
Zde bude INDEX odkazovat na A3, protože to je 2nd buňka v rozmezí A2: A5 a vrátí výsledek 42. Pro náš celkový cíl pak můžeme tyto dva spojit do:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Výhodou je, že jsme mohli vrátit výsledek ze sloupce nalevo od místa, kde jsme hledali.
Dvojrozměrné vyhledávání
Podívejme se na naši tabulku dříve:
Tentokrát však chceme načíst konkrétní statistiku. Zavolali jsme, že chceme hledat Reboundy v buňce H1. Místo toho, abyste museli psát několik příkazů IF, abyste určili, ze kterého sloupce chcete získat výsledek, můžete znovu použít funkci MATCH. Funkce INDEX umožňuje zadat hodnotu řádku a hodnota sloupce. Zde přidáme další funkci MATCH, abychom určili, který sloupec chceme. To bude vypadat
= MATCH (H1, A1: E1, 0)
Naše buňka v H1 je rozevírací seznam, který nám umožňuje vybrat, jakou kategorii chceme hledat, a poté naše MATCH určuje, do kterého sloupce v tabulce patří. Pojďme zapojit tento nový bit do našeho předchozího vzorce. Všimněte si, že musíme vyladit první argument tak, aby byl dvourozměrný, protože už nechceme jen výsledek ze sloupce A.
= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
V našem příkladu chceme najít Rebounds pro Charlieho. Náš vzorec to vyhodnotí takto:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Nyní jsme vytvořili flexibilní nastavení, které umožňuje uživateli načíst z naší tabulky libovolnou hodnotu, kterou chtějí, aniž by museli psát více vzorců nebo větvení příkazů IF.
Více sekcí
Není to často používáno, ale INDEX má pátý argument, který lze určit, který plocha v rámci argumentu jeden použít. To znamená, že potřebujeme způsob, jak předat více oblastí do prvního argumentu. To lze provést pomocí další sady závorek. Tento příklad bude ilustrovat, jak byste mohli načíst výsledky z různých tabulek na listu pomocí INDEX.
Zde je rozložení, které použijeme. Máme statistiky pro tři různé čtvrtiny hry.
V buňkách H1: H3 jsme pro různé možnosti vytvořili rozevírací seznamy Ověření dat. Rozbalovací nabídka pro Quarter pochází z J2: J4. Použijeme to pro další příkaz MATCH, abychom zjistili, kterou oblast použít. Náš vzorec v H4 bude vypadat takto:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Už jsme diskutovali o tom, jak fungují dvě vnitřní funkce MATCH, pojďme se tedy zaměřit na první a poslední argument:
= INDEX ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Funkci INDEX jsme v prvním argumentu dali více polí tím, že jsme je všechny uzavřeli do závorek. Druhý způsob, jak to můžete udělat, je použít vzorce - definovat jméno. Můžete definovat název s názvem „MyTables“ s definicí
= INDEX (MyTable, MATCH (H2, Table1347 [Název], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Vraťme se k celému prohlášení. Naše různé funkce MATCH řeknou funkci INDEX přesně, kde hledat. Nejprve určíme, že „Charlie“ je 3rd řádek. Dále chceme „Rebounds“, což je 4th sloupec. Nakonec jsme se rozhodli, že chceme výsledek od 2nd stůl. Vzorec bude vyhodnocen 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
Jak jsme zmínili na začátku tohoto příkladu, jste omezeni na to, aby tabulky byly na stejném listu. Pokud dokážete napsat správné způsoby, jak sdělit svému INDEXU, ze kterého řádku, sloupce a/nebo oblasti chcete získávat data, INDEX vám velmi dobře poslouží.
Tabulky Google -INDEX & MATCH
Všechny výše uvedené příklady fungují v Tabulkách Google úplně stejně jako v Excelu.
