V tomto článku se naučíme, jak pomocí funkce INDEX a MATCH vyhledávat hodnoty v Excelu.
Funkce INDEX & MATCH nahrazuje funkci vlookup
Obvykle používáme funkci VLOOKUP k nalezení hodnoty, která zná číslo řádku a číslo sloupce. Pro funkci VLOOKUP potřebujeme mít kritéria shody čísel sloupců a řádků a nejdůležitější věc, kterou má hodnota zjistit, že je vpravo od odpovídající hodnoty. Data to však vždy nepodporují. Mnohokrát potřebujeme pouze vyhledat hodnoty bez znalosti indexu řádku nebo sloupce, ale pouze s hodnotami, které se shodují. Například: zjištění platu zaměstnance s ID zaměstnance. V tomto budeme hledat ID zaměstnance pro index řádků a Plat pro sloupcový index. Pojďme se naučit, jak můžeme toto vyhledávání provádět pomocí INDEX & MATCH.
Jak problém vyřešit?
Aby vzorec nejprve porozuměl, musíme si trochu zrevidovat následující funkce
- Funkce INDEX
- Funkce MATCH
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í hodnoty 2 z pole záhlaví sloupce. Čísla indexů budou nyní vložena do funkce INDEX, aby se hodnoty získaly pod vyhledávací hodnotou z dat tabulky.
Obecný vzorec:
| = INDEX (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
data : řada hodnot uvnitř tabulky bez záhlaví
lookup_value1 : hodnota k vyhledání v záhlaví řádku.
záhlaví řádků : Řádkové 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. A to dokazuje, že vzorec funguje dobře, a pro pochybnosti se podívejte do níže uvedených poznámek.
Nyní použijeme přibližnou shodu se záhlavími řádků a záhlavími sloupců jako čísly. Přibližná shoda má 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.
Přesné shody vyhledávání můžete také provádět pomocí funkcí INDEX a MATCH v aplikaci Excel. Další informace o tom, jak provádět vyhledávání rozlišující malá a velká písmena pomocí funkce INDEX & MATCH v aplikaci Excel. Dílčí shody můžete také vyhledat pomocí zástupných znaků v aplikaci Excel.
Jak vidíte na výše uvedeném snímku, dostali jsme Cenu získanou podle výšky (34) & Šířka (21) jako 53.10. A to dokazuje, že vzorec funguje dobře, a pro pochybnosti viz níže uvedené poznámky pro lepší pochopení.
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 tento článek o tom, jak použít funkci INDEX a MATCH k vyhledání hodnoty v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
K vyhledání hodnoty použijte INDEX a MATCH : Vzorec INDEX-MATCH se používá k dynamickému a přesnému vyhledávání hodnoty v dané tabulce. Toto je alternativa k funkci VLOOKUP a překonává nedostatky funkce VLOOKUP.
Použijte VLOOKUP ze dvou nebo více vyhledávacích tabulek : Pro vyhledávání z více tabulek můžeme použít přístup IFERROR. Pro vyhledávání z více tabulek je chyba považována za přepínač pro další tabulku. Další metodou může být přístup If.
Jak provádět vyhledávání rozlišující malá a velká písmena v aplikaci Excel : Funkce VLOOKUP aplikace Excel nerozlišuje velká a malá písmena a vrátí první odpovídající hodnotu ze seznamu. INDEX-MATCH není výjimkou, ale lze jej upravit tak, aby rozlišoval velká a malá písmena. Podívejme se, jak…
Vyhledávání často se vyskytujícího textu s kritérii v aplikaci Excel : Vyhledávání se nejčastěji objevuje v textu v rozsahu, který používáme INDEX-MATCH s funkcí MODE. Zde je metoda.
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.