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 používat jsou dostupné pro uživatele Microsoft 365. V dnešním videu si ukážeme trik na to, jak se odkázat na vybraný sloupec v dynamické oblasti, nebo trik, jak pomocí funkce UNIQUE vytvořit jedinečný seznam z nesousedících sloupců. Celý report bude samozřejmě plně dynamický, to znamená, že pokud do zdrojové tabulky přidáme nová data, tak se všechny vzorce automaticky aktualizují.
Excelový soubor ke stažení:
V příkladu máme zdrojovou tabulku, ve které máme údaje o prodejích jednotlivých produktů za jeden rok. Naším úkolem je vytvořit vedle tabulky souhrnnou tabulku, ve které budou vidět prodeje produktů podle měst. Souhrnná tabulka navíc musí reagovat na dva rozbalovací seznamy, ve kterých si budeme moci určit typ produktu a měsíc, pro který chceme data zobrazit. Během tvorby takové tabulky narazíme na několik chytáků, které budeme muset postupně vyřešit.
Začneme tím, že zdrojovou tabulku změníme na excelovou tabulku. A to proto, že jakékoliv vzorce, které se odkazují na excelovou tabulku se automaticky aktualizují, pokud do ní přidáme nová data. Klikneme do zdrojové tabulky a zmáčkneme klávesovou kombinaci CTRL+T. Potvrdíme, že tabulka má záhlaví a tabulka se změní na excelovou tabulku. Ještě excelovou tabulku pojmenujeme. Klikneme do tabulky a na kartě Návrh tabulky tabulku pojmenujeme jako Zdroj. Pro jednoduchost budeme tvořit přehledové tabulky vedle této zdrojové tabulky, abychom se nemuseli neustále překlikávat z listu na list. V praxi by bylo ale lepší, kdybyste od sebe odlišili zdrojová data a přehledové tabulky a umístili přehledové tabulky na samostatné listy.
Začneme tím, že vytvoříme rozbalovací seznamy. První rozbalovací seznam má dávat na výběr mezi pánskou a dámskou módou. Samozřejmě bychom do rozbalovacího seznamu mohli tyto dvě položky vepsat, jelikož se jedná jen o dvě položky, nicméně nevíme, zda v budoucnosti například nepřibude nová kategorie do tabulky, takže z toho důvodu vytvoříme pomocný seznam pro rozbalovací seznam. Sloupec nadepíšeme jako Seznam typ a použijeme funkci UNIQUE. Ve funkci UNIQUE se odkážeme na sloupec v excelové tabulce. Najedeme myší na záhlaví excelové tabulky na sloupec typ produktu, počkáme až se z kurzoru myši stane černá šipka směřující dolů a potvrdíme výběr. Ukončíme funkci a potvrdíme. Funkce UNIQUE vrátila jedinečný seznam položek ze sloupce. Položky chceme ještě seřadit podle abecedy, takže funkci UNIQUE zabalíme do funkce SORT, kde nemusíme nic jiného vyplňovat, jelikož řadíme pouze jeden sloupec a základní nastavení funkce je seřadit položky vzestupně. Potvrdíme funkci a máme seřazený seznam položek.
Tento seznam se teď stane zdrojem rozbalovacího seznamu. Klikneme do buňky, kde chceme mít seznam a na kartě Data vybereme Ověření dat, seznam a jako zdroj se odkážeme na první buňku pomocného seznamu a abychom zajistili, že v rozbalovacím seznamu budou vždy zahrnuté všechny položky, tak se na zbytek seznamu odkážeme pomocí křížku. Potvrdíme vytvoření rozbalovacího seznamu.
Následuje druhý rozbalovací seznam, který má obsahovat seznam měsíců. Abychom byli schopní takový seznam vytvořit, tak do zdrojové tabulky musíme přidat pomocný sloupec měsíc. V tomto pomocném sloupci vyselektuje název měsíce z datumu. Použijeme funkci HODNOTA.NA.TEXT, anglicky funkce TEXT, ve které označíme první datum v tabulce a jako formát použijeme „mmmm“. Ukončíme funkci a potvrdíme a máme z datumů vyselektované názvy měsíců.
Tento pomocný sloupec teď použijeme jako zdroj pro rozbalovací seznam. Vytvoříme druhý pomocný sloupec, který nazveme jako Seznam měsíce a použijeme opět funkci UNIQUE. Ve funkci UNIQUE se stejným způsobem odkážeme na celý sloupec ve zdrojové tabulce. Potvrdíme funkci UNIQUE a máme jedinečný seznam měsíců. Abychom měli seznam měsíců seřazený nikoliv podle abecedy, ale podle skutečného pořadí měsíců, tak můžeme použít jednoduchý trik se seřazením zdrojové tabulky. Klikneme do záhlaví excelové tabulky so sloupce datum a vybereme seřadit vzestupně a tím se tabulka seřadí, čímž se seřadí i měsíce v seznamu.
Teď z tohoto seznamu měsíců můžeme vytvořit rozbalovací seznam. Klikneme do buňky, karta Data, Ověření dat a seznam. Do odkazu napíšeme rovná se a odkážeme se na první buňku seznamu a opět s křížkem, abychom zajistili, že bude vždy celý seznam zahrnutý do rozbalovacího seznamu. Potvrdíme vytvoření seznamu a zkontrolujeme, že se seznam vložil do buňky.
Teď můžeme přistoupit k tvorbě souhrnné tabulky. Naším úkolem je zobrazit celkem prodané kusy produktů dle barvy, a to na základě výběru v rozbalovacích seznamech. Nejprve se vypořádáme s tím, že musíme vytvořit jedinečný seznam pro produkty dle barvy. Kdyby tyto dva sloupce byly v excelové tabulce vedle sebe, tak je to jednoduché, jelikož bychom ve funkci UNIQUE označili oba sloupce. Ve zdrojové tabulce ale sloupce vedle sebe nemáme a ve funkci UNIQUE nemůžeme označit sloupce, které spolu nesousedí. Navíc má celý jedinečný seznam reagovat na rozbalovací seznamy. Začneme postupně. Nejprve k sobě spojíme produkt a barvu. To uděláme pomocí dynamické funkce ZVOLITSLOUPCE, anglicky funkce CHOOSECOL. Tato funkce nám dovolí z tabulky vybrat pouze zvolené sloupce. Napíšeme funkci ZVOLITSLOUPCE, kde označíme celou zdrojovou tabulku. A teď následují pořadová čísla sloupců, které chceme spojit. Chceme k sobě spojit produkt a barvu, což je sloupec 2 a 5. Funkci ukončíme a potvrdíme a funkce z excelové tabulky doručí pouze tyto dva sloupce.
Vrátíme se k reportu a navážeme tyto dva sloupce na rozbalovací seznamy, což uděláme pomocí funkce FILTER. Funkci ZVOLITSLOUPCE zabalíme do funkce FILTER, kde polem je funkce ZVOLITSLOUPCE, a podle jakých kritérií chceme tyto dva sloupce filtrovat? Máme dvě kritéria, takže každé kritérium budeme muset uvést do vlastních závorek a jelikož mají obě kritéria platit zároveň, tak je mezi sebou budeme muset vynásobit. První podmínka je, že se typ produktu rovná typu vybranému v rozbalovacím seznamu, ukončíme závorku a to vynásobíme druhou podmínkou, že se měsíc v pomocném sloupci rovná měsíci v rozbalovacím seznamu. Ukončíme funkce a potvrdíme. Teď se spojené sloupce produkt a barva vyfiltrovali pro zvolená kritéria.
Poslední co zbývá, je z tohoto seznamu vytvořit jedinečný seznam, takže to celé zabalíme do funkce UNIQUE. A pokud chceme seznam ještě seřadit, tak to zabalíme do funkce SORT.
První část tabulky je hotová.
K těmto produktům teď musíme spočítat celkové množství prodaných produktů. S tím pomůže funkce SUMIFS. Napíšeme funkci SUMIFS, kde nejprve označíme sloupec, který chceme sčítat, což je sloupec s počtem prodaných kusů. Následuje první oblast kritérií, což je typ produktu v excelové tabulce a jako kritérium je typ v rozbalovacím seznamu. Středník a druhá oblast kritérií, což je sloupec s měsíci v tabulce a jako kritérium je vybraný měsíc. A teď musíme označit jako další kritérium produkt a barvu. Takže jako oblast kritérií sloupec s produkty ve zdrojové tabulce a jako kritérium je produkt v souhrnné tabulce. Když se ale na sloupec odkážeme, tak si všimněte, že se označila celá oblast včetně barvy a to proto, že produkt a barva vznikly z jedné funkce. Pokud bychom teď funkci SUMIFS potvrdili, tak se vrátí nuly, jelikož se produkt a barva spojí a takový spojený sloupec ve zdrojové tabulce nemáme. Na označení pouze jednoho sloupce ve společné oblasti existuje trik s funkcí INDEX. Napíšeme funkci INDEX, kde označíme celou oblast produkt a barva, řádek přeskočíme, a jako sloupec vybereme sloupec, který má funkce INDEX vrátit, tedy sloupec produkt, což je první sloupec. Ukončíme funkci. Teď funkce INDEX z dynamické oblasti dvou sloupců doručí do funkce SUMIFS pouze první sloupec. A ještě zbývá poslední kritérium, což je město. Takže označíme sloupec s městem v tabulce a jako kritérium opět funkce INDEX, kde se opět odkážeme na dynamickou oblast, řádek přeskočíme a jako sloupec vybereme dvojku. Ukončíme závorky a funkci potvrdíme. Funkce SUMIFS doručila správné součty prodaných kusů k jedinečnému seznamu produktů a barev.
Souhrnná tabulka je hotová. Pokud do zdrojové tabulky přidáme nová data, tak tím, že pracujeme s excelovou tabulkou a dynamickými funkcemi, tak se nová data automaticky zahrnou do výpočtů.
Pro členy Akademie Excelu máme na webu Akademie Excelu i navazující video, kde si ukážeme, jak z této tabulky vytvořit plně dynamický graf, a to pomocí funkce LET.
Jedna odpověď
😊