Vyhledávání v Excelu, když funkce SVYHLEDAT nefunguje

Vyhledávání v Excelu a přiřazování hledaných hodnot je jedním z nejčastějších úkolů, které se v Excelu řeší. I přesto, že je funkce SVYHLEDAT základní funkcí pro vyhledávání v Excelu, tak funkce SVYHLEDAT ovšem v mnoha praktických příkladech nefunguje bez toho, aniž byste museli změnit strukturu zdrojových tabulek. Proto vám v dnešním videu ukáži tři jiné metody, které můžete pro vyhledávání v Excelu použít. Jako bonus, všechny metody fungují jak na horizontální, tak vertikální tabulky.

Excelový soubor ke stažení:

Vyhledávání v Excelu bez funkce SVYHLEDAT

V dnešním příkladu máme ve zdrojové tabulce sloupce se společnostmi, odvětvím, ve kterém společnosti působí, sídly společností a jejich fiktivními tržbami. Našim úkolem je najít tržby k vybraným společnostem. Zádrhel je ovšem v to, že společnosti máme v tabulce zcela vpravo, takže nemůžeme použít funkci SVYHLEDAT, jelikož ta není schopná vyhledávat směrem doleva od hodnot, dle kterých vyhledáváme. Jak tedy příklad vyřešit, pokud nemůžeme změnit pořadí sloupců ve zdrojových datech?

Následující metody vyhledávání v Excelu fungují pouze tehdy, pokud se v seznamu hodnot nevyskytují duplicitní záznamy. Funkce INDEX a POZVYHLEDAT, SVYHLEDAT, SUMIF i XLOOKUP fungují pouze na jedinečné seznamy hodnot. Pokud potřebujete vyhledávat v seznamu s duplicitami, tak můžete použít trik s funkcí SVYHLEDAT nebo použít jinou metodu vyhledávání v Excelu s duplicitami

 

Funkce INDEX a POZVYHLEDAT/MATCH

Kombinace funkcí INDEX a POZVYHLEDAT (anglicky funkce MATCH) je skvělá pro vyhledávání v Excelu. Tato kombinace funkcí vám totiž dovoluje vyhledávat bez omezení, která limitují funkci SVYHLEDAT. Na rozdíl od funkce SVYHLEDAT umí tato kombinace funkcí vyhledávat napravo od zadaných hodnot, stejně jako v řádcích nebo sloupcích, takže nemusíte používat dvě různé funkce jako SVYHLEDAT a VVYHLEDAT.

Pokud potřebujete bližší vysvětlení toho, jak funkce INDEX a POZVYHLEDAT fungují, tak se podívejte na video a článek o funkcích INDEX a POZVYHLEDAT

= INDEX (pole; řádek; [sloupec])

= POZVYHLEDAT/MATCH (co; prohledat; [shoda])

Funkce INDEX se u vyhledávání chová trochu jako GPS v mapě. Najde vám hledanou hodnotu, pokud ji zadáte správné souřadnice. Funkce INDEX tedy vrátí hodnotu, kterou hledáte, a to dle koordinací, které ji zadáte. U vyhledávání tedy vždy začneme funkcí INDEX, kde v parametru pole označíme hledané hodnoty. V našem příkladu hledáme tržby společností, takže označíme sloupec C5:C20. Jelikož máme ale v plánu stahovat vzorec dolu pro všechny společnosti, tak musíme sloupec řádně zafixovat ($C$5:$C$20). 

Na rozdíl od funkce SVYHLEDAT zde není potřeba označovat celou zdrojovou tabulku, ale pouze sloupec s hledanými hodnotami. Ve funkci INDEX následuje argument řádek. A právě tento argument ve funkci INDEX nahradí funkce POZVYHLEDAT. 

Prvním argumentem ve funkci POZVYHLEDAT je, co hledáme. Na tomto místě označíme první společnost, kterou hledáme, v našem případě buňku G5. Druhým argumentem funkce POZVYHLEDAT je prohledat. To je oblast, ve které se nachází hodnoty, dle kterých hledáme. V našem případě vyhledáváme dle společností, takže označíme sloupec D5:D20 ve zdrojové tabulce. Opět nesmíme zapomenout sloupec řádně zafixovat ($D$5:$D$20). Poslední parametr shoda ve funkci POZVYHLEDAT je velmi podobný nepovinnému argumentu u funkce SVYHLEDAT – určuje jakou shodu hledáme. Pro náš příklad použijeme přesnou shodu a vyplníme nulu. Ukončíme závorky a funkce potvrdíme.

