Tweet

SVYHLEDAT (VLOOKUP) - funkce Excel

Přehled kapitol o funkci SVYHLEDAT

Tento článek jsem z důvodu přehlednosti rozdělil do samostatných kapitol:

• Teoretické základy
  • Úvodem SVYHLEDAT - trocha teorie a ukázka jak pracuje
  • Syntaxe SVYHLEDAT - syntaxe funkce
• Základní příklady
  • Přiřadit zaměstnance - nejednodužší příklad
  • Dodací list - přiřadit hodnoty na základě vloženého ID
  • Tabulka přímo ve funkci
  • Jak zjistit jmeniny - Zadáte datum a obdržíte jméno kdo má jmeniny
  • Doplnit cenu dle váhy - jak využít SVYHLEDAT pro přibližnou shodu (cena za balík)
• Pokročilejší příklady
  • Označit řádky splňující podmínku
  • SVYHLEDAT jako maticový vzorec - automaticky přiřadit cenu do faktury
  • SVYHLEDAT - tabulka pomocí definovaného názvu - jak odkazovat na definovaný název oblasti (buňky)
  • SVYHLEDAT dvě podmínky - Potřebujete-li hledat v tabulce na základě dvou podmínek
  • SVYHLEDAT dvě podmínky - Potřebujete-li hledat v tabulce na základě dvou podmínek, které se kříží
  • SVYHLEDAT více zdrojových tabulek - Potřebujete-li spojit několik tabulek
• Pro doplnění
  • Chyby a jejich odstranění - možné problémy s funkci SVYHLEDAT
  • Doplňující informace - kde hledat, co si přečíst, čím nahradit SVYHLEDAT

Poznámka: Tuto funkci zkouší většina personalistů, když zjišťují zda "umíte" Excel.

Úvodem do SVYHLEDAT

Ukázka

Určím, co a ze kterého sloupce chci získat.

Například dosazení hodnot do objednávky ze seznamu výrobků (viz praktický příklad v dalším textu).

Syntaxe SVYHLEDAT

Syntaxe v české verzi

SVYHLEDAT(hledat; tabulka; sloupec [;typ])

Syntaxe v anglické verzi

V anglické verzi Excelu, je oddělovačem čárka ne středník.

VLOOKUP(hledat, tabulka,sloupec [,typ])

Popis atributu funkce:

• hledat - hodnota (číslo, logická hodnota, odkaz), hledaná v prvním sloupci tabulky.
• tabulka - prohledávaná tabulka u které je první sloupec prohledáván. Argument tabulka může být odkaz na oblast nebo název oblasti.
• sloupec - je celé číslo sloupce v argumentu tabulka, ze kterého se bude přiřazovat. Lze zadávat od 1 až max. hodnoty počtu sloupců v tabulce (tabulka). První sloupec má hodnotu 1.
• typ nepovinný parametr - nabývá logických hodnot (0 - NEPRAVDA - FALSE, 1 -PRAVDA - TRUE).
  • PRAVDA - (nezadáno/prázdné) - přesná shoda nebo nejvyšší hodnotu, která je menší než hledaná.
  • NEPRAVDA - vrátí hodnotu pokud najde přesnou shodu.

Volba funkce z karty vzorce

Pokud funkci vyberete na kartě Vzorce v sekci Knihovna funkcí funkce Vyhledávací a referenční obdržíte následující dialogové okno:

Poznámky:

Písmeno S v názvu funkce SVYHLEDAT znamená sloupec/svisle. Tj. hledaná data musí být ve sloupcích napravo od požadované hodnoty v prvním sloupci Pro anglické VLOOKUP, kde V jako vertikální.

Hodnoty v prvním sloupci prohledávané tabulky musí být seřazeny vzestupně, jinak funkce SVYHLEDAT vrací nesprávné hodnoty!

Nerozlišuje malá a vleká písmena (ač by se někdy hodilo).

Existuje-li v prvním sloupci tabulky více shodných hodnot, funkce SVYHLEDAT vrátí nevýše nalezenou hodnotu (první od konce/spodu).

