Funkcia VLOOKUP v Exceli 4x inak – špeciality

Chcete si napísať do životopisu, že viete Excel? Prečítajte si našu sériu článkov o tom ako používať funkciu VLOOKUP (toto je posledná časť venovaná špeciálnym použitiam VLOOKUPu), príďte na kurzy aspoň Excel I. a Excel II. (VLOOKUP prberáme na Excel III.) a smelo môžete. Na prijímacom pohovore budete v pohode, pretože budete v Exceli viac-menej doma. Inak si ho radšej neuvádzajte ako jednu zo svojich znalostí – v niektorých podnikoch sa pýtajú pri overovaní vedomostí veľmi konkrétne na Excel i jeho funkcie. Možno sa vám zíde vedieť aj ako je to so špeciálnym používaním VLOOKUPu.

VLOOKUP a špecialitky

Hoci to nebýva často, stretnete sa v praxi s prípadom, keď vidíte, že by ste sa vedeli k výsledku rýchlo prepracovať pomocou funkcie VLOOKUP, ale… niečo tomu chýba, výsledok pokrivkáva, nie je celkom správny. Podobne ako v príklade na zistenie obratu firiem, ktorých názov si presne nepamätáte, z predchádzajúcej časti tejto série.

Alebo vám VLOOKUP vracia chybu, hoci pri kontrole vidíte, že hľadaný údaj sa v stĺpci zjavne nachádza. Alebo keď nevie VLOOKUP nájsť údaj kvôli rozdielu medzi malými a veľkými písmenami. Alebo…

Na pripomenutie, tu nájdete nápovedu autorského tímu Excelu o tom, čo je funkcia VLOOKUP.
Pozrime sa v tejto časti na niekoľko špecialitiek, o ktorých sa bežne v súvislosti s funkciou VLOOKUP nedozviete.

Ako riešiť, keď VLOOKUP vypisuje chybu

Úloha:
Zistiť názov obchodného reťazca prislúchajúceho obchodníkovi, ktorého meno sme zapísali do béžovej bunky.

Ak zapíšeme do béžovej bunky meno obchodníka, ktoré sa v zozname nenachádza, alebo ho napíšeme nesprávne, funkcia VLOOKUP vypíše, že hľadaný údaj je nedostupný. Excel nevie, že Michal je Mišo. V bunke vypíše chybové hlásenie #NEDOSTUPNÝ a hotovo.
Používateľa nepoteší omrežované chybové hlásenie už len preto, že ruší estetiku vytvorených tabuliek. Tento oznam však bežnému užívateľovi nehovorí nič a už vôbec o ďalších možnostiach ako sa dopracovať k výsledku.

Ak pracujeme s číslami, môže takáto chybová hláška viesť dokonca k zreťazeniu chýb. V prípade, že VLOOKUP hľadanú hodnotu nenájde, vráti chybové hlásenie a ďalšie funkcie (napríklad SUM pre súčet stĺpca) nemôžu uskutočniť výpočty s chybovými bunkami. Tiež vypíšu chybu.

Excel našťastie ponúka viacero možností ako upraviť zápis tak, aby výsledok dopadol úplne inak. Tu je jedna z nich.
Pomocou ďalšej funkcie doplníme VLOOKUP tak, aby aj menej skúsený používateľ nehádzal po výsledku #NEDOSTUPNÝ flintu do žita.

V prípade, že chceme chybové hlásenie obísť a nahradiť ho zmysluplným oznamom, vnoríme v našom príklade funkciu VLOOKUP do funkcie IFERROR a nastavíme oznam, ktorý sa objaví v prípade zlyhania pri vyhľadávaní.

Funkcia IFERROR je tým pomocníkom, ktorý vypíše zvolené oznámenie namiesto chyby. V slovenskej verzii Excelu považuje funkcia za chybové hlásenia výsledky #NEDOSTUPNÝ, #HODNOTA!, #ODKAZ!, #DELENIENULOU!, #ČÍSLO!, #NÁZOV? a #NEPLATNÝ!.
Zápis funkcie vyzerá nasleodvne:

=IFERROR(hodnota;hodnota_ak_chyba)

Ak funkcia zistí, že hodnota je chyba, vypíše druhý argument. Inak prvý. Takto môžeme chybové hlášky Excelu nahradiť ľubovoľným textom alebo číslom. ktoré uvedieme v druhom argumente funkcie. Napríklad „Údaj nie je k dispozícii“, „Nemáme!“, alebo aj zložitejším „Hľadaný údaj nie je k dispozícii. Skontrolujte správnosť zápisu hľadaného mena a zdrojových údajov.“ V prípade číselných hodnôt sa používa – ak to má zmysel – nahradenie chyby nulou.

Takto jednoducho (alebo zložito) môže vyzerať zápis vzorca pre náš príklad:

=IFERROR(VLOOKUP(F3;$B$3:$C$14;2;FALSE);“Nie je k dispozícii.“)

alebo

=IFERROR(VLOOKUP(F3;$B$3:$C$14;2;FALSE);“Hľadaný údaj nie je k dispozícii. Skontrolujte správnosť zápisu hľadaného mena a zdrojových údajov.“)

Funkcia IFERROR skontroluje, či je hodnota vrátená prvým argumentom (v tomto prípade funkciou VLOOKUP) chyba, alebo nie. Ak nie je, vypíše výsledok hľadania VLOOKUPom, ak je, v bunke sa objaví obsah druhého argumentu.

Čo však, ak…

VLOOKUP vypíše chybu, hoci sa hľadaný údaj v tabuľke nachádza?

Vidíte na vlastné oči, že v tabuľke sa hľadaný údaj „Mišo“ nachádza, ale funkcia VLOOKUP vytrvalo vypisuje chybu.
Príčina býva prozaická. To, čo vidíte, nie je to, čo vidíte.
Stáva sa 😀

Keď importujete údaje z databázy, alebo ich získate ako výstup z nejakého programu či od niekoho iného, neviete presne ako sú v skutočnosti zapísané. Na pohľad môžu byť v poriadku, ale v skutočnosti… v bunkách sa občas nachádzajú znaky, ktoré sa javia ako prázdne, no sú tam. Ak máte takéto „medzery“ pred, za alebo medzi hodnotami, ktoré VLOOKUPom vyhľadávate, pre VLOOKUP to nikdy nebude presná zhoda, preto vypíše #NEDOSTUPNÝ. Tak ako na príklade na obrázku.

Úloha:
Chceme, aby Excel vypísal názov obchodného reťazca prislúchajúceho obchodníkovi, ktorého meno sme zapísali do béžovej bunky.

Hoci v bunke B5 jasne vidíme hľadaný výraz „Mišo“, VLOOKUP ho nenašiel.
Čo s tým?

V prípade, že VLOOKUP nefunguje kvôli nadbytočným medzerám v bunke, je riešením funkcia TRIM. Táto funkcia odstráni znaky medzery z textu okrem jednotlivých medzier medzi slovami (dve a viac medzier medzi slovami nahradí jednou medzerou). Môžeme ju použiť pri riešení našej úlohy dvoma spôsobmi.

Prvý spôsob – vytvoríme nový stĺpec. Obsah buniek v novom stĺpci budú tvoriť výsledky funkcie TRIM, ktorá zabezpečí odstránenie všetkých nadbytočných medzier. V novom stĺpci budeme následne vyhľadávať pomocou funkcie VLOOKUP už medzi „očistenými“ menami obchodníkov.

Pri tomto spôsobe použijeme vzorec s funkciou VLOOKUP nasledovne:

=VLOOKUP(G3;B3:D167;3;FALSE)

VLOOKUP bude vyhľadávať meno z bunky G3 (v našom prípade „Mišo“) v prvom stĺpci v oblasti B3:D167 a keď ho nájde, vypíše obsah bunky z tretieho stĺpca na riadku, kde sa vyskytuje Mišo. A je to.

Iný spôsob použitia funkcie TRIM je vzorec vnorený priamo do funkcie VLOOKUP. Tento spôsob poskytne správny výsledok bez vytvárania pomocného stĺpca. Zápis vzorca vyzerá trochu inak:

=VLOOKUP(F3;TRIM(B3:C167);2;FALSE)

Pozor, pretože ak tento vzorec vložíme klávesom Enter ako zvyčajne, nebude fungovať. Je to maticový vzorec. Preto je potrebné pri zapisovaní do bunky použiť kombináciu klávesov Ctrl + Shift + Enter. A už to funguje!

