Funkcia VLOOKUP v Exceli 4x inak – meniny

Hoci pri riešení úplne bežných úloh sa s ňou stretneme v praxi zriedkavejšie, funkcia VLOOKUP patrí dlhé roky medzi najznámejšie funkcie Excelu. Dokonca je tak populárna, že sa v niektorých podnikoch na ňu pýtajú už na pohovore, pretože ju používajú aj na personálnom. Použitie v praxi je naozaj veľmi široké, zvlášť ak k nej pridáme ešte nejakú ďalšiu funkciu… Pri poznaní a splnení pár jednoduchých podmienok umožňuje vyhľadávanie žiadaných údajov v tabuľkách rôznymi spôsobmi. Ako ju používať, si ukážeme na príkladoch v sérii článkov.

Kedy má meniny?

Kedy má meniny Anna, Marek, Martin, Stanislava? Kedy Miroslava, Michal, Juraj, Monika? A kedy Irina? Občas potrebujeme zistiť dátum preto, že máme blízkych, priateľov, alebo kolegov s takým menom. Alebo naopak, chceme zistiť, kedy majú naši blízki, známi meniny… Stalo sa vám, že vás v práci trklo a pri pohľade na kyticu v rukách kolegyne ste potrebovali bleskovo zistiť, kto má dnes meniny? Alebo kto má meniny zajtra? Prípadne kto má v slovenskom kalendári meniny 29.4.? 🙂 BTW – vraj je to najhľadanejší dátum. Let’s go!

Jasne, napíšeme do Googlu a je to. Google ponúkne 9140 možností a odpoveď skôr či neskôr nájdeme, ale… Keby ešte Google spravil prehľad viacerých mien, ktoré nás zaujímajú… a odpovedal hneď po napísaní mena alebo dátumu, to by bolo! A ešte správne! Chceme to všetko zistiť bez listovania v kalendári?

Vieme si pomôcť tabuľkou v Exceli s použitím funkcie VLOOKUP. Pre tých, ktorých zaujíma iba výsledok, potrebujú náhodou len odpoveď na svoju otázku ohľadom menín – riešenie nájdete v tabuľke v závere článku. Použité údaje sú podľa kalendária schváleného Ministerstvom kultúry SR…
Ostatným, ktorí chcú porozumieť funkcii VLOOKUP (a spol.), urobí možno radosť nasledujúci popis použitia na praktickom príklade hľadania odpovedí na otázky týkajúce sa mena. Ako zvyčajne, s čerešničkou na konci 🙂

Funkcia VLOOKUP

VLOOKUP – príklad

V príklade budeme mať v jednom stĺpci dátumy a v ostatných troch mená (v slovenskom kalendári máme 460 mien, dní v roku „len“ 365, z toho štyri bezmenné, teda máme aj dni, keď majú sviatok tri mená). Jednoduchý príklad, ktorému sa budeme venovať v tejto časti, je vyhľadanie zodpovedajúceho údaja v tabuľke na riadku.
Od Excelu chceme, aby nám po napísaní dátumu ukázal meno oslávenca, resp. oslávencov. To je prvá úloha.
Druhá úloha je opačná – chceme, aby Excel k napísanému menu našiel dátum, kedy má dotyčný meniny.
A doplníme to o zobrazenie – kto má meniny v aktuálny deň, dnes.

Aby funkcia VLOOKUP spoľahlivo fungovala, vyžaduje správny zápis. Ako argumenty uvádzame:

V slovenskom Exceli budú argumenty funkcie oddelené bodkočiarkami a zápis do bunky bude vyzerať obecne takto:

=VLOOKUP(ČO;KDE;PORADIE stĺpca, v ktorom sa nachádza výsledný údaj;[0 alebo 1]*)

Tajomstvo úspešného použitia funkcie VLOOKUP spočíva vo vhodnom usporiadaní stĺpcov v rozsahoch/tabuľkách, s ktorými pracujeme. V našom prípade potrebujeme tabuľku usporiadať tak, aby sa údaj, podľa ktorého hľadáme (ČO je dátum menín), nachádzal vľavo od údaja, ktorý chceme vyhľadať (meno). V tabuľke budeme mať v prvom stĺpci dátumy a usporiadame ich vzostupne.**

Na obrázku sa v bunke G3 zobrazuje zápis vzorca s funkciou VLOOKUP, ktorý sa nachádza v bunke D3.
Riadok vzorcov zobrazuje obsah bunky F3, v ktorej je navyše pridaná funkcia IF. Ak funkcia VLOOKUP totiž meno v stĺpci nenájde, vypíše údaj 0, s použitím IFu nám vypíše prázdnu bunku („“). Ako vidno v poslednom stĺpci zdrojového kalendária, mená nie sú pri každom dátume, takže nula by sa vyskytovala často…

Poznámky:
* posledný údaj je v zápise funkcie v hranatých zátvorkách uvedený len preto, že je nepovinný, do bunky v Exceli hranaté zátvorky nepíšeme
** ak by neboli usporiadané vzostupne, alebo by bol niektorý dátum duplikovaný, Excel nebude pracovať správne – preto nie je tabuľka vytvorená v dvoch stĺpcoch s opakovaním dátumu a priradením iného mena, ale mená sú v troch stĺpcoch vedľa seba – dátum je tak jedinečný

