SVYHLEDAT

Jistě jste se při práci s MS Excelem setkali se situací, kdy jste potřebovali k jedné databázové tabulce přiřadit záznamy z jiné databázové tabulky. Často je potřeba tyto záznamy přiřadit podle nějakého jedinečného identifikátoru, který se shoduje v obou tabulkách, např. podle rodného čísla, čísla smlouvy, čísla faktury apod.

Pokud by obě databázové tabulky měly stejný počet záznamů, jednalo by se o úkol celkem jednoduchý – seřadili bychom je podle stejného identifikátoru, např. podle čísla faktury a nakopírovali vedle sebe. Ale co když v jedné z tabulek nějaká ta faktura chybí, nebo naopak přebývá? Pak je tu právě funkce SVYHLEDAT (vyhledává v tabulkách, kde jsou data zapsaná svisle pod sebou, nebo VVYHLEDAT, pokud jsou data zapsaná vodorovně vedle sebe). Sám název funkce napoví – VodorovněVYHLEDAT = VVYHLEDAT, nebo SvisleVYHLEDAT= SVYHLEDAT)

Vložení funkce

Nejčastěji bývají tabulky řazené svisle pod sebou, tedy začneme funkcí SVYHLEDAT. Tuto funkci najdeme na pásu karet VZORCE, pod vyhledávacími funkcemi, viz obr. 1.

Obr. 1 - vložení funkce

Argumenty funkce

Argumenty funkce jsou tyto:

• HLEDAT – "co hledám" - sem zadáme klíčovou hodnotu, kterou chceme hledat
• TABULKA – "kde hledám" - zde označíme tabulku, ve které chceme klíčovou hodnotu vyhledat a získat data ze stejného řádku, ale jiného sloupce prohledávané tabulky
• SLOUPEC – z jakého sloupce prohledávané tabulky chceme vrátit výsledek, počítáno z leva. Tedy pokud vybereme např. tabulku v rozsahu A1 – D750 a zajímají nás údaje ze sloupce B, zadáme číslo 2, jelikož sloupec B je druhý zleva. Počítáno vždy pouze v rámci výše zadané tabulky, nikoli od kraje sešitu.
• TYP – vyhledávat můžeme na úplnou shodu, nebo na intervalovou shodu (nejbližší hodnota). Pokud chceme hledat přesně (úplná shoda), zadáme do tohoto parametru hodnotu NEPRAVDA nebo nula (0), pokud hledáme na intervalovou shodu, zadáme PRAVDA (1). Jedná se o nepovinný argument, tedy pokud ho nezadáme, budeme hledat intervalovou nejbližší hodnotu, jako bychom zadali PRAVDA.

Obr. 2 - Parametry funkce Svyhledat

Příklad použití funkce SVYHLEDAT – přibližná shoda

Jsme majitelé obchodní firmy, která zaměstnává 5 dealerů, kteří dostávají nějaký fixní plat a k tomu provizi. Výši provize mají stanovenu následovně:

Pokud dealer prodá alespoň za 25000,-Kč, dostává provizi ve výši 12%, když prodá za 40000,-Kč a více, zvedá se mu provize o 0,5%, viz obr. 3.

Obr. 3 – Provize

Nyní je konec měsíce a máme k dispozici od dealerů výkaz tržeb, viz obr. 4.

Obr. 4 - Měsíční výkaz

Naším úkolem tedy, je vyhledat tržbu dealera v tabulce provizí a zjistit výši provize, která mu bude vyplacena. Všimněte si, že tržby dealerů jsou v částkách, které nekorespondují s částkami v tabulce provizí. Budeme tedy muset hledat tyto hodnoty pomocí nejbližší intervalové shody, tedy do argumentu TYP zadáme PRAVDA (1).

Funkci zapíšeme takto:

=SVYHLEDAT(C16;B4:C10;2;PRAVDA)

Obr. 5 - SVyhledat na intervalovou shodu

Červená šipka označuje hodnotu, kterou hledám "Co hledám" a žlutá šipka označuje "Kde hledám". Dvojka v dalším parametru označuje číslo sloupce, ze kterého si přeji získat návratovou hodnotu (výsledek). V tomto případě sloupec provize v procentech.

Tedy např. pro částku 37700,-Kč (C16) v tabulce provizí (B4:C10) jsme obdrželi výsledek 12% ze sloupce provize v % (sloupec číslo 2 v tabulce) a hledal jsme na intervalovou shodu nejbližší hodnotu (PRAVDA).