Funkce SVYHLEDAT a VVYHLEDAT jsou podobné (stačí pochopit jednu, druhá je analogická). Rozdíl je v tom, máte-li data ve sloupcích, použijete (SVYHLEDAT). Pokud jsou data v řádcích, použijete (VVYHLEDAT).

Zpět na seznam kapitol o funkci SVYHLEDAT.

Přiřadit zaměstnance

Mám osobní čísla (Id zaměstance), kterým náleží odměna a tabulku se všemi zaměstanci s jejich osobním číslem (ID) a jménem. Já potřebuji do tabulky doplnit jejich jména.

Zdrojové tabulka

Mám Excel ve kterém mám dvě tabulky, první do které budu vkládat funkci. Jedná se o seznam oceněných zaměstanaců, kde již mám jejich osobní čísla (Id zaměstannec). Druhá tabulka je zdrojová, tabulka obsahuje zaměstance seřazené podle jejich osobního čísla (ID zaměstance) a v dalších sloupcích je jméno daného zaměstance a třeba oddělení ve kterém pracuje.

Řešení

Potřebujete vložit jméno. Využijete funkci SVYHLEDAT. Z předchozí kapitoly již víte jakou syntaxi funkce SVYHLEDAT má, takže ji vyplníte, Označíte buňku B2 a vložíte do ní funkci:

Syntaxe pro připomenutí: SVYHLEDAT(hledat; tabulka; sloupec [;typ])

• hledat - Z cílové tabulky odkaz osobní číslo zaměstance (ID), tj. buňka A2
• tabulka - oznsačíte zdrojovou tabulku, doporučuji všechny sloupce (zaměstanci mohou přibývat. Takže zadáte E:G (zdrojová tabulka je mezi sloupci E:G a ve sloupci G je osobní číslo zaměstnance)
• sloupce - číslo sloupce ve kterém je jméno, tj. 2. E je první sloupec, F je druhý sloupec a je v něm jméno (pro oddělení by byl sloupec 3, je jako třetí).
• typ - 0 neboli NEPRAVDA - která zajistí, že Excel najde přesnou shodu. číslo zaměstance které hledáte se musí v tabulce nacházet přesně ve stejném tvaru (hodnotě).

Takže byste měli mít zadáno:

=SVYHLEDAT(A2;E:G;2;0)

Funkci již stačí rozkopírovat.

Řešení ukázka

Řešení hotovo

Po rozkopírování dostanete:

Procvičení

Jako procvičení si můžete do tabulky doplnit i oddělení oceněných zaměstanců.

Dodací list - pomocí SVYHLEDAT

Potřebujete-li vytvořit dodací list, kdy jen vyplníte ID výrobků a automaticky se vám doplní jeho název, prodejní cena.

Zdrojová tabulka

Předpokládám, že máte k dispozici tabulku s prodávanými výrobky, kdy ID je unikátní pro každý výrobek. Tabulka může obsahovat i jiné údaje (nákupní cena), které se dají využít v jiných výpočtech (obchodní marže).

Řešení

Pro zjištění názvu výrobku na základě udaného ID.

=SVYHLEDAT(A7;$A$18:$G$25;2;NEPRAVDA)

Pro zjištění ceny výrobku na základě udaného ID. Pozor, nutno počítat i sloučené sloupce, takže jde o sloupec 7 (ne jak by na první pohled zdálo 3).

=SVYHLEDAT(A7;$A$18:$G$25;7;NEPRAVDA)

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT - Dodací list soubor pro Excel 2007 a novější.

Zpět na seznam

Tabulka přímo ve funkci

Tabulku můžete do SVYHLEDAT zadat i přímo do funkce. Použitá konvence:

• oddělovače
  • {} začátek a konec pro vkládaní tabulky
  • ; oddělovat hodnot v daném řádku tabulky
  • | přechod na nový řádek v tabulce
• vlastní data v tabulce
  • "Leden" - textové hodnoty do uvozovek
  • 1 - číslené hodnoty se zapisuji přímo

Zdrojová tabulka

Tabulka kterou vytvoříme bude vypadat:

Leden	1
Únor	2
Březen	3
Duben	4
Květen	5
Červen	6
Červenec	7
Srpen	8
Září	9
Říjen	10
Listopad	11
Prosinec	12

Výše uvedenou tabulku lze přepsat na:

{"Leden";1|"Únor";2|"Březen";3|"Duben";4|"Květen";5|"Červen";6|"Červenec";7|"Srpen";8|"Září";9|"Říjen";10|"Listopad";11|"Prosinec";12}

Ve funkci SVYHLEDAT bude pak vypadat:

=SVYHLEDAT(C4;{"Leden";1|"Únor";2|"Březen";3|"Duben";4|"Květen";5|"Červen";6|"Červenec";7|"Srpen";8|"Září";9|"Říjen";10|"Listopad";11|"Prosinec";12};2;NEPRAVDA)

V buňce C4 bude zadávaná hodnota názvu měsíce (Leden, Únor, ...) výsledkem bude pořadové číslo měsíce.

Poznámka: Využivá někdo prakticky tento zápis? Osobně jsme jen nepoužil, ale pár dotazu jsem obdržel. Mám totiž dojem, že jde jen o testovací otázku na zkouškách. Holt cihlu do ruky nevezme, ale odborník to je ;)

