Funkcia VLOOKUP v Exceli 4x inak – meniny

článok na blog uverejnený: 31. mája 2020

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 používame spravidla vtedy, keď potrebujeme nájsť (a priradiť) jednému údaju v tabuľke iný údaj, ktorý sa nachádza v inej tabuľke. Na ilustráciu – v jednej tabuľke používame napr. číslo zákazníka v tabuľke objednávok odkazujúce na inú tabuľku, v ktorej sú k číslu rozpísané detailné informácie (meno zákazníka, právna forma, adresa,…). Ako do prvej tabuľky doplniť detaily o zákazníkovi z druhej tabuľky obsahujúcej základné údaje? VLOOKUPom.
Funkciu VLOOKUP môžeme tiež použiť, ak chceme vyhľadať meno zamestnanca na základe jeho osobného čísla, cenu výrobku, keď poznáme jeho názov a pod. Týmto príkladom sa budeme venovať v nasledujúcich častiach.

Pozrime sa na príklad s vyhľadávaním mena podľa dátumu menín a dátumu menín podľa mena.

funkcia vlookup v exceli - ako správne zapísať argumenty

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:

  1. hľadaný údaj – ČO (presnejšie, podľa čoho) ideme hľadať,
  2. KDE – v ktorej oblasti/rozsahu má funkcia nami hľadaný údaj ČO vyhľadávať, pričom hľadaný údaj musí byť vždy v prvom stĺpci tabuľky/rozsahu – teda ak ČO vyhľadávame v stĺpci C, tabuľku musíme upraviť tak, aby prvý stĺpec tabuľky bol C,
  3. PORADIE stĺpca v tabuľke, v ktorom sa nachádza výsledný – nášmu ČO zodpovedajúci údaj – teda ak zadáme pracovnú oblasť nášho zoznamu mien C11:F376 ako rozsah, stĺpec C má poradie prvý, D druhý atď.,
  4. nepovinný parameter, argument, ktorý môže byť TRUE (1) alebo FALSE (0) – ak argument neuvedieme, jeho predvolená hodnota bude automaticky TRUE (hľadanie približnej zhody), preto ak potrebujeme vyhľadávať presnú zhodu, musíme napísať FALSE, alebo zadať nulu.

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

=VLOOKUP(podľa ČOho;KDE;PORADIE stĺpca, v ktorom 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.**

funkcia vlookup v exceli - jednoduchý príklad použitia

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 vlookup v exceli - jednoduchý príklad použitia pri hľadaní v kalendári

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 je VLOOKUP nevhodný. 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 riešení 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.

funkcia vlookup v exceli - príklad použitia pri hľadaní funkciou MATCH

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.

funkcia vlookup v exceli - ako nezabudnúť na sviatok svojej lásky

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-vertiá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.

stiahnuť

súbor "Hľadať-meniny"

autorom článku je: Ján Tóth
- absolvent FEI STU a FM UK. Po ukončení štúdia pracoval rok vo vydavateľstve ako vedúci IT, následne na vedúcich pozíciách súkromných firiem a tiež ako riaditeľ slovenského zastúpenia nadnárodnej spoločnosti. Autor a spoluautor kníh z oblasti IT, článkov z oblasti zdravia a kozmetiky, prekladateľ, prednášateľ, v súčasnosti pracuje ako lektor a konzultant na voľnej nohe.

Páčil sa vám článok? Pošleme vám podobné.
Aktivujte si okamžité zasielanie noviniek a článkov – už vám od nás nikdy nič neunikne. Môžeme vás okamžite informovať o aktuálnej akcii, novinke, špeciálnej kampani či zľave alebo novom článku na našom blogu. Akonáhle sa u nás v novinkách niečo zmení alebo pridá, do pár minút vám automaticky posielame email s odkazom na novinku na našu stránku. A odhlásiť sa môžete kedykoľvek. Nič neriskujete.


Nikam neodchádzajte, pozrite si naše novinky, akcie alebo si
prečítajte ďalšie podobné články:

Podobné články z kategórie
Excel, funkcie v exceli, Kancelária a MS Office, VLOOKUP