Vyhledávání v Excelu s duplicitami není vždy úplně jednoduchý úkol, jelikož pro ně nemůžeme použít standardní vyhledávací funkce jako SVYHLEDAT nebo kombinaci funkcí INDEX a POZVYHLEDAT a dokonce ani XLOOKUP. Všechny tyto funkce totiž vždy vyhledají pouze první shodu a tu přiřadí ke všem opakujícím se položkám. Pokud tedy potřebujeme k duplicitním položkám najít všechny správné hodnoty, musíme zpravidla použít kombinace funkcí pro vyhledávání v Excelu s duplicitami nebo jiné triky, jak tuto nepříjemnost obejít. Jeden takový trik s funkcí SVYHLEDAT vám dnes ukáži v tomto videu.
Excelový soubor ke stažení:
Vyhledávání v Excelu s duplicitami
V dnešní zdrojové tabulce máme společnosti, jejich tržby, odvětví, ve kterém působí a jejich sídlo. Naším úkolem je přiřadit do druhé tabulky k doplnění tržby u hledaných společností. Jelikož máme zdrojovou tabulku malou, tak je na první pohled patrné, že ve zdrojové tabulce máme duplicity. Některé společnosti se nám opakují, ale mají rozdílné tržby. Z takto malé zdrojové tabulky jsou hned patrné opakující se názvy společností, co kdyby ale tabulka měla tisíce řádků?
Než začnete s vyhledávacími funkcemi v Excelu, tak je dobré si vždy ověřit, zda nemáte ve zdroji duplicitní záznamy. V našem příkladu můžeme pro odhalení duplicit použít jednoduchý trik s funkcí COUNTIF. Vedle zdrojové tabulky si vytvoříme kontrolní sloupec, kde do buňky G5 napíšeme funkci COUNTIF. Pokud jste předplatiteli služby Office 365 a máte k dispozici nové array funkce, tak můžete ve funkci COUNTIF označit v prvním parametru celý sloupec se společnosti (C5:C22) a jako kritérium označit opět celý sloupec se společnostmi (C5:C22). U starších Excelů se ve funkci COUNTIF označí jako první parametr nejprve sloupec se společnostmi ($C$5:$C$22) a jako kritérium se označí první společnost v seznamu (C5). Na rozdíl od verze s Office 365 se tato funkce musí stáhnout pro všechny řádky.
Rozdíl v zápisech vidíte na obrázku č.2.
Funkce COUNTIF spočítala výskyt duplicitních položek v seznamu. U prvního výskytu názvu společnosti se objevila na řádku jednička, pokud se název společnosti opakuje, tak funkce COUNTIF přiřadí pořadové číslo. Pokud máme tedy máme ve sloupci Kontrola jiné číslo než jedna, tak víme, že máme v seznamu duplicitní záznamy a nemůžeme tedy použít k vyhledávání klasické vyhledávací funkce.
Funkce SVYHLEDAT s duplicitami
Pokud nechceme k vyhledávání s duplicitami v Excelu používat složité maticové vzorce, tak si u funkce SVYHLEDAT můžeme pomoci s pomocným sloupcem. Funkce SVYHLEDAT potřebuje ke svému správnému fungování jedinečné hodnoty, takže musíme z duplicitních záznamů udělat jedinečné hodnoty.
I k tomuto účelu můžeme využít funkce COUNTIF. Z důvodu toho, že funkce SVYHLEDAT vyhledává vždy doprava, si před zdrojovou tabulku napíšeme další pomocný sloupec, ve kterém si opět pomůžeme s funkcí COUNTIF. Pomocí funkce COUNTIF vytvoříme dynamické rozpětí, které před každý název společnosti přiřadí pořadové číslo, a to dle toho, kolikrát se daný název společnosti vyskytuje ve zdrojové tabulce. Dynamické rozpětí se vytvoří tak, že ve funkci COUNTIF v prvním parametru označíme rozpětí C5:C5, což je první buňka. První buňku C5 zafixujeme pro řádky i sloupce klávesou F4 ($C$5). Jako kritérium označíme opět buňku C5. Tím, že jsme první buňku v prvním parametru zafixovali, tak se tím, jak funkci budeme stahovat po řádcích dolů bude tvořit dynamické rozpětí buněk, které spočítá, kolikrát se daný název nachází v tabulce a přiřadí pořadové číslo. K funkci COUNTIF nakonec přiřadíme pomocí znaku & název společnosti.
Ve sloupci B tak díky spojení funkce COUNTIF a názvu společnosti vznikl pro každý řádek jedinečný název, který teď můžeme využít pro vyhledávání v Excelu ve funkci SVYHLEDAT.
V zásadě teď máte dva způsoby, jak funkci SVYHLEDAT s jedinečnými názvy použít. Jedinečné názvy se totiž musí opakovat i v tabulce k doplnění.
Možnost I. – Vytvoříme stejný pomocný sloupec s jedinečnými názvy i před tabulkou k doplnění. V tomto případě bychom ve funkci SVYHLEDAT následně jako hledaný výraz použili hodnoty z tohoto pomocného sloupce.
Možnost II. – Celou funkci COUNTIF, která vytvoří jedinečné názvy společností zakomponujeme do funkce SVYHLEDAT. V tomto případě budeme potřebovat pouze jeden pomocný sloupec u zdrojové tabulky.
Rozdílný zápis těchto dvou možností je vidět na obrázku č. 4. Ať už použijete jakoukoliv variantu zápisu funkce SVYHLEDAT, tak nesmíte zapomenout na to, že vyhledáváte dle pomocného sloupce B, musíte tedy i tento sloupec zahrnout do parametru tabulka ve funkci SVYHLEDAT.
Jedna odpověď
Dobrý den,
chci upozornit na chybu ve vaší “zdrojové” tabulce.
Funkce countif nezobrazí v tomto případě pořadové číslo, ale počet výskytů? Výsledek tak bude 2x 2MAKRO a ne jak uvádíte 1MAKRO a 2MAKRO.
Vaši tabulku jsem si 1:1 přepsal a vzorec vyzkoušel.
Přeji pěkný den,
Olda