Obrázek č.1 Funkce INDEX a POZVYHLEDAT pro vyhledávání v Excelu

Funkce XLOOKUP

Funkce XLOOKUP je dostupná předplatitelům služby Office 365. Jedná se o vylepšenou verzi funkce SVYHLEDAT. Na rozdíl od funkce SVYHLEDAT vyhledává funkce XLOOKUP i směrem doleva. Funkce XLOOKUP tak odstranila největší nevýhodu funkce SVYHLEDAT.

= XLOOKUP(co; prohledat; vrátit; [pokud nenalezeno];[režim shody];[režim vyhledávání])

Funkce XLOOKUP má tři základní povinné parametry. Prvním parametrem je, co hledáme. Druhým parametrem je sloupec, ve kterém hodnotu hledáme a posledním povinným argumentem je, co chceme vrátit, tedy pole, ve kterých se nacházejí naše hledané odpovědi. To je v podstatě celé, ostatní parametry jsou nepovinné. Pokud předpokládáme, že by se hledaná hodnota nemusela v seznamu vyskytovat můžeme rovnou vyplnit i nepovinný argument Pokud nenalezeno. V tomto parametru můžeme specifikovat, co chceme vrátit, pokud hodnota nalezena nebude. Odpadá tak nutnost balit funkci do funkce IFERROR, tak jak tomu bylo u funkce SVYHLEDAT. 

V našem příkladu tak ve funkci XLOOKUP jako první parametr označíme buňku, kterou hledáme (J5). Jako druhý parametr označíme sloupec, dle kterého vyhledáváme, což je sloupec se společnostmi ve zdrojové tabulce ($E$5:$E$20). Posledním parametrem je, co chceme vrátit, v našem příkladu chceme vrátit tržby, takže označíme celý sloupec ($C$5:$C$20). Na rozdíl od funkce SVYHLEDAT je funkce XLOOKUP nastavená v základním nastavení na režim přesné shody, takže další parametry nemusíme vyplňovat. 

Obrázek č.2 Funkce XLOOKUP pro vyhledávání v Excelu

Funkce SUMIF

Věděli jste, že pro vyhledávání v tabulce, ve které se nevyskytují duplicity, můžete použít i funkci SUMIF

= SUMIF(oblast; kritéria; [součet])

Pro vyhledávání v Excelu do funkce SUMIF napíšeme do prvního parametru funkce sloupec, dle kterého chceme vyhledávat, což je sloupec se společnostmi ($E$5:$E$20). Následuje parametr kritéria, což je v tomto případě první hledaná společnost, tedy buňka M5. Posledním parametrem je součet, což je hledaný sloupec s tržbami ($C$5:$C$20). Právě proto, že se pro vyhledávání používá funkce SUMIF a parametr součet, tak se nesmí v seznamu vyskytovat duplicitní záznamy. Pokud by se ve zdrojové tabulce vyskytovaly něktré společnosti duplicitně, tak by funkce SUMIF tržby sečetla a došlo by k navrácení nesprávného výsledku. 

Obrázek č.3 Funkce SUMIF pro vyhledávání v Excelu

Vyhledávání v Excelu u horizontální tabulky

Funkce INDEX a POZVYHLEDAT, XLOOKUP, SUMIF lze využít i pro vyhledávání v Excelu u horizontálně otočených tabulek. Na rozdíl od funkce SVYHLEDAT, která na horizontálně orientovanou tabulku nefunguje a musíte použít funkci VVYHLEDAT, můžete tyto funkce použít i na horizontální data. Ukázku použití funkcí INDEX a POZVYHLEDAT, XLOOKUP a SUMIF v horizontální poloze vidíte na obrázku č.4. 

Obrázek č.4 Vyhledávání v Excelu horizontální tabulka

MOHLO BY VÁS ZAJÍMAT

Dynamický prodejní report v Excelu | Microsoft 365

V dnešním videu se podíváme na to, jak pomocí dynamických funkcí v Excelu vytvořit plně dynamický prodejní report. Dynamické funkce, které budeme dnes pro vytvoření prodejního reportu

Začínáme s Power BI | Excel a Power BI

V dnešním videu se podíváme na základy práce v Power BI. Power BI je nástroj od společnosti Microsoft, který slouží k interaktivní vizualizaci a analýze dat. Specifikem Power

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *