V dnešním videu vám ukáži trik, který většina uživatelů vůbec nezná – moderní způsob vyhledávání, díky kterému zobrazíte všechny shody v jediné buňce. Vyhledávání více hodnot k jedné položce byl v Excelu vždy problém. Vyhledávací funkce jako SVYHLEDAT, POZVYHLEDAT ale i XLOOKUP vždy vyhledají pouze první hodnotu, na kterou narazí. S výjimkou funkce XLOOKUP, která umí vyhledat buď první nebo poslední hodnotu, ale co když potřebujete dohledat k položce všechny hodnoty z tabulky?
Excelový soubor ke stažení:
Pro členy Akademie Excelu máme na webu ještě navazující video, kde si ukážeme třetí způsob, jak podobný příklad vyřešit s pomocí nových funkcí MAP a LAMBDA.
V příkladu máme tabulku se zaměstnanci a ve druhém sloupci máme pro zaměstnance uvedené certifikáty. Zaměstnanci se v tabulce mohou opakovat, jelikož se každý nově získaný certifikát zapíše na nový řádek. Takže pro jednoho zaměstnance existuje i více zápisů, pokud má více certifikátů. Naším úkolem je vedle do tabulky dohledat ke každému zaměstnanci všechny certifikáty, a nejen to, certifikáty je potřeba vypsat za sebou do jedné buňky. A k tomu navíc máme zjistit, kteří zaměstnanci nemají certifikát BOZP, který je pro každého povinný.
První co uděláme je, že změníme zdrojovou tabulku na excelovou tabulku. Klikneme do tabulky a použijeme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a z obyčejného rozsahu dat se stane excelová tabulka. Tabulku rovno pojmenujeme a nazveme ji jako Data a odstraníme toto typické proužkování, takže necháme označenou tabulku a ve stylech tabulky vybereme žádný styl.
Řešení příkladu s verzí Microsoft 2021
Začneme tím, že v souhrnné tabulce vytvoříme jedinečný seznam zaměstnanců z tabulky. K tomu použijeme funkci UNIQUE. Ve funkci UNIQUE označíme sloupec se zaměstnanci a potvrdíme.
K tomuto seznamu zaměstnanců teď musíme dohledat všechny certifikáty. Jelikož ale víme, že žádná vyhledávací funkce nenajde více záznamů, tak místo klasických vyhledávacích funkci použijeme funkci FILTER. Ve funkci FILTER nejprve označíme hodnoty, které chceme filtrovat, což je sloupec s certifikáty. A jako parametr zahrnuje následuje logická podmínka, což je, že se sloupec se zaměstnanci musí rovnat prvnímu zaměstnanci v seznamu. Funkce FILTER vrátí pro prvního zaměstnance všechny certifikáty.
Nicméně my potřebujeme, aby byly všechny certifikáty uvedené za sebou v jedné buňce. K tomu můžeme použít funkci TEXTJOIN. Takže funkci FILTER zabalíme do funkce TEXTJOIN, kde nejprve určíme oddělovač. Jednotlivé certifikáty chceme oddělit čárkou a za čárkou chceme mezeru. Ve druhém parametru můžeme vyplnit pravdu, což znamená, že v případě výskytu prázdných buněk by je funkce TEXTJOIN ignorovala. A text je funkce FILTER. Potvrdím funkci a kombinace funkcí TEXTJOIN a FILTER vrátila seznam všech certifikáty v jedné buňce a certifikáty jsou oddělené čárkou. Tuto funkci teď můžeme stáhnout dolů pro všechny zaměstnance.
Poslední co zbývá vyřešit je, zjistit, zda mají zaměstnanci BOZP školení. K tomu abychom zjistili, zda se školení BOZP vyskytuje mezi certifikáty můžeme použít funkci HLEDAT, anglicky funkci SEARCH. Ve funkci HLEDAT jako co hledáme napíšeme v uvozovkách BOZP. A tento text hledáme v certifikátech. Funkci potvrdíme a pokud funkce HLEDAT slovo najde, tak vrátí číslo. Když funkci stáhneme dolů, tak můžeme ověřit, že na řádcích, kde není tento certifikát se vrací chyba.
Abychom tuto funkci mohli použít v podmínce, tak nejprve funkci HLEDAT zabalíme do funkce JE.ČÍSLO neboli funkce ISNUMBER. Tato funkce vrátí pravdu, pokud je v buňce číslo a nepravdu, pokud je tam cokoliv jiného, včetně chybové hodnoty.
A teď tuto funkci můžeme zabalit do funkce KDYŽ neboli funkce IF, kde stanovíme, co se má vrátit, pokud daný zaměstnanec má certifikát BOZP. Řekněme, že chceme řádky označit ikonami, takže použijeme klávesy Windows a tečku a do buňky vložíme fajfku a křížek. Nesmíme zapomenout na to, že tyto znaky jsou text, takže je musíme zabalit do uvozovek.
Teď máme označené řádky, podle toho, který zaměstnanec má ve výčtu certifikátů BOZP.
Když přidáme nového zaměstnance do tabulky, a řekněme, že tento zaměstnanec bude mít BOZP školení, tak se do souhrnné tabulky přidá jméno zaměstnance, jelikož se funkce UNIQUE odkazuje na sloupec excelové tabulky. Jelikož se ale ve funkci FILTER nemůžeme odkázat v parametru zahrnuje na více buněk, tak se funkce FILTER automaticky neprotáhne. Museli bychom se k tabulce vrátit a funkci protáhnout, stejně jako funkci KDYŽ.
Pokud byste chtěli ošetřit, že se souhrnná tabulka s použitím těchto funkcí automaticky rozšíří, tak byste funkci FILTER zabalili do funkce IFERROR, kde stanovíme, že v případě chyby se vrátí prázdný textový řetězec.
A funkci KDYŽ bychom zabalili do druhé funkce KDYŽ, kde bychom ověřili, že buňka s certifikáty je prázdná, pokud je prázdná, chceme opět prázdný textový řetězec, a pokud prázdná není, tak chceme funkci KDYŽ.
Tyto funkce teď můžeme protáhnout na více řádků a pokud přidáme dalšího nováčka s certifikátem, tak se celá tabulka automaticky rozšíří.
Řešení příkladu s Microsoft 365
Ti z vás, kteří jsou předplatitelé Microsoft 365 a mají přístup k funkci SESKUPITPODLE neboli funkce GROUPBY mohou použít následující postup. Výhodou je, že celá tabulka bude plně dynamická, to znamená, že se bude měnit její velikost v závislosti na zdrojové tabulce.
Začneme s funkcí SESKUPITPODLE. O této funkci máme na Akademii Excelu rovněž celé video, kde funkci vysvětluji do detailu. Odkaz na toto video najdete v popisku tohoto videa. Ve funkci SESKUPITPODLE nejprve označíme pole, které chceme mít na řádcích tabulky. Na řádcích tabulky, chceme mít jedinečný seznam zaměstnanců, takže označíme sloupec zaměstnanci. Nemusíme řešit jedinečný seznam, ten za nás vytvoří sama funkce SESKUPITPODLE.
Druhým parametrem jsou hodnoty. Tedy hodnoty, které chceme mít v poli tabulky. To jsou certifikáty. Následuje funkce. Ve funkci SESKUPITPODLE většinou agregujeme číselné hodnoty, teď ale pracujeme s textem, který navíc nechceme ani sečíst ani spočítat, ale chceme ho zobrazit, musíme tedy vybrat funkci ARRAYTOTEXT.
Potvrdíme funkci a funkce vrátila seskupené certifikáty oddělené středníkem v tabulce. Nicméně zde máme maličkost, kterou musíme dořešit. Funkce zobrazuje i řádek celkem, což v podstatě momentálně zobrazuje všechny certifikáty. Tento řádek nepotřebujeme, takže ho ve funkci SESKUPITPODLE vypneme.
A takto jednoduše z jedné buňky vytvoříte pomocí funkce SESKUPITPODLE tabulku se všemi certifikáty. Tabulka je dynamická, což poznáte podle modrého orámování, takže jakmile přidáte nového zaměstnance do tabulky, tak se celá tabulka automaticky rozšíří.
Ještě ale musíme dořešit to, zda má zaměstnanec certifikát BOZP. V minulém příkladu jsme si ukázali způsob s funkci HLEDAT. Pokud ale máte přístup k novým funkcím REGEX, tak můžete použít funkci REGEXTEST. Napíšeme funkci REGEXTEST, kde označíme text, kde hledáme certifikát a jako vzor napíšeme BOZP v uvozovkách. Funkce REGEXTEST vrátí pravdu, pokud BOZP najde a nepravdu, pokud toto slovo nenajde.
A teď můžete tuto funkci zabalit do funkce KDYŽ a použít stejný postup a ikonami.
Zdánlivě máme hotovo, až na drobný detail. Všimli jste si, kde jsme udělali chybu?
Ve funkci REGEXTEST jsme se odkázali na fixní rozsah ve sloupci certifikáty. Pokud bychom teď přidali nového zaměstnance do tabulky, tak funkce SESKUPITPODLE bude reagovat, ale poslední sloupec s certifikátem nikoliv. Aby byla celá tabulka dynamická, tak musíme tuto část ve funkci REGEXTEST nahradit odkazem na dynamické pole.
Jak se ale odkážeme na sloupec, který vznikl z jedné buňky? Pomocí funkce ZVOLITSLOUPCE neboli funkce CHOOSECOL. Než to ale uděláme, tak bychom to mohli celé vylepšit a zajistit, že celá tabulka vznikne z jedné buňky.
Celé to tedy zabalíme do funkce LET. Ve funkci LET můžeme stanovit různé výpočty jako variabilní vstupy a následně je používat v dalších výpočtech. A abychom nemuseli funkci KDYŽ znovu psát, tak si je nejprve zkopírujeme. Funkci smažeme a vrátíme se do první buňky k funkci.
Před funkci SESKUPITPODLE napíšeme funkci LET a abychom se ve funkci lépe vyznali, tak funkci SESKUPITPODLE posuneme na další řádek pomocí ALT+ENTER. Tomuto výpočtu musíme dát název, můžeme ho pojmenovat jakkoliv, třeba x. Takže pod variabilním výpočtem x e skrývá funkce SESKUPITPODLE, která vytvoří tabulku se zaměstnanci a jejich certifikáty.
Posuneme se na další řádek a tam vložíme zkopírovanou funkci KDYŽ. A zde musíme udělat několik úprav. Nejprve výpočet pojmenujeme a nazveme ho jako y. A teď musíme upravit tuto fixní část ve funkci REGEXTEST. Zde potřebujeme druhý sloupec tabulky, která vznikne pomocí funkce SESKUPITPODLE, takže to nahradíme funkcí ZVOLITSLOUPCE neboli CHOOSECOL, kde tabulka je x, jelikož to je výpočet, pomocí kterého vznikne tabulka a chceme druhý sloupec.
Teď máme tedy definované dva výpočty. Výpočet x vytvoří tabulku a výpočet y k tomu dohledá, zda má zaměstnanec certifikát BOZP.
No a chceme aby výsledkem byla sloučená tabulka z těchto dvou výpočtů. Takže to spojíme funkci SROVNAT.VODOROVNĚ neboli HSTACK.
A teď máte tabulku, která vznikla z jedné funkce LET a bude plně dynamicky reagovat na jakoukoliv změnu ve zdrojové tabulce.
