Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Funkce Excel VLOOKUP v Excelu vyhledat hodnotu.
Přehled funkcí VLOOKUP
Funkce VLOOKUP Funkce Vlookup znamená vertikální vyhledávání. Vyhledá hodnotu v levém sloupci tabulky. Poté vrátí hodnotu o zadaný počet sloupců napravo od nalezené hodnoty. Je to stejné jako vyhledávání, kromě toho, že vyhledává hodnoty svisle místo vodorovně.
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a argumenty funkce VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Hodnota, kterou chcete vyhledat.
tabulka_pole - Rozsah dat, který obsahuje jak hodnotu, kterou chcete vyhledat, tak hodnotu, kterou má vlookup vrátit. Sloupec obsahující hledané hodnoty musí být sloupec zcela vlevo.
col_index_num - Číslo sloupce datového rozsahu, ze kterého chcete vrátit hodnotu.
range_lookup - Pravda nebo lež. FALSE vyhledá přesnou shodu. TRUE vyhledá nejbližší shodu, která je menší nebo rovna hodnotě lookup_value. Pokud je vybrána hodnota TRUE, musí být sloupec zcela vlevo (vyhledávací sloupec) řazen vzestupně (od nejnižší k nejvyšší).
Co je funkce VLOOKUP?
Jako jedna ze starších funkcí ve světě tabulek slouží funkce VLOOKUP PROTIertikální Vyhledávání. Má několik omezení, která jsou často překonána jinými funkcemi, jako je INDEX/MATCH. Má však tu výhodu, že je jedinou funkcí, která dokáže sama vyhledávat přesné shody. Bývá také jednou z prvních funkcí, o kterých se lidé učí.
Základní příklad
Podívejme se na ukázku dat z klasifikační knihy. Budeme se zabývat několika příklady získávání informací pro konkrétní studenty.
Pokud chceme zjistit, do jaké třídy Bob patří, napsali bychom vzorec:
| 1 | = VLOOKUP („Bob“, A2: C5, 2, FALSE) |
Důležité je mít na paměti, že položka, kterou hledáme (Bob), musí být v prvním sloupci našeho rozsahu vyhledávání (A2: C5). Řekli jsme funkci, že chceme vrátit hodnotu z 2nd sloupec, což je v tomto případě sloupec B. Nakonec jsme naznačili, že chceme provést an přesná shoda umístěním False jako posledního argumentu. Zde bude odpověď „Čtení“.
Boční tip: Jako konečný argument můžete také použít číslo 0 namísto False, protože mají stejnou hodnotu. Někteří lidé tomu dávají přednost, protože se to píše rychleji. Jen vězte, že obojí je přijatelné.
Posunutá data
Abychom přidali nějaké vysvětlení k našemu prvnímu příkladu, vyhledávací položka nemusí být ve sloupci A vaší tabulky, ale pouze v prvním sloupci vašeho rozsahu vyhledávání. Použijme stejnou datovou sadu:
Nyní najdeme známku pro třídu vědy. Náš vzorec by byl
| 1 | = VLOOKUP („Věda“, B2: C5, 2, NEPRAVDA) |
Toto je stále platný vzorec, protože první sloupec našeho rozsahu vyhledávání je sloupec B, kde bude nalezen náš hledaný výraz „Věda“. Vracíme hodnotu z 2nd sloupec rozsahu vyhledávání, což je v tomto případě sloupec C. Odpověď pak zní „A-“.
Použití zástupných znaků
Funkce VLOOKUP podporuje použití zástupných znaků „*“ a „?“ při vyhledávání. Řekněme například, že jsme zapomněli, jak se píše Frankovo jméno, a chtěli jsme jen vyhledat jméno, které začíná na „F“. Mohli bychom napsat vzorec
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
To by dokázalo najít jméno Frank v řádku 5 a poté vrátit hodnotu z 2nd relativní sloupec. V tomto případě bude odpověď „Věda“.
Nepřesná shoda
Většinu času se budete chtít ujistit, že poslední argument ve VLOOKUP je False (nebo 0), abyste získali přesnou shodu. Existuje však několik případů, kdy možná hledáte nepřesnou shodu. Pokud máte seznam seřazených dat, můžete také použít VLOOKUP k vrácení výsledku u položky, která je buď stejná, nebo další nejmenší. To se často používá při řešení rostoucích rozsahů čísel, například v daňové tabulce nebo provizních bonusech.
Řekněme, že chcete najít sazbu daně pro příjem zadaný do buňky D2. Vzorec v D4 může být:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Rozdíl v tomto vzorci je v tom, že náš poslední argument je „Pravda“. V našem konkrétním příkladu vidíme, že když naši jednotlivci vloží příjem ve výši 45 000 USD, budou mít daňovou sazbu 15%.
Poznámka: Ačkoli obvykle požadujeme přesnou shodu s argumentem False, zapomenete zadat 4th ve VLOOKUP, výchozí hodnota je True. To může způsobit neočekávané výsledky, zejména při práci s textovými hodnotami.
Dynamický sloupec
VLOOKUP vyžaduje, abyste zadali argument, ze kterého sloupce chcete vrátit hodnotu, ale příležitost může nastat, když nevíte, kde sloupec bude, nebo chcete svému uživateli umožnit změnit, ze kterého sloupce se má vrátit. V těchto případech může být užitečné použít funkci MATCH k určení čísla sloupce.
Podívejme se znovu na náš příklad klasifikační knihy s některými vstupy v E2 a E4. Abychom získali číslo sloupce, mohli bychom napsat vzorec
| 1 | = MATCH (E2, A1: C1, 0) |
To se pokusí najít přesnou polohu „Grade“ v rozsahu A1: C1. Odpověď bude 3. Když to víme, můžeme to zapojit do funkce VLOOKUP a napsat vzorec v E6 takto:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Funkce MATCH se tedy vyhodnotí na 3 a to řekne VLOOKUP, aby vrátil výsledek z 3rd sloupec v rozsahu A2: C5. Celkově pak získáme požadovaný výsledek „C“. Náš vzorec je nyní dynamický v tom, že můžeme změnit buď sloupec, který se má podívat, nebo název, který se má hledat.
Omezení VLOOKUP
Jak již bylo zmíněno na začátku článku, největším pádem VLOOKUP je, že vyžaduje, aby se hledaný výraz nacházel v nejvíce levém sloupci rozsahu vyhledávání. I když existuje několik fantastických triků, které můžete udělat, abyste to překonali, běžnou alternativou je použití INDEX a MATCH. Tato kombinace vám poskytne větší flexibilitu a někdy může jít dokonce o rychlejší výpočet.
VLOOKUP v Tabulkách Google
Funkce VLOOKUP funguje v Tabulkách Google úplně stejně jako v Excelu:
Další poznámky
Pomocí funkce VLOOKUP proveďte VERTIKÁLNÍ vyhledávání. VLOOKUP hledá přesnou shodu (range_lookup = FALSE) nebo nejbližší shoda, která je stejná nebo menší než hodnota lookup_value (range_lookup = TRUE, pouze číselné hodnoty) v prvním řádku pole_tabulky. Potom vrátí odpovídající hodnotu, n počet řádků pod shodou.
Při použití VLOOKUP k nalezení přesné shody nejprve definujete identifikační hodnotu, kterou chcete hledat jako lookup_value. Tato identifikační hodnota může být SSN, ID zaměstnance, jméno nebo jiný jedinečný identifikátor.
Dále definujete rozsah (nazývaný tabulka_pole), který obsahuje identifikátory v horním řádku a jakékoli hodnoty, které chcete nakonec hledat v řádcích pod ním. DŮLEŽITÉ: Jedinečné identifikátory musí být v horním řádku. Pokud tomu tak není, musíte buď přesunout řádek nahoru, nebo místo VLOOKUP použít MATCH / INDEX.
Za třetí, definujte číslo řádku (řádek_index) z tabulka_pole že se chcete vrátit. Pamatujte, že první řádek obsahující jedinečné identifikátory je řádek 1. Druhý řádek je řádek 2 atd.
Nakonec musíte zadat, zda se má v souboru vyhledávat přesná shoda (FALSE) nebo nejbližší shoda (TRUE) range_lookup. Pokud je vybrána možnost přesné shody a není nalezena přesná shoda, je vrácena chyba (#N/A). Chcete -li, aby vzorec vrátil prázdné nebo „nenalezeno“, nebo jakoukoli jinou hodnotu místo chybové hodnoty (#N/A), použijte funkci VLOŽIT IFERROR.
Chcete -li použít funkci VLOOKUP k vrácení přibližné sady shod: range_lookup = PRAVDA. Tato možnost je k dispozici pouze pro číselné hodnoty. Hodnoty musí být seřazeny vzestupně.
Chcete -li provést horizontální vyhledávání, použijte místo toho funkci HLOOKUP.
Příklady VLOOKUP ve VBA
Ve VBA můžete také použít funkci VLOOKUP. Typ:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Argumenty funkce (lookup_value atd.) Můžete buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.
Zpět na seznam všech funkcí v Excelu
