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

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

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.

V nových verzích Excelu je možné používat i modernější alternativu, funkci XLOOKUP.

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 vpravo v růžové tabulce. Tedy např. k lízátkům chci přiřadit 18, k oplatkám 8 atd.

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:

  • “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:

 A výsledek vypadá takto:

 

Poznámky pro náročnější:

  • Přibližně od roku 2020 je funkce SVYHLEDAT v některých verzích nahrazena funkcí XLOOKUP.
  • 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).
  • 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 vrátí chybu.

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

Funkci SVYHLEDAT si můžete procvičit tady

Související návody

Videonávod:

Funkci SVYHLEDAT se věnuje tahle lekce našeho elearningového kurzu:

Elearning středně pokročilý Excel:

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

1 Komentář

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

  6. 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. 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. 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. 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. 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. 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. 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. 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ěď

  15. 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. 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. 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. Nezkoušel jsem to, ale mělo by fungovat:
    =svyhledat(zleva(a1;244)&”*”;druhý argument;třetí argument;0)

  19. 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ý.

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

  23. Dobrý den,
    po použití funkce svyhledat se mi zvětšil soubor na 80 MB. Pro poslání mailem to je problém. Dá se potom tento soubor změnšit?

  24. Dalo by se nastavit, kdybych měl čtyři tabulky, a v každé bych měl jména lidí a počet hodin, které odpracovali za čtvrtletí, aby se sloučili do jedné jediné? Jména a odpracované hodiny za celý rok?

  26. Dobrý den. Potřebuji v tabulce vyhledat stejná data ve sloupci A a všechny nalezené včetně hodnot ve sloupci B zobrazit v jiné tabulce. Lze to nějakým způsobem? použila jsem SVYHLEDAT , ale ta vrátí pouze první nalezenou hodnotu a další ne. Děkuji, Renata

  27. Dobrý den,
    chtěla bych poprosit o radu, tvořím objednávkový list.
    V prvním listu mám tabulku s celkovou nabídkou služeb a potřebovala bych aby se mi na druhy list zkopírovaly jen řádky kde ve sloupci ks neni 0,takže pouze ty služby a materiál který si zákazník zvolil. Ať zkouším co zkouším, nemohu se dobrat výsledku.
    Děkuji Eva

Komentáře není možné přidávat