SVYHLEDAT / VLOOKUP – funkce pro propojování více tabulek

V tomto článku je popsán způsob, jak pomocí funkce SVYHLEDAT (anglicky VLOOKUP) přiřazovat hodnoty ze dvou tabulek. Takovéto přiřazování je nejtypičtějším použitím funkce SVYHLEDAT / VLOOKUP.

Příklad

Potřebuji z jedné tabulky doplňovat hodnoty do druhé. V modré tabulce vlevo chci vyplnit chybějící sloupec E - dle ceníku, který je v červené tabulce. Tedy např. k lízátkům chci přiřadit 18, k oplatkám 8 atd.

svyhledat

Návod

Pomůže mi funkce, která se jmenuje SVYHLEDAT (anglicky VLOOKUP). Do buňky E2 zapíšu:

  • =SVYHLEDAT(B:B;G:H;2;0)

Protože:

  • "=" začíná každou funkci
  • "SVYHLEDAT" je název funkce
  • "B:B" proto, že ve sloupci B se vyskytují názvy položek, podle kterých má Excel v pravé tabulce hledat příslušnou cenu. Pokud bych místo toho zadal B2, výsledek by byl stejný - je to druhý alternativní způsob zápisu.
  • "G:H" 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, kdy zařazuji do intervalů, a nebo v případě, kdy je výpočet pomalý a je třeba jej zrychlit. V případě použití jedničky musí být tabulka, ze které se čerpá, vzestupně seřazená - jinak funkce hází chybné hodnoty!

Pokud chci funkci zadat přes formulář (Vložit - Funkce...), vypadá zápis takto:

svyhledat dialog

 A výsledek vypadá takto:

svyhledat výsledek

Poznámky:

  • Kdyby tabulka, ze které se vybírá, byla "obrácená", použil bych místo funkce SVYHLEDAT funkci VVYHLEDAT a ve třetím parametru by bylo číslo řádku místo čísla sloupce, 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, a dokonce i v jiném souboru.
  • 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á.
  • Funkce SVYHLEDAT se dá nahradit funkcí DZÍSKAT / DGET. Liší se v tom, že pokud je nalezených hodnot více, tak SVYHLEDAT vrátí první z nich, zatímco DZÍSKAT vyrátí chybu.

Chcete-li si stáhnout tabulku, uvedenou v tomto článku, klikněte zde.

Související návody

Videonávod

