V dnešním videu si ukážeme 5 užitečné triků s nejpoužívanější vyhledávací funkcí SVYHLEDAT, anglicky funkcí VLOOKUP. Tyto triky zajistí, že bude funkce SVYHLEDAT dynamičtější. Ukážeme si třeba, jak vyjádřit ve funkci SVYHLEDAT sloupec dynamicky, jak vyhledávat v přeházených sloupcích, jak vyhledávat z více tabulek a nakonec si ukážeme i trik pro profíky, což je, jak překonat největší nevýhodu funkce SVYHLEDAT, neboli, jak vyhledávat s funkcí SVYHLEDAT doleva.
Pro použití funkce SVYHLEDAT s duplicitami se podívejte na video Vyhledávání s duplicitami pomocí funkce SVYHLEDAT.
V některých případech je lepší použít i jiné vyhledávací funkce Když SVYHLEDAT nefunguje (INDEX & POZVYHLEDAT, XLOOKUP)
Excelový soubor ke stažení
Dynamický sloupec
V prvním příkladu máme tabulku s měsíci, produkty a hodnotami. Vedle tabulky chceme pro vybraný produkt (H4) vyhledat hodnotu. Nicméně chceme hodnotu vyhledat na základě měsíce, který vybereme (I3). Jednou z největších nevýhod funkce SVYHLEDAT je to, že ve třetím parametru funkce sloupec, musíme natvrdo napsat pořadové číslo sloupce, ze kterého chceme vrátit hledanou hodnotu. Zkusíme to.
Napíšeme funkci SVYHLEDAT, kde jako hledanou hodnotu označíme hledaný produkt (H4). Následuje tabulka, což je celá zdrojová tabulka (A4:F11). A teď následuje pořadové číslo sloupce. A zde je právě problém. Pokud vyberu leden, chci vyhledávat ve druhém sloupci tabulky, kde je leden, když ale vyberu květen, tak chci vyhledávat v šestém sloupci tabulky atd. Potřebuji tedy vyjádřit parametr sloupec ve funkci SVYHLEDAT dynamicky. S tím nám pomůže další vyhledávací funkce POZVYHLEDAT, anglicky funkce MATCH.
Místo natvrdo napsaného pořadového čísla sloupce zde napíšeme funkci POZVYHLEDAT, kde jako co hledáme označíme hledaný měsíc (I3). Kde tento měsíc hledáme? Hledáme ho v záhlaví zdrojové tabulky (A3:F3) a hledáme přesnou shodu. Ukončíme závorku u funkce POZVYHLEDAT a přepneme se do posledního parametru funkce SVYHLEDAT, kde rovněž hledáme přesnou shodu, takže nulu. Funkci potvrdíme.
Máme vybraný leden, takže funkce SVYHLEDAT správně našla hodnotu pro vybraný produkt v měsíci leden. Pokud ale měsíc změníme na duben, tak se hodnota správně přepočítá, jelikož funkce POZVYHLEDAT doručí ve funkci SVYHLEDAT správné pořadové číslo sloupce.
Pořadová čísla ve sloupci
V dalším příkladu máme stejnou tabulku, ale tentokrát chceme vyhledat hodnoty pro zvolený produkt pro všechny měsíce. Klasicky bychom museli vytvořit funkci SVYHLEDAT pro leden, kde by bylo pořadové číslo sloupce 2, jelikož leden je druhý sloupec zdrojové tabulky. Potom bychom funkci zkopírovali, vložili do února a pořadové číslo sloupce změnili na trojku a tak dále pro všechny měsíce.
A nebo si můžeme ulehčit práci pomocí funkce SLOUPCE. Začneme s funkcí SVYHLEDAT, kde označíme hledaný produkt (H15). Jelikož budeme vzorec přetahovat doprava, tak musíme buňku plně zafixovat klávesou F4 ($H$15). Následuje tabulka, kde tento produkt hledáme (A15:F22), opět plně zafixována klávesou F4 ($A$15:$F$22). A místo parametru sloupce napíšeme funkci SLOUPCE.
Ve funkci SLOUPCE napíšeme koordinace buňky, ve které se právě nacházíme (I15), dvojtečka a opět ta samá buňka (I15:I15). První buňku v tomto rozpětí ovšem zafixujeme plně klávesou F4 ($I$15:I15). Ukončíme závorku u funkce SLOUPCE. Funkce SLOUPCE teď vrátí číslo jedna, jelikož rozpětí mezi těmito buňkami je jeden sloupec. Tím, že jsme ale první buňku zafixovali, tak jak funkci potáhneme doprava, tak se bude rozpětí zvětšovat a funkce SLOUPCE vrátí pořadová čísla 1,2,3 atd. Nicméně k funkci SLOUPCE musíme ještě přičíst jedničku, jelikož leden je ve zdrojové tabulce ve skutečnosti druhým sloupcem, nikoliv prvním.
Středník a následuje poslední parametr shoda ve funkci SVYHLEDAT, což je přesná shoda. Ukončíme závorku a funkci potvrdíme. Přetáhneme funkci doprava a funkce SVYHLEDAT díky funkci SLOUPCE vrací správné hodnoty.
Přeházené sloupce
Trik s vyhledávací funkcí POZVYHLEDAT můžete použít i v případě, že budete mít sloupce, dle kterých chcete vyhledávat přeházené. Zdrojová tabulka je stejná jako v předchozích příkladech, opět chceme vyhledávat produkt, ale měsíce pro vyhledávání máme přeházené.
Začneme tedy s funkcí SVYHLEDAT, kde označíme hledaný produkt, který plně zafixujeme klávesou F4 ($H$26). Následuje zdrojová tabulka, plně zafixovaná ($A$26:$F$33). A místo parametru sloupce napíšeme funkci POZVYHLEDAT, kde nejprve označíme hledaný měsíc, který zafixujeme pouze pro řádky (I$25), kde tento měsíc hledáme? Hledáme ho v záhlaví zdrojové tabulky, kterou plně zafixujeme ($A$25:$F$25). A hledáme přesnou shodu. Ukončíme závorku a hledáme přesnou shodu i ve funkci SVYHLEDAT. Ukončíme funkci, potvrdíme a přetáhneme funkci doprava pro všechny měsíce. Funkce POZVYHLEDAT zajistila, že funkce SVYHLEDAT našla správné hodnoty i v případě přeházených sloupců.
SVYHLEDAT z více tabulek
V dalším příkladu máme dvě zdrojové tabulky. Jedna tabulka obsahuje hodnoty pro leden a druhá pro únor. Naším úkolem je vyhledat správnou hodnotu podle zvoleného produktu a měsíce. Zde máme dvě možnosti, jak to vyřešit. Můžeme použít funkci KDYŽ, anglicky funkci IF nebo funkci IFS a nebo můžeme použít funkci NEPŘÍMÝ.ODKAZ neboli funkci INDIRECT.
Začneme s funkcí KDYŽ. Do pole hodnota (I38) napíšeme funkci KDYŽ, kde podmínka bude, pokud se vybraný měsíc rovná lednu, slovo leden musí být v uvozovkách, jelikož se jedná o textovou hodnotu (I37=”Leden”). Pokud se měsíc bude rovnat lednu, tak chceme funkci SVYHLEDAT, kde vybereme hledaný produkt (I36). Tabulka je v tomto případě lednová tabulka, takže označíme celou tabulku (A37:B44). Hledaná hodnota je ve druhém sloupci a hledáme přesnou shodu. Ukončíme závorku u funkci SVYHLEDAT a přepneme se do parametru ne u funkce KDYŽ.
Máme jen dvě tabulky, takže pokud podmínka splněná nebude, tak se musí jednat o únorovou tabulku, takže opět napíšeme funkci SVYHLEDAT, kde opět hledáme produkt (I36), ale tentokrát ho hledáme v únorové tabulce (D37:E44), sloupec je opět druhý a hledáme přesnou shodu.
Ukončíme závorky u funkce SVYHLEDAT i KDYŽ a potvrdíme. Funkce vrací správný výsledek. Změníme měsíc a funkce se správně přepočítala.
Druhou možností je použít funkci INDIRECT neboli NEPŘÍMÝ.ODKAZ a pojmenované oblasti. V takovém případě bychom nejprve označili lednovou tabulku a přejmenovali oblast na Leden. Důležité je, aby se název oblasti shodoval s pojmenováním měsíce, podle kterého vyhledáváme. Druhou tabulku přejmenujeme na Únor. Potvrdíme a můžeme se vrhnout na funkci SVYHLEDAT.
Napíšeme funkci SVYHLEDAT, kde nejprve označíme hledaný produkt (I36). Následuje tabulka, kterou najdeme pomocí funkce NEPŘÍMÝ.ODKAZ. V této funkci pouze označíme hledaný měsíc (I37). Právě zde je důležité, aby se názvy měsíce shodovali. Funkce NEPŘÍMÝ.ODKAZ totiž najde oblast, kterou určí buňka a doručí správnou tabulku. Následuje sloupec, což je druhý sloupec a přesná shoda. Funkci ukončíme, potvrdíme a zkontrolujeme výsledek.
SVYHLEDAT a vyhledávání doleva
Poslední trik, který vám ukáži je, jak oblafnout funkci SVYHLEDAT, aby vyhledávala doleva. Každý kdo někdy funkci SVYHLEDAT použil zjistil, že funkce funguje jen v případě, že se hledaná hodnota nachází ve zdrojové tabulce nalevo od hledaných hodnot. Pokud se nachází vpravo, tak funkce SVYHLEDAT nefunguje a vrátí chybovou hlášku. V takovém případě pak musíte použít jiné vyhledávací funkce jako INDEX & POZVYHLEDAT nebo XLOOKUP. Co když ale chcete použít funkci SVYHLEDAT a hledané hodnoty máte vpravo?
Základem triku je funkce ZVOLIT neboli funkce CHOOSE, která je dostupná v Excelech od verze 2010. Funkce ZVOLIT nám dovoluje zvolit si dle indexového čísla hodnoty. My tuto funkci ale dnes využijeme k tomu, aby nám vrátila virtuální tabulku, kde bude ale sloupec s produkty vlevo, místo vpravo. Napíšeme funkci ZVOLIT, kde v indexovém číslu napíšeme složenou závorku, kterou napíšeme stisknutím pravého ALT a písmene B. Zde napíšeme 1\2\3\4 a ukončíme složenou závorku {1\2\3\4}. Toto je vyjádření pro 4 sloupce. Sloupec jedna, sloupec dva, tři a 4.
Následuje hodnota 1. Zde označíme sloupec ve zdrojové tabulce, který chceme ve virtuální tabulce zobrazit jako první, což je právě sloupec s produkty (D48:D55). Následuje hodnota dva, tedy druhý sloupec, což je leden (A48:A55), následuje sloupec s únorem (B48:B55) a březnem (C48:C55). Ukončíme závorku u funkce ZVOLIT a podíváme se, co funkce vrací. Ti z vás, kteří používají Excely 2021 a Office 365 mohou potvrdit klávesou ENTER. Ti z vás, kteří používají starší verze Excelu, pokud potvrdí vzorec klávesou ENTER, tak se jim zobrazí pouze první hodnota a je to proto, že Excel nemůže vrátit všechny hodnoty do jedné buňky. Pokud se chcete podívat na to, co opravdu funkce vrací, tak funkci označte a zmáčkněte klávesu F9. Zobrazí se vám celá tabulka po jednotlivých řádcích. Funkce tedy vrací správný výsledek, vrací celou tabulku ve virtuální podobě, kde jsou produkty prvním sloupcem tabulky. Názorně to můžete vidět u mě, jelikož nové Excely umí maticové funkce zobrazit, tak jak to vidíte u mě.
Základ tedy máme. Funkce vrací tabulku se správným pořadím sloupců. Teď můžeme funkci ZVOLIT zabalit do funkce SVYHLEDAT. Před funkci napíšeme funkci SVYHLEDAT, kde označíme hledaný produkt (H48). Tabulka je celá funkce ZVOLIT, která doručuje virtuální tabulku se správně seřazenými sloupci. Následuje parametr sloupce. Potřebujeme, aby hodnota reagovala a vybraný měsíc, takže použijeme funkci POZVYHLEDAT, kde označíme hledaný měsíc (I47), tento měsíc hledáme v záhlaví zdrojové tabulky (A47:D47) hledáme přesnou shodu. Ukončíme závorku a k funkci POZVYHLEDAT musíme ještě přičíst jedničku, jelikož nesmíme zapomenout na to, že ve virtuální tabulce máme jako první měsíc produkt, nikoliv leden. Hledáme přesnou shodu i u funkce SVYHLEDAT. Potvrdíme a vrátil se správný výsledek. Zkusíme změnit měsíc a funkce reaguje správně, a to i přesto, že máme ve zdrojové tabulce produkty úplně vpravo.
Jedna odpověď
Tu funkci zvolit nepoužívám a toto řešení by mě nenapadlo. Díky za tip.
Přesto u takovýchto případů používám, dle mého názoru jednodušší řešení, kombinaci pozvyhledat a index. Vypadalo by to takto: =INDEX($A$48:$C$55;POZVYHLEDAT($H$53;$D$48:$D$55;0);POZVYHLEDAT($I$52;A47:D47;0))