Funkcia VLOOKUP v Exceli 4x inak – personalistika

Funkcia VLOOKUP patrí dlhé roky medzi často používané funkcie Excelu. Ak píšete do životopisu, že viete Excel, mali by ste vedieť ako používať VLOOKUP. Ak ho používať neviete, radšej Excel neuvádzajte ako jednu zo svojich silných stránok. Vážne. VLOOKUP patrí medzi funkcie, ktoré by ste mali poznať aj v prípade, že sa uchádzate o miesto na personálnom oddelení. Použitie VLOOKUPu v praxi je naozaj široké – a používa sa aj na personálnych oddeleniach. Či už samostatne, alebo spolu s ďalšími funkciami. Predovšetkým totiž pomáha pri vyhľadávaní a dopĺňaní údajov v tabuľkách. Ako používať VLOOKUP ukazujeme na príkladoch v sérii článkov, toto je druhá časť.

VLOOKUP v personalistike

Možností využitia v personalistike je veľa. Najčastejšie potrebuje personalista rôzne tipy výkazov súvisiacich s ľuďmi vo firme. Koľko majú rokov, prax, nakoľko spĺňajú kritériá na zaradenie, odmeny, akú majú na rôznych úrovniach priemernú mzdu a pod. V menších firmách môže byť personalista súčasne mzdárom a používa Excel aj na výpočty provízií atď.
Hlavne, aby to netrvalo pol dňa a výsledky boli správne.

Používanie funkcie VLOOKUP

Hoci v minulej časti sme si ukázali použitie funkcie VLOOKUP pri vyhľadávaní v jednej tabuľke, s použitím funkcie VLOOKUP sa častejšie stretneme pri obsahovo prepojených tabuľkách. Je to situácia, keď v jednej tabuľke používame číselný údaj (napr. číslo zákazníka v tabuľke objednávok / ľavá tabuľka dolu), ktorý zodpovedá číselnému údaju v inej tabuľke. A v tej sú k číselnému údaju priradené iné detailné informácie (napr. meno zákazníka, právna forma, adresa,… / tabuľka nižšie vpravo).

V praxi neraz potrebujeme do prvej tabuľky k zamestnancom (objednávkam, tovarom,…) doplniť detaily. Charakteristiky zamestnancov (parametre tovarov, zákazníkov,…) z druhej tabuľky, ktorá chýbajúce údaje obsahuje. Vlastne na základe zvoleného kritéria doplníme vybrané časti druhej tabuľky do prvej.

Ako zápis funkcie VLOOKUP vyzerá v Exceli – syntax i argumenty sme detailne popísali na príklade v predchádzajúcej časti seriálu Funkcia VLOOKUP v Exceli 4x inak. Pripomenie ich obrázok hore.

Ako funguje VLOOKUP v praxi, keď hľadáme konkrétne údaje, sa pozrieme na konkrétnom príklade. (VLOOKUP vie pracovať aj s nekonkrétnymi údajmi, v intervale – o tom nabudúce.)

Ako funguje VLOOKUP?

Predtým, než sa pustíme do riešenia nejakého príkladu, si pripomeňme požiadavky, ktoré musí spĺňať tabuľka, v ktorej budeme vyhľadávať (v našom príklade na obrázku je to tabuľka Zakaznici):

• musí obsahovať kľúčový údaj (v príklade ID_ZAK) ako prvý stĺpec – ak sa z nejakého dôvodu v prvom stĺpci nenachádza, treba pracovať len s vybranou oblasťou, kde bude kľúčový údaj prvým stĺpcom
• kľúčový stĺpec nesmie obsahovať duplicity (!) – ak sú duplicity nevyhnutné, použijeme vyhľadávanie cez viacero stĺpcov (VLOOKUP 4x inak – dodatok)
• údaje majú byť zoradené vzostupne – v prípade číselných údajov od najmenšieho hore po najväčší dolu, platí najmä pre intervalové vyhľadávanie popísané v ďalšom článku (VLOOKUP a počítanie zľavy).

Ak funkcia vypíše chybovú hodnotu #NEDOSTUPNÝ, znamená, že hľadané presné číslo nebolo nájdené.

Úloha 1:
Budeme vychádzať z príkladu na obrázku hore – z tabuliek Objednavky (vľavo) a Zakaznici (vpravo). Do tabuľky Objednavky chceme doplniť nový stĺpec s menom zákazníka. Teda okrem čísla zákazníka bude v tabuľke v samostatnom stĺpci jeho meno.

Postup:

