Projekt 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.

• Vysvětlení argumentů krok za krokem
• Syntaxe a argumenty
• Příklady VLOOKUP
  Přesná shoda vs. přibližná shoda   |   Vlookup s více podmínkami
• VLOOKUP Běžné chyby a řešení
  #N/A chyba   |   Chyba #HODNOTA   |   Chyba #REF   |   Nesprávná hodnota

Příklady VLOOKUP .xlsx Chyby VLOOKUP .xlsx

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říklady

Tato část ukazuje několik příkladů, které vám pomohou lépe porozumět funkci SVYHLEDAT.

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.

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 Příjmení sloupec a oddělení sloupec.

1. Přidejte pomocný sloupec nalevo od rozsahu dat a přidejte záhlaví. Viz snímek obrazovky:
   
   
2. 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 Příjmení 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.
3. 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

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 sloupce
  
  Můž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 číslo
  
  Klikně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 číslem
  
  Projekt 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)

  Poznámka: 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ů:

1. 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.
2. 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