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 spoluprci se SEDUO jsem vytvoil nkolik videokurz:

Jak vyut funkci SVYHLEDAT (VLOOKUP) v Microsoft Excel.

Od teoretickho vodu, pes syntaxi a po praktick pklady (jsou ke staen zdarma).

Doplnno: 30.1.2016 o kapitolu jak SVYHLEDAT pout pro vce zdrojovch tabulek

Pehled kapitol o funkci SVYHLEDAT

Tento lnek jsem z dvodu pehlednosti rozdlil do samostatnch kapitol:

Chlap ve kolce: "Dobr den, piel jsem si pro dt."
"A kter?"
"A nen to jedno? Stejn ho ztra rno zase pivedu."

Poznmka: Tuto funkci zkou vtina personalist, kdy zjiuj zda "umte" Excel.

vodem do SVYHLEDAT

Funkce SVYHLEDAT vyhled prvnm sloupci tabulky zadanou hodnotu a vrt hodnotu buky ve stejnm dku jinho sloupce dan tabulky.

Ukzka

Urm, co a ze kterho sloupce chci zskat.

MS Excel 2010 - SVYHLEDAT - ukzka

Napklad dosazen hodnot do objednvky ze seznamu vrobk (viz praktick pklad v dalm textu).


Syntaxe SVYHLEDAT

Funkce SVYHLEDAT (anglicky nzev: VLOOKUP) vyhled v prvnm sloupci tabulky poadovanou/hledanou hodnotu a vrt hodnotu, kter je ve stejnm dku (jako nalezen hodnota), ale nachz se v jinm (nmi urenm) sloupci zdrojov tabulky.

Syntaxe v esk verzi

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

Syntaxe v anglick verzi

V anglick verzi Excelu, je oddlovaem rka ne stednk.

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

Popis atributu funkce:

  • hledat - hodnota (slo, logick hodnota, odkaz), hledan v prvnm sloupci tabulky.
  • tabulka - prohledvan tabulka u kter je prvn sloupec prohledvn. Argument tabulka me bt odkaz na oblast nebo nzev oblasti.
  • sloupec - je cel slo sloupce v argumentu tabulka, ze kterho se bude piazovat. Lze zadvat od 1 a max. hodnoty potu sloupc v tabulce (tabulka). Prvn sloupec m hodnotu 1.
  • typ nepovinn parametr - nabv logickch hodnot (0 - NEPRAVDA - FALSE, 1 -PRAVDA - TRUE).
    • PRAVDA - (nezadno/przdn) - pesn shoda nebo nejvy hodnotu, kter je men ne hledan.
    • NEPRAVDA - vrt hodnotu pokud najde pesnou shodu.

Volba funkce z karty vzorce

Pokud funkci vyberete na kart Vzorce v sekci Knihovna funkc funkce Vyhledvac a referenn obdrte nsledujc dialogov okno:

MS Excel 2010 - funkce svyhledat vvyhledat

Poznmky:

Psmeno S v nzvu funkce SVYHLEDAT znamen sloupec/svisle. Tj. hledan data mus bt ve sloupcch napravo od poadovan hodnoty v prvnm sloupci Pro anglick VLOOKUP, kde V jako vertikln.

Hodnoty v prvnm sloupci prohledvan tabulky mus bt seazeny vzestupn, jinak funkce SVYHLEDAT vrac nesprvn hodnoty!

Nerozliuje mal a vlek psmena (a by se nkdy hodilo).

Existuje-li v prvnm sloupci tabulky vce shodnch hodnot, funkce SVYHLEDAT vrt neve nalezenou hodnotu (prvn od konce/spodu).

Funkce SVYHLEDAT a VVYHLEDAT jsou podobn (sta pochopit jednu, druh je analogick). Rozdl je v tom, mte-li data ve sloupcch, pouijete (SVYHLEDAT). Pokud jsou data v dcch, pouijete (VVYHLEDAT).

Zpt na seznam kapitol o funkci SVYHLEDAT.

Piadit zamstnance