1. Na uľahčenie práce obe tabuľky naformátujeme nástrojom Tabuľka (ak neviete, ako funguje, príďte na kurz Excel I. pre začiatočníkov),
2. Do prvej tabuľky vložíme na požadované miesto nový stĺpec (napr. označením a skratkou Ctrl – (+)) a pomenujeme ho meno_zak.
3. Mená zákazníkov budeme pomocou VLOOKUP vyhľadávať v stĺpci ID_ZAK v tabuľke Zakaznici.

V ľubovoľnej prázdnej bunke nového stĺpca (je jedno, v ktorej, keďže tabuľky sú formátované nástrojom Tabuľka), začneme písať vzorec s funkciou VLOOKUP. Vzorec bude vyzerať nasledovne:

=VLOOKUP(@[ID_ZAK];Zakaznici;2;0)

V tomto vzorci sme určili funkciou VLOOKUP Excelu, aby „hľadal“ v prvom stĺpci v tabuľke Zakaznici. VLOOKUP hľadá vždy v prvom stĺpci druhej tabuľky.
Prvý argument funkcie hovorí, že VLOOKUP má hľadať údaj zo stĺpca ID_ZAK prvej tabuľky (zavináč hovorí, že údaj je na riadku, na ktorom je zápis funkcie). Keď tento údaj v prvom stĺpci druhej tabuľky nájde, aby vypísal údaj, ktorý mu zodpovedá v druhom stĺpci (preto je tam číslo 2) druhej tabuľky.
Nula ako posledný argument hovorí Excelu, že funkcia VLOOKUP bude vyhľadávať údaj z prvej tabuľky presne. Teda presne ten údaj, ktorý je v prvej tabuľke. Žiadne odchýlky. Mimochodom – správnejšie sa namiesto nuly zapisuje FALSE.

Pri dopĺňaní stĺpca sme v príklade pracovali s tabuľkami formátovanými nástrojom Tabuľka (Vložiť / Tabuľky / Tabuľka), ktoré sme na ich špeciálnej karte premenovali (Nástroje tabuliek / Návrh / Vlastnosti) na Objednavky a Zakaznici. Preto v druhom argumente funkcie VLOOKUP odkazujeme na názov tabuľky Zakaznici. Ak druhá tabuľka nie je formátovaná nástrojom Tabuľka, vo funkcii VLOOKUP použijeme namiesto názvu tabuľky odkaz na rozsah buniek. V takom prípade ho však musíme uviesť s absolútnymi adresami, pretože vzorec s funkciou VLOOKUP sa automaticky rozkopíruje do všetkých buniek v stĺpci prvej tabuľky.

V našom príklade by teda výsledná funkcia vyzerala v prípade použitia normálneho rozsahu údajov (druhá tabuľka neformátovaná) nasledovne:

=VLOOKUP([@ID_ZAK];$F$1:$H$6;2;0)

Ako sa používa VLOOKUP (nielen) v personalistike

Úloha 2:
V tabuľke máme zoznam 100 zamestnancov so základnými údajmi. Podľa dĺžky ich praxe majú priradené kategórie praxe.
Potrebujeme vedieť konkrétne, koľko má kto rokov praxe. Z druhej tabuľky s prehľadom kategórií potrebujeme teda doplniť do prvej tabuľky údaje o praxi v rokoch.

Postup:
V prvom kroku riešenia pridáme do tabuľky stĺpec na miesto, kde budú požadované údaje. Pomocou skratky Ctrl – medzerník označíme miesto, kde bude umiestnený nový stĺpec a vložíme ho napr. skratkou Ctrl – (+).

Obe tabuľky sú formátované nástrojom Tabuľka, pričom pravá má označenie Kategorie. Pri zápise bude vzorec na riešenie tohoto  príkladu vyzerať nasledovne:

=VLOOKUP([@[Kategória praxe]];Kategorie;2;0)

Excel bude „hľadať“ v prvom stĺpci druhej tabuľky údaj zo stĺpca Kategória praxe prvej tabuľky a keď ho nájde, vypíše údaj, ktorý sa nachádza v druhom stĺpci (preto číslo 2) toho istého riadka v tabuľke Kategorie.
Nula na konci prikazuje Excelu, aby funkciou VLOOKUP vyhľadával v druhej tabuľke presne. V budúcom pokračovaní si ukážeme kedy a ako vyhľadávame pomocou funkcie VLOOKUP nepresne.

Máte pocit, že to je zložité? Ak ste neporozumeli, skúste prečítať článok ešte raz, ak ani potom, príďte na kurz Excel III., kde si fungovanie funkcie VLOOKUP ukážeme na príkladoch skutočných tabuliek z praxe (niekoľko desiatok tisíc riadkov).

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