22 comments on “SVYHLEDAT / VLOOKUP – funkce pro propojování více tabulek
  1. Jiří Beran napsal:

    Dobrý den,
    já úplně nerozumím zadání. Jestli chcete, pošlete mail na info@vyuka-excelu.cz s kouskem úkolu, který řešíte.
    J.

  2. Jiří Beran napsal:

    Dobrý den,
    pro ten celý sloupec by to fungovat mělo. Další možnost jsou pak absolutní odkazy:
    http://www.vyuka-excelu.cz/navody/funkce-a-vzorecky/navod-na-pouziti-absolutnich-odkazu/

  3. EVA napsal:

    Dobrý den
    Lze takto přiřadit číslo do tabulky dle dvou parametrů? Např. V tabulce, ze které chci odebírat hodnoty mám v jednom sloupečku: zboží, ve druhém: barva, ve třetím: cena. Potřebuji do druhé tabulky z výčtu zboží přiřadit cenu pouze k vybraným např. lízátkům červeným. Tedy parametry jsou lízátko/ červené = cena.
    Zkoušela jsem místo vašeho B:B dát jakoby B:C, ale funkce nefunguje.
    Děkuji 🙂

  4. Jiří Beran napsal:

    Dobrý den, tohle se dá obejít tak, že v obou tabulkách spojíte všechny sloupce do jednoho (např. pomocí funkce CONCATENATE), a pak hodnoty přiřazujete na základě těchto pomocných sloupečků.

  5. EVA napsal:

    Zkusila jsem, jde to 🙂 Díky

  6. Olda napsal:

    DObrý den,

    pomocí SVYHLEDAT přiřazuji pořadí jedince do tabulky celkových výsledků. Bohužel mám dva jedince stejného příjmení, tím pádem oba mají stejné umístění. Jak mohu rozšířit kritérium na Příjmení + Jméno?
    Děkuji

  7. Jiří Beran napsal:

    Jednoduchým trikem je spojit v obou propojených tabulkách jméno a příjmení dohromady (např. funkcí CONCATENATE). Pak máte v obou tabulkách např. „OndraNovak“. Timto spojenym klicem pak tabulky propojite beznou funkci svyhledat.

  8. Adam napsal:

    Dobrý den,
    vytvořil jsem si tabulku pro skladové zásoby náhradních dílů. Každý jeden list se jmenuje podle stroje ke kterému patří. Problém je, že některé součástky, jako např. filtry pasují na více strojů. Jde nějak propojit buňky ve více listech, abych na jednom listu ubral filtr a stejně tak by ubyl na dalších listech? Děkuji

  9. Kate napsal:

    Dobrý den, postupovala jsem přesně podle návodu, ale udělala se mi jen první řádka, zkopírovala jsem to tedy přes ten malý čtvereček, udělala jsem někde chybu?

  10. Kate napsal:

    Přes vložit funkci mi to opět udělá jen první řádku, není problém přes čtvereček funkci rozšířit, ale co když je položek 1000?

  11. lizal@laser-tech.cz napsal:

    Dobrý den, pročetl jsem si návody od vás i komentáře tu, ale řeším trošku odlišný problém a nejsem za to dopátrat se řešení, třeba budete vědět:
    Mám dvě kontingenční tabulky které obshují unikátní číslo zboží a cenu za jeden kus. Chtěl bych porovnat ceny za kus, ale jen u těch dílů co jsou v obou tabulkách a buď vypsat jen hodnoty které jsou v druhé tabulce vyší než v první příklad tabulka1 obsahuje díl 1122 za cenu 5Kč prohledám tabulku2 a zjistím že je tam za cenu 6Kč tak ať mi to vypíše na řádek díl a cenu a nebo vlastně stačilo by mi aby mi vypsal díl a cenu tam kde se ceny nebudou shodovat… prostě nějak porovnat ceny z obou tabulek a zjistit v které tabulce je to dražší a v které levnější

  12. Jiří Beran napsal:

    A nebylo by nejrychlejší udělat třetí tabulku, tam vypsat všechna zboží, natáhnout ceny z první tabulky, ceny z druhé tabulky, a porovnat je?

  13. Michal napsal:

    Zdravím, v návaznosti na dotaz Evy a Oldy si marně lámu hlavu nad tím, jak využít více parametrů aniž bych použil CONCATENATE.
    Lze to nějak? Chci zkrátka vyhledávat v tabulce podle více atributů, které bych potřeboval měnit. V SQL by to bylo „name=XYZ AND date=KLM and…“. Jak na to v Excelu?
    Díky za odpověď

  14. Jiří Beran napsal:

    Dobrý den Michale,
    s VLOOKUPem to myslím nejde. Dá se ale asi použít maticový zápis kombinace INDEXU a MATCH, viz tento návod:
    http://www.exceltactics.com/vlookup-multiple-criteria-using-index-match/
    Nemám s tím ale žádnou zkušenost, používám jednoduché spojení concatenate…

  15. Michal napsal:

    Dobrý den,

    mám problém s aplikací funkce SVYHLEDAT, protože zdrojová tabulka nemůže být seřazená vzestupně a funkce mi zřejmě proto nefunguje. Jedná se o to že ve zdrojové tabulce je seznam dokumentů podle jejich čísla a neustále se doplňuje. Chtěl jsem vytvořit na dalším listu tabulku, do které když vložím určitý seznam dokumentů, do sloupečku vpravo mi vypíše hodnoty z řádků vpravo ze zdrojové tabulky. Nemůžu přijít na to jestli je to tím seřazením zdroje nebo ne. Díky za radu.

  16. Jiří Beran napsal:

    Dobrý den, pokud v SVYHLEDAT dáte jako poslední argument nulu, pak tato funkce funguje stejně dobře pro seřazenou i neseřazenou tabulku. JB

  17. Jan Novák napsal:

    Dobrý den, pokud má vyhledávaný parametr více jak 255 znaků (přes CONCATENATE spojených několik buněk), SVYHLEDAT nefunguje (vrací CHYBHODN). Lze to nějak obejít ?

  18. Jiří Beran napsal:

    Nezkoušel jsem to, ale mělo by fungovat:
    =svyhledat(zleva(a1;244)&“*“;druhý argument;třetí argument;0)

  19. Jirka napsal:

    Dobrý den,
    potřeboval bych poradit jestli existuje nějaká funkce nebo jak vyřešit následující problém.
    Mám 1. tabulku kde se v prvním sloupci nachází celá jména např. Josef Dobrý a ve druhém sloupci jejich tituly např. Ing. V 2. tabulce chci aby se podle zadaného jména z rolovátka z první tabulky přiřadil titul.
    Řešil jsem to pomocí SVYHLEDAT. To funguje, když mám rozdílná jména (Jirka, Josef, Hozna), pokud ale použiju stejná jména, ale rozdílná příjmení (Josef Dobrý, Josef Opadavý), vyhazuje mi to hodnotu podle prvního nalezeného jména tedy v tomto případě Josef Dobrý.

  20. Jirka napsal:

    Už to funguje. problém jsem si vyřešil sám :D.

  21. zlata napsal:

    Dobry den,
    kde je tady chyba
    =VYHLEDAT(„C4:C“;’Source data‘!A:I;’Source data‘!3;0)
    EXCEL MI UPOZOTNUJE ZE Source data‘!3 to neni spravne

  22. Jiří Beran napsal:

    Zlata – chyba je úplně všude 🙂
    Na začátku to nesmí být v uvozovkách
    C4:C nelze – lze např. C:C nebo C4:C5 nebo C4 atd…
    ve třetím argumentu bude místo ’Source data‘!3 jenom 3
    a pak podle toho, jak vypadá tabulka.
    J.

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář
TOPlist