Mm osobn sla (Id zamstance), kterm nle odmna a tabulku se vemi zamstanci s jejich osobnm slem (ID) a jmnem. J potebuji do tabulky doplnit jejich jmna.

Zdrojov tabulka

Mm Excel ve kterm mm dv tabulky, prvn do kter budu vkldat funkci. Jedn se o seznam ocennch zamstanac, kde ji mm jejich osobn sla (Id zamstannec). Druh tabulka je zdrojov, tabulka obsahuje zamstance seazen podle jejich osobnho sla (ID zamstance) a v dalch sloupcch je jmno danho zamstance a teba oddlen ve kterm pracuje.

Zdrojov tabulka funkce SVYHLEDAT Excel

een

Potebujete vloit jmno. Vyuijete funkci SVYHLEDAT. Z pedchoz kapitoly ji vte jakou syntaxi funkce SVYHLEDAT m, take ji vyplnte, Oznate buku B2 a vlote do n funkci:

Syntaxe pro pipomenut: SVYHLEDAT(hledat; tabulka; sloupec [;typ])
  • hledat - Z clov tabulky odkaz osobn slo zamstance (ID), tj. buka A2
  • tabulka - oznsate zdrojovou tabulku, doporuuji vechny sloupce (zamstanci mohou pibvat. Take zadte E:G (zdrojov tabulka je mezi sloupci E:G a ve sloupci G je osobn slo zamstnance)
  • sloupce - slo sloupce ve kterm je jmno, tj. 2. E je prvn sloupec, F je druh sloupec a je v nm jmno (pro oddlen by byl sloupec 3, je jako tet).
  • typ - 0 neboli NEPRAVDA - kter zajist, e Excel najde pesnou shodu. slo zamstance kter hledte se mus v tabulce nachzet pesn ve stejnm tvaru (hodnot).

Take byste mli mt zadno:

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

Funkci ji sta rozkoprovat.

een ukzka

Vsledek funkce SVYHLEDAT Excel

een hotovo

Po rozkoprovn dostanete:

Vsledek funkce SVYHLEDAT Excel

Procvien

Jako procvien si mete do tabulky doplnit i oddlen ocennch zamstanc.

Dodac list - pomoc SVYHLEDAT

Potebujete-li vytvoit dodac list, kdy jen vyplnte ID vrobk a automaticky se vm dopln jeho nzev, prodejn cena.

Zdrojov tabulka

Pedpokldm, e mte k dispozici tabulku s prodvanmi vrobky, kdy ID je uniktn pro kad vrobek. Tabulka me obsahovat i jin daje (nkupn cena), kter se daj vyut v jinch vpotech (obchodn mare).

Zdrojov tabulka funkce SVYHLEDAT Excel 2010

een

Pro zjitn nzvu vrobku na zklad udanho ID.

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

Pro zjitn ceny vrobku na zklad udanho ID. Pozor, nutno potat i slouen sloupce, take jde o sloupec 7 (ne jak by na prvn pohled zdlo 3).

=SVYHLEDAT(A7;$A$18:$G$25;7;NEPRAVDA) Zdrojov tabulka funkce SVYHLEDAT Excel 2010

Ke staen

Ke staen zdarma: Funkce SVYHLEDAT - Dodac list soubor ve formtu *.xlsx soubor pro Excel 2007 a novj.

Zpt na seznam

Tabulka pmo ve funkci

Tabulku mete do SVYHLEDAT zadat i pmo do funkce. Pouit konvence:

  • oddlovae
    • {} zatek a konec pro vkldan tabulky
    • ; oddlovat hodnot v danm dku tabulky
    • | pechod na nov dek v tabulce
  • vlastn data v tabulce
    • "Leden" - textov hodnoty do uvozovek
    • 1 - slen hodnoty se zapisuji pmo

Zdrojov tabulka

Tabulka kterou vytvome bude vypadat:

Leden 1
nor 2
Bezen 3
Duben 4
Kvten 5
erven 6
ervenec 7
Srpen 8
Z 9
jen 10
Listopad 11
Prosinec 12

Ve uvedenou tabulku lze pepsat na:

{"Leden";1|"nor";2|"Bezen";3|"Duben";4|"Kvten";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|"Bezen";3|"Duben";4|"Kvten";5|"erven";6|"ervenec";7|"Srpen";8|"Z";9|"jen";10|"Listopad";11|"Prosinec";12};2;NEPRAVDA)

V buce C4 bude zadvan hodnota nzvu msce (Leden, nor, ...) vsledkem bude poadov slo msce.

Poznmka: Vyuiv nkdo prakticky tento zpis? Osobn jsme jen nepouil, ale pr dotazu jsem obdrel. Mm toti dojem, e jde jen o testovac otzku na zkoukch. Holt cihlu do ruky nevezme, ale odbornk to je ;)

Jak zjistit jmeniny pomoc SVYHLEDAT

kolem je podle zadanho dne a msce dohledat jmno osoby, kter m v tento den jmeniny.

Zdrojov data

Pro tvorbu tabulky budou poteba zdrojov data. Mete je sthnout z internetu napklad na http://svatky.pitevna.cz/seznam-svatku/ po men prav obdrte podobnou tabulku.

Poznmka: Cvin si mete vytvoit i druhou tabulku, ve kter se bude hledat datum jmenin, na zklad zadanho jmna.

MS Excel 2010 - vodn obrazovka

een

Tabulku mte tak nen nic jednoduho ne napsat sprvn funkci SVYHLEDAT.

Pro nalezen jmna osoby, kter m jmeniny:

=SVYHLEDAT(B5;B8:C384;2)

Pro zskan data jmenin pro zadan jmno mete zkusit samostatn, nebo opsat tento vzorec:

=SVYHLEDAT(E5;E8:F384;2)

Take nic tkho. A navc u nezapomenete na jmeniny sv drah poloviky. J to mm jednoduch 11.12. (dobe se to pamatuje).

Trik

Pokud do buky, kde se zadv datum zadte funkci:

=DNES()

Po sputn Vm Microsoft Excel sdl, kdo m aktuln den narozeniny :).

Seit ke staen

Ke staen zdarma: Funkce SVYHLEDAT - jak zjistit jmeniny. soubor ve formtu *.xlsx

Zpt na seznam kapitol o funkci SVYHLEDAT.

Doplnit cenu dle vhy

Mte tabulku s hmotnosti zsilky a cenou za pepravu. V een potebujete, abyste po zadn hmotnosti pepravovanho zbo obdrely cenu za pepravu. Tj. hmotnost je udna ve skocch. Hmotnost pepravovanho zbo a k tomu cena.

MS Excel 2010 - funkce svyhledat

een pkladu

Ukzka za pouit funkce SVYHLEDAT najde vhodnou cenu za pepravu. Pouijeme v typu PRAVDA, jeliko pjde o piblin hledn. Tj. pro 123,3 kg mus funkce SVYHLEDAT najt vhodnou sazbu.

Poznmka: Pozor u hmotnost, je uvedena cena od. Tj. pozor a se vm ceny neposunou o dek!

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

Stejnho vsledku doshnete, kdy (typ) - tj. PRAVDA vynechte.

=SVYHLEDAT(D6;A5:B10;2)

Poznmka: Tento kol lze eit i pomoc logick funkce KDY. V tomto lnku, jde o ukzku pouit funkc SVYHLEDAT.

Ke staen

Ke staen zdarma: Funkce SVYHLEDAT peprava soubor ve formtu *.xlsx

Oznait dky splujc podmnku

Mte k dispozici tabulku a potebujete oznait dky, kter maj poadovan datum (v ukzkovm pkladu 3.2.2011), pokud v danm dku je poadovan datum, obdrme text ANO. Jak na to?

MS Excel 2010 - funkce svyhledat vvyhledat

Ukzka een. Na konci lnku je pklad ke staen zdarma.

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

een m nevhodu, e v ppad, e nen splnno hledan kritrium, funkce vrt chybovou hodnotu #NENI_K_DISPOZICI. Toto mete obejt doplnnm funkce SVYHLEDAT o funkci IFERROR.

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

Poznmka: Pokud zapomenete ve funkci SVYHLEDAT uvst argument NEPRAVDA, hodnotu ANO budete mt i u novjho Data (datum).

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

Ke staen

Ke staen zdarma: Funkce SVYHLEDAT - pokud je splnna podmnka peprava soubor ve formtu *.xlsx

Dal monosti pouit: Kontrolovat zda je zbo na sklad.

SVYHLEDAT jako maticov vzorec

Automaticky piadit cenu do faktury. Mte tabulku vrobku a potebujete doplnist automaticky cenu. V pomocn tabulce je k dispozici jmno vrobku a jeho cena. Pi psan vzorce chceme vyut maticov vzorec.

MS Excel 2010 - svyhledat maticov

een pkladu

Ped pouitm funkce SVYHLEDAT oznate celou oblast, pro kterou bude pout maticov vzorec (v ukzkovm pkladu to je A5:A18). Ve funkci SVYHLEDAT doplnte patin argumenty.

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

Pro vloen maticovho vzorce nutn stisk Ctrl + Shift + Enter a je hotovo.

Poznmka: Podrobnji co jsou matice a jak na n v Microsoft Excelu jsem sepsal v lnku: Matice vod - Microsoft Excel.

Ke staen

Ke staen zdarma: Funkce SVYHLEDAT maticov soubor ve formtu *.xlsx

SVYHLEDAT - tabulka pomoc definovanho nzvu

Potebujete-li se odkzat na tabulku (buku) pomoc definovanho nzvu oblasti (buky). Toto ukzkov een pedpokld, e tabulka ve kter se hled m nzev "MojeOblast" a v buce F5 je zadno MojeOblast, pot se sta jen odkzat na tuto tabulku pomoc funkce NEPM.ODKAZ.

=SVYHLEDAT(F2;NEPM.ODKAZ(F5);2)

Pklad

MS Excel 2010 - funkce svyhledat - definovan nzev

Poznmka: Znte-li njak elegantnj een, jak se ve funkci SVYHLEDAT odkzat ne definovan nzev oblasti, mete uvst v komentch.

SVYHLEDAT dv podmnky

Potebujete-li hledat v tabulce na zklad dvou podmnek. Ve ukzce, mm prvn podmnku osobu (jmno prodavae) a druh podmnka tvrtlet, vsledkem bude hodnota prodeje.

Ukzka een

Pro zdrojovou tabulku vytvome pomocn sloupec, kde ob podmnky sloume (mus bt zajitno, e hodnoty budou jedinen).

Vyhledvac dotaz lze slouit B6&C6

=SVYHLEDAT(B6&C6;$A$10:$D$21;4;NEPRAVDA) Excel 2010 - SVYHLEDAT dv podmnky

Ke staen

Ke staen zdarma: Funkce SVYHLEDAT dv podmnky soubor ve formtu *.xlsx

SVYHLEDAT dv podmnky (ken)

Potebujete-li hledat v tabulce na zklad dvou podmnek, kter se vzjemn k. Ve ukzce, mm prvn podmnku prodvan vrobek a druh podmnka je msc, ve kterm se prodvalo, vsledkem bude hodnota prodeje.

Ukzka een

Pro een vyuijete funkci POZVYHLEDAT, kter ur na zklad podmnky (msc) slo sloupce ve kterm se bude hledat.

=SVYHLEDAT(B6;$A$10:$G$15;POZVYHLEDAT(C6;$A$9:$G$9;0);NEPRAVDA) Excel 2010 - SVYHLEDAT dv podmnky

Ke staen

Ke staen zdarma: Funkce SVYHLEDAT dv podmnky - ken soubor ve formtu *.xlsx

SVYHLEDAT vce zdrojovch tabulek

Obdrel jsem zajmav dotaz, jak spojit vce tabulek pro funkci SVYHLEDAT. een je nkolik (teba pes maticov vzorce, IFERROR), j si vybral een s vyuitm funkce JE.NEDEF a funkce KDY.

