V dnešním videu se podíváme na 5 způsobů, jak můžeme v Excelu seskupit a agregovat data. Jedná se o velmi častý úkol v Excelu. Máme rozsáhlou zdrojovou tabulku s opakujícími se produkty a potřebujeme z tabulky vytvořit jedinečný seznam produktů a k nim dopočítat celkové tržby. Ukážeme si pět způsobů, jak toho dosáhnout, od nejjednoduššího způsobu, až po pokročilé kombinace funkcí, které zajistí, že se jakákoliv změna propíše do souhrnné tabulky, až po dynamické funkce nebo Power Query. Každý způsob má svá pro i proti a každý se hodí použít v trochu jiném případě. A přesně to si projdeme v dnešním videu.
Excelový soubor ke stažení:
Než se ale pustíme do prvního způsobu, tak nejprve změníme zdrojová data na excelovou tabulku. Klikneme do tabulky a použijeme klávesovou kombinaci CTRL+T. Potvrdíme, že tabulka má záhlaví a z obyčejného rozsahu dat je excelová tabulka, kterou pojmenujeme jako Data a ještě odebereme toto typické proužkování, takže vybereme ve Stylu tabulky Žádný styl.
Základní funkce
Začneme pravděpodobně nejjednodušším způsobem, jak seskupit a agregovat data pomocí excelových funkcí. Nejprve vytvoříme jedinečný seznam produktů z tabulky. Zkopírujeme seznam produktů z excelové tabulky, vložíme ho do sloupce, kde chceme mít jedinečný seznam a odstraníme duplicity. Karta Data a Odebrat duplicity. Tím zůstane pouze jedinečný seznam produktů.
A k tomu teď dopočítáme celkové tržby. K tomu použijeme funkci SUMIF nebo SUMIFS, záleží na vaší preferenci. Já použiji třeba funkci SUMIFS. Nejprve označíme oblast součtu, což je sloupec s tržbami. Následuje oblast kritérií, což je sloupec s produkty a jako poslední první produkt v tabulce. Jelikož pracujeme s excelovou tabulkou, tak sloupce nemusíme fixovat. Funkci potvrdíme a pošleme ji dolů. Změníme formát na českou měnu a máme jednoduchou tabulku, kde máme uvedené produkty a jejich celkové tržby.
Výhodou tohoto postupu je, že je velmi jednoduchý a funguje ve všech verzích Excelů. Pokud ve zdrojové tabulce změníme nějakou existující hodnotu, tak se tato změna okamžitě propíše do celkové tabulky. Nevýhodou ale je, že pokud do tabulky přidáme nový produkt, tak už se tato změna nepropíše. A to proto, že jedinečný seznam produktů vznikl kopírováním a odstraněním duplicit a není tak propojený se zdrojovou tabulkou. V tomto případě bychom tedy buď museli celý proces s tvorbou tabulky buď opakovat a nebo nový produkt dopsat do tabulky a protáhnout funkci SUMIFS i pro tento nový produkt.
Kontingenční tabulka
Další možností, pravděpodobně druhou nejjednodušší, kterou můžete použít pro seskupení dat je kontingenční tabulka. V takovém případě klikneme do zdrojové tabulky a na kartě Vložení vybereme Kontingenční tabulka a vybereme z tabulky nebo oblasti. Tabulku vložíme v tomto případě vedle zdrojové tabulky na stejný list. Vytvoříme kontingenční tabulku, a to tak, že na řádky vložíme pole produkty a do pole hodnoty vložíme tržby. Tím se velmi jednoduše vytvoří tabulka bez použití jediné excelové funkce.
Výhodou je, že kontingenční tabulku je velmi jednoduché vytvořit. Výhodou je rovněž to, že je kontingenční tabulka propojená se zdrojem, takže pokud změníme částku a zároveň přidáme i nový produkt, tak se po aktualizaci kontingenční tabulky tyto změny promítnou do souhrnné tabulky, a to včetně nově přidaného produktu. Slabou nevýhodou kontingenčních tabulek je to, že nesmíte na obnovení dat zapomenout, jinak se vám nová data nepropíší do kontingenční tabulky.
Dynamické funkce
Ti z vás, kteří mají přístup k dynamickým funkcím mohou použít i následující postup. V tomto případě máme rovnou dvě možnosti. Pokud máte alespoň zakoupenou alespoň licenci 2021 nebo jste předplatitelem Microsoft 365, tak můžete použít funkci UNIQUE. Pomocí funkce UNIQUE nejprve vytvoříme jedinečný seznam produktů. Takže funkce UNIQUE, kde označíme sloupec s produkty. Potvrdíme a funkce vrátí jedinečný seznam hodnot.
A následně použijeme funkci SUMIF nebo SUMIFS. Takže opět oblastí součtu je sloupec s tržbami. Oblastí kritérií je sloupec s produkty a jako kritérium se odkážeme na první produktu. Abychom ale zajistili, že tabulka bude dynamická a reagovat na nově přidané produkty, tak za kritérium napíšeme křížek. Potvrdíme a máme seskupenou tabulku.
Výhodou tohoto přístupu je, že jelikož je seznam vytvořený pomocí funkce UNIQUE, tak se přidá nový produkt, který přidáme do tabulky a jelikož jsme se ve funkci SUMIFS na kritérium odkázali křížkem, tak se aktualizuje i součet tržeb. Nevýhodou samozřejmě je, že tuto možnost můžete využít, pouze pokud používáte novější verze Excelu.
Funkce SESKUPITPODLE
Pokud jste předplatiteli Microsoft 365 máte rovněž možnost použít zbrusu novou funkci SESKUPITPODLE neboli funkci GROUPBY. Tato funkce vytvoří celou seskupenou tabulku za vás, v podstatě se jedná o obdobu kontingenční tabulky. Napíšeme funkci SESKUPITPODLE, kde nejprve v parametru řádky označíme sloupec s produkty, a jelikož chceme aby v seskupení tabulce bylo i záhlaví, tak sloupec označíme včetně záhlaví. Následuje parametr hodnoty, což jsou hodnoty, které chceme agregovat, tedy sloupec s tržbami a opět včetně záhlaví. Následuje výpočet, tržby chceme sečíst, takže funkce SUMA a v parametru záhlaví vybereme trojku, jelikož chceme záhlaví zobrazit. Potvrdíme a funkce SESKUPITPODLE vytvořila souhrnnou tabulku včetně záhlaví a řádku celkového součtu na jedno kliknutí.
Výhodou je, že se jakákoliv změna okamžitě propíše do souhrnné tabulky, včetně nových produktů. Tuto funkci ale můžete využít pouze, pokud jste předplatiteli verze Microsoft 365.
Dynamický seznam
U dynamických funkcí a kontingenční tabulky je velkou výhodou to, že se při změně v datech vše aktualizuje a to včetně nově přidaných produktů. Pokud nemáte přístup k těmto novým funkcím a nechcete z nějakého důvodu použít kontingenční tabulku, a stejně chcete zajistit, aby i seznam jedinečných produktů reagoval na změny, tak můžete použít následující postup, který zahrnuje klasické excelové funkce dostupné ve všech verzích Excelů. Bude vám stačit několik málo funkcí, které chytře zkombinujete dohromady. To, co musíme vyřešit je dynamický seznam produktů, který bude reagovat na změny.
Na vytvoření dynamického jedinečného seznamu existuje následující trik. Jádrem řešení bude kombinace funkcí INDEX & POZVYHLEDAT neboli INDEX & MATCH. Začneme s funkcí INDEX, kde v parametru pole označíme seznam produktů ze zdrojové tabulky. Jelikož chceme, aby funkce INDEX vrátila seznam s produkty. A toto pole plně zafixujeme. Následuje funkce POZVYHLEDAT neboli funkce MATCH, kde v parametru co vyplníme nulu, a v parametru prohledat použijeme funkci COUNTIF. Ve funkci COUNTIF v parametru oblast vytvoříme dynamickou oblast ze záhlaví této souhrnné tabulky. Takže označíme první buňku záhlaví ku první buňce záhlaví a první buňku v tomto rozpětí plně zafixujeme. A v parametru kritérium označíme seznam produktů ze zdrojové tabulky, který plně zafixujeme. Funkci ukončíme a potvrdíme a stáhneme dolů.
Tato kombinace vrátí jedinečný seznam produktů, stáhneme funkci o několik řádků navíc, abychom zajistili, že v případě přidání nových produktů se tyto nově přidané produkty přidají do tabulky. Na těchto prázdných řádcích se ale momentálně vrací chybové hlášky. Takže celou funkci INDEX zabalíme do funkce IFERROR, kde stanovíme, že v případě chyby se má vrátit prázdný textový řetězec.
Teď dopočítáme celkové tržby pomocí funkce SUMIFS. Funkci stáhneme pro více řádků dolů a na těchto prázdných řádcích se zobrazí nuly.
Pokud je zde nechceme, tak můžeme pro jejich skrytí použít vlastní formát. Označíme buňky, Formát buněk a vlastní formát a použijeme formát pro klasickou kladnou a zápornou hodnotu a nulu, na třetím místě přeskočíme, tím nám nuly na oko zmizí. Přidáme další nový produkt do tabulky a vše správně reaguje.
Přidáme nový produkt do tabulky a funkce automaticky zahrnou produkt a celkový součet do tabulky.
Power Query
Poslední možností je použití nástroje Power Query. V takovém případě klikneme do zdrojové tabulky a na kartě Data vybereme Načíst data a z tabulky nebo oblasti. Tabulka se tím načte do Power Query. A zde na záložce Transformace máme možnost Seskupit podle. Seskupit podle chceme podle pole produkt. Máme zde možnost i nový sloupec pojmenovat, tak ho nazveme jako produkty. A v poli operace si vybereme jaký výpočet chceme provést, chceme sčítat tržby, takže vybereme součet a v poli sloupec vybereme sloupec, který chceme sčítat, což jsou tržby. Potvrdíme seskupení a zdrojová data se seskupila do agregované tabulky.
Teď stačí tabulku poslat zpátky do Excelu. Takže zavřít a načíst a vybereme si, že chceme tabulku načíst na existující list vedle zdrojových dat. Potvrdíme a souhrnná tabulka se vloží na list. Výhodou je, že je tato tabulka propojená se zdrojovými daty, takže pokud do tabulky přidáme nové produkty a změníme částky a obnovíme spojení mezi tabulkami, tak se všechny změny propíší do souhrnné tabulky.
