Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce » svyhledat-funkce-excel

SVYHLEDAT (VLOOKUP) - funkce Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak využít funkci SVYHLEDAT (VLOOKUP) v Microsoft Excel.

Od teoretického úvodu, přes syntaxi až po praktické příklady (jsou ke stažení zdarma).

Doplněno: 30.1.2016 o kapitolu jak SVYHLEDAT použít pro více zdrojových tabulek

Přehled kapitol o funkci SVYHLEDAT

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

Chlap ve školce: "Dobrý den, přišel jsem si pro dítě."
"A které?"
"A není to jedno? Stejně ho zítra ráno zase přivedu."

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

Úvodem do SVYHLEDAT

Funkce SVYHLEDAT vyhledá prvním sloupci tabulky zadanou hodnotu a vrátí hodnotu buňky ve stejném řádku jiného sloupce dané tabulky.

Ukázka

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

MS Excel 2010 - SVYHLEDAT - ukázka

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


Syntaxe SVYHLEDAT

Funkce SVYHLEDAT (anglicky název: VLOOKUP) vyhledá v prvním sloupci tabulky požadovanou/hledanou hodnotu a vrátí hodnotu, která je ve stejném řádku (jako nalezená hodnota), ale nachází se v jiném (námi určeném) sloupci zdrojové tabulky.

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:

MS Excel 2010 - funkce svyhledat vvyhledat

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.

Zdrojová tabulka funkce SVYHLEDAT Excel

Ř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

Výsledek funkce SVYHLEDAT Excel

Řešení hotovo

Po rozkopírování dostanete:

Výsledek funkce SVYHLEDAT Excel

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).

Zdrojová tabulka funkce SVYHLEDAT Excel 2010

Ř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) Zdrojová tabulka funkce SVYHLEDAT Excel 2010

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT - Dodací list soubor ve formátu *.xlsx 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.

MS Excel 2010 - Úvodní obrazovka

Ř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. soubor ve formátu *.xlsx

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.

MS Excel 2010 - funkce svyhledat

Ř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 soubor ve formátu *.xlsx

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?

MS Excel 2010 - funkce svyhledat vvyhledat

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 soubor ve formátu *.xlsx

Další možnosti použití: Kontrolovat zda je zboží na skladě.

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.

MS Excel 2010 - svyhledat maticově

Ř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ě soubor ve formátu *.xlsx

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

MS Excel 2010 - funkce svyhledat - definovaný název

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) Excel 2010 - SVYHLEDAT dvě podmínky

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT dvě podmínky soubor ve formátu *.xlsx

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) Excel 2010 - SVYHLEDAT dvě podmínky

Ke stažení

Ke stažení zdarma: Funkce SVYHLEDAT dvě podmínky - křížení soubor ve formátu *.xlsx

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

Excel - SVYHLEDAT více zdrojových tabulek

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:

Jak nahradit SVYHLEDAT v Excel

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:


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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře

" ; // echo "kompletni_cesta :" ; // echo $adresar_pro_vkladani ; // echo "
" ; // echo " Tady bude možnost přidání komentáře" ; // echo "
" ; // echo "
" ; $kompletni_cesta = "komentare"; $adresar_pro_vkladani = $kompletni_cesta; ?> \n"; reset($polozky); while (list($key, $val) = each($polozky)) { if ($val != "." && $val != "..") { if (!is_dir($val)) { // echo "$val
\n"; $fp = FOpen ($adresar_pro_vkladani."/".$val, "r"); //otevře soubor book.dat pro čtení $data = FRead ($fp, FileSize($adresar_pro_vkladani."/".$val)); //přečte data ze souboru a uloží do proměnné "data" - kvůli tomu aby se zobrazoval poslední příspěvek nahoře FClose($fp); echo "$data"; } } } ?>




Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2020 |