Funkce SVYHLEDAT patří k nejvíce používaným nástrojům v Excelu. Běžně se využívá pro vyhledávání přesných shod, jako jsou například ID produktů nebo zákazníků. V tomto článku se ale podíváme na to, jak efektivně použít SVYHLEDAT pro vyhledávání v rozsahu hodnot.
Praktický příklad č. 1: Přiřazování známek na základě dosaženého skóre u zkoušky
Představme si, že máme seznam výsledků zkoušek a ke každému dosaženému skóre chceme přiřadit odpovídající známku. Ve sloupci A naší tabulky jsou uvedeny skutečné body ze zkoušek a ve sloupci B budeme zobrazovat vypočítané známky. Současně máme vpravo další tabulku (sloupce D a E), kde jsou uvedeny hranice skóre pro jednotlivé známky.
S pomocí funkce SVYHLEDAT můžeme na základě hodnot v rozsahu sloupce D přiřadit písmenné hodnocení ze sloupce E ke všem dosaženým výsledkům zkoušek.
Struktura vzorce SVYHLEDAT
Než přejdeme k aplikaci vzorce na náš konkrétní příklad, zopakujme si rychle syntaxi funkce SVYHLEDAT:
=SVYHLEDAT(hledaná_hodnota, tabulka, index_sloupce, typ_vyhledávání)
Jednotlivé proměnné ve vzorci mají tento význam:
hledaná_hodnota: Toto je hodnota, kterou hledáme. V našem případě to bude skóre ve sloupci A, začínaje buňkou A2.
tabulka: Často se neformálně označuje jako vyhledávací tabulka. U nás je to tabulka obsahující hranice skóre a k nim příslušné známky (rozsah D2:E7).
index_sloupce: Jedná se o číslo sloupce, ze kterého se budou vracet výsledky. V našem příkladu je to sloupec B, ale protože funkce SVYHLEDAT vyžaduje číselné označení, bude to sloupec 2.
typ_vyhledávání: Zde se jedná o logickou hodnotu, která může být buď PRAVDA, nebo NEPRAVDA. Používáme vyhledávání v rozsahu? V našem případě je odpověď ANO (což se v rámci funkce SVYHLEDAT reprezentuje jako „PRAVDA“).
Kompletní vzorec pro náš příklad tedy vypadá následovně:
=SVYHLEDAT(A2;$D$2:$E$7;2;PRAVDA)
Oblast tabulky je zde fixována pomocí absolutních odkazů ($), aby se při kopírování vzorce do dalších buněk sloupce B neměnila.
Na co si dát pozor
Při vyhledávání v rozsazích pomocí funkce SVYHLEDAT je velmi důležité, aby byl první sloupec vyhledávací tabulky (v tomto příkladu sloupec D) seřazen vzestupně. Vzorec se spoléhá na toto pořadí, aby mohl správně umístit hledanou hodnotu do patřičného rozsahu.
Níže je uveden výsledek, který by nastal, pokud bychom vyhledávací tabulku seřadili podle písmenné známky namísto skóre.
Je důležité si uvědomit, že pořadí je klíčové pouze při vyhledávání v rozsahu. Pokud na konci funkce SVYHLEDAT zadáte hodnotu NEPRAVDA, pořadí není tak důležité.
Praktický příklad č. 2: Výpočet slevy na základě celkové útraty zákazníka
V tomto případě máme k dispozici data o prodejích. Chceme poskytovat slevy z prodejních cen, přičemž procento slevy je odvislé od celkové částky, kterou zákazník utratil.
Vyhledávací tabulka (sloupce D a E) obsahuje slevy pro jednotlivé intervaly útrat.
Pro získání správné slevy z tabulky můžeme použít následující vzorec SVYHLEDAT:
=SVYHLEDAT(A2;$D$2:$E$7;2;PRAVDA)
Tento příklad je zajímavý, protože jej můžeme použít přímo ve vzorci k výpočtu výsledné ceny po odečtení slevy.
Uživatelé Excelu často vytvářejí komplikované vzorce pro tento typ podmíněné logiky. Avšak s funkcí SVYHLEDAT dosáhneme stejného výsledku mnohem elegantněji.
Níže je uveden vzorec, kde je funkce SVYHLEDAT včleněna pro výpočet konečné ceny po odečtení vypočítané slevy z částky prodeje ve sloupci A.
=A2-A2*SVYHLEDAT(A2;$D$2:$E$7;2;PRAVDA)
Funkce SVYHLEDAT se nehodí jen pro vyhledávání konkrétních záznamů, jako jsou zaměstnanci nebo produkty. Je mnohem univerzálnější, než si mnozí myslí, a její využití pro vyhledávání v rozsahu hodnot je toho jasným důkazem. Navíc ji můžeme použít jako elegantní alternativu k jiným, složitějším vzorcům.