Příklad
Potřebuji z jedné tabulky doplňovat hodnoty do druhé. Nebo, jinými slovy - potřebuji propojit dvě tabulky.V tomto případě ve vzorové tabulce potřebuji doplnit do sloupečku "Cena za kus" ceny z malé tabulky vpravo.
Např. u čokolády chci doplnit do buňky C5 cenu 12 Kč, která odpovídá čokoládě, u oplatky 8 Kč atd. Mohl bych to udělat ručně, ale u velkých tabulek by to bylo nepřiměřeně zdlouhavé.
Návod
Pomůže mi funkce, která se jmenuje SVYHLEDAT (anglicky VLOOKUP).Do buňky C5 vpíši:
=SVYHLEDAT(B5;E4:F9;2;0)
- "=" začíná každou funkci
- "SVYHLEDAT" je název funkce
- "B5" proto, že v této buňce je slovo "čokoláda" - slovo, podle kterého má Excel v pravé tabulce najít příslušnou cenu.
- "E4:F9" proto, že v této oblasti je umístěna tabulka, ze které se vybírá.
- "2" proto, že z malé tabulky, ze které se vybírá, se má doplnit hodnota, která je ve druhém sloupečku, což je sloupeček "Cena/ks".
- Poslední parametr je obvykle nula. Jednička se zadává v případech podobných tomuto.
Než funkci, zadanou do buňky E5, roztáhnu na další řádky, musím doplnit pevné (absolutní) odkazy a funkce pak vypadá takto:
=SVYHLEDAT(B5;$E$4:$F$9;2;0)
Zafixoval jsem druhý parametr - oblast tabulky, ze které se vybírá. Dělám to proto, že tato tabulka zůstane úplně stejná pro všechny řádky a při roztahování se nemá měnit.
Výsledek je takovýto:
Poznámky:
- Kdyby tabulka, ze které se vybírá, byla "obrácená" (stejně jako tabulka vpravo dole u výsledné tabulky), použil bych místo funkce SVYHLEDAT funkci VVYHLEDAT a ve třetím parametru by bylo číslo řádku místo čísla sloupce (v tomto případě je to tak jako tak dvojka), jinak se obě funkce používají obdobně.
- Jak si pamatovat názvy funkcí? "S" na začátku názvu funkce SVYHLEDAT je odvozené od slova "Svisle", "V" na začátku názvu funkce VVYHLEDAT je odvozeno od slova "Vodorovně".
- V anglické verzi se funkce jmenují VLOOKUP a HLOOKUP (vertical / horizontal).
- Uvedené funkce nahrazují "joinovací" dotaz přes dvě tabulky, se kterým se setkáváme v databázích, ale v excelu se jinak než popsanými funkcemi uskutečnit téměř nedá.
- Běžně nejde dohledávat hodnoty ze dvou tabulek (v mém případě nemohu mít dva ceníky a přiřazovat z obou současně). Dá se to obejít např. použitím funkce IFERROR (CHYBHODN), která vypadá takto: =CHYBHODN(SVYHLEDAT(B5;$E$4:$F$9;2;0);SVYHLEDAT(B5;odkaznadruhoutabulku;2;0))
Dá se to přeložit jako "vyhledej hodnotu v první tabulce, a když tam není, vyhledej v druhé tabulce", takže logicky pokud je hodnota v obou, má přednost první tabulka. - Každá tabulka může být v jiném listu - pak stačí standardně upravit odkaz.
- Pokud by tabulka, odkud čerpám (v našem případě ceník) měla obrácené sloupce, pak by SVYHLEDAT nefungovala. Ta funguje jen když je nejprve sloupec s "propojovacími" hodnotami (Název zboží) a až dál, kdekoliv vpravo od něj, sloupec, odkud se dosazuje (Cena / ks). V takovém případě bych musel buď sloupce prohodit (a třeba jeden z nich skrýt - abych nenarušil vzhled tabulky), nebo nahradit funkci SVYHLEDAT fintou s kombinací funkcí INDEX a POZVYHLEDAT(MATCH). Ta je na pořadí sloupců nezávislá.
Procvičení tématu:
Výborný návod, děkuji Ti.
OdpovědětVymazatKonečně jsem pochopil k čemu je funkce SVYHLEDAT dobrá.
Bylo mi potěšením :)
OdpovědětVymazatjak se dělá to ukotvení? nevím kde ten znak najít poř. jakou klávesovou zkratku použít :-(
OdpovědětVymazatKlobouk dolů před autorem, krásně vysvětlené.
OdpovědětVymazatDíky těmto stránkám je hned excel zábavnější a snazší :)
$ - AltGR + ů na české klávesnici - samozřejmě, že každý kdo pracuje s excelem více, používá F4. Při opakovaném stisknutí F4 měníme hodnotu absolutního odkazu - tj.
OdpovědětVymazat1. jedno stisk - řádek i sloupec
2. dva stisk - jenom sloupec má absolutní hodnotu
3. troj stisk - jenom řádek má absolutní hodnotu
4. čtvrtý stisk opět odemyká řádek i sloupec
Pá :-)))
K tomu "IF/Když - podrobně" - očekával bych spíše obšírnější vysvětlení vložených IF a zákonitosti jejich vyhodnocování - poukázat na časté chyby a jejich eliminaci. To prosím není kritika, ale spíše návrh jak ještě více pomoci začínajícím uživatelům. Jinak je váš web rozhodně přínosem.
dik za vyborne vysvetlenie, sice tato funkcia pre mna nebola nova, ale tu skratku s F4 som nepoznal - super
OdpovědětVymazatSouhlasím, funkce je praktická. Má však jedno úskalí. Při dodatečném vložení sloupce do prohledávané oblasti se nepřepočte parametr Sloupec a funkce začne vracet něco jiného, než jsme původně chtěli. Co by se dalo zapsat do parametru takového, aby po vložení sloupce do oblasti Excel zvětšil hodnotu o jednu a současně při vložení sloupce před prohledávanou oblast nechal vše být?
OdpovědětVymazatNevím jestli úplně přesně rozumím.
OdpovědětVymazatNicméně asi bych to udělal tak, že bych někam mimo zapsal funkci, která vyhodí číslo sloupce některé z buněk v prvním sloupci prohledávané tabulky, např. =column(A1), do jiné buňky pak číslo některé buňky z posledního sloupce tabulky.
Do třetí buňky bych tahle dvě čísla odečetl a tuto třetí buňku použil jako parametr Sloupec (asi bude třeba z ní ještě něco odečíst).
Myslím, že to může fungovat.
Ano, teď je to funkční! Jestliže mám matici B1:D4, mohu prohledávat =SVYHLEDAT(B3;B:D;3;0) a k hodnotě v B3 mi vzorec vrátí hodnotu v D3. Pokud chci přidávat sloupce před nebo do oblasti, abych nemusel vzorec neustále opravovat, mohu ho zapsat takto =SVYHLEDAT(B3;B:D;SLOUPEC(D1)-SLOUPEC(B1)+1;0). Škoda, že tohle nemají ošetřené...
OdpovědětVymazatNarazil jsem na jeden problém:
OdpovědětVymazatKdyž v pravé tabulce s cenami změním položky následovně:
čokoláda 50g
čokoláda 100g
a následně v levé tabulce prodejů zadám do jakéhokoliv řádku čokoláda 50g, tak se mi cena doplní správně, ale když zadám čokoláda 100g, tak se mi cena nevyplní a objeví se ni #N/A.
Netušíte někdo jak tento problém vyřešit?
To by mělo normálně fungovat, myslím, že máte někde nějaký překlep...
OdpovědětVymazatŽádný překlep, zkuste si to nasimulovat. Zkoušel jsem to několikrát v Excelu 2003 i v OpenOffice a výsledek stejný. Konkrétně jsem místo čokolád použil hodnoty EVO48PCB a EVO192PCB se stejným výsledkem. Na čokolády jsem to převedl pro lepší představivost, s tím že jsem to zkoušel přímo na této uvedené tabulce :o(
OdpovědětVymazatTak mi to pošlete na adresu uvedenou pro doučování...
OdpovědětVymazatTohohle problému se týká tento bod článku:
OdpovědětVymazatNejsou-li položky v "malé" tabulce seřazené podle abecedy, zadejte nulu do čtvrtého, volitelného parametru funkce.
tak tuhle poznamku jsem taky potrebovala zduraznit :D, uz mi to bezi vyborne, diky autorovi
OdpovědětVymazatDobrý den, mám problém potřeboval bych poradit zda je teda vůbec možný z těchto čísel pomocí nějakého vzorce vytáhnout vždy poslední čísla před pomlčkou. čísla ve sloupečku v bunkách a jsou tam i mezery. dávám 3 příklady
OdpovědětVymazat33 54 791 4 004-8 další v bunce 33 54 791 4 015-4 a další v bunce 33 54 791 4 022-0 a ted potřebuji pomocí nějakého vzorce či pomocí nějakých kroků docílit aby se zase do dalšího sloupce napsalo tady to 4004 pod to do bunky 4015 pod to do bunky 4022
předem děkuji za odpověď chotik
Pro Váš problém je to takto:
OdpovědětVymazat=CONCATENATE(ČÁST(A1;11;1);ČÁST(A1;13;3))
Bližší vysvětlení viz popisy funkcí "Část" a "Concatenate".
A co v situaci, kdy mám dvě tabulky, ve kterých potřebuju data vyhledávat? Bylo by možné nějak vyřešit tuhle situaci? Tuším, že půjde o konstrukci s IF a VLOOKUP, ale konkrétní realizace se mi nedaří :-(
OdpovědětVymazatDobrý den, nevím, jak to myslíte, popište přesně, co chcete dělat.
OdpovědětVymazatŘekněme, že mám 3 tabulky. V primární tabulce mám identifikátory (třeba čokoláda, bonbony, želé atd.). Do řádků k nim potřebuju dodat ceny. Ty jsou pod stejnými identifikátory umístěny v dalších 2 tabulkách. Problém je, že čokoláda a bonbony jsou v jedné tabulce, želé ve druhé.
OdpovědětVymazatPotřebuji tedy prohledat obě tabulky, pomocí identifikátorů spárovat obsah z obou a dosadit ceny do primární tabulky.
Řešení, že bych druhou a třetí tabulku spojil dohromady není reálné (dostal bych se s počtem záznamů nad 1 mil.)
OK, to je dost složité. Popsané je to třeba tady.
OdpovědětVymazathttp://www.ozgrid.com/forum/showthread.php?t=92840&page=1
Jestli ale pracujete s takto velkými databázemi, rozhodně bych to řešil v Accessu nebo v jiném db nástroji - bude to snadnější.
To jste kouzelník. Parádní zdroj informací :-) Bohužel Access neovládám.
OdpovědětVymazatJak myslíte, ale tohle jsou přesně věci, které se v Accessu řeší mnohem rychleji, intuitivněji a není třeba žádné velké učení.
OdpovědětVymazatDobrý den,
OdpovědětVymazatchtěla bych se zeptat, jestli existuje nějaká možnost, jak sloučit data ze 2 tabulek s použitím více parametrů. Příklad - mám 2 tabulky s daty o jménech zaměstnanců, názvech projektů (kdy každý zaměstnanec pracuje na více projektech) a měsících, kdy pracovali. V jedné tabulce jsou k těmto datům přiřazeny počty odpracovaných hodin, ve druhé tabulce jejich úvazky. Tyto 2 tabulky bych potřebovala sloučit tak, abych měla ve výsledné tabulce Jméno, Název projektu, Měsíc (=> tato data jsou v obou tabulkách), Počet odpracovaných dnů v daném měsíci a na daném projektu, Úvazek v daném měsíci a na daném projektu.
Děkuji za radu :)
No, jestli máte i web Accessní triky, tak si rád nechám poradit :). Zatím je pro mě ale Access jedna velká neznámá.
OdpovědětVymazatDěkuji :) zkusila jsem to obejít vytvořením 2 pivot tabulek a sloučením těchto 2 tabulek do jedné, zatím to funguje :)
OdpovědětVymazatHezký den
...jinak pokud jsou tabulky jen dvě, dá se to asi sfouknout tímhle vzorcem přes funkci IFERROR:
OdpovědětVymazat=IFERROR(SVYHLEDAT(B5;$H$7:$I$9;2;0);SVYHLEDAT(B5;$K$7:$L$8;2;0))
Říkám tím:
Když v první tabulce nenajdeš (hodnota je chybová), podívej se do druhé.
Dobrý den,
OdpovědětVymazatděkuji za perfektní vysvětlení, jen bych ještě potřebovala vychytat ještě jednu věc. Nejde nějak obejít to pravidlo "prvního sloupce tabulky"? Tzn. že mam napr. sloupce Titul, Jmeno, Prijmeni a potřebuji tyto údaje doplnit do jiné tabulky dle Příjmení - nebo to proste musím všechno přeházet, tak, aby bylo nejdříve Příjmeni poté jméno a pak titul? Jak postupovat, když potřebuji hledat podle více sloupců? Např. pokud by bylo pro několi lidí shodné příjmení, tak abych měla jistotu tím, že např. přidám ještě vyhledávání dle sloupce jméno. Potřebovala bych, aby ta fce vypadala nejak takto SVYHLEDAT(Novotný+Jan;tabulka H2:J34 (jm., prijmeni,zamestnani);3,0). Snad je to aslespon trochu srozumitelne. Diky Jana
Dobrý den, tohle už je trochu oříšek a musel bych se nad tím zamyslet. Jestli chcete, pošlete mi soubor mailem (třeba s anonymními daty). Jestli půjde vyřešit, pošlu vám vyřešený soubor a vy mi pošlete na účet cca dvě stovky.
OdpovědětVymazatHezký den,
J.
Dobrý den,velice si cením návodů v těchto stránkách a rád bych se na Vás obrátil s prosbou o radu.Funkce SVyhledat na mé tabulce se po zadání vzorce objeví HODNOTA NENÍ K DISPOZICI,což mě užírá,protože mám vše do puntíku stejně jak je v návodu.Tušíte,v čem by mohla být chyba?
OdpovědětVymazatTo vypadá na nějaký překlep... nevím, pošlete soubor.
OdpovědětVymazatVzorec patri do bunky E5 a nie F5. Inac diky!
OdpovědětVymazatNojo, díky, opraveno...
OdpovědětVymazatDobrý den, mám dotaz, jak použít funkci svyhledat, když mám tabulku o 3 sloupcích, v prvním je datum, ve druhém kurz v EUR a ve třetím kurz v USD. D8le mám druhou tabulku, kde mám částku v měně (USD, EUR,CZK), měnu a datum zdanitelného plnění (datum kurzu). Nějak mi ale nejde připojit první tabulku ke druhé, aby mi to vypsalo vždy kurz k příslušné měně a datu. Děkuji za odpověď
OdpovědětVymazatDobrý den, jedním z řešení je zadat tři různé funkce SVYHLEDAT a s použitím funkce KDYŽ vybrat, která bude použita.
OdpovědětVymazatno a pokud mam problem ze z puvodni tabulky potrebuji vyhledavat podle parametru prodano kusu a dostat zpet co to bylo za zbozi ? (typicky bych chtel treba vedet ktereho zbozi bylo prodano nejvice, ale tabulku mam razenou podle datumu a nechci ji radit jinak, ale chci si vytahnout dejme tomu tri nejprodavanejsi druhy zbozi)
OdpovědětVymazatno a pokud mam problem ze z puvodni tabulky potrebuji vyhledavat podle parametru prodano kusu a dostat zpet co to bylo za zbozi ? (typicky bych chtel treba vedet ktereho zbozi bylo prodano nejvice, ale tabulku mam razenou podle datumu a nechci ji radit jinak, ale chci si vytahnout dejme tomu tri nejprodavanejsi druhy zbozi)
OdpovědětVymazatDobrý den,
OdpovědětVymazatje možné, že pokud potřebuju Vlookup hodnoty z druhého souboru, bude mi ta funkce vyhazovat N/A i když při ručním vyhledávání mám v obou tabulkách shodné hodnoty? Jaký vliv má kompatibilita souborů, nebo to, že "čerpaná" tabulka už obsahuje Vlookup data z dalšího souboru?
Dobrý den, umím odpovědět jen na jednu otázku - to, že "čerpaná" tabulka už sama čerpá odjinud vliv nemá.
OdpovědětVymazatDobrý den, chtěl bych se zeptat jestli je možné nějak nahradit #N/A jako výsledek hledání za číslo popř. bez hodnoty. Vyhledávám v tabulce která vždy neobsahuje všechny hledané výrazy.
OdpovědětVymazatDobrý den mám takový dotaz, funguje mi podle návodu vše správně, jde ale nějak zařídit pokud bych chtěl například v první tabulce všechny hodnoty vynulovat? Druhá tabulka vypadá tak, že mám jen jeden řádek kam zadávám název a podle něj se vyhledává a v prví tabulce má každý řádek jiný název. první tabulka: název , data - druhá tabulka: dolním název a data a doplní se do celé tabulky první. děkuji
OdpovědětVymazatDobrý den, rád bych se Vás zeptal a požádal o pomoc. Potřebuji aby se mi do buňky (třeba)"D1" promítnul text (hodnota výpočtu) z příslušného řádku (buňky), který odpovídá parametru v buňce "A1" (tam bych rád zadal číslo patřičného řádku). Řádky jsem si čísloval 1,2,3,...200 ve sloupci A, ale když použiji do "D1" vzorec : =SVYHLEDAT($A$1;$A$4:$A$11111;4) napíše se mi "REF!". Používám verzi 2000 a tuším že někdy dříve se mi stalo že vzorec s hodnotou prostého čísla nefungoval a pomohlo až když jsem k číslu přidal nějaké písmeno (např. 1a,2a,..). Předem moc díky za radu.
OdpovědětVymazatDobrý den,
OdpovědětVymazatzkuste opravit vzorec takto:
=SVYHLEDAT($A$1;$A$4:$A$11111;4;0)
a když to nepomůže, pošlete mi soubor na beranjiri25@gmail.com.
J.
Dobry den
OdpovědětVymazatjak dostanu z jedne tabulky do druhe cenu a nazev, aby se to doplnovalo do druhe tabulky ? predem dekuji :)
Dobry den
OdpovědětVymazatjak dostanu z jedne tabulky do druhe cenu a nazev, aby se to doplnovalo do druhe tabulky ? predem dekuji :)
Dobrý den, super návod, moc mi pomohl. Díky.
OdpovědětVymazatZdravím, musím pochválit tyto výborné stránky.Měl bych dotaz,nevím jestli je přímo související s touto funkcí,avšak jiná mě nenapadá.Na tomto odkazu je mnou vytvorěná tabulka a já bych rád věděl,jak by bylo možné do políček B15 a B16 vložit jaký měsíc byl ten nejméně a nejvíce výdělečný (tabulka čistý)
OdpovědětVymazathttp://imageshack.us/photo/my-images/202/dotazz.jpg/
Děkuji za odpověď (i pokud se jí nedočkám) :-D
To není problém - jen musíte mít sloupec s názvy měsíců až za sloupcem za tím, podle čeho hledáte. Čili přeskládat sloupečky - přičemž pamatujte, že pokud si nechcete tabulku zpřeházet, můžete použít sloupečky skryté.
OdpovědětVymazatZdravím. Děkuji za výborné návody, ale bohužel se mi nedaří vyřešit můj problém. Mám jednu hlavní tabulku, do které chci dle výběru dvou veličin (podmínek) načítat data z jiných tabulek. Tj., aby se mi vždy zkopírovali data z řádku nějaké tabulky dle podmínek. Př.: v prvním sloupci zadám 1, vybere se tabulka s indexem 1. V dalším sloupci zadám A a z té vybrané tabulky 1 bude tahat řádek, který bude mít stejný název (A). Zkoušel jsem to řešit pomocí funkce když, ale přišlo mi to hodně kostrbaté. Díky za každou pomoc.
OdpovědětVymazatMožná by to šlo nějakou kombinací IF a INDIRECT...
OdpovědětVymazatDobrý den,
OdpovědětVymazatpoužívám funci SVYHLEDAT v jednom sešitu na různých listech a funkce funguje špatně (nevyhledá se vše). Pokud použiji SVYHLEDAT z různých sešitů vše funguje bez problémů. Kde může být problém?
Moc Děkuji
Tomáš
Třeba v datových typech. Ujistěte se, že neporovnáváte např. texty s čísly. J.
OdpovědětVymazatDobrý den, jak prosím využít funkce VLOOKUP, když kritérium vyhledávání je v bunce matice pouze část textu. Např. kritérium vyhledávání je písmeno B, podoba bunky v matici je však A|B|C. Děkuji
OdpovědětVymazatDobrý den,
OdpovědětVymazatnerozumím přesně, nicméně obecně si zřejmě budete muset kromě VLOOKUP pohrát i s funkcemi ČÁST, ZLEVA, CONCATENATE...
Pokud chcete, pošlete příklad na info@excelentnitriky.com.
J.
Zdravím, mám dvě tabulky, obě jsou o dvou sloupcích. V první tabulce je v prvním sloupci(např "B")a ve vedlejším ("C") jsou nějaké číselné hodnoty. Datumy ve sloupci "A" jdou od shora sice popořadě, ale nejsou tam všechna, takže např.: 4.1.2014,8.1.2014, 10.1.2014. Ve druhé tabulce jsou sloupce "C" a "D". Ve sloupci "C" jsou také datumy, ale všechny (od 1.1. do 31.12.). Potřebuji toto: do sloupce "D" vepsat hodnoty, a to buď nulu (v případě, že datum v "C" není ve sloupci "A"), a nebo hodnotu z "B". Výsledná druhá tabulka ("C" a "D") tedy bude taková, že vedle dat, která jsou obsažená v "A" budou stejné hodnoty jako v "B". V ostatních datumech (neobsažených v "A") bude nula. Jaký je, prosím, vzorec do sloupce "D"? Díky moc
OdpovědětVymazatJestli to dobře chápu, použil bych takovouto funkci:
OdpovědětVymazat=KDYŽ(JE.CHYBHODN(SVYHLEDAT(C1;A:B;2;0));"hodnota chybí";SVYHLEDAT(C1;A:B;2;0))
Tedy pokyn Excelu: Když funkce SVYHLEDAT nic nenajde, napiš "hodnota chybí". Jinak (když něco najde) napiš to, co najdeš.
dobry den, mam podobny problem. V stlpci A sa vybera nazov skoly z rozbalovacieho zoznamu. na zaklade tejto hodnoty sa v stlpcoch B a C priraduju hodnoty mesto a kraj z pomocnej tabulky umiestnenej na inom liste. ked vzorec vlookup roztiahnem do stlpcov B a C riadky v tychto stlpcoch kde este nebola pridelena hodnota v stlpci A (to sa doplna postupne)sa vyplnia krizikmi s popisom N/A. neda sa to obist nejakou podmienkou tak, aby ostali prazdne a zaplnili sa az vtedy ked sa vyberie hodnota v stlpci A? inymi slovami v pomocnej tabulke nesmu byt prazdne bunky, inak mi to nefunguje
OdpovědětVymazatdakujem frantisek
Mělo by stačít zkombinovat KDYŽ a SVYHLEDAT.
OdpovědětVymazatTedy nějak takto: KDYŽ(je.prázdné(A1;"";SVYHLEDAT(...))
mate pravdu, uz to ide tak ako treba. dakujem pekne za usmernenie
OdpovědětVymazatDEKUJI MOC! Jste vyborny ucitel. Jen bych nekde v clanku vypichl dulezitou tuto Vasi vetu, ktera me zachranila, do te doby mi to neslo: "To není problém - jen musíte mít sloupec s názvy měsíců až za sloupcem za tím, podle čeho hledáte. Čili přeskládat sloupečky - přičemž pamatujte, že pokud si nechcete tabulku zpřeházet, můžete použít sloupečky skryté."
OdpovědětVymazatRespektive ji nejak obecne prepsat, ze ta hodnota, ktera se ma priradit podle vyhledane hodnoty, musi byt vic vpravo.
Jestli jsem to tedy dobre pochopil, kazdopadne, kdyz jsem to mel naopak, neslapalo to, coz bylo k zesileni.
Jeste jednou diky.
Doplnil jsem do textu. Pokud ale chcete doplňovat z tabulky s "prohozenými" sloupci, doporučuji podívat se i na toto:
OdpovědětVymazathttp://www.excelentnitriky.com/2013/09/figl-s-kombinaci-funkci-index-match.html
Mockrát upřímně děkuji, ani nevíte, kolik jste mi ušetřil času! Kéž bych vám mohl taky nějak pomoci :-)
OdpovědětVymazatMějte se.