V dnešním videu si ukážeme, jak vytvořit dva na sobě závislé rozbalovací seznamy s položkami Vše. Jedná se o jedno z nejžádanějších videí, o které si píšete. Kromě toho, že v rozbalovacích seznamech máme položky, tak zde máme i možnost Vše, která pokud ji vybereme, ignoruje filtr na souhrnné tabulce. A pokud vybereme v obou seznamech Vše, tak se odebere filtr kompletně. Jak tyto závislé rozbalovací seznamy a souhrnnou tabulku vytvořit si ukážeme v tomto videu.
Excelový soubor ke stažení:
Ve zdrojové tabulce máme HR data, společnosti, oddělení a zaměstnance včetně mezd. V rozbalovacích seznamech si chceme vybrat společnost a oddělení a podle toho vyfiltrovat data do souhrnné tabulky. Pokud ale vybereme v jednom nebo v obou seznamech Vše, tak se odstraní uplatněné filtry, což je vlastnost, kterou klasické rozbalovací seznamy nenabízejí.
Malé upozornění, pokud chcete následovat instrukce v tomto videu, musíte mít alespoň Office 2021 a nebo být předplatitelem služby Microsoft 365. Pokud by vás zajímalo, tak stejného výsledku dosáhnout i ve starších verzích Excelu, tak mi dejte vědět v komentáři pod videem.
Abychom rovněž zajistili, že tabulka bude reagovat na nově přidaná data do tabulky, tak zdrojovou tabulku převedeme na excelovou tabulku. Klikneme do tabulky a použijeme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a tabulku pojmenujeme jako Data. A rovnou odstraníme toto typické proužkování, a to tak, že ve stylech tabulky vybereme Žádný styl.
Rozbalovací seznamy
Nejprve musíme vytvořit dva rozbalovací seznamy. A v těchto rozbalovacích seznamech se má zobrazovat jedinečný seznam oddělení a společností včetně položky Vše. Tyto seznamy můžete vytvořit několika způsoby. Ideálním řešením je použít funkci UNIQUE, která vrátí jedinečný seznam hodnot ze seznamu s duplicitami. V pomocném sloupci napíšeme funkci UNIQUE, ve které označíme celý sloupec s oddělením. A funkce doručí jedinečný seznam oddělení.
Do tohoto seznamu, ideálně navrch potřebujeme ale dostat i slovo Vše. Pokud máte přístup k funkci SROVNAT.SVISLE neboli funkci VSTACK, tak můžete funkci UNIQUE zabalit do funkce SROVNAT.SVISLE a jako první pole napsat slovo Vše a jako druhé pole bude funkce UNIQUE. To po potvrzení vytvoří seznam včetně slova Vše.
Pokud tuto funkci nemáte, tak slovo Vše můžete napsat nad seznam a pod ním použít funkci UNIQUE. Slovo Vše napište nad funkci UNIQUE, protože kdybyste slovo napsali pod funkci a následně se vám rozšířil seznam s produkty, tak funkce UNIQUE vrátí chybu Přesah.
Stejným způsobem vytvoříme i druhý rozbalovací seznam se společnostmi. Takže funkce SROVNAT.SVISLE neboli VSTACK, jako první pole napíšeme slovo Vše a jako druhé pole bude funkce UNIQUE, ve které označíme sloupec se společnostmi.
Tyto pomocné seznamy použijeme v rozbalovacích seznamech. Klikneme do buňky Oddělení a na kartě Data vybereme Ověření dat a v nabídce vybereme Seznam. Ve zdroji se odkážeme na první buňku dynamického seznamu a abychom zajistili, že se odkážeme na celou dynamickou oblast, tak buňku napíšeme křížek.
Takto vytvoříme oba seznamy.
Vybereme v každém seznamu jedno oddělení a jednu společnost.
Souhrnná tabulka
Když máme rozbalovací seznamy hotové, tak začneme tvořit funkci FILTER, pomocí které vyfiltrujeme zdrojovou tabulku podle kritérií v rozbalovacích seznamech. Začneme tím, že máme v každém seznamu vybranou položku, což znamená, že obě podmínky musí platit zároveň. Takže ve funkci FILTER bude v parametru zahrnuje logická podmínka A. Takže ve funkci FILTER v prvním parametru pole označíme část zdrojové tabulky, kterou chceme filtrovat, což je sloupec zaměstnanec a mzda. A v poli zahrnuje musíme uplatnit dvě podmínky v logickém vyjádření A. Každá podmínka musí být v samostatných závorkách a mezi sebou je vynásobíme.
Po potvrzení funkce FILTER funguje a vrací správně vyfiltrovaná data pro kombinaci oddělení a společnosti.
Nicméně, pokud v jednom z rozbalovacích seznamů vybereme Vše, tak funkce FILTER vrátí chybu.
Aby funkce FILTER správně reagovali, tak musíme zabalit funkce do podmínek KDYŽ. Nicméně existují celkem 4 scénáře. První je, že bude vybrané oddělení a v rozbalovacím seznamu společnost bude vybráno Vše. Nebo to bude obráceně, bude vybráno Vše v rozbalovacím seznamu oddělení a pak bude vybraná společnost. Nebo bude vybrané oddělení i společnost a nebo bude v obou seznamech vybráno Vše.
Začneme postupně. První podmínka bude, pokud vybereme Vše v seznamu oddělení. Takže funkce KDYŽ, kde ověříme podmínku, že v seznamu oddělení je vybráno Vše. Pokud ano, tak chceme vrátit funkci FILTER, kde se vyfiltrují sloupce tabulky pro vybranou společnost.
Následuje druhá možnost, a to, že v seznamu společnost bude vybráno Vše. Takže další funkce KDYŽ, kde ověříme podmínku, že se seznam se společností rovná Vše, a pokud ano, tak chceme vrátit funkci FILTER, kde vyfiltrujeme data pouze pro vybrané oddělení.
Třetí možnost je, že bude v obou seznamech vybráno Vše. V takovém případě chceme vrátit všechny řádky ze zdrojové tabulky, takže pouze označíme sloupce zaměstnanec až mzda.
A poslední možnost je, že v každém seznamu bude vybrána položka, tedy oddělení i společnost, a pak chceme vrátit funkci FILTER, kterou již máme vytvořenou.
Potvrdíme funkci a zkusíme zda funguje.
Vše funguje, dokud nevybereme Vše v obou seznamech. V takovém případě se vrátí chyba. Víte proč?
Protože podmínka, ve které ověřujeme, že se oba seznamy rovnají Vše musí ve skutečnosti být uvedená ve vícenásobných podmínkách KDYŽ jako první.
Takže tuto funkci vyjmeme a vložíme ji na začátek.
Poslední, co zbývá ověřit je, že existují všechny kombinace mezi rozbalovacími seznamy. Pokud totiž vybereme neexitující kombinaci, tak funkce vrátí chybu.
To lze lehce ošetřit v poslední funkci FILTER, kde využijeme nepovinného parametru, ve kterém stanovíme, co chceme vrátit, pokud zadaná kombinace neexistuje. Třeba slovo Neexistuje.
Jelikož jsme zdrojovou tabulku změnili na začátku na excelovou tabulku, tak pokud do zdrojové tabulky přidáme nová data, včetně nového oddělení, tak bude celá souhrnná tabulka včetně rozbalovacích seznamů reagovat a nové položky se automaticky zahrnou.
2 komentáře
Dobrý den, šlo by to i ve starších verzí excelu? Naše firma používá verzi 2019. Děkuji
Moc pěkné.
Šel by nějak upravit FILTER, aby pro hodnoty Vše * Vše vypsal celou tabulku?
Pro ostatní případy se mi líbí
=FILTER(Data[[Zaměstnanec]:[Mzda]];
KDYŽ(G2=”Vše”;1;(Data[Oddělení]=G2))*KDYŽ(G3=”Vše”;1;(Data[Společnost]=G3));
“neexistuje”)
Děkuji