V tomto článku se naučíme Jak extrahovat sloupcový index z tabulky v Excelu.
Scénář:
Při práci s dlouho rozptýlenými daty mnohokrát potřebujeme data nejprve vyčistit, než na nich budeme pracovat. Trvá to dlouho a chtěli jste extrahovat nějaké dotazy. K extrahování dat obvykle používáte funkci VLOOKUP. Ale když máme dlouhá data s mnoha sloupcovými poli, pak je to nepořádné, protože funkce VLOOKUP vyžaduje správný sloupcový index jako číslo, jinak vzorec vrátí chybu. Právě zde je vysvětlení vzorce pro tento druh problému.
Jak problém vyřešit?
K tomu použijeme funkci MATCH. Pokud jste o této funkci ještě neslyšeli, zvykněte si. Je to vyhledávací funkce aplikace Excel, která vrací Index vyhledávané hodnoty v poli. Zde potřebujeme získat indexové číslo názvu sloupce. Poté přejdeme k dalšímu kroku Jak vyhledávat hodnoty v tabulkách pomocí funkce MATCH. Níže je obecný vzorec
Obecný vzorec:
| = MATCH (název_sloupce, záhlaví_tabulky, 0) |
název_sloupce: vyhledávací hodnota
table_header: pole záhlaví tabulky
0: vyhledejte přesnou shodu
Příklad:
Pochopit to všechno může být matoucí. Pojďme pochopit vzorec s vysvětlením a příkladem. Zde máme datovou tabulku v Listu 1 ($ A $ 1: $ U $ 9995). Záhlaví tabulky tedy máme jako A1: U1 (první řádek tabulky).
Podívejme se na níže uvedený vzorec, který se má použít v tabulce.
Použijte vzorec
| = MATCH (C4, Sheet1! $ A $ 1: $ U $ 1,0) |
Vysvětlení:
- Funkce MATCH vyhledává hodnotu v buňce C4 „ID objednávky“
- List1! $ A $ 1: $ U $ 1 je argument vyhledávacího pole.
- Pro vyhledání přesné shody je zadán argument 0.
Jak vidíte, sloupcový index ID objednávky v tabulce je 2. Je však velmi nepříjemné používat záhlaví tabulky jako úplnou zkratku, takže pro pole záhlaví tabulky používáme pojmenovaný rozsah. Další informace o pojmenovaných rozsazích najdete zde. Pojmenovaný rozsah použitý pro záhlaví tabulky (List1! $ A $ 1: $ U $ 1) je „záhlaví“.
Použijte vzorec.
| = MATCH (C4, záhlaví, 0) |
Zde výše uvedený snímek vysvětluje dvě věci. První je Sloupcový index stavu v tabulce je 11 a druhý se jmenuje rozsah "záhlaví" funguje dobře. Zkopírujte vzorec pro zbývající názvy sloupců pomocí Ctrl + D nebo přetažením dolů z pravého dolního okraje použité buňky.
Zde máme všechny sloupce Index. Nyní můžeme použít jako vstup do funkce VLOOKUP, jak je znázorněno níže.
Rejstřík MATCH ve funkci VLOOKUP:
Nyní máme řešení, jak získat sloupcový index tabulky. Vzorec můžeme použít jako vstup do funkce VLOOKUP. Potřebujeme například název produktu zakoupený jménem zákazníka „Pete Kriz“.
Použijte vzorec:
| = VLOOKUP (D10, tabulka, MATCH (E9, záhlaví, 0), 0) |
Poznámka: ujistěte se, že vyhledávací hodnota v D10 (Pete Kriz) musí být v prvním sloupci tabulky.
Jak vidíte, vzorec vrací název produktu ze jména zákazníka v tabulce. Obecně nepoužíváme MATCH s funkcí VLOOKUP, protože vyhledávací hodnota musí být v prvním sloupci, což se sotva stane. Používáme tedy kombinaci funkce INDEX a MATCH. Další informace o tom, jak vyhledat hodnotu pomocí funkce INDEX a MATCH.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Vzorec funguje pro text i čísla.
- Funkce vrací chybu #NA, pokud argument vyhledávacího pole funkce MATCH nemá stejnou délku pole tabulky.
- Vzorec vrací chybu, pokud lookup_value neodpovídá hodnotě v tabulce lookup_array.
- Funkce odpovídá přesné hodnotě, protože argument typu shody pro funkci MATCH je 0.
- Použijte argument -1 pro méně než, 0 pro přesnou shodu a 1 pro větší než shodu vyhledávání.
- 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 extrahovat sloupcový index z tabulky v aplikaci Excel. Zde najdete další články o hodnotách vyhledávání v Excelu a funkcích Excelu 2019. 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 : 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 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.
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 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.
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.