V dnešním videu navážeme na video z minulého týdne, ve kterém jsme si představili novou funkci, která míří do Excelu, funkci KONTPODLE neboli funkci PIVOTBY. Dnes si představíme druhou funkci, která rovněž brzy bude dostupná předplatitelům Microsoft 365, a to funkci SESKUPITPODLE neboli funkci GROUPBY. Tato funkce naprosto změní styl, jakým v Excelu pracujete. Tato funkce totiž sumarizuje data ze zdrojové tabulky, tak jak to dělá kontingenční tabulka. Na rozdíl od kontingenční tabulky ale nepotřebujete obnovit spojení proto, aby se změny ve zdroji projevili v tabulce. Pojďme si tuto skvělou funkci představit.
Excelový soubor ke stažení:
Funkce SESKUPITPODLE má tři povinné parametry. Použití těchto parametrů si ukážeme na jednoduchém příkladu.
=SESKUPITPODLE (row_fields; values; function; [field_headers]; [total_depth]; [sort_order]; [filter_array]; [field_relationship])
V Excelu máme zdrojovou excelovou tabulku nazvanou jako Zdroj. Z této tabulky potřebujeme sečíst tržby pro produkty. Klikneme do buňky a napíšeme funkci SESKUPITPODLE neboli funkci GROUPBY. Prvním povinným parametrem je pole řádků, tedy podle jakého pole chceme na řádcích seskupit hodnoty. Tvoříme tabulku, kde budou zobrazené součty tržeb pro produkty, takže jako pole řádků označíme sloupec s produkty ve zdrojové tabulce.
Následuje druhý povinný parametr, což jsou hodnoty, tedy hodnoty, které chceme sumarizovat. V našem případě to jsou tržby, takže označíme ve zdrojové tabulce sloupec tržeb.
Posledním povinným parametrem je funkce. Zde si můžeme vybrat, jaký výpočet chceme s hodnotami provést. Na výběr máme z několika různých výpočtů. Od součtu pomocí funkce SUMA, máme zde funkce jako POČET a POČET2 nebo medián a procento. Chceme součet tržeb pro produkty, takže vybereme funkci SUMA.
Ukončíme funkci a potvrdíme. Funkce SESKUPITPODLE vrátila souhrnnou tabulku, kde máme součet tržeb podle produktů. Stejně jako funkce KONTPODLE vrátí i funkce SESKUPITPODLE automaticky řádek celkového součtu.
Funkce je samozřejmě dynamická, takže pokud cokoliv změníme ve zdrojové tabulce, tak se tato změna okamžitě promítne do tabulky vytvoření pomocí funkce SESKUPITPODLE. Pokud přidáme nový produkt, tak se tabulka automaticky roztáhne, řádek celkového součtu se posune a nový produkt se zahrne do tabulky.
Ve funkci SESKUPITPODLE máme i funkci PROCENTO. Vrátíme se k funkci a jelikož chceme funkci upravit, tak musíme kliknout do prví buňky, jelikož každá dynamická funkce se upravuje z první buňky a rozlévá se do ostatních buněk. Klikneme do funkce a měníme funkci ze SUMA na PROCENTO. Po potvrzení se výpočet změní na vyjádření tržeb pro produkty na procento z celkového součtu. Akorát musíme změnit formát čísla na procenta.
Nebo můžeme změnit funkci na POČET a tím pádem se změní souhrnná tabulka na součet celkem prodaných produktů. Akorát opět musíme změnit formát na obecné číslo.
Funkci SESKUPITPODLE můžeme použít i pro seskupení více polí. Řekněme, že chceme seskupit tržby podle typu a druhu produktu. Klikneme do buňky, najdeme funkci SESKUPITPODLE, jako pole řádků označíme pole, podle kterých chceme seskupovat. V našem případě podle typu a druhu produktu, takže označíme tyto dva sloupce a tentokrát je označíme i se záhlavím.
Následují hodnoty, což jsou tržby, takže označíme sloupec s tržbami, opět včetně záhlaví a jako funkci vybereme funkci SUMA, jelikož chceme součet tržeb. A přepneme se do prvního nepovinného parametru, což je záhlaví sloupců. Zde si můžeme vybrat, zda chceme zobrazit v tabulce záhlaví. Pokud záhlaví chceme zobrazit, tak vybereme možnost 3.
Pokud naopak záhlaví v tabulce nechceme, tak tento parametr nevyplňujeme, jelikož to je základní nastavení funkce, nebo vyplníme nulu. Necháme v parametru trojku, tedy, že chceme zobrazit záhlaví, funkci ukončíme a potvrdíme a funkce vrátí souhrnnou tabulku, kde máme součet tržeb podle dvou polí. Vidíme zde součet nejprve podle typu produktu a následně podle druhu produktu. Stejně tak v tabulce vidíme záhlaví a řádek celkového součtu.
Podíváme se na další nepovinné parametry, které jsou shodné s funkcí KONTPODLE. Dalším nepovinným parametrem je parametr, který rozhoduje o zobrazení součtů. Můžeme si zde vybrat zda chceme řádek celkového součtu zobrazit nebo nezobrazovat. Co víc, můžeme si vybrat, že chceme zobrazit řádek mezisoučtu, v takovém případě vybereme dvojku. Do tabulky se tím vloží řádky mezisoučtů a řádek celkového součtu zůstane.
Pokud bychom chtěli řádek celkového součtu odstranit, vybereme možnost
nulu.
Pokud chceme součty zobrazit v horní části tabulky místo dole, tak
vybereme možnost -2. Tím se řádek celkového součtu přesune nahoru a rovněž se
řádky mezisoučtů zobrazí nad kategorií, místo pod ní.
Necháme v parametru dvojku, tak aby se součty a mezisoučty zobrazily dole v tabulce a přepneme se do parametru seřazení. Zde si můžeme vybrat, podle jakého sloupce se souhrnná tabulka seřadí. Máme na výběr vzestupné nebo sestupné řazení. To, podle jakého sloupce se tabulka seřadí, se určuje pořadovým číslem sloupce. Kladná hodnota znamená vzestupné řazení a záporná hodnota sestupné řazení.
Řekněme, že chceme tuto souhrnnou tabulku seřadit sestupně podle tržeb. Takže do tohoto parametru napíšeme mínus tři, jelikož tržby jsou třetím sloupcem tabulky. Po potvrzení se každá kategorie seřadí sestupně podle tržeb.
Kdybychom naopak chtěli seřadit tabulku vzestupně podle druhu produktu, tak v tomto parametru vyplníme plus dva. Tím se tabulka seřadí podle abecedy, takže máme nejprve doplňky, pak oblečení a jako poslední obuv.
Dalším nepovinným parametrem je filtr. Souhrnnou tabulku totiž můžeme filtrovat. Řekněme, že chceme v tabulce jen tržby za pobočku Praha. Využijeme tedy filtru a v tomto parametru stanovíme logické pravidlo. A logické pravidlo je, že se sloupec s pobočkami bude rovnat pobočce Praha. Ukončíme funkci a potvrdíme a tabulka se vyfiltruje pouze souhrnné pro vybranou pobočku.
Poslední nepovinný parametr dovoluje určit řazení polí v souhrnné tabulce, pokud sumarizujeme data pode více polí. Takový příklad máme i v naší tabulce. Data máme agregovaná podle typu a druhu produktu. Základním nastavením tohoto parametru je nula, tedy hierarchie. To znamená, že se nejprve seřadí první sloupec. Máme u sebe dámskou módu a pak následuje pánská móda. Druhý sloupec se seřadil v závislosti na prvním sloupci. Takže zde máme seřazené všechny druhy produktu v rámci dámské módy a následně všechny druhy produktu v rámci pánské módy.
Kdybychom v tomto parametru ale vybrali jedničku, tak se vrátí chyba. A je to proto, protože máme v tabulce zapnuté mezisoučty. V případě, že v posledním parametru vybereme jedničku, tak nelze zobrazit mezisoučty, takže se musíme k funkci vrátit a smazat nepovinný parametr součtů.
Po potvrzení se zobrazí souhrnná tabulka, kde je řazení jiné než v předchozím případě. Teď máme u sebe doplňky jak z dámské, tak pánské módy, následuje oblečení, opět jak pro dámskou, tak pánskou módu atd.
