Obsah článku:
Syntax: Funkcia VLOOKUP
Obsahuje nasledovné argumenty:
Vyhľadávaná_hodnota: ide o hodnotu, ktorá bude hľadaná v prvom stĺpci tabuľky, ktorá je načítaná v argumente pole_tabuľky.
V prípade, že sa táto hodnota v prvom stĺpci nenachádza, VLOOKUP vráti hodnotu #NEDOSTUPNÝ, inak vráti hodnotu zo želaného stĺpca.
Ľudskou rečou: ide o spoločný identifikátor, ktorý sa nachádza v zdrojovej tabuľke. Hodnota, podľa ktorej vieme údaje priradiť.
Pole_tabuľky: ide o tabuľku (oblasť), z ktorej chceme vrátiť určité hodnoty, a v ktorej prvý stĺpec bude prehľadaný. Túto tabuľku je vhodné vždy ukotviť, klávesou F4.
Číslo_indexu_stĺpca: ide o číslo stĺpca z tabuľky Pole_tabuľky. Ak napríklad chceme vrátiť hodnoty z tretieho stĺpca, zadáme do toho poľa číslo 3.
Vyhľadávanie_rozsahu: ide o logické hodnoty TRUE (1, prázdna bunka) alebo FALSE (0). Logická hodnota TRUE dovoľuje priradiť najbližšiu hodnotu, v prípade, že sa hľadaná hodnota v tabuľke nenachádza.
Inak povedané, dovolíme hľadať Excelu v intervaloch.
Logická hodnota FALSE sa využíva pri hľadaní textových hodnôt alebo presných čísel, akými sú napríklad čísla faktúr alebo objednávok. Dovolíme Excelu hľadať len presné hodnoty.
Potrebujete pomôcť s Excelom?
Na čo si pri VLOOKUPe dávať pozor?
- Hodnoty, na základe ktorých vyhľadávame musia byť v 1. stĺpci načítanej tabuľky v argumente – Pole_tabuľky
- Akým spôsobom načítavame tabuľku, z ktorej čerpáme. Nevyužívajme načítavanie celých stĺpcov, t.j. C:G, aj keď by sa Vám to možno zdalo rýchle. Výrazne si tak Excel preťažujeme.
- Tabuľku, z ktorej čerpáme vždy ukotvíme (F4). Nič tým nepokazíme.
Nevýhody tejto funkcie
- Funkcia VLOOKUP prehľadáva iba prvý stĺpec v tabuľke, ktorú sme načítali do argumentu Pole_tabuľky. Takže, to čo nám tabuľky spája musí byť v 1. stĺpci načítanej tabuľky.
- Hodnoty, ktoré chceme z načítanej tabuľky vrátiť, sa do argumentu Číslo_indexu_stĺpca zadávajú ako konštanta: 2, 3, 4… Tým pádom, je veľmi komplikované pridávanie nových stĺpcov do tabuľky, ktorú sme načítali.
Príklad: VLOOKUP funkcia do praxe
Majme teda 2 tabuľky.
Prvú (vľavo) môžeme pomenovať ako Objednávky.
Druhú (vpravo) ako Zákazníci.
Potrebovali by sme údaje z tabuľky Zákazníci presunúť, priradiť, nakopírovať… do tabuľky Objednávky. Ak by ste to pri takomto malom počte záznamov robili ručne, tak to nie je nič hrozné.
Avšak, ak by ste mali 12 000 objednávok a 1 000 zákazníkov? Tak ručné riešenie, by už bolo časovo náročné.
Čo si treba uvedomiť ako prvé?
Hodnota, ktorá nám tieto tabuľky spája, t.j. nachádza sa aj v tabuľke Objednávky, aj v tabuľke Zákazník je C_klienta (číslo klienta). Vieme teda jednoznačne povedať, že objednávku 84 vykonal klient s číslom 1, čiže Ján Pekný.
Hodnoty meno a priezvisko budeme načítavať z tabuľky Zákazník. Číslo klienta je v tejto tabuľke v prvom stĺpci, tak vieme použiť funkciu VLOOKUP.
Ako tento príklad vyriešiť?
Načítanie krstného mena:
Vyhľadávaná_hodnota: bude bunka C3, pretože ideme hľadať meno klienta, ktorý je označený ako 1.
Pole_tabuľky: bude oblasť / tabuľka I2:K7. Pozor na ukotvenie F4! Čiže tabuľka, kde sa nachádzajú údaje.
Číslo_indexu_stĺpca: bude číslo 2. Chceme, aby nám VLOOKUP vrátil krstné meno klienta, a tie sú v 2. stĺpci, preto 2.
Vyhľadávanie_rozsahu: bude 0. Pretože čísla klientov sú jedinečné identifikátory. Presné hodnoty. Nie intervaly.
Načítanie priezviska:
Vyhľadávaná_hodnota: bude bunka C3, pretože ideme hľadať priezvisko klienta, ktorý je označený ako 1.
Pole_tabuľky: bude oblasť / tabuľka I2:K7. Pozor na ukotvenie F4! Čiže tabuľka, kde sa nachádzajú údaje.
Číslo_indexu_stĺpca: bude číslo 3. Chceme, aby nám VLOOKUP vrátil priezvisko klienta, a tie sú v 3. stĺpci, preto 3.
Vyhľadávanie_rozsahu: bude 0. Pretože čísla klientov sú jedinečné identifikátory. Presné hodnoty. Nie intervaly.
VLOOKUP ľudskou rečou a stručne
VLOOKUP
V = Vertikálne
LOOKUP = Vyhľadávanie
Funkcii VLOOKUP treba zadať určitú hodnotu, ktorú má ísť hľadať.
Zadať jej tabuľku, kam to má ísť hľadať. Túto tabuľku ukotviť (F4).
Funkcia VLOOKUP prehľadá prvý stĺpec v tejto tabuľke.
Treba jej zadať číslo stĺpca, z ktorého chcete načítať údaje. Ako konštantu / číslo.
Zadať akou formou má prehľadávať. 0= presne. 1 = intervaly.
Video: VLOOKUP
VLOOKUP mi nestačí. Čo ďalej?
Následne môžeme využiť funkcie alebo kombinácie funkcií:
HLOOKUP
VLOOKUP + MATCH
IFERROR + VLOOKUP
INDEX + MATCH
HLOOKUP funkcia
Funkcia HLOOKUP, rovnako ako aj funkcia VLOOKUP priraďuje, presúva, kopíruje údaje na základe spoločného identifikátora z jednej tabuľky do druhej.
Rozdiel je však v tom, že funkcia HLOOKUP pracuje horizontálne (H), čiže prehľadá prvý riadok nami zadanej tabuľky. VLOOKUP prehľadáva prvý stĺpec t.j. vertikálne (V).
Príklad: HLOOKUP funkcia do praxe
Máme dve tabuľky, ktoré nám spája názov tovaru: HDD, FLASH, RAM a SSD. Potrebovali by sme do ľavej tabuľky priradiť ceny z pravej tabuľky. Táto tabuľka je však horizontálna, čiže identifikátory, ktoré tabuľky spájajú sa nachádzajú v prvom riadku.
Na vyriešenie tohto príkladu potrebujeme použiť funkciu HLOOKUP.
VLOOKUP vs. HLOOKUP : Čo sa zmenilo?
HLOOKUP funkcia prehľadáva prvý riadok nami zadanej tabuľky. VLOOKUP prehľadá prvý stĺpec.
Následne sa mení argument Číslo_indexu_stĺpca na argument Číslo_indexu_riadka.
Stále je to konštanta / číslo, avšak pozeráme na to, z koľkého riadka chceme hodnoty načítať.
Video: HLOOKUP
Funkcia VLOOKUP a intervaly
Pomocou funkcie VLOOKUP vieme priraďovať údaje aj na základe intervalov.
Napríklad:
- Na základe počtu kúpených kusov vieme klientovi priradiť určitú zľavu
- Na základe dátumu nákupu (dátumy sú v Exceli čísla) vieme priradiť nejaký darček
- Na základe výšky platu vieme dať zamestnancom určitú odmenu
- a pod.
Príklad: Funkcia VLOOKUP a intervaly do praxe
Na základe počtu kúpených kusov potrebujeme klientovi priradiť určitú zľavu.
- Ak klient kúpi 0-4 kusy dostane 0% zľavu.
- Ak klient kúpi 5-9 kusov dostane 1% zľavu.
- Ak klient kúpi 10-14 kusov dostane 2% zľavu.
- a pod.
Na priloženom obrázku môžete vidieť, ako by to pri funkcii VLOOKUP vyzeralo. Zmena nastane v argumente Vyhľadávanie_rozsahu, kde zadáme číslo 1. Takto VLOOKUPu poviete, aby hľadal danú hodnotu v intervaloch.
Video: VLOOKUP intervaly
Označovať len tabuľku (oblasť) alebo celé stĺpce?
Veľmi častým problémom je pri práci s funkciou VLOOKUP preťažovanie Excelu. V prípade, ak pracujeme s veľkým množstvo dát, veľa tabuľkami a máme v nich veľa VLOOKUP funkcií, je veľmi pravdepodobné, že nám Excel začne zamŕzať.
Je to spôsobené označovaním celých stĺpcov do argumentu Pole_tabuľky. Stane sa tak to, že zbytočne do funkcii VLOOKUP načítame milióny buniek, ktoré musí Excel prehľadávať.
Ako teda tento problém vyriešiť? Pozrite si naše video.
Video: Načítanie oblasti alebo stĺpcov
Pomenovanie zdrojovej tabuľky cez pole názvov
Veľmi efektívny spôsob, ktorým môžete tabuľku do VLOOKUPu načítať je ten, že si zdrojovú tabuľku pomenujete cez pole názvov. Výhoda je tá, že si VLOOKUP nebudete preťažovať a aktualizácia zdrojových údajov je tiež veľmi rýchla a efektívna.
Túto možnosť nájdete aj na karte Vzorce v skupine Definované názvy, kde je nástroj Definovať názov.
Aký je postup:
- Označte si tabuľku, ktorú chcete pomenovať
- Aktivujte nástroj Definovať názov
- Zadajte názov a kliknite na OK. Pozor, že názov nesmie obsahovať medzery
- Následne, keď pri funkcii VLOOKUP alebo HLOOKUP označíte tabuľku, Excel Vám ju automaticky prepíše na Vami zvolený názov
V nasledovnom videu si ukážme ako tento nástroj použiť aj do praxe. Na čo si dávať pozor, a ako tabuľky následne rýchlo a efektívne rozšíriť.
Video: Ako pomenovať tabuľku cez pole názvov
Funkcia MATCH – Pridajte ju do VLOOKUPu a upgradnite ho
Ak budete s funkciou VLOOKUP pracovať na dennej báze a pokročilej úrovni, tak zistíte, že jednou z veľkých nevýhod tejto funkcie je označovanie stĺpcov, z ktorých čerpáme pomocou čísel (konštánt).
Aj v predchádzajúcich príkladoch sme zadávali hodnoty 2, 3 – kedy sme čerpali údaje z druhého a tretieho stĺpca.
Problém pri používaní konštánt:
Problém s konštantami nastane vtedy, keď by sme do zdrojovej tabuľky potrebovali doplniť nové stĺpce. Bez pokazenia si funkcií a ich následného opravovania, by sme nové stĺpce vedeli doplniť iba na koniec zdrojovej tabuľky.
Ak by sme dopĺňali nové stĺpce do stredu tabuľky, funkcia VLOOKUp nebude vedieť, že krstné mená už nie sú 2. stĺpec, ale povedzme 4. Museli by sme tak všetky funkcie opraviť.
Ako sa dá táto nevýhoda odstrániť Vám ukážeme vo videu na príklade do praxe. Pozrite si ho a posuňte svoju prácu s Excelom na vyšší level.
Video: Funkcia VLOOKUP + funkcia MATCH
Ako spraviť najlepší VLOOKUP ever
Ako teda spraviť svoj najlepší VLOOKUP, ktorý ste kedy urobili?
- Použite nástroj formátovať ako tabuľku alebo si pomenujte zdrojovú tabuľku cez pole názvov
- Namiesto konštánt použite funkciu MATCH
- Ak máte podozrenie, že sa nejaké údaje v tabuľke nenachádzajú, použite funkciu IFERROR
Pozrite si naše video, kde Vám to všetko ukážeme na príklade z praxe.
Video: Najlepší VLOOKUP ever!
