Výukový program Excel VLOOKUP pro začátečníky
Charlotte Millerová
⚡ Chytré shrnutí
Tutoriál k funkci VLOOKUP v Excelu vysvětluje, jak vertikální vyhledávání prohledává první sloupec tabulky a vrací odpovídající hodnotu z jiného sloupce. Tato příručka se zabývá syntaxí, přesnými a přibližnými shodami, vyhledáváním napříč tabulkami, běžnými chybami a moderní alternativou k funkci XLOOKUP.
Co je VLOOKUP?
VLOOKUP (V znamená Vertikální) je vestavěná funkce aplikace Excel, která navazuje vztah mezi sloupci v tabulce. Umožňuje vyhledat hodnotu v jednom sloupci a vrátit odpovídající hodnotu z jiného sloupce ve stejném řádku.
Syntaxe a argumenty funkce VLOOKUP
Před použitím funkce VLOOKUP je užitečné porozumět struktuře vzorce. Funkce přijímá čtyři argumenty a dodržuje konzistentní vzorec ve všech verzích Excelu.
- lookup_value — hodnota, kterou chcete najít (odkaz na buňku nebo literál).
- table_array — rozsah buněk obsahující vyhledávací sloupec a sloupec s návratovou hodnotou.
- col_index_num — číslo sloupce v table_array, ze kterého se má vrátit hodnota (1 je sloupec nejvíce vlevo).
- range_lookup — NEPRAVDA pro přesnou shodu, PRAVDA (nebo vynecháno) pro přibližnou shodu u seřazených dat.
důležité: Vyhledávací hodnota musí být v levém sloupci pole table_array a funkce VLOOKUP prohledává pouze zleva doprava.
Použití funkce VLOOKUP
Pokud potřebujete najít konkrétní informace ve velké tabulce nebo opakovaně načíst stejnou hodnotu, funkce VLOOKUP ušetří ve srovnání s ručním filtrováním značné množství času.
Zvažte a Tabulka platů společnosti spravováno finančním týmem. Začnete se známou informací – indexem – a pomocí funkce VLOOKUP načtete neznámou hodnotu.
Například již znáte jméno zaměstnance:
A chcete si vyhledat plat zaměstnance:
Tabulka Excelu pro výše uvedený příklad:
Stáhněte si výše uvedený soubor Excel
Abychom našli neznámý plat zaměstnance, zadáme zaměstnance Code který je již k dispozici.
Použitím funkce VLOOKUP se hodnota platu odpovídající danému zaměstnanci Code se zobrazí automaticky.
Jak používat funkci VLOOKUP v Excelu
Postupujte podle tohoto podrobného návodu k použití funkce VLOOKUP v Excelu:
Krok 1) Přejděte do cílové buňky
Klikněte na buňku, kde chcete zobrazit plat vybraného zaměstnance – v tomto příkladu buňku H3.
Krok 2) Zadejte funkci VLOOKUP =VLOOKUP()
Zadejte funkci do buňky. Začněte znaménkem rovnosti (které Excelu sdělí, že následuje vzorec) a poté klíčovým slovem VLOOKUP: =SVYHLEDAT().
Závorky obsahují sadu argumentů (data, která funkce potřebuje).
Funkce VLOOKUP vyžaduje čtyři argumenty:
Krok 3) První argument — vyhledávací hodnota
První argument je odkaz na buňku pro hodnotu, kterou chcete vyhledat. V tomto případě je to Zaměstnanec Code je vyhledávací hodnota, takže prvním argumentem je H2 – buňka, jejíž obsah by měl Excel hledat.
Krok 4) Druhý argument — tabulkové pole
Toto se vztahuje k bloku hodnot, které se mají prohledávat, v Excelu známému jako pole tabulky nebo vyhledávací tabulka. V našem příkladu vyhledávací tabulka běží z B2 do E25.
POZNÁMKA: Vyhledávací sloupec musí být sloupcem v tabulce nejvíce vlevo.
Krok 5) Třetí argument — col_index_num
Toto určuje funkci VLOOKUP, který sloupec v tabulce obsahuje návratovou hodnotu. Plat zaměstnance se nachází ve čtvrtém sloupci, takže index sloupce je 4.
Krok 6) Čtvrtý argument – přesná nebo přibližná shoda
Posledním argumentem je příznak vyhledávání v rozsahu. Řídí, zda funkce VLOOKUP vrací přesnou nebo přibližnou shodu. V tomto případě chceme přesnou shodu (NEPRAVDA).
- NEPRAVDIVÉ — přesná shoda.
- TRUE — přibližná shoda.
Krok 7) Stiskněte Enter
Stisknutím klávesy Enter dokončete vzorec. Zpočátku se zobrazí chyba, protože žádný zaměstnanec Code již nebyl zadán do H2.
Jakmile zadáte platného zaměstnance Code V buňce H2 vrátí buňka odpovídající plat zaměstnance.
Stručně řečeno, vzorec říká Excelu, že známé hodnoty se nacházejí v levém sloupci dat (Zaměstnanec Code). Funkce VLOOKUP poté prohledá tabulku a vrátí hodnotu čtvrtého sloupce v odpovídajícím řádku – plat zaměstnance.
Tento příklad zahrnoval přesné shody (klíčové slovo FALSE). Následující část vysvětluje přibližné shody.
SVYHLEDAT pro přibližné shody (Klíčové slovo TRUE jako poslední parametr)
Uvažujme scénář, ve kterém tabulka vypočítává slevy pro zákazníky, kteří si nezakoupí přesně desítky nebo stovky položek.
Jak je uvedeno níže, společnost uplatňuje slevy na množství od 1 do 10 000:
Stáhněte si výše uvedený soubor Excel
Zákazník si jen zřídka koupí přesně 100 nebo 1 000 jednotek. Režim přibližné shody umožňuje funkci VLOOKUP najít nejbližší nižší hodnotu, místo aby trvala na přesné číslici. Kroky:
Krok 1) Klikněte na buňku, kam se má funkce VLOOKUP přidat – odkaz na buňku I2.
Krok 2) Do buňky zadejte =VYHLEDAT() a do závorek přidejte argumenty.
Krok 3) Argument 1: Zadejte odkaz na buňku, jejíž hodnota se má porovnat s vyhledávací tabulkou.
Krok 4) Argument 2: Vyberte vyhledávací tabulku – zde sloupce Množství a Sleva.
Krok 5) Argument 3: Zadejte index sloupce ve vyhledávací tabulce, ze kterého se má vrátit odpovídající hodnota.
Krok 6) Argument 4: Nastavte poslední argument na TRUE pro přibližné shody.
Krok 7) Stiskněte klávesu Enter. Vzorec se nyní použije na buňku. Když zadáte libovolné množství, Excel vrátí pásmo slevy na základě přibližné shody.
POZNÁMKA: Pokud čtvrtý argument necháte prázdný, Excel použije výchozí hodnotu TRUE (přibližná shoda). Pro přibližné shody musí být vyhledávací sloupec seřazen vzestupně.
Funkce Vlookup použitá mezi 2 různými listy umístěnými ve stejném sešitu
Nyní si představte sešit se dvěma listy. List 1 uvádí zaměstnance. Code, Jméno a funkce; List 2 uvádí zaměstnance Code a Plat zaměstnance.
LIST 1:
LIST 2:
Stáhněte si výše uvedený soubor Excel
Cílem je konsolidovat všechna data na Listu 1, jak je uvedeno níže:
SVYHLEDAT dokáže agregovat data, takže zaměstnanec Code, Jméno a Plat se zobrazují společně na jednom listu.
Začneme na listu 2, protože poskytuje dva argumenty – zde je sloupec Plat zaměstnance a index sloupce je 2.
Chceme najít plat, který odpovídá každému zaměstnanci Code.
Data sahají od A2 do B25 – to je naše tabulkové pole.
Krok 1) Přepněte na List 1 a zadejte zobrazené nadpisy.
Krok 2) Klikněte na buňku vedle položky Plat zaměstnance – buňka F3 – kam se má vložit vzorec VLOOKUP.
Zadejte funkci VLOOKUP: =VLOOKUP().
Krok 3) Argument 1: Zadejte F2 – buňku obsahující zaměstnance Code aby se shodovaly ve vyhledávací tabulce.
Krok 4) Argument 2: Vyhledávací tabulka se nachází na druhém listu, proto na ni odkazujte s názvem listu: List2!A2:B25.
Krok 5) Argument 3: Zadejte index sloupce ve vyhledávací tabulce, který obsahuje návratovou hodnotu.
Krok 6) Argument 4: Pro přesnou shodu použijte NEPRAVDA, protože chceme přesný plat, který odpovídá každému zaměstnanci. Code.
Krok 7) Stiskněte Enter. Když zadáte zaměstnance Code, buňka vrátí odpovídající plat z Listu 2.
Časté chyby funkce VLOOKUP a jejich opravy
I zkušení uživatelé narážejí na chyby funkce VLOOKUP. Nejčastější chyby a jejich rychlé opravy:
- # N / A — Funkce VLOOKUP nemůže najít hledanou hodnotu. Zkontrolujte, zda se v prvním sloupci pole table_array nenacházejí nadbytečné mezery, neshodné datové typy (čísla uložená jako text) nebo zda hodnota skutečně existuje.
- #REF! — col_index_num je větší než počet sloupců v table_array. Snižte index sloupce nebo rozšiřte rozsah.
- #HODNOTA! — Hodnota col_index_num je menší než 1 nebo je argument neplatný. Zkontrolujte syntaxi vzorce.
- Vrátil se nesprávný výsledek — čtvrtý argument je PRAVDA nebo je vynechán, ale vyhledávací sloupec není seřazen. Přepněte na NEPRAVDA nebo seřaďte sloupec vzestupně.
- Uzamčené reference — při kopírování vzorce dolů používejte absolutní odkazy (například $B$2:$E$25), aby se table_array neposouvala.
VLOOKUP vs. XLOOKUP: Který byste měli použít?
Microsoft představil XLOOKUP v Microsoft 365 a Excel 2021 jako moderní náhrada za funkci VLOOKUP. Odstraňuje několik omezení funkce VLOOKUP a nyní je doporučenou volbou v podporovaných verzích.
| vlastnost | VLOOKUP | XLOOKUP |
|---|---|---|
| Směr hledání | Pouze zleva doprava | Libovolný směr (doleva, doprava, nahoru, dolů) |
| Výchozí typ shody | Přibližné (PRAVDA) | Přesný |
| Manipulace s „if-not-found“ (pokud nenalezen) | Vráceno #N/A | Vestavěný argument if_not_found |
| Index sloupce | Pevně zadané číslo | Odkaz na rozsah návratového sloupce |
| dostupnost | Všechny verze Excelu | Microsoft 365, Excel 2021, Excel pro web |
Kdy zvolit funkci VLOOKUP: Sešit musí běžet v Excelu 2019 nebo starším, jinak udržujete starší vzorce. Kdy zvolit XLOOKUP: Vytváříte nové sešity v moderním Excelu a chcete vyhledávání vlevo, čistší zpracování chyb a přesnou shodu ve výchozím nastavení. Další informace o vyhledávacích funkcích naleznete v Výukové programy k Excelu série.
Závěr
Tři výše uvedené scénáře vysvětlují, jak funkce VLOOKUP funguje pro přesné shody, přibližné shody a křížové odkazy. Procvičujte si ji na vlastních datových sadách, abyste si osvojili plynulost. VLOOKUP zůstává důležitou funkcí v MS-Excel pro efektivní správu dat a XLOOKUP rozšiřuje tuto sadu nástrojů v moderním Excelu.
