Funkcia VLOOKUP v Exceli hľadá hodnotu v rozsahu buniek a potom vráti hodnotu, ktorá je v rovnakom riadku ako hodnota, ktorú hľadáte.
VLOOKUP, čo znamená „Vertikálne vyhľadávanie“, je vyhľadávacia funkcia, ktorá hľadá hodnotu v stĺpci úplne vľavo (prvý stĺpec) rozsahu a vracia paralelnú hodnotu zo stĺpca napravo. Funkcia VLOOKUP iba vyhľadá (zhora nadol) hodnotu v tabuľke usporiadanej vertikálne.
Povedzme napríklad, že máme zoznam zásob v pracovnom hárku s tabuľkou s názvami položiek, dátumom nákupu, množstvom a cenou. Potom by sme mohli použiť funkciu VLOOKUP v inom pracovnom hárku na extrahovanie množstva a ceny pre určitý názov položky z pracovného hárka inventára.
Funkcia VLOOKUP môže na prvý pohľad vyzerať skľučujúco, ale v skutočnosti je jej použitie celkom jednoduché, keď pochopíte, ako funguje. V tomto návode vám ukážeme, ako používať funkciu VLOOKUP v Exceli.
VLOOKUP Syntax a argumenty
Ak sa chystáte použiť funkciu VLOOKUP, musíte poznať jej syntax a jej argumenty.
Syntax funkcie VLOOKUP:
=VLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,číslo_stĺpca,[vyhľadávanie_rozsahu])Táto funkcia pozostáva zo 4 parametrov alebo argumentov:
- vyhľadávaná_hodnota: Toto určuje hodnotu, ktorú hľadáte v prvom stĺpci daného poľa tabuľky. Hodnota vyhľadávania musí byť vždy úplne vľavo (stĺpec tabuľky vyhľadávania.
- table_array: Toto je tabuľka (rozsah buniek), v ktorej chcete vyhľadať hodnotu. Táto tabuľka (vyhľadávacia tabuľka) môže byť v rovnakom hárku alebo inom hárku alebo dokonca v inom zošite.
- col_index_num: Toto určuje číslo stĺpca poľa tabuľky, ktoré má hodnotu, ktorú chcete extrahovať.
- [vyhľadávanie_rozsahu]: Tento parameter určuje, či chcete extrahovať presnú alebo približnú zhodu. Je to buď PRAVDA, alebo NEPRAVDA, zadajte „NEPRAVDA“, ak chcete presnú hodnotu, alebo zadajte „PRAVDA“, ak súhlasíte s približnou hodnotou.
Použitie funkcie VLOOKUP v Exceli
Pozrime sa, ako používať funkciu VLOOKUP v programe Microsoft Excel.
Základný príklad
Ak chcete použiť funkciu VLOOKUP, musíte si najprv vytvoriť databázu alebo tabuľku (pozri nižšie).
Potom vytvorte tabuľku alebo rozsah, z ktorého chcete vyhľadať, a extrahujte hodnoty z tabuľky vyhľadávania.
Ďalej vyberte bunku, do ktorej chcete extrahovať hodnotu, a zadajte nasledujúci vzorec VLOOKUP. Napríklad, chceme vyhľadať telefónne číslo „Ena“, potom musíme zadať vyhľadávaciu hodnotu ako B13, A2:E10 ako pole tabuľky, 5 ako číslo stĺpca telefónneho čísla a FALSE, aby sme vrátili presnú hodnotu hodnotu. Potom stlačením klávesu „Enter“ dokončite vzorec.
=VLOOKUP(B13;A2:E10;5;FALSE)Rozsah tabuľky nemusíte zadávať ručne, stačí vybrať rozsah alebo tabuľku pomocou myši pre argument table_array. A automaticky sa pridá do argumentu.
Pamätajte, že na to, aby to fungovalo, musí byť vyhľadávacia hodnota v našej tabuľke vyhľadávania úplne vľavo (A2:E10). Lookup_value tiež nemusí byť nevyhnutne v stĺpci A pracovného hárka, musí to byť len stĺpec úplne vľavo v rozsahu, ktorý chcete hľadať.
Vlookup vyzerá správne
Funkcia VLOOKUP sa môže pozerať iba napravo od tabuľky. Hľadá hodnotu v prvom stĺpci tabuľky alebo rozsahu a extrahuje zodpovedajúcu hodnotu zo stĺpca napravo.
Presná zhoda
Funkcia Excel VLOOKUP má dva spôsoby párovania, sú to: presné a približné. Parameter ‚vyhľadávanie_rozsahu‘ vo funkcii VLOOKUP určuje, aký druh vás hľadáte, presný alebo približný.
Ak zadáte range_lookup ako „FALSE“ alebo „0“, vzorec hľadá hodnotu, ktorá sa presne rovná hodnote lookup_value (môže to byť číslo, text alebo dátum).
=VLOOKUP(A9;A2:D5;3;FALSE)
Ak sa v tabuľke nenájde presná zhoda, vráti chybu #N/A. Keď sme sa pokúsili vyhľadať „Japonsko“ a vrátiť jeho zodpovedajúcu hodnotu v stĺpci 4, objaví sa chyba #N/A, pretože v prvom stĺpci tabuľky nie je „Japonsko“.
V poslednom argumente môžete zadať číslo „0“ alebo „FALSE“. Obe znamenajú v Exceli to isté.
Približná zhoda
Niekedy nevyhnutne nepotrebujete presnú zhodu, stačí najlepšia zhoda. V takýchto prípadoch môžete použiť režim približnej zhody. Nastavte posledný argument funkcie na „TRUE“, aby ste našli približnú zhodu. Predvolená hodnota je TRUE, čo znamená, že ak nepridáte posledný argument, funkcia predvolene použije približnú zhodu.
=VLOOKUP(B10;A2:B7;2;PRAVDA)V tomto príklade nepotrebujeme presné skóre, aby sme našli vhodnú známku. Všetko, čo potrebujeme, je, aby boli známky v tomto rozsahu skóre.
Ak funkcia VLOOKUP nájde presnú zhodu, vráti túto hodnotu. Ak vo vyššie uvedenom príklade vzorec nemôže nájsť vyhľadávaciu hodnotu 89 v prvom stĺpci, vráti ďalšiu najväčšiu hodnotu (80).
Prvý zápas
Ak stĺpec úplne vľavo v tabuľke obsahuje duplikáty, funkcia VLOOKUP nájde a vráti prvú zhodu.
Napríklad funkcia VLOOKUP je nakonfigurovaná tak, aby našla priezvisko pre krstné meno „Mia“. Keďže existujú 2 záznamy s krstným menom „Mia“, funkcia vráti priezvisko pre prvý záznam, „Bena“.
Zápas so zástupnými znakmi
Funkcia VLOOKUP vám umožňuje nájsť čiastočnú zhodu na zadanej hodnote pomocou zástupných znakov. Ak chcete nájsť hodnotu, ktorá obsahuje vyhľadávanú hodnotu na ľubovoľnej pozícii, pridajte znak ampersand (&), čím spojíte našu vyhľadávaciu hodnotu so zástupným znakom (*). Pomocou znakov „$“ vytvorte absolútne odkazy na bunky a pridajte zástupný znak „*“ pred alebo za hodnotu vyhľadávania.
V príklade máme len časť vyhľadávacej hodnoty (Vin) v bunke B13. Ak teda chcete vykonať čiastočnú zhodu s danými znakmi, spojte za odkazom na bunku zástupný znak „*“.
=VLOOKUP($B$13&"*",$A$2:$E$10,3;NEPRAVDA)
Viacnásobné vyhľadávanie
Funkcia VLOOKUP vám umožňuje vytvoriť dynamické obojsmerné vyhľadávanie, ktoré sa zhoduje v riadkoch aj v stĺpcoch. V nasledujúcom príklade je funkcia VLOOKUP nastavená na vykonávanie vyhľadávania na základe mena (Mayra) a mesta. Syntax v B14 je:
=VLOOKUP(B13;A2:E10,MATCH(A14;A1:E1;0);0)
Ako VLOOKUP z iného hárku v Exceli
Zvyčajne sa funkcia VLOOKUP používa na vrátenie zodpovedajúcich hodnôt zo samostatného pracovného hárka a zriedka sa používa s údajmi v tom istom hárku.
Ak chcete vyhľadávať z iného hárka Excelu, ale z toho istého zošita, zadajte názov hárka pred table_array s výkričníkom (!).
Ak chcete napríklad vyhľadať hodnotu bunky A2 pracovného hárka „Produkty“ v rozsahu A2:B8 na pracovnom hárku „Ceny položiek“ a vrátiť zodpovedajúcu hodnotu zo stĺpca B:
=VLOOKUP(A2;Ceny položky!$A$2:$C$8,2;NEPRAVDA)Obrázok nižšie zobrazuje tabuľku v pracovnom hárku „Ceny položiek“.
Keď zadáme vzorec VLOOKUP do stĺpca C pracovného hárka „Produkty“, získa zodpovedajúce údaje z pracovného hárka „Ceny položiek“.
Ako VLOOKUP z iného zošita v Exceli
Hodnotu môžete vyhľadať aj v úplne inom zošite. Ak chcete VLOOKUP z iného zošita, musíte názov zošita uzavrieť do hranatých zátvoriek, za ktorým bude nasledovať názov hárka pred tabuľka_pole s výkričníkom (!) (ako je znázornené nižšie).
Napríklad použite tento vzorec na vyhľadanie hodnoty bunky A2 iného pracovného hárka z pracovného hárka s názvom „ItemPrices“ v zošite „Item.xlsx“:
=VLOOKUP(A2,[Item.xls]Ceny položiek!$A$2:$B$8,2;NEPRAVDA)Najprv otvorte oba zošity, potom začnite zadávať vzorec do bunky C2 hárka (Produktový hárok) a keď sa dostanete k argumentu table_array, prejdite do hlavného zošita s údajmi (Item.xlsx) a vyberte rozsah tabuľky. Týmto spôsobom nemusíte ručne zadávať názov zošita a hárka. Zadajte zvyšok argumentov a stlačením klávesu „Enter“ dokončite funkciu.
Aj keď zatvoríte zošit, ktorý obsahuje vyhľadávaciu tabuľku, vzorec VLOOKUP bude naďalej fungovať, ale teraz môžete vidieť celú cestu k zatvorenému zošitu, ako je znázornené na nasledujúcej snímke obrazovky.
Použite funkciu VLOOKUP z pásu kariet Excel
Ak si nepamätáte vzorce, vždy máte prístup k funkcii VLOOKUP z pásu Excel. Ak chcete získať prístup k funkcii VLOOKUP, prejdite na kartu „Vzorce“ v páse programu Excel a kliknite na ikonu „Vyhľadať a referencie“. Potom vyberte možnosť „VLOOKUP“ v spodnej časti rozbaľovacej ponuky.
Potom zadajte argumenty do dialógového okna „Argumenty funkcií“. Potom kliknite na tlačidlo „OK“.
V príklade sme v tabuľke hľadali krstné meno „Sherill“, aby sme vrátili jeho zodpovedajúci stav v stĺpci D.
Dúfame, že ste sa z tohto článku naučili používať funkciu VLOOKUP v Exceli. Ak sa chcete dozvedieť viac o tom, ako používať Excel, pozrite si naše ďalšie články súvisiace s Excelom.