Poznámka 2: Vyhledávané hodnoty musí být vpravo od sloupce, ve kterém jsou klíčové hodnoty, podle kterých hledáme. V tomto případě napravo od sloupce prodej Kč. Kdyby sloupce byly prohozené, tedy provize v % byla vlevo od prodej Kč, funkce by nefungovala.

Poznámka 3: Pokud hledáme nejbližší hodnotu na intervalovou shodu, musí být hodnoty v prvním sloupci seřazeny vzestupně; kdyby nebyly, funkce by nemusela fungovat správně. Ukážeme si na příkladu, již napsanou funkci zkopíruji v tabulce Tržby září 2014 dolů a jak je vidět na obr. 6, vše funguje jak má.

Obr. 6 - Pokud vyhledáváme na intervalovou shodu, tabulka musí být seřazena vzestupně

Pokud však tabulka seřazena vzestupně nebude, funkce vrací chybné výsledky, viz obr. 7.

Obr. 7 - Výsledky neseřazené tabulky

Pokud by mě zajímal výsledek v Kč a ne v %, vzorec bych zapsal takto:

=SVYHLEDAT(C16;B4:C10;2;PRAVDA)*C16

Výsledek by vypadal takto:

Obr. 8 - Výsledek v korunách

Poznámka 4: funkce SVYHLEDAT vyhledává na intervalovou shodu vždy směrem k nejblíže nižší známé hodnotě, z toho důvodu částce 69900 přiřadila 13%, i když je tato částka blíž k 70000 než k 55000. Kdyby např. dealer Holoubek neprodal za 37700 ale za 24800, funkce by nám vrátila chybovou hlášku #NENÍ K DISPOZICI. S tím je třeba dopředu počítat a předejít této chybové hlášce např. vložením záznamu prodej Kč 0 provize v % 0%, nebo lze využít funkci CHYBHODN (IFERROR).

Obr. 9 - Funkce SVYHLEDAT vyhledává přibližně směrem k nule

Příklad použití funkce SVYHLEDAT – přesná shoda

V tomto máme za úkol obeslat zákazníky, kteří nám dosud neuhradili fakturu, nebo ji neuhradili v plné výši. Jedno oddělení nám dodá tabulku se jmény a osobními daty dlužníků, druhé oddělení nám dodá tabulku s aktuální výší dluhu a já nyní potřebuji přiřadit správnou výši dluhu ke správné smlouvě. Přitom někteří již zaplatili, tedy nemohu obě tabulky seřadit podle čísla smlouvy a nakopírovat do jedné. Společným ID pro obě tabulky je právě číslo smlouvy. Tabulka se jmény dlužníků vypadá např. takto:

Obr. 10 - Tabulka dlužníků

A takto vypadá tabulka s aktuálními dlužnými částkami:

Obr. 11 - Tabulka výše dluhu

Postup:
klikneme do buňky J2 (sloupec výše dluhu) a napíšeme vzorec:

=SVYHLEDAT(I2;List2!A:B;2;NEPRAVDA)

Výsledek vypadá asi takto. Pokud se v buňce objeví text “#NENÍ K DISPOZICI“, znamená to, že související hodnota nebyla nalezena – tato smlouva již byla zaplacena a v druhé tabulce se již nenachází, viz obr. 12.

Obr. 12 - SVYHLEDAT přesně

VVYHLEDAT

Jak jsem již uvedl výše, tato funkce funguje stejně, jako SVYHLEDAT s tím rozdílem, že vyhledává vodorovně – tedy v řádcích.

Ukážeme si na následujícím příkladu, opět potřebujeme ohodnotit dealery, ovšem tentokrát je tabulka s provizemi vytvořena vodorovně.

Obr. 13 – VVYHLEDAT

Argumenty funkce jsou zadány stejně jako u SVYHLEDAT, akorát si musíme dát pozor při kopírování vzorce – když na tabulku odkážeme relativně, posune se odkaz při kopírování a proto  funkce nebude fungovat, tedy na ní musíme odkázat absolutně – zamknout ji pomocí kláv. F4.

Obr. 14 - Chyba - na tabulku bylo odkázáno relativně

Správně „zamčený“ vzorec by měl vypadat takto:

=VVYHLEDAT(F8;$G$2:$M$3;2;PRAVDA)