Jak zjistit jmeniny pomocí SVYHLEDAT

Úkolem je podle zadaného dne a měsíce dohledat jméno osoby, která má v tento den jmeniny.

Zdrojová data

Pro tvorbu tabulky budou potřeba zdrojová data. Můžete je stáhnout z internetu například na http://svatky.pitevna.cz/seznam-svatku/ po menší úpravě obdržíte podobnou tabulku.

Poznámka: Cvičně si můžete vytvořit i druhou tabulku, ve které se bude hledat datum jmenin, na základě zadaného jména.

Řešení

Tabulku máte tak není nic jednoduššího než napsat správně funkci SVYHLEDAT.

Pro nalezení jména osoby, která má jmeniny:

=SVYHLEDAT(B5;B8:C384;2)

Pro získaní data jmenin pro zadané jméno můžete zkusit samostatně, nebo opsat tento vzorec:

=SVYHLEDAT(E5;E8:F384;2)

Takže nic těžkého. A navíc už nezapomenete na jmeniny své drahé polovičky. Já to mám jednoduché 11.12. (dobře se to pamatuje).

Trik

Pokud do buňky, kde se zadává datum zadáte funkci:

=DNES()

Po spuštění Vám Microsoft Excel sdělí, kdo má aktuální den narozeniny :).

Sešit ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT - jak zjistit jmeniny.

Zpět na seznam kapitol o funkci SVYHLEDAT.

Doplnit cenu dle váhy

Máte tabulku s hmotnosti zásilky a cenou za přepravu. V řešení potřebujete, abyste po zadání hmotnosti přepravovaného zboží obdržely cenu za přepravu. Tj. hmotnost je udána ve skocích. Hmotnost přepravovaného zboží a k tomu cena.

Řešení příkladu

Ukázka za použití funkce SVYHLEDAT najde vhodnou cenu za přepravu. Použijeme v typu PRAVDA, jelikož půjde o přibližné hledání. Tj. pro 123,3 kg musí funkce SVYHLEDAT najít vhodnou sazbu.

Poznámka: Pozor u hmotností, je uvedena cena od. Tj. pozor ať se vám ceny neposunou o řádek!

=SVYHLEDAT(D6;A5:B10;2;PRAVDA)

Stejného výsledku dosáhnete, když (typ) - tj. PRAVDA vynecháte.

=SVYHLEDAT(D6;A5:B10;2)

Poznámka: Tento úkol lze řešit i pomocí logické funkce KDYŽ. V tomto článku, jde o ukázku použití funkcí SVYHLEDAT.

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT přeprava

