V tomto článku se naučíme, jak vyhledávat hodnoty ve 2d tabulce pomocí funkce INDEX-MATCH-MATCH v Excelu.
Scénář:
Předpokládejme, že je třeba provést více vyhledávání z tabulky, která má stovky sloupců. V takových případech bude použití různých vzorců pro každé vyhledávání trvat příliš dlouho. Co takhle vytvořit vzorec dynamického vyhledávání, který můžete vyhledat podle zadaného záhlaví. Ano, můžeme to udělat. Tento vzorec se nazývá vzorec INDEX MATCH MATCH nebo řekněme vzorec pro vyhledávání 2d.
Jak problém vyřešit?
Aby vzorec nejprve porozuměl, musíme si trochu zrevidovat následující funkce
- Funkce INDEX
- Funkce MATCH
Funkce INDEX vrací hodnotu v daném indexu v poli.
Funkce MATCH vrací index prvního vzhledu hodnoty v poli (pole jedné dimenze).
Nyní vytvoříme vzorec pomocí výše uvedených funkcí. Funkce shody vrátí index vyhledávací hodnoty1 v poli záhlaví řádku. A další funkce MATCH vrátí index vyhledávací hodnoty2 v poli záhlaví sloupce. Čísla indexů budou nyní vložena do funkce INDEX, aby se hodnoty dostaly pod vyhledávací hodnotu z dat 2D tabulky.
Obecný vzorec:
= INDEX (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))
Data: pole hodnot uvnitř tabulky bez záhlaví
lookup_value1 : hodnota k vyhledání v záhlaví řádku.
záhlaví řádků : Řádek Indexové pole k vyhledání.
lookup_value1 : hodnota k vyhledání v záhlaví sloupce.
hlavičky sloupců : sloupec Indexové pole k vyhledání.
Příklad:
Výše uvedená tvrzení mohou být komplikovaná k pochopení. Pojďme to tedy pochopit pomocí vzorce v příkladu
Zde máme seznam skóre, které studenti získali se seznamem předmětů. Potřebujeme najít skóre pro konkrétního studenta (Garyho) a předmět (sociální studia), jak je znázorněno na snímku níže.
Hodnota Student1 musí odpovídat poli Row_header a Subject value2 musí odpovídat poli Column_header.
Použijte vzorec v buňce J6:
= INDEX (tabulka, MATCH (J5, řádek, 0, MATCH (J4, sloupec, 0)))
Vysvětlení:
- Funkce MATCH odpovídá hodnotě Student v buňce J4 s polem záhlaví řádku a vrací jeho pozici 3 jako číslo.
- Funkce MATCH odpovídá hodnotě Subject v buňce J5 s polem záhlaví sloupců a vrací její pozici 4 jako číslo.
- Funkce INDEX převezme číslo indexu řádku a sloupce, vyhledá data v tabulce a vrátí odpovídající hodnotu.
- Argument typu MATCH je pevně nastaven na 0. Protože vzorec extrahuje přesnou shodu.
Zde jsou hodnoty vzorce uvedeny jako odkazy na buňky a row_header, table a column_header uvedeny jako pojmenované rozsahy.
Jak vidíte na výše uvedeném snímku, získali jsme skóre získané studentem Gary v Předmětu Sociologie jako 36.
Dokazuje, že vzorec funguje dobře, a v případě pochybností si přečtěte níže uvedené poznámky.
Nyní použijeme přibližnou shodu se záhlavími řádků a záhlavími sloupců jako čísly. Přibližná shoda přebírá pouze číselné hodnoty, protože na textové hodnoty neexistuje žádný způsob
Zde máme cenu hodnot podle výšky a šířky produktu. Potřebujeme najít cenu pro konkrétní výšku (34) a šířku (21), jak je uvedeno na snímku níže.
Hodnota Height1 musí odpovídat poli Row_header a Width value2 musí odpovídat poli Column_header.
Použijte vzorec v buňce K6:
= INDEX (data, MATCH (K4, výška, 1, MATCH (K5, šířka, 1)))
Vysvětlení:
- Funkce MATCH odpovídá hodnotě Height v buňce K4 s polem záhlaví řádku a vrací jeho pozici 3 jako číslo.
- Funkce MATCH odpovídá hodnotě Width v buňce K5 s polem záhlaví sloupců a vrací jeho pozici 2 jako číslo.
- Funkce INDEX převezme číslo indexu řádku a sloupce, vyhledá data v tabulce a vrátí odpovídající hodnotu.
- Argument typu MATCH je pevně nastaven na 1. Protože vzorec extrahuje přibližnou shodu.
Zde jsou hodnoty vzorce uvedeny jako odkazy na buňky a row_header, data a column_header jsou uvedeny jako pojmenované rozsahy, jak je uvedeno ve snímku výše.
Jak vidíte na výše uvedeném snímku, máme cenu získanou podle výšky (34) & Šířka (21) jako 53.10. Dokazuje, že vzorec funguje dobře, a v případě pochybností se podívejte do poznámek níže, kde najdete další informace.
Poznámky:
- Funkce vrací chybu #NA, pokud je argument vyhledávacího pole pro funkci MATCH 2D pole, což je pole záhlaví dat…
- Funkce odpovídá přesné hodnotě, protože argument typu shody pro funkci MATCH je 0.
- Hodnoty vyhledávání lze zadat jako odkaz na buňku nebo přímo pomocí symbolu uvozovky (") ve vzorci jako argumentů.
Doufám, že jste pochopili, jak používat tabulku Lookup in 2 D pomocí funkce INDEX & MATCH v aplikaci Excel. Prozkoumejte další články v hodnotě vyhledávání v Excelu zde. Neváhejte a uveďte své dotazy níže v poli pro komentáře. Určitě vám pomůžeme.
K vyhledání hodnoty použijte INDEX a MATCH : Funkce INDEX & MATCH k vyhledání hodnoty podle potřeby.
SUM rozsah s INDEX v Excelu : Pomocí funkce INDEX najděte součet hodnot podle potřeby.
Jak používat funkci SUMA v Excelu : Najděte součet čísel pomocí funkce SUM vysvětlené na příkladu.
Jak používat funkci INDEX v Excelu : Najděte INDEX pole pomocí funkce INDEX vysvětlené na příkladu.
Jak používat funkci MATCH v Excelu : Najděte MATCH v poli pomocí hodnoty INDEX uvnitř funkce MATCH vysvětlené na příkladu.
Jak používat funkci LOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce LOOKUP vysvětlené na příkladu.
Jak používat funkci VLOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce VLOOKUP vysvětlené na příkladu.
Jak používat funkci HLOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce HLOOKUP vysvětlené na příkladu.
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity
Upravte rozevírací seznam
Absolutní reference v Excelu
Pokud s podmíněným formátováním
Pokud se zástupnými znaky
Vlookup podle data
Připojte jméno a příjmení v Excelu
