V dnešním videu se vrátíme ke dvěma funkcím, které jsme si představili v minulých dvou videích. Jedná se o funkce KONTPODLE a SESKUPITPODLE neboli funkce PIVOTBY a GROUPBY. Tyto funkce jsou nové dynamické funkce, které budou brzy dostupné pro předplatitele Microsoft 365 a jelikož se jedná o funkce, které bez nadsázky změní styl práce v Excelu, tak by bylo fajn, ukázat si s nimi několik pokročilejších triků.
Excelový soubor ke stažení:
A pokud zatím nevíte, jak s těmito funkcemi pracovat, tak se nejprve podívejte na předchozí video o tom, jak pracovat s funkcí KONTPODLE a jak funkci SESKUPITPODLE používat. Triky si budeme ukazovat na funkci SESKUPITPODLE, ale spousta z nich bude platná i pro funkci KONTPODLE.
Pro ukázku triků použijeme zdrojovou excelovou tabulku, kterou máme pojmenovanou jako Zdroj.
Seskupení z nesousedících sloupců
Začneme tím, že si ukážeme, jak můžeme zobrazit souhrn dat pro sloupce, které spolu nesousedí ve zdrojové tabulce. Řekněme, že chceme zobrazit souhrn tržeb pro typ produktu a pobočku. Jedná se o sloupce, které spolu ve zdrojové tabulce nesousedí. Ve funkci SESKUPITPODLE můžeme v základu ale označit pouze sloupce, které spolu sousedí. Můžeme to ale obejít pomocí funkce SROVNAT.VODOROVNĚ neboli funkce HSTACK. Začneme s funkci SESKUPITPODLE, kde máme hned první parametr pole řádků. Právě zde potřebujeme seskupit nesousedící sloupce, takže použijeme funkci SROVNAT.VODOROVNĚ, kde označíme nejprve sloupec typ produktu a pak pobočku. Tato funkce srovná vedle sebe tyto dva sloupce. A pak pokračujeme klasicky. Následují hodnoty, takže sloupec tržeb a tržby chceme sčítat, takže jako funkci vybereme SUMU. Funkci potvrdíme a teď máme souhrnnou tabulku, kde na řádcích máme pole, která spolu ve zdrojové tabulce nesousedí.
Více výpočtů v tabulce
Jako další si ukážeme, jak zobrazit více výpočtů v rámci jedné souhrnné tabulky. Začneme tím, že napíšeme funkci SESKUPITPODLE, kde chceme seskupit hodnoty podle produktů, takže sloupec s produkty. Chceme seskupit tržby, takže v hodnotách označíme sloupec s tržbami. A teď chceme v souhrnné tabulce zobrazit vedle sebe součet tržeb, počet tržeb a procento z celkových tržeb. Toho docílíme opět pomocí funkce SROVNAT.VODOROVNĚ neboli funkce HSTACK. Tento postup není úplně intuitivní, jelikož, když do parametru funkce napíšeme funkci SROVNAT.VODOROVNĚ, tak se nám nenabídne nápověda k funkci, ale trik bude fungovat. A do této funkce postupně napíšeme názvy funkcí, které chceme vedle sebe, takže SUMA, POČET a PROCENTO. Ukončíme funkce a potvrdíme a teď máme v souhrnné tabulce všechny tři výpočty vedle sebe. Jediné, co musíme udělat, je hodnoty správně naformátovat.
Řazení podle více sloupců
V dalším příkladu máme vytvořenou souhrnnou tabulku podle druhu produktu a pobočky. Řekněme, že chceme tabulku nejprve seřadit podle prvního sloupce druh produktu sestupně a pak v rámci druhu produktu chceme ještě pobočky naopak seřadit vzestupně. Jak to uděláme? Využijeme maticového zápisu. Klikneme do funkce SESKUPITPODLE a přepneme se do nepovinného parametru řazení. Zde máme v základu na výběr vybrat si typ řazení podle znaménka a pořadové číslo sloupce, podle kterého chceme tabulku řadit. Když ale chceme řadit tabulku podle více sloupců, tak použijeme maticový zápis, takže otevřeme složenou závorku a nejprve chceme tabulku seřadit podle prvního sloupce sestupně, takže -1 a následně chceme řadit podle druhého sloupce vzestupně, takže dvojka. Ukončíme složenou závorku a funkci potvrdíme a teď máme seřazenou souhrnnou tabulku podle dvou pravidel.
Seskupení více číselných sloupců
Stejně jako můžeme vytvořit souhrnnou tabulku podle více polí řádků, můžeme vedle sebe seskupit i více číselných hodnot. V další tabulce chceme vytvořit souhrn tržeb, marže i nákladů pro produkty. Takže funkce SESKUPITPODLE, kde na řádcích chceme seskupení podle produktů, a teď v hodnotách označíme číselná pole, která chceme sčítat, takže sloupec tržba, sloupec marže a sloupec náklad. A ve funkci vybereme SUMA. Funkci ukončíme a potvrdíme a teď máme souhrnnou tabulku, kde máme sečtené tržby, náklady a marže pro jednotlivé produkty.
Seskupení více nesousedících číselných sloupců
Stejně jako můžeme seskupit hodnoty v tabulce podle nesousedících polí, tak můžeme seskupit hodnoty v tabulce podle nesousedících číselných polí. Použijeme stejný trik s funkcí SROVNAT.VODOROVNĚ. Takže funkce SESKUPITPODLE, kde chceme seskupit hodnoty podle produktů a teď chceme vedle sebe seskupit dvě číselná pole, která spolu ve zdrojové tabulce nesousedí, třeba sloupec tržba a náklad. Takže funkce SROVNAT.VODOROVNĚ, kde označíme tyto dva sloupce. Ukončíme funkci a ještě vybereme výpočet SUMA. Potvrdíme funkci a máme souhrnnou tabulku vytvořenou.
Vícenásobné filtrační kritérium
V nepovinném parametru filtr můžeme samozřejmě použít i vícenásobné kritérium A i NEBO. Ukážeme si to třeba s logickou podmínkou A. Chceme vytvořit souhrnnou tabulku s produkty a tržbami, ale jen pro pánské produkty, které se prodaly mezi červencem a prosincem. Máme zde tedy dvě podmínky, které mají platit zároveň. Takže funkce SESKUPITPODLE, kde nejprve v parametru pole řádku označíme produkty, následuje sloupec tržby a jako funkce SUMA. Následně se přepneme až do parametru filtr, kde stanovíme dvě logické podmínky a jelikož mají být ve vyjádření A, tak je budeme mezi sebou násobit. Tak jako ve funkci FILTER, každá podmínka musí být v samostatných závorkách. Takže v první závorce ověříme podmínku, že se typ produktu rovná pánské módě a to vynásobíme druhou podmínkou, že měsíc je vyšší nebo rovno 7, tím zajistíme, že se započítají pouze tržby mezi červencem a prosincem. Ukončíme funkce a potvrdíme a teď máme souhrnnou tabulku na základě vícenásobného filtru. Samozřejmě bychom tato filtrační kritéria mohli navázat třeba na rozbalovací seznamy a filtrovat pak pomocí tohoto seznamu.
Seskupení datumů
Velmi často v kontingenční tabulce seskupujeme data podle let a měsíců. To samé můžeme udělat i s pomocí funkce SESKUPITPODLE. V tabulce máme údaje jen za jeden rok, takže řekněme, že chceme vidět seskupení tržeb po měsících. Napíšeme funkci SESKUPITPODLE, kde nejprve použijeme funkci MĚSÍC, neboli funkci MONTH. V této funkci označíme datum a pokračujeme tím, že označíme tržby a chceme součet. Potvrdíme funkci a funkce vrátí seskupené tržby podle měsíců. Funkce MĚSÍC vrátila pořadová čísla měsíců.
Kdybychom chtěli názvy měsíců, tak to uděláme tak, že místo funkce MĚSÍC použijeme funkci HODNOTA.NA.TEXT neboli funkci TEXT, kde použijeme formát „mmmm“. Nicméně po potvrzení se vrátí měsíce seřazené podle abecedy.
Pokud chceme klasické řazení od ledna do prosince, tak můžeme využít funkci MĚSÍC. V takovém případě bychom v parametru pole řádky napsali funkci SROVNAT.VODOROVNĚ, kde bychom použili funkci MĚSÍC a pak jako druhé pole funkci HODNOTA.NA.TEXT. Potvrdíme a tím se přidá sloupec s pořadovým číslem měsíců a díky tomu se měsíce seřadí správně.
Stejně tak můžeme chtít použít například označení rok a měsíc, v takovém případě necháme pouze funkci HODNOTA.NA.TEXT a použijeme formát „rrrr-mm“. A takto jednoduše můžeme různě seskupovat hodnoty podle datumových polí.