Označit řádky splňující podmínku

Máte k dispozici tabulku a potřebujete označit řádky, které mají požadovaný datum (v ukázkovém příkladu 3.2.2011), pokud v daném řádku je požadované datum, obdržíme text ANO. Jak na to?

Ukázka řešení. Na konci článku je příklad ke stažení zdarma.

Platí pro sloupec "C" =SVYHLEDAT(A5;$H$6:$H$6;2;NEPRAVDA)

Řešení má nevýhodu, že v případě, že není splněno hledané kritérium, funkce vrátí chybovou hodnotu #NENI_K_DISPOZICI. Toto můžete obejít doplněním funkce SVYHLEDAT o funkci IFERROR.

Platí pro sloupec "D" =IFERROR(SVYHLEDAT(A5;$H$6:$I$6;2;NEPRAVDA);" ")

Poznámka: Pokud zapomenete ve funkci SVYHLEDAT uvést argument NEPRAVDA, hodnotu ANO budete mít i u novějšího Data (datum).

Ukázka pro sloupec "E" =SVYHLEDAT(A5;$H$6:$I$6;2)

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT - pokud je splněna podmínka přeprava

SVYHLEDAT jako maticový vzorec

Automaticky přiřadit cenu do faktury. Máte tabulku výrobku a potřebujete doplnist automaticky cenu. V pomocné tabulce je k dispozici jméno výrobku a jeho cena. Při psaní vzorce chceme využít maticový vzorec.

Řešení příkladu

Před použitím funkce SVYHLEDAT označte celou oblast, pro kterou bude použít maticový vzorec (v ukázkovém příkladu to je A5:A18). Ve funkci SVYHLEDAT doplníte patřičné argumenty.

=SVYHLEDAT(A5:A18;D8:E11;2)

Pro vložení maticového vzorce nutný stisk Ctrl + Shift + Enter a je hotovo.

Poznámka: Podrobněji co jsou matice a jak na ně v Microsoft Excelu jsem sepsal v článku: Matice úvod - Microsoft Excel.

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT maticově

SVYHLEDAT - tabulka pomocí definovaného názvu

Potřebujete-li se odkázat na tabulku (buňku) pomocí definovaného názvu oblasti (buňky). Toto ukázkové řešení předpokládá, že tabulka ve která se hledá má název "MojeOblast" a v buňce F5 je zadáno MojeOblast, poté se stačí jen odkázat na tuto tabulku pomocí funkce NEPŘÍMÝ.ODKAZ.

=SVYHLEDAT(F2;NEPŘÍMÝ.ODKAZ(F5);2)

Příklad

Poznámka: Znáte-li nějaké elegantnější řešení, jak se ve funkci SVYHLEDAT odkázat ne definovaný název oblasti, můžete uvést v komentářích.

SVYHLEDAT dvě podmínky

Potřebujete-li hledat v tabulce na základě dvou podmínek. Ve ukázce, mám první podmínku osobu (jméno prodavače) a druhá podmínka čtvrtletí, výsledkem bude hodnota prodeje.

Ukázka řešení

Pro zdrojovou tabulku vytvoříme pomocný sloupec, kde obě podmínky sloučíme (musí být zajištěno, že hodnoty budou jedinečné).

Vyhledávací dotaz lze sloučit B6&C6

=SVYHLEDAT(B6&C6;$A$10:$D$21;4;NEPRAVDA)

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT dvě podmínky

SVYHLEDAT dvě podmínky (křížení)

Potřebujete-li hledat v tabulce na základě dvou podmínek, které se vzájemně kříží. Ve ukázce, mám první podmínku prodávaný výrobek a druhá podmínka je měsíc, ve kterém se prodávalo, výsledkem bude hodnota prodeje.

Ukázka řešení

Pro řešení využijete funkci POZVYHLEDAT, které určí na základě podmínky (měsíc) číslo sloupce ve kterém se bude hledat.

=SVYHLEDAT(B6;$A$10:$G$15;POZVYHLEDAT(C6;$A$9:$G$9;0);NEPRAVDA)

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT dvě podmínky - křížení

