Pokud už nějakou dobu čtete tipy pro Excel, vždy jste našli někoho, kdo mluví o používání funkcí Excel INDEX () & MATCH () namísto Excel VLOOKUP. Když jsem mluvil sám za sebe, bylo vždy příliš těžké pokusit se zvládnout DVA nové funkce současně. Ale je to skvělý trik. Dejte mi pět minut a pokusím se to vysvětlit jednoduchou angličtinou.
30sekundová recenze VLOOKUP
Řekněme, že máte tabulku záznamů zaměstnanců. První sloupec je číslo zaměstnance a zbývající sloupce jsou různá data o zaměstnanci. Kdykoli máte v listu číslo zaměstnance, můžete pomocí VLOOKUP vrátit konkrétní datum o zaměstnanci. Syntaxe je VLOOKUP (hodnota, rozsah dat, číslo sloupce, FALSE). V aplikaci Excel říká: „Přejít na datový rozsah. Najděte řádek, který má (hodnotu) v prvním sloupci datového rozsahu. Vraťte (sloupec.) Tu hodnotu z tohoto řádku. Jakmile se vám to podaří, je to velmi jednoduché a výkonné.
Problém
Jednoho dne máte situaci, kdy máte jméno zaměstnance, ale potřebujete číslo zaměstnance. Na následujícím obrázku máte jméno v A10 a potřebujete najít číslo zaměstnance v B10.
Když je pole klíče napravo od dat, která chcete načíst, VLOOKUP nebude fungovat. Pokud by pouze VLOOKUP přijal -1 jako číslo sloupce, nebyl by žádný problém. Ale není. Jedním běžným řešením je dočasně vložit nový sloupec A, zkopírovat sloupec jmen do nového sloupce A, naplnit VLOOKUP, Vložit speciální hodnoty a poté dočasný sloupec A odstranit.
Navrhnu vám, abyste se chopili výzvy a pokusili se použít tuto jednokrokovou metodu. Ano, budete muset na několik týdnů připevnit vzorec na zeď, ale to jste s VLOOKUP udělali už dávno, že?
Myslím, že důvod, proč je to tak obtížné, spočívá v tom, že používáte dvě funkce, které jste pravděpodobně nikdy předtím nepoužívali. Dovolte mi, abych to rozdělil na dvě části.
Nejprve je tu funkce INDEX (). Toto je strašně pojmenovaná funkce. Když někdo řekne „index“, nevyčaruje mi to nic podobného tomu, co tato funkce dělá. Index vyžaduje tři argumenty.
=INDEX(data range, row number, column number)
V angličtině přejde Excel do datového rozsahu a vrátí vám hodnotu v průsečíku tého řádku (číslo řádku) a tého sloupce (číslo sloupce). Hej, přemýšlej o tom - to je docela jednoduché, že? =INDEX($A$2:$C$6,4,2)vám dá hodnotu v B5.
Uplatnění index (), aby náš problém, můžete zjistit, že k vrácení počtu zaměstnanců z rozsahu, měli byste použít toto: =INDEX($A$2:$A$6,?,1). Ve skutečnosti se tento kousek zdá tak triviální, že se zdá zbytečný. Když však otazník nahradíte funkcí MATCH (), máte řešení.
Zde je syntaxe:
=MATCH(Value, Single-column data range, FALSE)
Říká aplikaci Excel: „Hledejte v datovém rozsahu a řekněte mi relativní číslo řádku, kde najdete shodu pro (data). Chcete-li tedy zjistit, který řádek má zaměstnance v A10, použijte =MATCH(A10,$B$2:$B$6,FALSE). Ano, je to složitější než Index , ale mělo by to být hned v uličce profesionálů VLOOKUP. Pokud A10 obsahuje „Miller, Bob“, pak tento ZÁPAS vrátí, že je ve 3. řadě rozsahu B2: B6.
Tady to je - funkce MATCH () řekne funkci indexu, který řádek má hledat - máte hotovo. Vezměte funkci Index, nahraďte náš otazník funkcí MATCH a nyní můžete udělat ekvivalent VLOOKUPů, když pole klíče není v levém sloupci. Zde je funkce, kterou lze použít:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Lepicí poznámka na mé zdi to ve skutečnosti ukazuje jako dva řádky. Nejprve jsem napsal vysvětlení pro MATCH (). Níže jsem napsal vysvětlení pro INDEX (). Pak jsem nakreslil tvar trychtýře mezi těmito dvěma, což naznačuje, že funkce MATCH () klesá na 2. argument funkce INDEX ().
Prvních párkrát, co jsem musel udělat jeden z nich, jsem byl v pokušení jen zabouchnout nový dočasný sloupec A, ale prošel jsem bolestí, že jsem to udělal místo toho. Je rychlejší a vyžaduje méně manipulace. Až si tedy příště budete přát dát do funkce VLOOKUP záporné číslo, vyzkoušejte tuto podivnou kombinaci INDEXU a ZÁPASU k vyřešení vašich problémů.
