Pro ty z vás, kteří se dobře orientují v Excelu, jste pravděpodobně velmi dobře obeznámeni s funkcí VLOOKUP. Funkce VLOOKUPse používá k nalezení hodnoty v jiné buňce na základě nějakého shodného textu ve stejném řádku.
Pokud jste stále v VLOOKUP, můžete se podívat na můj předchozí příspěvek na jak používat VLOOKUP v Excelu.
Jak moc silný, jak je,
Tento článek vám ukáže omezení, kde VLOOKUPnelze použít a zavést v Excelu jinou funkci nazvanou INDEX-MATCH, která může problém vyřešit.
INDEX MATCH Příklad Excelu
Použití následujících Příklad tabulky Excel, máme seznam majitelů aut a jejich jméno. V tomto příkladu se pokusíme chytit ID vozuna základě modelu vozuuvedeného u více vlastníků, jak je uvedeno níže:
In_content_1 all: [300x250] / dfp: [640x360]->
Na samostatném listu nazvaném CarTypemáme jednoduchou databázi aut s ID, Model autaa Color
S tímto nastavením tabulky Funkce VLOOKUPmůže fungovat, pouze pokud jsou data, která chceme načíst, umístěna ve sloupci napravo od toho, co se snažíme shodovat (pole Model automobilu).
Jinými slovy, s touto strukturou tabulky, protože se ji snažíme přizpůsobit na základě modelu automobilu, můžeme získat pouze informace Barva(Ne ID, protože sloupec IDje umístěn vlevo od sloupce Model auta.)
Je to proto, že u VLOOKUP se musí vyhledávací hodnota objevit v prvním sloupci a vyhledávací sloupce musí být vpravo. V našem příkladu není splněna žádná z těchto podmínek.
Dobrou zprávou je, že INDEX-MATCHnám k tomu pomůže. V praxi to ve skutečnosti kombinuje dvě funkce Excelu, které mohou pracovat samostatně: funkce INDEXa funkce MATCH.
Pro účely tohoto článku však budeme hovořit pouze o kombinaci obou s cílem replikovat funkci VLOOKUP
Vzorec se může zdát trochu dlouhý a na první pohled zastrašující. Jakmile ji však několikrát použijete, naučíte se syntaxi podle srdce.
Toto je úplný vzorec v našem příkladu:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Zde je rozpis pro každou sekci
= INDEX (- „=“označuje začátek vzorce v buňce a INDEXje první část funkce Excelu, kterou používáme.
CarType! $ A $ 2: $ A $ 5- sloupce na listu CarType, kde jsou obsažena data, která bychom chtěli načíst. V tomto příkladu IDkaždého modelu automobilu.
MATCH (- druhá část funkce Excelu, která používáme.
B4- Buňka, která obsahuje hledaný text, který používáme (Model vozu) .
CarType! $ B $ 2: $ B $ 5- Sloupce v listu CarTypes údaji, které použijeme pro porovnání s hledaným textem.
0))- Označuje, že hledaný text se musí přesně shodovat s textem v odpovídajícím sloupci (tj. CarType! $ B $ 2: $ B $ 5). Pokud není nalezena přesná shoda, vrátí se vzorec #N/A
Poznámka: pamatujte na dvojitou závorku na konci této funkce “))”a čárky mezi argumenty.
Osobně jsem se vzdálil od VLOOKUP a nyní používám INDEX-MATCH, protože je schopen děláte víc než VLOOKUP.
Funkce INDEX-MATCHmají také další výhody ve srovnání s VLOOKUP
Když pracujeme s velkými datovými soubory, kde samotný výpočet může trvat mnoho času díky mnoha funkcím VLOOKUP, zjistíte, že jakmile nahradíte všechny z těchto vzorců s INDEX-MATCH bude celkový výpočet počítán rychleji.
Pokud naše referenční tabulka obsahuje klíčový text, který chceme prohledat ve sloupci Ca data, která potřebujeme získat, jsou v sloupec AQ, budeme muset vědět / spočítat, kolik sloupců je mezi sloupcem C a sloupcem AQ při použití VLOOKUPu.
Pomocí funkcí INDEX-MATCH můžeme přímo vybrat indexový sloupec (tj. sloupec AQ), kde musíme získat data a vybrat sloupec, který má být uzavřen (tj sloupec C).
VLOOKUP je dnes docela běžný, ale málo vědět o společném používání funkcí INDEX-MATCH.
Delší řetězec ve funkci INDEX-MATCH vám pomůže vypadat jako odborník na zpracování složitých a pokročilých funkcí Excelu. Užijte si to!