SVYHLEDAT více zdrojových tabulek

Obdržel jsem zajímavý dotaz, jak spojit více tabulek pro funkci SVYHLEDAT. Řešení je několik (třeba přes maticové vzorce, IFERROR), já si vybral řešení s využitím funkce JE.NEDEF a funkce KDYŽ.

Jak funguje? Při nenalezení hodnoty funkci SVYHLEDAT obdržíte chybovou hodnotu #NENÍ_K_DISPOZICI, funkce JE.NEDEF zobrazí PRAVDA , NEPRAVDA v jakém stavu je výsledek funkce. No a samotná funkce KDYŽ už jen zobrazí hodnotu z příslušného listu. Protože máme listy tři, je potřeby funkci KDYŽ vnořovat. Pro ukázku funkce, ať nemusíte opisovat ;)

=SVYHLEDAT(C15;ListA!A:B;2;0) =KDYŽ(JE.NEDEF(SVYHLEDAT(C21;ListA!A:B;2;0));"Není v listu";"Je v daném listu") =KDYŽ(JE.NEDEF(SVYHLEDAT(K28;ListA!A:B;2;0));KDYŽ(JE.NEDEF(SVYHLEDAT(K28;ListB!A:B;2;0));SVYHLEDAT(K28;ListC!A:B;2;0);SVYHLEDAT(K28;ListB!A:B;2;0));SVYHLEDAT(K28;ListA!A:B;2;0))

Další řešení přes IFERROR, Děkuji za inspiraci Jirka Beran.

=IFERROR(SVYHLEDAT(C26;ListA!A:B;2;0);IFERROR(SVYHLEDAT(C26;ListB!A:B;2;0);SVYHLEDAT(C26;ListC!A:B;2;0)))

Další možnost je vylepšit o odkaz na buňky ve kterých budou názvy listů atd.

Ukázka

Ke stažení

V přípravě

Chyby a jejich odstranění

Při odlaďování vašich tabulek s funkcí SVYHLEDAT můžete narazit na problémy.

Syntaktické chyby

Musíte opravit syntaxi, Pozor při kopírování ukázek, anglické verze Microsoft Excelu používají čárku (,), české středník (;).

Nesprávné seřazení

Hodnoty v prvním sloupci prohledávané tabulky musí být seřazeny vzestupně, jinak funkce SVYHLEDAT vrací nesprávné hodnoty!

Malá hodnota

Pokud je hledaná hodnota menší než nejmenší hodnota v prvním sloupci tabulky, vrátí funkce SVYHLEDAT chybovou hodnotu #N/A (záleží na typ).

Argumentu sloupec

Špatně nastaven argument sloupec, menší než 1, vrátí funkce SVYHLEDAT chybovou hodnotu #HODNOTA!. Nebo pokud je hodnota argumentu sloupec větší než počet sloupců v oblasti tabulka, vrátí funkce SVYHLEDAT chybovou hodnotu #REF!.

Zpět na seznam kapitol o funkci SVYHLEDAT.

Doplňující informace

• V EN verzi Excelu a VBA je název funkce VLOOKUP (vertical).
• Položky v druhé tabulce (tj. z které se vyhledává) doporučuji řadit abecedně.
• Tabulky pro funkci SVYHLEDAT mohou být v různých listech

Související články:

• INDEX a POZVYHLEDAT
• další referenční a vyhledávací funkce
• Funkce POSUN

Jak nahradit SVYHLEDAT

Například když "referenční" sloupec není v prohledávané tabulce jako první. Řešením je použití funkci INDEX a POZVYHLEDAT. Podrobněji je uvedeno v samostatném článku, včetně sešitu ke stažení zdarma:

• Náhrada SVYHLEDAT pomocí INDEX a POZVYHLEDAT

Závěrem

Zvládáte používat funkci SVYHLEDAT? Máte nějaký zajímavý příklad použití? Můžete doplnit do komentářů.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře