Funkcia VLOOKUP v Exceli – tri praktické príklady

Príklad 1:
Na samostatných hárkoch zošita máme po mesiacoch napr. predaje tovarov (na hárkoch môžu byť rôzne údaje za oddelenia, divízie, podniky, kvartálne predaje a pod.). V našom príklade ovocie. Potrebujeme údaje predajov z jednotlivých mesiacov prehľadne zobraziť v jednej tabuľke a tiež zistiť a porovnať ich mesačné súčty.

Nižšie rozpísané riešenie príkladu má viacero modifikácii – podľa typu zdrojovej tabuľky.
Zdrojová tabuľka môže byť jednoduchý rozsah údajov (náš prípad predajov za január), formátovaná tabuľka Excel (predaje za február), alebo pomenovaný rozsah údajov (predaje za marec). Pozrime sa ako použiť VLOOKUP pri riešení tohto príkladu a jeho modifikácie v jednotlivých prípadoch.

Pomocou funkcie VLOOKUP môžeme údaje jednoducho „vytiahnuť“ zo zdrojových tabuliek s predajmi, ktoré sú na hárkoch Jan, Feb, Mar a umiestniť ich do jednej tabuľky. Riešenie predpokladá, že v zdrojových tabuľkách sú tie isté produkty, rovnako nazvané, ktoré majú byť zobrazené vo výslednej tabuľke. Nemusia byť v rovnakom poradí, nesmú však obsahovať duplicity.

Zápis funkcie pre vyplnenie predajov za prvú položku – ananás – do výslednej tabuľky z januára by vyzeral v bežnej tabuľke nasledovne:

=VLOOKUP(B14;Jan!$B$14:$C$20;2;FALSE)

V prípade, že je naša výsledná tabuľka formátovaná pomocou nástroja Tabuľka (na karte Vložiť) ako tabuľka Excel, bude zápis vyzerať takto:

=VLOOKUP([@Položka];Jan!$B$14:$C$20;2;FALSE)

Funkcia VLOOKUP bude hľadať obsah bunky B14 (ananás) na hárku „Jan“ v oblasti $B$14:$C$20. Keď hľadaný údaj nájde, zobrazí tretí argument funkcie VLOOKUP obsah stĺpca 2 zdrojovej tabuľky (predaný počet ks).

VLOOKUP bude vyzerať trochu inak pre februárové predaje ananásu. Zdrojové údaje za február sú zapísané v tabuľke Excel, ktorej sme dali názov „Predaj_feb“, preto bude druhý argument funkcie VLOOKUP vyzerať nasledovne:

=VLOOKUP([@Položka];Predaj_feb;2;FALSE)

Ak budeme vzorec s funkciou písať priamo do bunky, Excel pri písaní prvých písmen názvu prehľadávanej tabuľky ponúkne všetky názvy tabuliek Excel a pomenovaných oblastí, ktoré obsahujú písaný reťazec znakov (viď obrázok). Stačí označiť správny názov a vložiť ho dvojklikom myši alebo klávesom TAB.

Po dopísaní zvyšných dvoch argumentov funkcie VLOOKUP vyhľadá obsah bunky B14 (ananás) vo formátovanej tabuľke Excel s názvom „Predaj_feb“. Ak sú zdrojové údaje zapísané v tabuľke Excel, nemusíme špecifikovať meno hárka, pretože Excel sám nájde umiestnenie tabuľky v zošite.

Po nájdení ananásu v tabuľke predajov zobrazí vo výslednej tabuľke tretí argument funkcie VLOOKUP obsah zo stĺpca 2 prehľadávanej tabuľky (predaný počet ks) – ako v predchádzajúcom prípade.

Údaje o predajoch za marec sú zapísané v oblasti, ktorú sme po označení pomenovali v poli názvov ako „Predaj_mar“. Pri písaní vzorca s funkciou priamo do bunky Excel ponúka všetky názvy tabuliek a pomenovaných oblastí s napísaným reťazcom znakov (všimnite si rozdiel v ikonke tabuľky Excel a pomenovanej oblasti). Vyberieme a vložíme do vzorca našu pomenovanú oblasť.

Pre marec bude vyzerať zápis podobne ako v predchádzajúcom prípade:

=VLOOKUP([@Položka];Predaj_mar;2;FALSE)

VLOOKUP bude hľadať ananás v pomenovanej oblasti s názvom „Predaj_mar“. Tak ako pri excelovskej tabuľke nemusíme špecifikovať meno hárka, pretože Excel si nájde umiestnenie pomenovanej oblasti v zošite. Po vyhľadaní ananásu v prehľadávanej oblasti zobrazí v bunke s funkciou obsah stĺpca 2 (predaný počet ks).

Po dorátaní súčtov stĺpcov dostaneme tabuľku ako na obrázku nižšie. Ak použijeme na riadku súčtu funkciu SUM alebo klávesovú skratku ľavý Alt + =, Excel vloží do bunky funkciu SUBTOTAL(109;…), ktorú používa pre výpočet súčtu v tabuľke Excel.

Riešenie pre pokročilejších
V prípade, že naša výsledná tabuľka nie je formátovaná tabuľka Excel, pri použití kombinácie funkcií VLOOKUP a INDIRECT nie je potrebné písať pre každý stĺpec novú funkciu VLOOKUP. V priloženom súbore nájdete riešenie príkladu s hárkami 1, 2 a 3, na ktorých sú všetky zdrojové údaje vo formátovaných tabuľkách Excel. Vo výsledku dostaneme tú istú tabuľku pomocou jediného zápisu.

Vzorec bude vyzerať takto:

=VLOOKUP($B3;INDIRECT(„Predaj_“&C$2); 2;FALSE)

Pri kopírovaní vzorca môžete použiť dvojklik a ťahanie za zelený štvorček, alebo Ctrl-C a Ctrl-V 🙂

Ako skontrolovať výskyt údajov funkciou VLOOKUP

Príklad 2
Máme za úlohu skontrolovať a vyhodnotiť, či sú predané tovary ešte k dispozícii v sklade, alebo sú vypredané. Zdrojové údaje o vypredaných produktoch sú uvedené v prvom prípade v rozsahu údajov (rú) a v druhom prípade v Tabuľke Excel (et).

Existuje viacero riešení tejto úlohy. Použijeme kombináciu funkcií IF a VLOOKUP.

Vzorec zapísaný do bunky C3 v našom riešení príkladu by mohol vyzerať takto:

=IF((VLOOKUP(B3; $E$3:$E$6; 1; FALSE))=B3;“vypredané“;“áno“)

Funkcia VLOOKUP vyhľadáva obsah bunky B3 (ananás) v rozsahu údajov E3:E6. Vyhľadáva presne (argument FALSE). Keď nájde, vráti hodnotu „ananás“. To znamená, že ananás je vypredaný a pomocou funkcie IF vo vzorci hore môžeme zobraziť odpoveď „vypredané“.
Lenže toto riešenie má háčik – ak sa údaj v tabuľke vypredaných nenachádza, čo je práve prípad nášho ananásu, VLOOKUP vráti chybové hlásenie #NEDOSTUPNÝ (v anglickej verzii #N/A).

Preto funkciou ISERROR (alebo ISNA) zabezpečíme výstup funkcie VLOOKUP v podobe TRUE/FALSE.

Upravený vzorec bude potom vyzerať takto:

=IF(ISERROR(VLOOKUP(B3; $E$3:$E$6; 1; FALSE));“áno“;“vypredané“)

Keď VLOOKUP textový údaj „ananás“ v tabuľke predaných produktov nájde, vráti hodnotu „ananás“, ISERROR následne vráti na výstupe TRUE (teda ananás je vypredaný), inak vypíše FALSE (ananás je na sklade). S tým už môžeme ďalej rozhodovať funkciou IF o spôsobe výpisu. Funkcia IF premení výstup TRUE na výpis v bunke „vypredaný“, výstup FALSE na výpis „áno“ (je na sklade).

Pri použití zdrojových údajov o predanom ovocí v tabuľke Excel, ktorú sme pomenovali „Predané“,  bude zápis vyzerať takto:

=IF(ISERROR(VLOOKUP(B3; Predané; 1; FALSE));“áno“;“vypredané“)

Výsledok sa objaví tak ako vidno na obrázkoch.

Ako iné riešenie môžeme použiť kombináciu funkcií IF, ISNA a VLOOKUP. Tiež je možné použiť napr. IF, IFERROR a VLOOKUP, za zmienku stojí i použitie funkcie VLOOKUP v kombinácii s novšou funkciou SWITCH (a ISNA) –  kompletné vzorce nájdete v zošite na stiahnutie.

ISERROR alebo ISNA?

Aký bude vo funkčnosti rozdiel, ak namiesto funkcie ISERROR použijeme funkciu ISNA?

Obe funkcie ISERROR aj ISNA slúžia na detekciu chýb v Exceli, ale líšia sa v rozsahu chýb, ktoré dokážu zachytiť.

ISERROR zachytáva všetky typy chýb v Exceli, to znamená, že vracia TRUE pri ktorejkoľvek z uvedených chýb:

• #NEDOSTUPNÝ (#N/A),
• #HODNOTA! (#VALUE!),
• #ODKAZ! (#REF!),
• #DELENIE_NULOU! (#DIV/0!),
• #ČÍSLO! (#NUM!),
• #NÁZOV? (#NAME?),
• #NULA! (#NULL!).

Je to všeobecnejšia funkcia na kontrolu chýb.

ISNA reaguje výhradne na chybu #NEDOSTUPNÝ (#N/A – Not Available). Vracia TRUE iba pri chybe #NEDOSTUPNÝ (#N/A), pri všetkých ostatných chybách vracia FALSE. Je špecializovaná na situácie, keď hodnota nie je k dispozícii (napríklad pri našom nedostupnom výsledku hľadania VLOOKUP bude fungovať).

Ak sa produkt nenájde, funkcia ISERROR(…) vráti TRUE, funkcia ISNA(…) vráti TRUE – tu sa vo výsledku zhodujú.

V prípade, že nastane iná chyba (napr. #ODKAZ! kvôli nesprávnemu rozsahu), ISERROR(…) vráti TRUE, teda chybu indikuje, avšak ISNA(…) vráti FALSE – akoby to chyba nebola…

Ako získať viacero údajov z tabuliek do jednej pomocou VLOOKUP

Príklad 3
Chceme vytvoriť súhrnnú tabuľku odbytu jednotlivých produktov po krajoch s tržbami. V ďalšej tabuľke chceme vidieť súhrn objednávok s produktami a tržbami za objednávky. Zdrojové údaje máme na rôznych hárkoch členené po krajoch – obsahujú prehľad objednávok, ich obsah a ceny. 

Podobne ako v predchádzajúcom príklade, existuje viacero spôsobov riešenia tejto úlohy.
V predchádzajúcom príklade bola funkcia VLOOKUP vnorená vo funkcii IF, tentoraz bude IF vnorený vo funkcii VLOOKUP. Takto necháme Excel na základe podmienky rozhodovať, v ktorej tabuľke bude funkcia VLOOKUP vyhľadávať (predpokladáme, že hľadané údaje sa vyskytujú v každej tabuľke iba raz). Výsledok optimalizujeme pomocou funkcie IFERROR pre prípady, keď bude výsledok vyhľadávania neúspešný.

Vzorec zapísaný do bunky C3 v tomto riešení príkladu bude vyzerať vo finále takto:

=IFERROR(VLOOKUP($B3;IF(C$2=“východ“;predaj_východ;IF(C$2=“stred“;predaj_stred;IF(C$2=“západ“;predaj_západ)));2;FALSE);“nepredáva sa“)

Oblasti výskytu vyhľadávaného argumentu sme definovali ako pomenované oblasti rôzne podľa krajov – predaj_východ, predaj_stred, predaj_západ.

Funkcii VLOOKUP v druhom argumente definujeme oblasť, kde má vyhľadávať prvý argument (B3). Keďže vyhľadávaný údaj sa bude nachádzať v rôznych tabuľkách podľa krajov, funkciou IF určíme pomocou údaja v hlavičke, na ktorom hárku, presnejšie v ktorej pomenovanej oblasti má funkcia VLOOKUP vyhľadávanie vykonať. Rozhodovacia časť vzorca vyzerá takto:

IF(C$2=“východ“; predaj_východ; IF(C$2=“stred“; predaj_stred; IF(C$2=“západ“; predaj_západ)))

Keď sa bude v hlavičke stĺpca nachádzať text „východ“, VLOOKUP bude prehľadávať pomenovanú oblasť „predaj_východ“, keď bude v hlavičke text „stred“, bude prehľadávať oblasť „predaj_stred“ atď.
Po nájdení vyhľadávanej položky vypíše obsah jej susedného stĺpca vpravo.

Výsledky vyhľadávania pomocou funkcie VLOOKUP sú dodatočne upravené aj pre prípady neúspešného vyhľadávania vnorením do funkcie IFERROR. Keď VLOOKUP produkt nenájde ani v jednej tabuľke, funkcia IFERROR zabezpečí vypísanie textu „nepredáva sa“.

Takto po skopírovaní vzorca do ostatných buniek tabuľky vyzerá výsledok:

VLOOKUP, IFERROR a COLUMNS

V prvom riešení použijeme funkciu VLOOKUP spolu s IFERROR a COLUMNS, ktoré sú dostupné vo verziách Excel minimálne od roku 2007.

Vytvoríme vzorec, ktorý bude postupne prehľadávať tri hárky (západ → stred → východ) a po nájdení vráti zodpovedajúci stĺpec. Ak hodnotu nenájde v žiadnom hárku, zobrazí „nenájdené“.

Do bunky C3 preto zapíšeme vzorec tak, aby funkcia VLOOKUP vyhľadávala obsah bunky B3  (číslo objednávky) postupne na hárku „západ → stred → východ“ v oblasti $B$3:D$7. Keď hľadaný údaj nájde, vypíše z prehľadávanej tabuľky obsah stĺpca B, pri skopírovaní vzorca do ďalšieho stĺpca vypíše z prehľadávanej tabuľky obsah stĺpca C.
A ak hľadaný obsah bunky B3 nenájde ani v jednej z troch tabuliek, vypíše „nenájdené“.

=IFERROR(VLOOKUP($B3;západ!$B$3:D$7; COLUMNS($B$2:C$2);FALSE);IFERROR(VLOOKUP($B3;stred!$B$3:$D$7;COLUMNS($B$2:C$2);FALSE);IFERROR(VLOOKUP($B3;východ!$B$3:$D$7;COLUMNS($B$2:C$2);FALSE);“nenájdené“)))

Vyzerá to zložito, ale keď sa pozriete lepšie, vidíte len trojité vnorenie toho istého zápisu so zmenou názvu prehľadávaného hárka. Tento zápis bude ešte dlhší, ak budú údaje na viac než troch hárkoch. Preto si v nasledujúcom zápise ukážeme vzorec upravený pomocou funkcie INDIRECT, ktorá umožní jednoducho prehľadať všetky relevantné hárky uvedené v zozname na samostatnom hárku.

VLOOKUP, IFERROR a INDIRECT

V druhom prípade budeme tú istú úlohu riešiť funkciami VLOOKUP, IFERROR a COLUMNS, ktoré doplníme o INDIRECT. Pre prípady nenájdených objednávok použijeme vo vzorci ešte funkciu IFNA.

Toto je dynamická verzia predchádzajúceho riešenia. Excel bude hľadať hodnotu v hárkoch, ktorých názvy sú uložené v bunke, nie pevne zapísané vo vzorci. Ak ju nenájde v žiadnom uvedenom hárku, zobrazí „nenájdené“.

Do bunky C3 zapíšeme vzorec tak, aby funkcia VLOOKUP vyhľadávala obsah bunky B3  (číslo objednávky) postupne na rôznych hárkoch podľa nasmerovania funkciou INDIRECT z hárku „hárky“, Ten obsahuje zoznam hárkov určených na prehľadávanie.
Keď VLOOKUP hľadaný údaj nájde, vypíše z prehľadávanej tabuľky obsah druhého stĺpca v poradí. V prípade, že hľadaný obsah bunky B3 nenájde ani v jednej z tabuliek, vnorenie do funkcie IFNA zabezpečí, že vypíše „nenájdené“.

=IFNA(IFERROR(VLOOKUP($B3;INDIRECT(„‚“&hárky!B$3&“‚!B:D“);2;0); IFERROR(VLOOKUP($B3;INDIRECT(„‚“&hárky!B$4&“‚!B:D“);2;0); VLOOKUP($B3;INDIRECT(„‚“&hárky!B$5&“‚!B:D“);2;0)));“nenájdené“)

Tento vzorec je možné s prihliadnutím na použitie funkcie COLUMNS v predchádzajúcom riešení upraviť na kopírovanie (nebude potrebné prepísať vo funkcii VLOOKUP stĺpec s hodnotou objednávky z 2 na 3) takto:

=IFNA(IFERROR(VLOOKUP($B3;INDIRECT(„‚“&hárky!B$3&“‚!B:D“);COLUMNS($B$2:C2);0);  IFERROR(VLOOKUP($B3;INDIRECT(„‚“&hárky!B$4&“‚!B:D“);COLUMNS($B$2:C2);0); VLOOKUP($B3;INDIRECT(„‚“&hárky!B$5&“‚!B:D“);COLUMNS($B$2:C2);0)));“nenájdené“)

Teraz je možné vzorec kopírovať aj napravo aj nadol!
Výhody tohto prístupu:

• žiadne úpravy vzorcov – jeden vzorec pre všetky stĺpce
• prehľadnosť – zoznam používaných hárkov na jednom mieste
• flexibilita – nová tabuľka údajov na nový hárok, stačí zmeniť názov hárka v zozname

Všetky riešenia si môžete stiahnuť v samostatnom excelovskom zošite priamo z článku a pozrieť sa, vyskúšať vo vlastnom Exceli ako fungujú funkcie v nich použité.

Poznámka:
vzorce uvedené v tomto texte nemusia pri skopírovaní fungovať kvôli rozdielu medzi slovenskými a anglickými úvodzovkami, použite vzorce zo stiahnutého zošita