Funkcia VLOOKUP v Exceli 4x inak – zľavy

Napísali ste si do životopisu, že viete Excel? Ak neviete ako používať funkciu VLOOKUP, radšej Excel neuvádzajte ako jednu zo svojich silných stránok. VLOOKUP patrí medzi funkcie, na ktoré sa pýtajú pri overovaní vedomostí pomerne často. Tak aby ste si nespravili hanbu hneď na začiatku. Táto funkcia sa používa najmä pri vyhľadávaní i dopĺňaní údajov do tabuliek. Používanie VLOOKUP ukazujeme na príkladoch z praxe v sérii článkov, toto je tretia časť, v ktorej sa venujeme vyhľadávaniu približnou zhodou.

VLOOKUP a zľavy?

V praxi sa čas od času stretneme s prípadom, že potrebujeme priradiť zákazníkovi zľavu na základe výšky nákupu. Zľava patrí medzi stimulátory k nákupu a preto sa objavuje pri predaji všade na svete takmer tak často ako samotné produkty. Čím viac zákazník nakúpi, tým vyššiu zľavu dostane. Pripomína vám to každodenné reklamy?

Ako na priraďovanie zliav po nákupe s Excelom sa pozrieme v tejto časti venovanej intervalovému (približnému) vyhľadávaniu pomocou funkcie VLOOKUP.

Nastavenie posledného argumentu VLOOKUP

Pri vyhľadávaní pomocou funkcie VLOOKUP určujeme pomocou posledného – štvrtého argumentu, aký typ zhody má VLOOKUP pri vyhľadávaní použiť. Zhoda môže byť presná (štvrtý argument nastavíme FALSE alebo 0), alebo približná (nastavíme TRUE alebo 1). Štvrtý argument funkcie je nepovinný, voliteľný argument. V prípade, že ho neuvedieme, VLOOKUP automaticky pracuje s prednastavenou hodnotou TRUE (alebo 1), teda vyhľadáva približnú zhodu.

Ak chceme dostať správne výsledky pri použití približného vyhľadávania, je namieste pred použitím funkcie skontrolovať, či je stĺpec tabuľky, v ktorej vyhľadávame, zoradený vzostupne (zhora nadol). Môže byť zoradený vzostupne číselne, alebo podľa abecedy – v oboch týchto prípadoch bude funkcia pracovať správne a bude vyhľadávať najbližšiu hodnotu.
Inak môže byť výsledok nepresný.

Okrem toho je dobre vedieť, že ak funkcia VLOOKUP hľadanú hodnotu nenájde, vypíše najväčšiu hodnotu, ktorá je menšia ako prvý argument. A tiež, že ak je hľadaná hodnota menšia ako najmenšia hodnota v stĺpci, vypíše chybu #NEDOSTUPNÝ. Túto chybu vypíše i prípade, že je štvrtý argument FALSE a nenájde presnú zhodu (na obrázku nižšie).

Hľadanie v intervale a VLOOKUP

Úloha:
Potrebujeme priradiť k nákupom za určité obdobie výšku zľavy na základe hodnoty nákupu. Cieľom je, aby zákazníci dostali v nasledujúcom období zľavu podľa výšky nákupu za predchádzajúce sledované obdobie.

Postup:
Ak potrebujeme v tabuľke objednávok priradiť zľavy, ktoré sú definované na základe výšky hodnoty nákupu v tabuľke zliav, použijeme funkciu VLOOKUP s nastaveným argumentom na vyhľadávanie v číselnom intervale.

• Vyhľadávanie v intervale nastavíme vo funkcii VLOOKUP štvrtým argumentom, priradíme mu hodnotu TRUE alebo 1.
• Aby VLOOKUP pre tento typ príkladu správne fungoval, musia hodnoty nákupov a zľavy pokryť všetky možnosti, teda od začiatku intervalu. Nemôžeme napríklad začať zľavou 2% s tým, že do 500 € žiadna zľava nie je. Nákupom od 0 do 499,99 € priradíme zľavu 0%, nákupu od 500 do 999,99 € zľavu 2% atď.
• Podmienkou správneho vyhľadávania v intervaloch je vzostupné zoradenie hodnôt v stĺpci Hodnota nákupu (od najmenšej k najväčšej).

Zápis funkcie VLOOKUP bude pri riešení veľmi podobný ako v príklade v predošlom článku, líšiť sa bude v poslednom argumente, ktorý v tomto príklade nastavíme na 1. Je zrejmé, že celkové ceny nákupov nie sú presné okrúhle hodnoty v prvom stĺpci pravej tabuľky, ale pohybujú sa v uvedených intervaloch. Preto musíme na vyhľadávanie použiť vyhľadávanie v intervaloch, ktoré vo funkcii VLOOKUP nazývame približné (alebo tiež intervalové).

