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
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.
Funkce SVYHLEDAT vyhledá prvním sloupci tabulky zadanou hodnotu a vrátí hodnotu buňky ve stejném řádku jiného sloupce dané tabulky.
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).
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.
SVYHLEDAT(hledat; tabulka; sloupec [;typ])
V anglické verzi Excelu, je oddělovačem čárka ne středník.
VLOOKUP(hledat, tabulka,sloupec [,typ])
Popis atributu funkce:
Pokud funkci vyberete na kartě Vzorce v sekci Knihovna funkcí funkce Vyhledávací a referenční obdržíte následující dialogové okno:
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.
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.
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.
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])
Takže byste měli mít zadáno:
=SVYHLEDAT(A2;E:G;2;0)
Funkci již stačí rozkopírovat.
Po rozkopírování dostanete:
Jako procvičení si můžete do tabulky doplnit i oddělení oceněných zaměstanců.
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.
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).
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í zdarma:
Funkce SVYHLEDAT - Dodací list
soubor pro Excel 2007 a novější.
Tabulku můžete do SVYHLEDAT zadat i přímo do funkce. Použitá konvence:
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 ;)
Úkolem je podle zadaného dne a měsíce dohledat jméno osoby, která má v tento den jmeniny.
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.
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).
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 :).
Ke stažení zdarma:
Funkce SVYHLEDAT - jak zjistit jmeniny.
Zpět na seznam kapitol o funkci SVYHLEDAT.
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.
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í zdarma:
Funkce SVYHLEDAT přeprava
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í zdarma:
Funkce SVYHLEDAT - pokud je splněna podmínka přeprava
Další možnosti použití: Kontrolovat zda je zboží na skladě.
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.
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í zdarma:
Funkce SVYHLEDAT maticově
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)
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.
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.
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í zdarma:
Funkce SVYHLEDAT dvě podmínky
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.
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í zdarma:
Funkce SVYHLEDAT dvě podmínky - křížení
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.
V přípravě
Při odlaďování vašich tabulek s funkcí SVYHLEDAT můžete narazit na problémy.
Musíte opravit syntaxi, Pozor při kopírování ukázek, anglické verze Microsoft Excelu používají čárku (,), české středník (;).
Hodnoty v prvním sloupci prohledávané tabulky musí být seřazeny vzestupně, jinak funkce SVYHLEDAT vrací nesprávné hodnoty!
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).
Š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.
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:
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
Pomohl vám článek? Vyřešili jste problém? Můžete mě podpořit zakoupení tabulky (samozdřejmě čokoládové), když kafe nepiji ;) Odkaz na zakoupení čokolády. Za veškerou podporu vám děkuji a samozdřejmě jí využiji do zdokonalování a rozšířování webu.
Případně přidejte odkaz na vaši oblíbenou sociální síť, případně využijste hashtag #JakNaExcel .
Děkuji za váš čas a doufám, že jste nalezli odpověď na svůj problém.
Narazili jste v článku na nejasnost, chybu? Máte tip na vylepšení nebo doplnění článku? Budu rád pokud se zmínite v komentářích.
Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). 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 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 - 2025 |