Poznámka:
V prípade, že problém pre VLOOKUP vytvárajú medzery, je TRIM postačujúce riešenie. Je však dobré vedieť, že medzinárodne používaná tabuľka kódovania znakov Unicode obsahuje ešte iné neviditeľné znaky. Napríklad znak medzery nazývaný „pevná medzera“ (desiatková hodnota 160, v HTML kóde  ), ktorý sa pomerne často vyskytuje najmä na webe. Používa sa na zalamovanie predčasne ukončených riadkov v odseku. Pevnú medzeru, či iné netlačiteľné znaky funkcia TRIM neodstraňuje.
Problematike odstraňovania takýchto znakov a postprocesingu importovaných údajov funkciami Excelu CODE, CLEAN, TRIM, SUBSTITUTE, REPLACE a i. sa venujeme podrobnejšie v časti kurzu Excel IV. – práca s veľkým množstvom dát a databázy.

Pozrime sa teraz na inú špecialitku, s ktorou sa môžeme pri používaní funkcie VLOOKUP stretnúť.

Ako zobraziť celý riadok tabuľky vyhľadaný funkciou VLOOKUP

Úloha:
Potrebujeme priradiť k priezvisku zamestnanca všetky údaje, ktoré sa nachádzajú v tabuľke zoznamu zamestnancov.

Keď použijeme funkciu VLOOKUP, štandardne dostaneme jeden údaj zo stĺpca špecifikovaného tretím argumentom v zápise funkcie. Ak potrebujeme priradiť vyhľadávanému údaju viaceré, alebo všetky údaje z riadka v zdrojovej tabuľke, použijeme jednoduchú fintu – funkciu VLOOKUP zapíšeme použitím kombinovaného adresovania s pridaním funkcie COLUMN a skopírujeme ju do buniek riadka, kde sa majú zobraziť cieľové údaje.

Vzorec zapísaný do prvej bunky C18 v našom príklade bude vyzerať takto:

=VLOOKUP($B18;$B$2:$T$13;COLUMN(A1)+1;FALSE)

Prvý argument funkcie VLOOKUP bude odkaz na béžovú bunky, do ktorej píšeme vyhľadávané priezvisko zamestnanca.
Druhý argument je rozsah údajov, v ktorom prebieha vyhľadávanie. Keďže ho budeme kopírovať, ja potrebné fixovať jeho pozíciu.

Vyššie uvedený vzorec vypíše do bunky C18 meno prislúchajúce k hľadanému priezvisku.

V ďalšom kroku vzorec v bunke skopírujeme potiahnutím za úchytku v pravom dolnom rohu smerom doprava až po bunku T18. Vzhľadom na zápis z fixovaným stĺpcom vyhľadávanej hodnoty a použitie funkcie COLUMN skopírovaný vzorec postupne vypíše do vedľajších buniek údaje z príslušných stĺpcov na riadku s hľadaným priezviskom.

Keďže VLOOKUP vypisuje nenaformátované hodnoty, je potrebné ešte výstupné bunky naformátovať (najmä bunky E18 a G18 s dátumom).

Tip: Vo funkcii COLUMN môžeme v tomto prípade v prvej bunke priamo zapísať odkaz na bunku susedného stĺpca (B1) a nemusíme vrátenú hodnotu zväčšovať o 1.

VLOOKUP a špeciality na obrazovke

Toto bola trojukážka chuťoviek, ktoré môžete pri používaní funkcie VLOOKUP objaviť. Existuje ešte viacero iných kombinácií s inými funkciami, no nie sú tak časté ako tu uvedené. Veríme, že vám seriál o používaní funkcie VLOOKUP trochu pomôže k tomu, aby ste funkcii rozumeli a používali ju v pohode.

VLOOKUP sa v praxi používa pomerne často, lebo s ním človek veľa dokáže, avšak má svoje limity a mušky. O niektorých sme napísali v tejto sérii, a o praktických využitiach sa dozviete u nás na kurze Excel III. Na kurze sa VLOOKUPu venujeme na príkladoch tabuliek z praxe podrobnejšie. Príďte si rozšíriť vedomosti z Excelu a využite možnosť spýtať sa lektora, čo vám nie je na VLOOKUPe jasné… radi vysvetlíme 😉

Pomohol vám tento článok? Myslíte, že by pomohol aj niekomu vo vašom okolí? Zdieľajte ho pokojne na FB
(tlačidlo Zdieľať pod nadpisom), IG, LI, alebo naň prepošlite svojim známym odkaz. Autor sa neurazí 🙂