V dnešním videu navážeme na předchozí video, kde jsme si ukázali, jak do rozbalovacího seznamu dostat položku vybrat vše. Video ukazovalo, jak to udělat s pomocí nových funkcí jako FILTER nebo UNIQUE. Jelikož se ale spoust z vás ptala na to, jak to udělat i ve starších verzích Excelu, tak je to přesně to, co si dnes ukážeme. A sami můžete na konci posoudit, která verze je snazší. Řešení v tomto videu jsou vhodná pro všechny verze Excelů, tedy i pro ty, co nemají přístup k dynamickým polím a funkcím v rámci nových Excelů nebo předplatného Microsoft 365.
Excelový soubor ke stažení:
Nejprve si krátce připomeneme, co se vlastně snažíme vytvořit. Ze zdrojové tabulky chceme filtrovat data do souhrnné tabulky pomocí rozbalovacích seznamů. V prvním rozbalovacím seznamu si budeme moci vybrat oddělení a ve druhém společnost. Vše chceme pokud možno dynamické, to znamená, že když do zdrojové tabulky přidáme nová data, tak se tato data projeví jak v rozbalovacích seznamech, tak v souhrnné tabulce.
Tak jdeme na to.
Nejprve převedeme zdrojovou tabulku na excelovou tabulku, a to proto abychom zajistili, že se nově přidaná data zahrnou do všech souvisejících výpočtů. Tabulku pojmenujeme jako Data.
Rozbalovací seznamy
Začneme tím, že vytvoříme zdrojové seznamy pro rozbalovací seznamy. Jako první napíšeme možnost Vše, tato možnost se nebude měnit a není ani závislá na zdrojové tabulce, takže ji sem můžeme jednoduše napsat.
Samozřejmě, že ti z vás, kteří nepotřebují zcela dynamické řešení seznamu by mohli jen zkopírovat seznam oddělení a pomocí Odstranění duplicit odstranit duplicity. Tím by zůstal jedinečný seznam hodnot. Je to jednoduché a rychlé řešení, ale nevýhodou je, že pokud přidáme nové položky do zdrojové tabulky, tak by se nám tyto položky neobjevily v seznamu.
Naopak pokud chcete dynamické řešení, které bude reagovat na nové položky, tak použijete následující postup.
Pro vytvoření jedinečného seznamu pomocí starších funkcí existuje následující kombinace funkcí INDEX a POZVYHLEDAT neboli INDEX a MATCH. Začneme funkcí INDEX, kde v parametru pole označujeme, jaké položky chceme vrátit. Tvoříme seznam oddělení, takže označíme sloupec s oddělením. V parametru řádky napíšeme funkci POZVYHLEDAT neboli funkci MATCH, ve které hledáme nulu, a tuto nulu hledáme v poli, které vytvoříme pomocí funkce COUNTIF, ve které bude dynamické rozpětí. Dynamické rozpětí bude od první buňky v seznamu, tedy od položky Vše, která bude neměnná. A ve druhém parametru funkce COUNTIF bude sloupec s oddělením. Ukončíme funkci COUNTIF a nezapomeneme do parametru shoda ve funkci POZVYHLEDAT vybrat nulu, tedy přesnou shodu. Starší verze Excelů musí tuto funkci potvrdit kombinací kláves CTRL+SHIFT a ENTER.
Funkci stáhneme dolů pro více řádků a vrátí se jedinečný seznam oddělení. Proč tato kombinace funguje? Rozebereme si funkci zevnitř. Část funkce, na kterou asi nejste zvyklí je funkce COUNTIF ve funkci POZVYHLEDAT. Když si ji označíme na prvním řádku, tak tato funkce vrátí sérii nul. A my pomocí funkce POZVYHLEDAT hledáme nulu a víme, že vyhledávací funkce vždy vrátí pouze první výskyt, takže na prvním řádku funkce INDEX vrátí první nulu, takže oddělení HR.
Když si tuto část funkce označíme na druhém řádku, tak se na řádcích HR vrátí jedničky a u ostatních oddělení nuly. A opět kombinace vyhledávacích funkcí vrátí první výskyt, hledáme stále nulu, takže teď vrátí Logistiku. A takto to pokračuje.
Na třetím řádku jsou jedničky u HR a Logistiky a nula začíná u práva, takže se vrátí oddělení Práva.
A jelikož chceme, aby tato funkce reagovala na nově přidaná oddělení do zdrojové tabulky, tak funkci protáhneme o několik řádků níže a abychom se zbavili těchto chybových hlášek, tak funkci zabalíme do IFERROR, kde stanovíme, že v případě chyby se má vrátit prázdný textový řetězec.
Ten samý postup použijeme i pro druhý seznam společností. Takže funkce INDEX, kde označíme sloupec Společností, funkce POZVYHLEDAT, kde hledáme nulu, ve druhém parametru bude funkce COUNTIF, kde opět vytvoříme dynamické rozpětí a jako druhý parametr označíme sloupec se společnostmi. Ukončíme funkci COUNTIF a nezapomeneme na přesnou shodu ve funkci POZVYHLEDAT. Ještě než funkci pošleme dolů, tak ji zabalíme do funkce IFERROR, kde v případě chyby chceme vrátit prázdný textový řetězec.
Seznamy jsou hotové. Když ale klikneme do buňky, kde chceme mít první rozbalovací seznam a ve zdroji označíme celé rozpětí buněk, včetně buněk navíc, tak se po potvrzení v rozbalovacím seznamu vrátí prázdná místa.
Tato prázdná místa reprezentují prázdné textové řetězce, které zajišťují, že se seznam bude rozšiřovat. Toto prázdné místo v seznamu nechceme, jelikož by hrozilo, že ho někdo omylem vybere a pak se v tabulce nic nevrátí.
Abychom se tohoto prázdného místa zbavili, tak vytvoříme druhý seznam, který vytvoříme pomocí funkce POSUN neboli funkce OFFSET. Takže funkce POSUN, kde nejprve označujeme styčný bod, odkud se funkce začne posouvat, což je buňka záhlaví nad seznamem Oddělení. Tuto buňku plně zafixujeme. Následně se má funkce z tohoto styčného bodu posunout o jeden řádek níže, kde začíná seznam. V rámci sloupců se funkce posouvat nemá, takže nula a v parametru výška musíme funkci POSUN říct, že chceme zahrnout pouze text, bez prázdných textových řetězců. Takže funkce COUNTIF, kde označíme celý seznam včetně prázdných buněk, plně zafixovaný a jako parametr použijeme vyjádření „?*“. Starší verze Excelu musí tuto funkci potvrdit pomocí kláves CTRL+SHIFT a ENTER. Vrátí se název prvního oddělení.
Abychom tuto funkci mohli použít v rozbalovacím seznamu a vrátil se vždy celý seznam, tak ji musíme pojmenovat ve správci názvů. Takže tuto funkci zkopírujeme a otevřeme správce názvů a vložíme ji do názvu a oblast pojmenujeme jako oddělení.
Potvrdíme a teď klikneme do buňky, kde chceme mít rozbalovací seznam a do odkazu napíšeme tento název oddělení. Zkusíme seznam a funkce POSUN doručí všechna oddělení bez prázdných buněk. A pokud přidáme nové oddělení, tak se toto oddělení zahrne do seznamu.
To samé zopakujeme i pro seznam společností. Funkce POSUN, styčným bodem je buňka záhlaví, chceme se posunout o jeden řádek níže, v rámci sloupce se posouvat nechceme a v parametru výška je funkce COUNTIF, kde v seznamu počítáme výskyt textu. A funkci potvrdíme klávesami CTRL+SHIFT a ENTER. Funkci zkopírujeme a vložíme ji do správce názvů, pojmenujeme ji jako společnosti.
Teď můžeme vytvořit druhý rozbalovací seznam.
Souhrnná tabulka
Teď musíme vytvořit tabulku, která bude reagovat na dva filtry v podobě rozbalovacích seznamů. A jelikož nemáme přístup k funkci FILTER, tak to budeme muset obejít trikem s funkcí AGGREGATE. Nejprve vytvoříme funkci, která bude reagovat na to, když vybereme oddělení a společnost v rozbalovacích seznamech a následně vyřešíme kombinace s Vybrat vše. Takže v obou rozbalovacích seznamech vybereme položky HR a Microsoft.
A opět, nejprve začneme funkci stavět zevnitř. Nejprve musíme ověřit, na kterých řádcích zdrojové tabulky je kombinace položek z rozbalovacích seznamů.
Nejprve ověříme, na kterých řádcích je oddělení z rozbalovacího seznamu. Takže označíme sloupec zdrojové tabulky a to se má rovnat oddělení z rozbalovacího seznamu. Tuto buňku zafixujeme plně a potvrdíme. Mě se celá tato podmínka rozlije na všechny řádky, jelikož používám Microsoft 365, nicméně vy se staršími verzemi Excelu tuto funkci stáhněte pro všechny řádky dolů. Na řádcích, kde je oddělení se vrátí pravdy a na ostatních řádcích nepravdy.
Máme ale i druhou podmínku se společností. A jelikož obě podmínky musí platit zároveň, tak je mezi sebou vynásobíme a každá podmínka musí být v samostatných závorkách. A podmínka je, že se sloupec se Společností musí rovnat společnosti, která je v rozbalovacím seznamu. Tato buňka opět plně zafixovaná. Když to potvrdíme a vy to stáhnete dolů, tak se vrátí jedničky na řádcích, kde je zadaná kombinace rozbalovacích seznamů, na ostatních řádcích se vrátí nuly.
Teď tedy víme, na kterých řádcích máme zadanou kombinaci oddělení a společnosti a to jsou řádky, které chceme filtrovat. Pro funkci AGGREGATE potřebujeme ale přeměnit tyto jedničky na pořadová čísla řádků. To uděláme pomocí kombinací funkcí ŘÁDEK neboli funkce ROW. Tyto dvě podmínky zabalíme do samostatných závorek a vynásobíme to závorkou, kde bude funkce ŘÁDEK, ve které označíme jakýkoliv sloupec excelové tabulky a od toho odečteme druhou funkci ŘÁDEK, kde označíme záhlaví stejného sloupce. Ukončíme funkce a když to potvrdíme, tak se na řádcích vrátí pořadová čísla řádků, na ostatních řádcích jsou nuly. Mě se funkce opět rozlila do všech řádků, vy ji musíte stáhnout dolů.
A jelikož funkce AGGREGATE umí odfiltrovat chybové hodnoty, tak potřebujeme místo těchto nul chybové hlášky. A jak je vyrobíme? Jedničku vydělíme celou touto funkcí. Jelikož jednička vydělená nulou vrátí chybu.
Teď to konečně můžeme zabalit do funkce AGGREGATE. Takže funkce AGGREGATE, kde potřebujeme funkci SMALL, takže číslo 15. Funkce SMALL vrátí všechna čísla od nejmenšího, což jsou tato čísla a ve druhém parametru vybereme trojku nebo šestku, tedy že chceme ignorovat chybové hodnoty. V parametru matice je celá tato naše funkce a ještě musíme vyplnit parametr k. Tedy kolikátou hodnotu pro funkci SMALL chceme vrátit. Nejprve chceme první hodnotu, pak druhou, pak třetí atd. Takže v parametru k vytvoříme dynamické rozpětí pomocí funkce ŘÁDKY. Toto je celá funkce AGGREGATE.
Když tuto funkci stáhnete dolů pro všechny řádky, tak se vám nahoře vrátí pořadová čísla řádků, na kterých je zadaná kombinace rozbalovacích seznamů. A na ostatních řádcích se vrátí chybového hodnoty. A jak dostaneme k těmto pořadovým číslům jména lidí? Zabalíme to do funkce INDEX. Takže funkce INDEX, kde označíme sloupec Oddělení a v parametru řádky bude tato funkce AGGREGATE. Když to potvrdíte a stáhnete funkci dolů, tak se vám vrátí jména lidí, kteří splňují uvedenou kombinaci.
Poslední co zbývá vyřešit jsou tyto chybové hlášky. Takže funkce IFERROR, kde stanovíme, že v případě chyby se má vrátit prázdný textový řetězec.
Tato roztomilá funkce je hotová. Funkce můžete teď stáhnout minimálně na délku celé zdrojové tabulky, ideálně na více řádků, aby se nové údaje zahrnuly do tabulky.
Pro sloupec Mzda použijeme ten samý postup, můžeme si ušetřit práci a tuto funkci zkopírovat a vložit ji vedle. A jen změnit ve funkci INDEX sloupec ze zaměstnance na mzdu.
Změníme výběr v rozbalovacích seznamech a funkce fungují, až dokud nevybereme možnost Vše.
Abychom ošetřili výběr Vše, tak můžeme použít vícenásobné podmínky KDYŽ nebo funkci IFS, to je na vás. Aby bylo řešení opravdu pro všechny, tak použijeme více násobné podmínky.
Vícenásobná podmínka
Před tuto funkci napíšeme funkci KDYŽ a začneme tvořit vícenásobnou podmínku. Začneme podmínkou, kdy bude v obou seznamech vybrána varianta Vše. A jelikož musí obě podmínky platit zároveň, tak ve funkci KDYŽ musíme použít ještě funkci A. Ve funkci A ošetříme, že v obou rozbalovacích seznamech bude vybrána položka Vše.
V takovém případě chceme vrátit v podstatě celou tabulku, takže funkce INDEX, kde označíme sloupec Zaměstnanec a v parametru řádky napíšeme funkci ŘÁDKY, kde vytvoříme dynamické rozpětí z této aktivní buňky.
Následovat bude druhá funkce KDYŽ, kde ošetříme situaci, kde bude Vše vybráno v seznamu Oddělení. A jako funkci použijeme funkci AGGREGATE, kterou jsme vytvořili a jen z ní smažeme jednu podmínku.
Třetí podmínka bude funkce KDYŽ, kde ošetříme situaci, kde bude Vše vybráno v seznamu Společnost.
A jako poslední zbývá situace, kdy bude v obou seznamech vybrána položka, což je první funkce, kterou jsm vytvořili.
A nakonec funkci zabalíme do funkce IFERROR, kde ošetříme, že v případě chyby se vrátí prázdný textový řetězec.
Tak to je celé. Stejný postup použijeme i ve sloupci Mzda.
Toto řešení bude fungovat i v případě, že do zdrojové tabulky přidáte nová data včetně nového oddělení nebo společnosti.
Hotovo.
