VLOOKUP – funkcia pre prepájanie viacerých tabuliek

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

V tomto článku je popísaný spôsob, ako pomocou funkcie VLOOKUP priraďovať hodnoty z dvoch tabuliek. Takéto priraďovanie je najtypickejším použitím funkcie VLOOKUP.

Príklad

Potrebujem z jednej tabuľky doplňovať hodnoty do druhej. V modrej tabuľke vľavo chcem vyplniť chýbajúci stĺpec E – podľa cenníku, ktorý je v červenej tabuľke. Teda napr. k lízatkám chcem priradiť 18, k oblátkam 8 atď.

puvodni_tabulka

Návod

Do bunky E2 zapíšem:

=VLOOKUP(B:B;G:H;2;0)

  • “=” začíná každou funkci
  • “VLOOKUP” je názov funkcie
  • “B:B” preto, že v stĺpci B sa vyskytujú názvy položiek, podľa ktorých má Excel v pravej tabuľke hľadať príslušnú cenu. Ak by som miesto toho zadal B2, výsledok by bol rovnaký – je to druhý alternatívny spôsob zápisu.
  • “G:H” preto, že v tejto oblasti je umiestnená tabuľka, z ktorej sa vyberá.
  • “2” preto, že z malej tabuľky, z ktorej sa vyberá, sa má doplniť hodnota, ktorá je v druhom stĺpčeku, čo je stĺpček “Cena/ks”.
  • Posledný parameter je obvykle nula. Jednotka sa zadáva v prípadoch podobných tomuto, kedy zaraďujem do intervalov, alebo v prípade, keď je výpočet pomalý a je potrebné ho zrýchliť. V prípade použitia jednotky musí byť tabuľka, z ktorej sa čerpá, zoradená vzostupne – inak funkcia hádže chybné hodnoty!

Ak chcem funkciu zadať cez formulár (Vložit – Funkcia…), vypadá zápis takto:

dialog

A výsledok vypadá takto:

hotová tabulka

Poznámky:

  • Keby tabuľka, z ktorej sa vyberá, bola “obrátená” (rovnako ako tabuľka vpravo dole u výslednej tabuľky), použil by som miesto funkcie VLOOKUP funkciu HLOOKUP a v treťom parametri by bolo číslo riadku namiesto čísla stĺpca, ináč sa obidve funkcie používajú podobne.
  • V anglické verzi se funkce jmenují VLOOKUP a HLOOKUP (vertical / horizontal).
  • Uvedené funkcie nahrádzajú “joinovaciu” požiadavku cez dve tabuľky, s ktorou sa stretávame v databázach, ale v Exceli sa inak ako popísanými funkciami uskutočniť skoro ani nedá.
  • Obvykle nejde dohľadávať hodnoty z dvoch tabuliek (v mojom prípade nemôžem mať dva cenníky a priraďovať z oboch súčasne). Dá sa to obísť napr. použitím funkcie IFERROR, ktorá vypadá takto: =IFERROR(VLOOKUP(B5;$E$4:$F$9;2;0);VLOOKUP(B5;odkaznadruhoutabulku;2;0)) Dá sa to preložiť ako “vyhľadaj hodnotu v prvej tabuľke, a keď tam nie je, vyhľadaj v druhej tabuľke”, takže logicky ak je hodnota v oboch, má prednosť prvá tabuľka.
  • Každá tabuľka môže byť v inom liste – potom stačí štandardne upraviť odkaz, a dokonca aj v inom súbore.
  • Ak by tabuľka, z ktorej čerpám (v našom prípade cenník) mala obrátené stĺpce, potom by VLOOKUP nefungovala. Tá funguje iba keď je najprv stĺpec s “prepojovacími” hodnotami (Názov tovaru) a až ďalej, kdekoľvek vpravo od neho, stĺpec, odkiaľ sa dosadzuje (Cena / ks). V takom prípade by som musel buď stĺpce prehodiť (a napríklad jeden z nich skryť – aby som nenarušil vzhľad tabuľky), alebo nahradiť funkciu VLOOKUP fintou s kombináciou funkcií INDEX a MATCH. Tá je na poradí stĺpcov nezávislá.
  • Funkcia VLOOKUP sa dá nahradiť funkciou DGET. Líši sa v tom, že ak je nájdených hodnôt viac, tak VLOOKUP vráti prvú z nich, zatiaľ čo DGET vypočíta chybu.

Ak si chcete stiahnuť tabuľku, uvedenú v tomto článku, kliknite sem.

VIdeo

Táto lekcia formou elearningu:

Elearning středně pokročilý Excel za 300 Kč:

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.