Tabuľku s priradením zliav sme naformátovali nástrojom Tabuľka a na špeciálnej karte premenovali na TabulkaZliav.
Zápis vzorca s funkciou VLOOKUP v poslednom stĺpci ľavej tabuľky potom vyzerá takto:

=VLOOKUP([@[Celková cena]];TabulkaZliav;2;TRUE)

Funkcia VLOOKUP vyhľadá údaj zo stĺpca Celková cena (prvý argument funkcie) v prvom stĺpci tabuľky TabulkaZliav (druhý argument), na základe štvrtého argumentu funkcie nájde najbližšiu nasledujúcu hodnotu v prvom stĺpci druhej tabuľky a vypíše výšku zľavy na riadku z jej druhého stĺpca (tretí argument).

Dodatočne ešte naformátujeme stĺpec so zľavami na formát percent. Keďže používame tabuľku formátovanú nástrojom Tabuľka, označíme posledný stĺpec pomocou Ctrl – medzerník a klikneme na štýl %, prípadne ešte rozšírime zobrazenie o jedno desatinné miesto.

A je to!

Vyhľadávanie pomocou zástupných znakov a VLOOKUP

Úloha 2:
V tabuľke máme zoznam obratov obchodných partnerov za sledované obdobie. Chceme zistiť obraty troch z nich – dm, Sunpharma a Europharma, ale nevieme ich presné označenie na vyhľadanie.

Postup:
Ak použijeme ako vyhľadávanú hodnotu vo vzorci napríklad „dm“ so štvrtým argumentom TRUE – teda približná zhoda, ktorej je venovaný tento článok, nebudeme úspešní. Excel vypíše hlúposti, ktoré nájde náhodným procesom. Približná zhoda totiž vyžaduje zoradenie zoznamu vzostupne, zatiaľ čo naše údaje sú zoradené podľa obratu.
V prípade argumentu FALSE zasa vypíše #NEDOSTUPNÝ.
Pretože v prvom stĺpci ľavej tabuľky Obraty nie sú splnené podmienky na použitie približnej ani presnej zhody, musíme použiť iný spôsob zadania kritéria.

V prípade, že je hľadaná hodnota text (naša úloha), môžeme pri vyhľadávaní použiť zástupné znaky. Použijeme vyhľadanie časti názvu pomocou zástupných znakov priamo vo funkcii VLOOKUP.

Použijeme nasledujúci vzorec:

=VLOOKUP(“*“&[@skratka]&“*“;Obraty;2;FALSE)

Táto funkcia obsahuje ako prvý argument namiesto obyčajnej vyhľadávanej hodnoty v podobe adresy bunky (alebo absolútneho výrazu v úvodzovkách) rozšírenie na oboch stranách o zástupný znak hviezdičky *. Hviezdička symbolizuje Excelu zástupný znak, za ktorý „dosadí“ pri vyhľadávaní ľubovoľný počet iných znakov.

Použitie hviezdičky na oboch stranách vyhľadávacej hodnoty v zápise “*“&[@skratka]&“*“ hovorí programu, že má vyhľadať akýkoľvek text obsahujúci reťazec znakov v bunke [@skratka].

Pripomeňme si, že funkcia VLOOKUP vždy vypíše prvú hodnotu, ktorú objaví a ďalej v prehľadávaní nepokračuje. Preto v prípade „dm“ je výsledok správne, v prípade „sun“ tiež. Avšak v prípade „pharma“ vypísal Excel údaj v poradí skoršej Unipharmy (hoci v zadaní sme chceli údaj za Europharmu). Výraz „pharma“ našiel pri prehľadávaní stĺpca s názvami firiem najskôr vo výraze Unipharma. Vypísal preto údaj – obrat zodpovedajúci tejto bunke, ktorá splnila podmienku výskytu hľadaného textu a ďalšie v poradí ignoroval. Ak by sme použili na vyhľadanie výraz „euro“, uspeli by sme – VLOOKUP by vyhľadal výraz pomocou zástupných znakov správne.

Pozn.: Ak vám uvedený vzorec nefunguje, skontrolujte, či ste nezapísali namiesto dvojitých úvodzoviek používaných v slovenčine dva po sebe idúce apostrofy 🙂

Kurz VLOOKUPu?

Možno máte po dočítaní až sem pocit, že ste neporozumeli, že funkcia VLOOKUP je pre vás zložitá. Je na pohľad zložitá. Len na pohľad. Skúste článok prečítať ešte raz, pomaly, dôsledne. Keby nepomohlo, existuje ešte tretia cesta.
Na kurze Excel III. sa funkcií VLOOKUP venujeme na príkladoch tabuliek z praxe podrobnejšie. Príďte si doplniť a rozšíriť vedomosti z Excelu a spýtajte sa, čo vám nie je na tom VLOOKUPe jasné… vysvetlíme aj individuálne 😉

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