V dnešním videu si představíme první ze dvou nových funkcí, které míří do Excelu. Jedná se o funkci KONTPODLE neboli anglicky funkci PIVOTBY. Tato funkce je zatím dostupná jen pro Insidery a Beta channel v Excelu, ale brzy by měla být spuštěná i pro veřejnost používající Microsoft 365. Funkce KONTPODLE je funkce, která tvoří agregované tabulky na způsob kontingenčních tabulek, proto rovněž anglicky název PIVOTBY. Na rozdíl od kontingenčních tabulek má ale tu výhodu, že nemusíme při změně ve zdrojových datech aktualizovat propojení mezi zdrojem a tabulkou. Funkce KONTPODLE reaguje okamžitě a dovoluje nám vytvářet suprové přehledové tabulky jednou funkcí. Pojďme se na tuto funkci podívat.
Excelový soubor ke stažení:
V příkladu máme zdrojovou excelovou tabulku pojmenovanou jako Zdroj. V tabulce jsou uvedené produkty, tržby a další informace o prodeji. Z této zdrojové tabulky potřebujeme rychle vytvořit několik souhrnných tabulek. První tabulek má zobrazovat součet tržeb pro produkty. Jasně, že bychom na tomto místě mohli použít kontingenční tabulku, pokud víme, jak ji vytvořit a jak s ní pracovat. Nebo bychom mohli použít kombinaci různých funkcí jako UNIQUE a SUMIFS. Brzy ale budete moci použít i novou funkci KONTPODLE.
=KONTPODLE (row_fields; dol_fields; values; function; [field_headers]; [row_total_depth]; [row_sort_order]; [col_total_depth];[col_sort_order]; [filter_array]; [relative_to])
Klikneme do buňky a najdeme funkci KONTPODLE neboli funkci PIVOTBY. Funkce KONTPODLE má čtyři povinné parametry a spoustu nepovinných parametrů. Začneme s povinnými parametry. Do buňky napíšeme rovná se a najdeme funkci KONTPODLE neboli PIVOTBY. Prvním parametrem funkce jsou pole řádky, tedy jaké pole ze zdrojové tabulky chceme mít na řádcích. Na řádcích chceme mít produkty, takže ve zdrojové tabulce označíme sloupec s produkty.
Následuje parametr pole sloupců, tedy jaké pole chceme mít ve sloupcích. Zatím nechceme dělit tržby pode sloupců, takže toto pole vynecháme a napíšeme středník.
Následuje parametr hodnoty. To jsou hodnoty, které chceme pro řádky a sloupce agregovat neboli se kterými chceme počítat. Nás zajímají součty tržeb, takže v parametru hodnoty označíme sloupec tržba.
A jako poslední povinný parametr je výpočet. Když se do něho přepneme, tak se nám nabídne několik základních výpočtů, které ve funkci KONTPODLE můžeme použít. Od základních jako je SUMA, PRŮMĚR, MIN a MAX zde máme i medián nebo procento. Začneme s funkcí SUMA, jelikož chceme tržby sčítat. To je vše co v základním nastavení musíme ve funkci KONTPODLE nastavit. Funkci ukončíme a potvrdíme a funkce KONTPODLE doručí souhrnnou tabulku, ve které máme sečtené tržby pro jednotlivé produkty. A nejen to, funkce KONTPODLE do souhrnné tabulky automaticky přidala i řádek celkového součty, kde sečetla celkové tržby za všechny produkty.
To, že se jedná o dynamické pole, které vzniklo z dynamické funkce, poznáme podle toho, že když do tabulky klikneme, tak se celá oblast orámuje touto modrou linkou. Funkce je rovněž aktivní pouze v první buňce, když klikneme do ostatních buněk, tak je funkce v příkazovém řádku šedivá, to znamená, že mazat nebo upravovat můžeme funkci pouze z první buňky.
Funkce KONTPODLE stejně jako ostatní dynamické funkce nepřenáší podkladový formát hodnot, takže na hodnoty akorát musíme přenést správný formát. Hodnoty označíme a změníme formát na českou měnu bez desetinných míst.
Výhodou oproti kontingenční tabulce je to, že pokud teď ve zdrojové tabulce přepíšeme jeden produkt na zcela nový, třeba na dámské župany, tak se tento produkt okamžitě přidá do tabulky bez nutnosti obnovovat spojení. Stejně tak, pokud změníme jakoukoliv hodnotu, tak se tato změna okamžitě propíše do souhrnné tabulky.
Naše souhrnná tabulka nemá záhlaví. I to ale můžeme vyřešit pomocí funkce KONTPODLE a prvního nepovinného parametru funkce. Musíme se ale k funkci vrátit a pokud chceme do tabulky zahrnout i název záhlaví, tak musíme označit i sloupce včetně záhlaví. Teď máme ve funkcích označené sloupce pouze bez záhlaví. Opravíme tedy nejprve pole, tak aby se označila i záhlaví excelové tabulky, což uděláme tak, že jedním stisknutím označíme sloupec hodnot a když na sloupec klikneme podruhé, tak se označí i záhlaví sloupce.
To samé uděláme i u pole hodnoty, ať to máme konzistentní.
Teď se přepneme do prvního nepovinného parametru funkce, což je záhlaví tabulky. Zde máme možnost záhlaví vůbec nezobrazovat, což je základní nastavení funkce. Pokud chceme naopak záhlaví sloupců zobrazit, tak vybereme možnost 3. Pokud označíme sloupce i se záhlavím, ale záhlaví zobrazovat nechceme, tak vybereme možnost 1.
Vybereme možnost 3 a funkci potvrdíme a do tabulky se přidá i záhlaví sloupců.
Vytvoříme druhou tabulku, kde tentokrát chceme vytvořit tabulku, která bude vypadat jako typická kontingenční tabulka. Na řádcích budou produkty, v hodnotách součet tržeb, ale tržby budou ještě ve sloupcích rozdělené podle typu produktu.
Klikneme do buňky, najdeme funkci KONTPODLE neboli funkci PIVOTBY a jako první parametr pole řádky označíme pole, které chceme na řádcích, což jsou produkty.
Následuje pole sloupců, které tentokrát nepřeskočíme, ale ve sloupcích chceme mít rozdělení podle typu produktu, takže označíme tento sloupec. Následují hodnoty, což je sloupec s tržbami a tržby chceme opět sčítat, takže jako výpočet vybereme funkci SUMA. Funkci potvrdíme a vrátí se nám souhrnná tabulka s tržbami rozdělenými podle produktů a podle typu produktu.
Funkce opět nepřenesla formát, takže označíme hodnoty a vybereme českou měnu bez desetinných míst. Všimněte si, že souhrnná tabulka automaticky v tomto případě přidala jak celkový součtový řádek, tak celkový součtový sloupec.
Kromě těchto jednoduchých tabulek ale můžeme pomocí funkce KONTPODLE tvořit i členitější tabulky, tak jak jsme zvyklí u kontingenčních tabulek. Řekněme, že tentokrát chceme vytvořit tabulku se souhrnem pro druh produktu a pobočky, tedy na řádcích chceme mít dvě pole. V poli hodnoty ale tentokrát nechceme mít součet tržeb, ale počet prodaných kusů produktu. Klikneme do buňky a najdeme funkci KONTPODLE. Pokud chceme mít na řádcích agregaci podle dvou sousedících polí, tak je v parametru označíme najednou. Takže chceme mít agregaci podle druhu produktu a pobočky, takže tyto dva sloupce označíme. A jelikož v tabulce budeme chtít i záhlaví, tak označíme sloupce včetně záhlaví.
Následuje pole sloupců, to tentokrát nepotřebujeme, takže pole přeskočíme.
Následují hodnoty, kde chceme mít počet prodaných kusů produktu. Co řádek to jeden prodej v tabulce. Takže vlastně chceme spočítat počet tržeb pro jednotlivé produkty, takže označíme sloupec tržeb včetně záhlaví.
A tržby chceme počítat, takže jako funkci vybereme POČET.
A chceme zobrazit i záhlaví tabulky, takže v prvním nepovinném parametru vybereme 3.
Funkci ukončíme a potvrdíme. Funkce KONTPODLE vrátila souhrnnou tabulku, kde máme počet prodaných kusů produktu podle pobočky a druhu produktu.
Do tabulky se rovněž přidal celkový řádek, kde vidíme počet prodaných kusů produktů celkem.
I tuto tabulku samozřejmě můžeme dále členit. Můžeme se k funkci vrátit a chtít vidět rozpad ještě podle typu produktu. V takovém případě bychom do druhého parametru vložili sloupec Typ produktu včetně záhlaví.
Teď máme v tabulce rovněž sloupec celkového součtu. Co kdybychom ale tyto součty v tabulce nechtěli a nebo naopak chtěli přidat i mezisoučty? Použijeme další nepovinný parametr.
Klikneme do funkce a přepneme se do dalšího nepovinného parametru, což jsou součty. V tomto parametru si můžeme nastavit, zda a jak chceme v souhrnné tabulce zobrazit celkové součty a mezisoučty. V základním nastavení se zobrazují v souhrnné tabulce celkové součty pro řádky a sloupce stejně jako v kontingenční tabulce.
Když v tomto parametru vybereme nulu, tak celkové součty odstraníme ze souhrnné tabulky.
Když v tomto parametru vybereme 2, tak se nejenže v tabulce zobrazí celkové součty, ale i mezisoučty, a to v situaci, kdy máme na řádcích více než jedno pole.
Parametry mínus jedna a dva jsou stejné jako předchozí jen s tím rozdílem, že se součty a mezisoučty zobrazí nahoře v tabulce, takže když vybereme mínus 2, tak se celkový součet zobrazí jako první řádek tabulky a mezisoučty se zobrazí nahoře nad kategorií.
Necháme v tomto parametru dvojku a rovnou budeme pokračovat.
Dalším nepovinným parametrem je typ seřazení tabulky. Zde si můžeme vybrat, podle kterého sloupce seřadíme souhrnnou tabulku a navíc si můžeme vybrat, zda hodnoty seřadíme vzestupně nebo sestupně. A to podle znaménka, které před číslo napíšeme. Základní nastavení je vzestupné řazení, které se označuje kladným číslem. Řekněme, že chceme tuto naši tabulku seřadit podle abecedy sestupně podle prvního sloupce druh produktu. Druh produktu je první sloupec tabulky a pokud ho chceme seřadit sestupně, tak před pořadové číslo sloupce napíšeme mínus. Takže do tohoto parametru napíšeme mínus jedna. Tím se tabulka seřadila podle abecedy od Z do A podle prvního sloupce.
Pokud bychom naopak chtěli seřadit tabulku podle počtu prodaných kusů a to vzestupně, tak do tohoto parametru napíšeme trojku. A tím pádem se celá tabulka seřadí podle celkového počtu prodaných kusů, a to v každé kategorii.
Kdybychom chtěli seřadit tabulku podle počtu sestupně, tak před pořadové číslo sloupce napíšeme mínus, takže mínus tři.
Další dva nepovinné parametry určují, zda chceme zobrazit součty a mezisoučty sloupců, což je to samé, co mezisoučty a celkové součty řádků, stejně tak další parametr řazení sloupců. To je to samé, co seřazení řádků, akorát pro sloupce.
Vytvoříme novou tabulku, tentokrát chceme souhrnnou tabulku, kde na řádcích budou produkty, které budou seřazené dle abecedy od A do Z, ve sloupcích bude rozdělení podle poboček, v hodnotách bude součet tržeb, ale do tabulky chceme zařadit pouze tržby od června do konce roku, tedy tržby od šestého měsíce. Začneme s funkcí KONTPODLE. V poli řádky označíme sloupec s produkty.
V poli sloupce označíme sloupec s pobočkami.
V hodnotách označíme sloupec s tržbami.
A jako výpočet vybereme funkci SUMA.
Záhlaví v tabulce nechceme, takže tento parametr přeskočíme, součty necháme standardní, takže opět parametr nevyplňujeme.
Přepneme se do seřazení řádků, kde chceme řadit vzestupně pode prvního sloupce, takže napíšeme jedničku. A teď se přepneme až do parametr filtr, kde můžeme stanovit podmínku pro filtrování souhrnné tabulky. Podmínka je, že sloupec s měsíce je vyšší nebo roven 6, tedy šestému měsíci. Ukončíme funkci a potvrdíme a máme souhrnnou tabulku, na které máme dokonce uplatněný filtr.
