Funkce VLOOKUP aplikace Excel
Auto Funkce VLOOKUP aplikace Excel je výkonný nástroj, který vám pomůže vyhledat zadanou hodnotu tak, že se svisle shoduje v prvním sloupci tabulky nebo rozsahu a poté vrátí odpovídající hodnotu z jiného sloupce ve stejném řádku. Přestože je funkce VLOOKUP neuvěřitelně užitečná, pro začátečníky může být někdy náročné ji uchopit. Tento tutoriál vám pomůže zvládnout SVYHLEDAT poskytnutím krok za krokem vysvětlení argumentů, užitečné příklady a řešení běžných chyb se můžete setkat při používání funkce VLOOKUP.
Related Videos
Vysvětlení argumentů krok za krokem
Jak je znázorněno na výše uvedeném snímku obrazovky, funkce VLOOKUP se používá k vyhledání e-mailu na základě daného ID čísla. Nyní poskytnu podrobné vysvětlení, jak v tomto příkladu použít SVYHLEDAT tak, že krok za krokem rozeberu každý argument.
Krok 1: Spusťte funkci VLOOKUP
Vyberte buňku (v tomto případě H6) pro výstup výsledku a poté spusťte funkci SVYHLEDAT zadáním následujícího obsahu do Formula Bar.
=VLOOKUP(
Krok 2: Zadejte vyhledávací hodnotu
Nejprve zadejte vyhledávací hodnotu (což je to, co hledáte) ve funkci VLOOKUP. Zde odkazuji na buňku G6, která obsahuje určité ID číslo 1005.
=VLOOKUP(G6
Krok 3: Zadejte pole tabulky
Dále určete rozsah buněk obsahující jak hodnotu, kterou hledáte, tak hodnotu, kterou chcete vrátit. V tomto případě volím rozsah B6:E12. Vzorec nyní vypadá takto:
=VLOOKUP(G6,B6:E12
=VLOOKUP(G6,$B$6:$E$12
Krok 4: Zadejte sloupec, ze kterého chcete vrátit hodnotu
Poté zadejte sloupec, ze kterého chcete vrátit hodnotu.
V tomto příkladu, protože potřebuji vrátit e-mail na základě ID čísla, zde zadám číslo 4, abych řekl VLOOKUP, aby vrátil hodnotu ze čtvrtého sloupce rozsahu dat.
=VLOOKUP(G6,B6:E12,4
Krok 5: Najděte přibližnou nebo přesnou shodu
Nakonec určete, zda hledáte přibližnou shodu nebo přesnou shodu.
- Chcete -li najít přesná shoda, musíte použít NEPRAVDIVÉ jako poslední argument.
- Chcete -li najít přibližná shoda, Použijte TRUE jako poslední argument, nebo jej nechte prázdný.
V tomto příkladu používám pro přesnou shodu hodnotu FALSE. Vzorec nyní vypadá takto:
=VLOOKUP(G6,B6:E12,4,FALSE
Stisknutím klávesy Enter získáte výsledek
Vysvětlením každého argumentu jeden po druhém ve výše uvedeném příkladu je nyní mnohem snazší pochopit syntaxi a argumenty funkce SVYHLEDAT.
Syntaxe a argumenty
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
- Vyhledávací_hodnota (povinné): Hodnota (skutečná hodnota nebo odkaz na buňku), kterou hledáte. Pamatujte, že tato hodnota musí být v prvním sloupci pole table_array.
- Tabulka_pole (povinné): Rozsah buněk obsahuje sloupec vyhledávací hodnoty i sloupec vrácené hodnoty.
- Col_index (povinné): Celé číslo představuje číslo sloupce, který obsahuje vrácenou hodnotu. Začíná číslem 1 pro sloupec zcela vlevo v poli table_array.
- Range_loakup (volitelné): Logická hodnota, která určuje, zda chcete, aby funkce VLOOKUP nalezla přibližnou nebo přesnou shodu.
- Přibližná shoda - Nastavte tento argument na TRUE, 1 nebo nechat být prázdný.
důležitý: Chcete-li najít přibližnou shodu, musí být hodnoty v prvním sloupci pole table_array seřazeny vzestupně pro případ, že funkce VLOOKUP vrátí nesprávný výsledek. - Přesná shoda - Nastavte tento argument na NEPRAVDIVÉ or 0.
- Přibližná shoda - Nastavte tento argument na TRUE, 1 nebo nechat být prázdný.
Příklad 1: Přesná shoda vs. přibližná shoda ve VLOOKUP
Pokud si nejste jisti přesnou a přibližnou shodou při používání funkce SVYHLEDAT, tato část vám může pomoci tento zmatek objasnit.
Přesná shoda ve VLOOKUP
V tomto příkladu najdu odpovídající názvy na základě skóre uvedených v rozsahu E6:E8, takže do buňky F6 zadám následující vzorec a přetáhnu úchyt automatického vyplňování dolů na F8. V tomto vzorci je poslední argument určen jako NEPRAVDIVÉ k provedení vyhledávání přesné shody.
=VLOOKUP(E6,$B$6:$C$12,2,FALSE)
Protože však skóre 98 v prvním sloupci rozsahu dat neexistuje, vrátí funkce VLOOKUP chybový výsledek #N/A.
Přibližná shoda ve VLOOKUP
Stále používáte výše uvedený příklad, pokud změníte poslední argument na TRUE, VLOOKUP provede přibližné vyhledávání shody. Pokud není nalezena žádná shoda, najde další největší hodnotu, která je menší než vyhledávací hodnota, a vrátí odpovídající výsledek.
=VLOOKUP(E6,$B$6:$C$12,2,TRUE)
Protože skóre 98 neexistuje, funkce VLOOKUP najde další největší hodnotu, která je menší než 98, což je 95, a vrátí název skóre 95 jako nejbližší výsledek.
- V tomto případě přibližné shody musí být hodnoty v prvním sloupci pole table_array seřazeny ve vzestupném pořadí. V opačném případě nemusí funkce VLOOKUP vrátit správnou hodnotu.
- Zde jsem uzamkl pole tabulky ($B$6:$C$12) ve funkci VLOOKUP, abych rychle odkazoval na konzistentní sadu dat proti více hodnotám vyhledávání.
Příklad 2: Použijte SVYHLEDAT s více kritérii
Tato část ukazuje, jak používat SVYHLEDAT s více podmínkami v Excelu. Jak je znázorněno na obrázku níže, pokud se snažíte najít plat na základě zadaného jména (v buňce H5) a oddělení (v buňce H6), postupujte podle níže uvedených kroků.
Krok 1: Přidejte pomocný sloupec pro zřetězení hodnot z vyhledávacích sloupců
V tomto případě musíme vytvořit pomocný sloupec pro zřetězení hodnot z Jméno sloupec a oddělení sloupec.
- Přidejte pomocný sloupec nalevo od rozsahu dat a přidejte záhlaví. Viz snímek obrazovky:
- V tomto pomocném sloupci vyberte první buňku pod záhlavím a do pole zadejte následující vzorec Formule bar, a stiskněte tlačítko vstoupit.
=C6&" "&D6
Poznámky: V tomto vzorci používáme ampersand (&) ke spojení textu do dvou sloupců za účelem vytvoření jednoho kusu textu.- C6 je křestní jméno Jméno sloupec ke spojení, D6 je prvním oddělením oddělení sloupec ke spojení.
- Hodnoty těchto dvou buněk jsou zřetězeny mezerou mezi nimi.
- Vyberte tuto výslednou buňku a přetáhněte ji Rukojeť automatického vyplňování dolů, chcete-li tento vzorec použít na další buňky ve stejném sloupci.
Krok 2: Použijte funkci VLOOKUP s danými kritérii
Vyberte buňku, do které chcete vypsat výsledek (zde vyberu I7), zadejte do pole následující vzorec Formule bar, a poté stiskněte tlačítko vstoupit.
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Výsledek
- Pomocný sloupec musí být použit jako první sloupec rozsahu dat.
- Nyní je sloupec plat pátý sloupec rozsahu dat, takže použijeme číslo 5 jako index sloupce ve vzorci.
- Musíme se připojit ke kritériím I5 a I6 (I5& " "&I6) stejným způsobem jako pomocný sloupec a použijte zřetězenou hodnotu jako lookup_value argument ve vzorci.
- Tyto dvě podmínky můžete také vložit přímo do argumentu lookup_value a oddělit je mezerou (pokud jsou podmínky textové, nezapomeňte je uzavřít do dvojitých uvozovek).
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- Lepší alternativa - vyhledávání s více kritérii během několika sekundAuto Vyhledávání více podmínek vlastnost Kutools pro Excel vám může pomoci snadno vyhledávat pomocí více kritérií během několika sekund. Získejte 30denní plnohodnotnou bezplatnou zkušební verzi!
Běžné chyby a řešení VLOOKUP
Tato část uvádí běžné chyby, se kterými se můžete setkat při používání funkce VLOOKUP, a poskytuje řešení, jak je opravit.
#N/A chyba se vrací
Nejčastější chybou funkce SVYHLEDAT je chyba #N/A, což znamená, že Excel nemohl najít hodnotu, kterou jste hledali. Zde je několik důvodů, proč může funkce VLOOKUP vrátit chybu #N/A.
Důvod 1: Vyhledávací hodnota není v prvním sloupci pole table_array
Jedním z omezení aplikace Excel SVYHLEDAT je, že vám umožňuje dívat se pouze zleva doprava. Vyhledávací hodnoty tedy musí být v prvním sloupci pole table_array.
Jak je znázorněno na níže uvedeném snímku obrazovky, chci vrátit jméno založené na dané pracovní pozici. Zde je vyhledávací hodnota (manažer prodeje) je ve druhém sloupci pole table_array a vrácená hodnota je nalevo od vyhledávacího sloupce, takže funkce SVYHLEDAT vrátí chybu #N/A.
Řešení
Tuto chybu můžete opravit pomocí libovolného z následujících řešení.
- Uspořádejte sloupceMůžete změnit uspořádání sloupců tak, aby se vyhledávací sloupec umístil do prvního sloupce tabulky table_array.
- Používejte funkce INDEX a MATCH společněZde používáme funkce INDEX a MATCH společně jako alternativu k VLOOKUP k vyřešení tohoto problému.
=INDEX(B6:B12,MATCH(F6,C6:C12,0))
- Použijte funkci XLOOKUP (k dispozici v Excel 365, Excel 2021 a novějších verzích)
=XLOOKUP(F6,C6:C12,B6:B12)
Důvod 2: Vyhledávací hodnota není ve vyhledávacím sloupci nalezena (přesná shoda)
Jedním z nejčastějších důvodů, proč funkce SVYHLEDAT vrací chybu #N/A, je, že hledaná hodnota nebyla nalezena.
Jak je ukázáno v příkladu níže, najdeme jméno na základě daného skóre 98 v E6. Toto skóre však v prvním sloupci rozsahu dat neexistuje, takže funkce SVYHLEDAT vrátí výsledek chyby #N/A.
Řešení
Chcete-li tuto chybu opravit, můžete zkusit jedno z následujících řešení.
- Pokud chcete, aby funkce VLOOKUP vyhledá další největší hodnotu, která je menší než vyhledávací hodnota, změňte poslední argument NEPRAVDIVÉ (přesná shoda) do TRUE (přibližná shoda). Další informace viz Příklad 1: Přesná shoda vs. přibližná shoda pomocí funkce VLOOKUP.
- Chcete-li se vyhnout změně posledního argumentu a získat připomenutí v případě, že vyhledávací hodnota nebude nalezena, můžete do funkce IFERROR zahrnout funkci VLOOKUP:
=IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")
Důvod 3: Vyhledávací hodnota je menší než nejmenší hodnota ve vyhledávacím sloupci (přibližná shoda)
Jak je znázorněno na snímku obrazovky níže, provádíte přibližné vyhledávání shody. Hodnota, kterou hledáte (v tomto případě ID číslo 1001), je menší než nejmenší hodnota 1002 ve vyhledávacím sloupci, proto funkce SVYHLEDAT vrátí chybu #N/A.
Řešení
Zde jsou pro vás dvě řešení.
- Ujistěte se, že vyhledávací hodnota je větší nebo rovna nejmenší hodnotě ve vyhledávacím sloupci.
- Pokud chcete, aby vám Excel připomněl, že vyhledávací hodnota nebyla nalezena, stačí vložit funkci SVYHLEDAT do funkce IFERROR následovně:
=IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")
Důvod 4: Čísla jsou formátována jako text
Jak můžete vidět na snímku obrazovky níže, výsledek chyby #N/A v tomto příkladu je způsoben nesouladem typu dat mezi vyhledávací buňkou (G6) a vyhledávacím sloupcem (B6:B12) původní tabulky. Zde je hodnota v G6 číslo a hodnoty v rozsahu B6:B12 jsou čísla formátovaná jako text.
Řešení
Chcete-li tento problém vyřešit, musíte převést hodnotu vyhledávání zpět na číslo. Zde jsou pro vás dvě metody.
- Použijte funkci Převést na čísloKlikněte na buňku, ve které chcete převést text na číslo, vyberte toto tlačítko vedle buňky a poté vyberte Převést na číslo.
- Použijte šikovný nástroj pro dávkový převod mezi textem a číslemAuto Převádějte mezi textem a číslem vlastnost Kutools pro Excel vám pomůže snadno převést rozsah buněk z textu na číslo a naopak. Získejte 30denní plnohodnotnou bezplatnou zkušební verzi!
Důvod 5: Table_array není konstantní při přetahování vzorce SVYHLEDAT do jiných buněk
Jak je znázorněno na obrázku níže, v E6 a E7 jsou dvě vyhledávací hodnoty. Po získání prvního výsledku v F6 přetáhněte vzorec SVYHLEDAT z buňky F6 do F7, vrátí se výsledek chyby #N/A. Je to proto, že odkazy na buňky (B6:C12) jsou ve výchozím nastavení relativní a upravují se při pohybu v řádcích dolů. Pole tabulky bylo přesunuto dolů do B7:C13, které již neobsahuje skóre vyhledávání 73.
Řešení
Musíte uzamknout pole tabulky, aby bylo konstantní přidáním a $ podepsat před řádky a sloupce v odkazech na buňky. Chcete-li se dozvědět více o absolutní referenci v Excelu, podívejte se na tento tutoriál: Excel absolutní reference (jak vytvořit a používat).
Chyba #VALUE se vrací
Následující podmínky mohou způsobit, že funkce VLOOKUP vrátí chybový výsledek #HODNOTA.
Důvod 1: Hodnota vyhledávání přesahuje 255 znaků
Jak je znázorněno na obrázku níže, vyhledávací hodnota v buňce H4 přesahuje 255 znaků, takže funkce SVYHLEDAT vrátí výsledek chyby #HODNOTA.
Řešení
Chcete-li toto omezení obejít, můžete použít jinou vyhledávací funkci, která zvládne delší řetězce. Zkuste jeden z následujících vzorců.
- INDEX a MATCH:
=INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
- Funkce XLOOKUP (k dispozici v Excel 365, Excel 2021 a novějších verzích):
=XLOOKUP(H4,B5:B11,E5:E11)
Důvod 2: Argument col_index je menší než 1
Index sloupce určuje číslo sloupce v poli tabulky, které obsahuje hodnotu, kterou chcete vrátit. Tento argument musí být kladné číslo, které odpovídá platnému sloupci v poli tabulky.
Pokud zadáte index sloupce, který je menší než 1 (tj. nula nebo záporný), funkce VLOOKUP nebude moci najít sloupec v poli tabulky.
Řešení
Chcete-li tento problém vyřešit, ujistěte se, že argument indexu sloupce ve vzorci SVYHLEDAT je kladné číslo, které odpovídá platnému sloupci v poli tabulky.
Chyba #REF se vrací
Tato část uvádí jeden důvod, proč funkce VLOOKUP vrací chybu #REF, a poskytuje řešení tohoto problému.
Důvod: Argument col_index je větší než počet sloupců
Jak můžete vidět na obrázku níže, pole tabulky má pouze 4 sloupce. Index sloupce, který jste zadali ve vzorci SVYHLEDAT, je však 5, což je větší počet než počet sloupců v poli tabulky. V důsledku toho nebude funkce VLOOKUP schopna sloupec najít a nakonec vrátí chybu #REF.
Řešení
- Zadejte správné číslo sloupce Ujistěte se, že argument indexu sloupce ve vzorci SVYHLEDAT je číslo, které odpovídá platnému sloupci v poli tabulky.
- Automaticky získat číslo sloupce na základě zadaného záhlaví sloupce Pokud tabulka obsahuje mnoho sloupců, můžete mít potíže s určením správného indexového čísla sloupce. Zde můžete vnořit funkci MATCH do funkce SVYHLEDAT, abyste našli pozici sloupce na základě určitého záhlaví sloupce.
=VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
Pozor: Ve výše uvedeném vzorci je MATCH("E-mail",B5:E5; 0) funkce se používá k získání čísla sloupce "Email" v časovém rozsahu B6:E12. Zde je výsledkem 4, která se používá jako col_index ve funkci VLOOKUP.
Byla vrácena nesprávná hodnota
Pokud zjistíte, že VLOOKUP nevrací správný výsledek, může to být způsobeno následujícími důvody
Důvod 1: Vyhledávací sloupec není seřazen vzestupně
Pokud jste nastavili poslední argument na TRUE (nebo nechal prázdné) pro přibližnou shodu a vyhledávací sloupec není seřazen vzestupně, může být výsledná hodnota nesprávná.
Řešení
Seřazení vyhledávacího sloupce ve vzestupném pořadí vám může pomoci vyřešit tento problém. Chcete-li to provést, postupujte podle následujících kroků:
- Vyberte datové buňky ve vyhledávacím sloupci a přejděte na Data klepněte na kartu Řadit od nejmenšího k největšímu v Třídit a filtrovat skupina.
- v Varování řazení dialogové okno vyberte Rozbalte výběr a klepněte na tlačítko OK.
Důvod 2: Sloupec je vložen nebo odstraněn
Jak je znázorněno na obrázku níže, hodnota, kterou jsem původně chtěl vrátit, je ve čtvrtém sloupci pole tabulky, takže číslo col_index zadávám jako 4. Po vložení nového sloupce se výsledný sloupec stane pátým sloupcem tabulky pole, což způsobí, že funkce VLOOKUP vrátí výsledek z nesprávného sloupce.
Řešení
Zde jsou pro vás dvě řešení.
- Indexové číslo sloupce můžete ručně změnit tak, aby odpovídalo pozici vráceného sloupce. Zde by se měl vzorec změnit na:
=VLOOKUP(H6,B6:F12,5,FALSE)
- Pokud chcete vždy vrátit výsledek z určitého sloupce, jako je v tomto příkladu sloupec Email. Následující vzorec může pomoci automaticky porovnat index sloupce na základě daného záhlaví sloupce bez ohledu na to, zda jsou sloupce vloženy nebo odstraněny z pole tabulky.
=VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)
Další funkční poznámky
- VLOOKUP hledá pouze hodnotu zleva doprava.
Vyhledávací hodnota je ve sloupci zcela vlevo a výsledná hodnota by měla být v libovolném sloupci napravo od vyhledávacího sloupce. - Pokud ponecháte poslední argument prázdný, funkce SVYHLEDAT standardně použije přibližnou shodu.
- VLOOKUP provádí vyhledávání bez ohledu na malá a velká písmena.
- V případě více shod vrátí funkce VLOOKUP pouze první shodu, kterou najde v poli tabulky, na základě pořadí řádků v poli tabulky.
Související články
20+ příkladů VLOOKUP pro začátečníky a pokročilé uživatele aplikace Excel
Tento tutoriál ukazuje, jak používat funkci Vlookup v Excelu, s desítkami základních a pokročilých příkladů krok za krokem.
VLOOKUP zprava doleva
Pokud chcete vyhledat konkrétní hodnotu v jakémkoli jiném sloupci a vrátit relativní hodnotu doleva, metody v tomto kurzu vám mohou pomoci tento úkol splnit.
Vlookup zdola nahoru
Tento výukový program poskytuje dvě metody, které vám pomohou vyhledat odpovídající hodnotu zdola nahoru.
Proveďte vyhledávání rozlišující malá a velká písmena
Chcete-li v aplikaci Excel provést SVYHLEDAT s rozlišováním malých a velkých písmen, metoda v tomto tutoriálu vám může pomoci.
VLOOKUP zachová formátování zdroje
Tento kurz poskytuje metodu, která vám pomůže zachovat veškeré formátování výsledné buňky při provádění Vlookup v Excelu.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Obsah
- Related Videos
- Vysvětlení argumentů krok za krokem
- Syntaxe a argumenty
- Příklady VLOOKUP
- Přesná shoda vs. přibližná shoda
- SVYHLEDAT s více podmínkami
- Běžné chyby a řešení
- #N/A chyba
- Chyba #HODNOTA
- Chyba #REF
- Nesprávná hodnota
- Další funkční poznámky
- Související články
- Nejlepší kancelářské nástroje produktivity
- Komentáře