Funkcia VLOOKUP nám už hľadá podľa ľubovoľného dátumu meno.  Takisto ju môžeme použiť pri vyhľadaní mena, ktoré prislúcha k aktuálnemu dňu otvorenia súboru. Kto má dnes (v deň otvorenia súboru) meniny, uvidíme v zelenom poli – stačilo do bunky C7 zapísať bezargumentovú funkciu =TODAY() 🙂

Funkcia MATCH

VLOOKUP nám pomohol poľahky nájsť a priradiť k dátumu meno, pretože dátumy boli usporiadané vzostupne. Ako však riešiť situáciu, keď potrebujeme v tabuľke/rozsahu hľadať a priraďovať k stĺpcu, ktorý z rôznych dôvodov nemôžeme usporiadať? To je náš prípad, keď chceme vyhľadávať podľa mena – nemôžeme tabuľku preusporiadať, pretože potom by nefungovalo predchádzajúce riešenie…

Na riešenie druhej úlohy – priradiť k menu dátum menín – nie je funkcia VLOOKUP vhodná. Použijeme inú trochu podobnú funkciu. Funkcia MATCH vyhľadáva určený údaj v definovanej oblasti buniek a bežne vráti relatívnu pozíciu údaja – koľká v poradí je bunka s hľadaným údajom. V kombinácii s funkciou INDEX ju vieme použiť niekedy efektívnejšie ako VLOOKUP. V našom prípade vráti zo susedného stĺpca hľadaný dátum. Tým, že pracujeme vždy iba s dvoma stĺpcami, Excel pomocou MATCH nájde a zobrazí hľadaný dátum aj bez použitia funkcie INDEX 🙂

Pri testovaní vzorca zistíme, že funkcia MATCH v prípade, že meno v prvom, druhom, ani v treťom stĺpci nenájde (keď ho napíšeme v nesprávnom tvare – napr. Peťo, alebo keď nie je vôbec v slovenskom kalendári), vypíše chybovú hlášku #NEDOSTUPNÝ. Preto MATCH doplníme funkciou IFERROR, ktorá v prípade, že MATCH meno nenájde, vypíše niečo kultivovanejšie. Napríklad „chybne napísané, alebo nie je v slovenskom kalendári“ 🙂

Kedy má meniny?

Kedy má meniny moja láska? A kedy má moja láska narodeniny?
Najmä muži na výročné dátumy zabúdajú… Nemusia, stačí Excel 😀

Keď máme tabuľku, nie je problém do nej doplniť ešte nejakú tú čerešničku. Meniny zistíme poľahky napísaním mena svojej lásky do vyhľadávacej bunky C5. Ak meno nenapíšeme správne alebo sa nenachádza v slovenskom kalendári, v bunke vedľa sa objaví oznámenie.
Ako pripomienku si dátum sviatku svojej lásky môžeme prepísať do bunky C9. Pokojne si tam môžeme napísať aj dátum narodenín. Odpočítadlo dní do sviatku je nastavené tak, že 14 dní pred sviatkom bunka zmení farebnú výplň a písmo na ružovo-červeno.

Ak chcete vedieť ako na to, stiahnite si súbor a pozrite do zákulisia vzorcov a nastavení bunky cez podmienené formátovanie.

Záver

Z trojice vyhľadávacích funkcií VLOOKUP, HLOOKUP a LOOKUP venujeme tento mini-seriál VLOOKUPu, pretože sa z uvedenej trojice vyhľadávacích funkcií používa najčastejšie. VLOOKUP (V-vertikálne) prehľadáva stĺpce. Dvojička funkcie, HLOOKUP (H-horizontálne) sa v praxi používa zriedkavejšie, spôsob použitia je podobný ako pri funkcii VLOOKUP. Novinka – ešte existuje jedna vyhľadávacia funkcia z rodiny LOOKUPov – je to nová funkcia od roku 2019. XLOOKUP funguje v ľubovoľnom smere a dáva pekné výsledky podľa predvoleného nastavenia, čo uľahčuje používanie v porovnaní s jej známejšími predchodcami.

Chcete rozumieť tomu ako vznikajú a fungujú rôzne tabuľky tak, aby naozaj poslúchali?
Naučte sa pracovať s Excelom viac ako len začiatočník. Funkcie a kontingenčné tabuľky a práca s nimi s podrobnejším výkladom lektora sú súčasťou dvojdňového kurzu Excel III – pre pokročilých. Alebo samostatne sa funkciám a vzorcom venujeme dva dni na kurze Excel – tvorba vzorcov a funkcií.

Je zrejmé, že za deň všetkých takmer päťsto funkcií nemáme šancu prebrať, za dva tiež nie, ale… Oba kurzy sú venované efektívnym spôsobom využitia najzaujímavejších funkcií Excelu v praxi. Odozvy ukazujú, že mierne pokročilí , ale aj ľudia, ktorí s Excelom pracujú už pár rokov, si ich pochvaľujú ako skvelú chuťovku na ceste k rozšíreniu pohľadu na svoje možnosti a zefektívneniu práce.

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í 🙂