Jak funguje? Pi nenalezen hodnoty funkci SVYHLEDAT obdrte chybovou hodnotu #NEN_K_DISPOZICI, funkce JE.NEDEF zobraz PRAVDA , NEPRAVDA v jakm stavu je vsledek funkce. No a samotn funkce KDY u jen zobraz hodnotu z pslunho listu. Protoe mme listy ti, je poteby funkci KDY vnoovat. Pro ukzku funkce, a nemuste opisovat ;)

=SVYHLEDAT(C15;ListA!A:B;2;0) =KDY(JE.NEDEF(SVYHLEDAT(C21;ListA!A:B;2;0));"Nen v listu";"Je v danm 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 een pes IFERROR, Dkuji 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 monost je vylepit o odkaz na buky ve kterch budou nzvy list atd.

Ukzka

Excel - SVYHLEDAT vce zdrojovch tabulek

Ke staen

V pprav

Chyby a jejich odstrann

Pi odlaovn vaich tabulek s funkc SVYHLEDAT mete narazit na problmy.

Syntaktick chyby

Muste opravit syntaxi, Pozor pi koprovn ukzek, anglick verze Microsoft Excelu pouvaj rku (,), esk stednk (;).

Nesprvn seazen

Hodnoty v prvnm sloupci prohledvan tabulky mus bt seazeny vzestupn, jinak funkce SVYHLEDAT vrac nesprvn hodnoty!

Mal hodnota

Pokud je hledan hodnota men ne nejmen hodnota v prvnm sloupci tabulky, vrt funkce SVYHLEDAT chybovou hodnotu #N/A (zle na typ).

Argumentu sloupec

patn nastaven argument sloupec, men ne 1, vrt funkce SVYHLEDAT chybovou hodnotu #HODNOTA!. Nebo pokud je hodnota argumentu sloupec vt ne poet sloupc v oblasti tabulka, vrt funkce SVYHLEDAT chybovou hodnotu #REF!.

Zpt na seznam kapitol o funkci SVYHLEDAT.

Doplujc informace

  • V EN verzi Excelu a VBA je nzev funkce VLOOKUP (vertical).
  • Poloky v druh tabulce (tj. z kter se vyhledv) doporuuji adit abecedn.
  • Tabulky pro funkci SVYHLEDAT mohou bt v rznch listech

Souvisejc lnky:

Jak nahradit SVYHLEDAT v Excel

Jak nahradit SVYHLEDAT

Napklad kdy "referenn" sloupec nen v prohledvan tabulce jako prvn. eenm je pouit funkci INDEX a POZVYHLEDAT. Podrobnji je uvedeno v samostatnm lnku, vetn seitu ke staen zdarma:


Zvrem

Zvldte pouvat funkci SVYHLEDAT? Mte njak zajmav pklad pouit? Mete 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

" ; echo "google_adtest=\"on\"; " ; echo "" ; echo "TEST REKLAMNIHO SYSTEMU" ; } else { echo "" ; } ?>

Komente

" ; // echo "kompletni_cesta :" ; // echo $adresar_pro_vkladani ; // echo "
" ; // echo " Tady bude monost pidn komente" ; // 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"); //oteve soubor book.dat pro ten $data = FRead ($fp, FileSize($adresar_pro_vkladani."/".$val)); //pete data ze souboru a ulo do promnn "data" - kvli tomu aby se zobrazoval posledn pspvek nahoe FClose($fp); echo "$data"; } } } ?>

" ; echo "google_adtest=\"on\"; " ; echo "" ; echo "TEST REKLAMNIHO SYSTEMU" ; } else { echo "" ; } ?>


Excel


Sdlejte

Pomohl Vm nvod?
Sdlejte na Facebooku, G+
LinkedIn...

Nov lnky


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Strnky o MS Office (Excel) produktu spolenosti Microsoft. Neslou jako technick podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lask 2004 - 2020 |