Seskupování a agregace dat v Excelu je užitečná technika, která zjednodušuje práci s velkými objemy dat. Pomocí seskupení můžete dočasně snížit počet řádků a sloupců, což usnadňuje práci s daty.
Seskupování řádků a sloupců
Seskupování řádků a sloupců se používá zejména v Excelu, kde se pracuje se spoustou dat na mnoha řádcích a sloupcích. Seskupení vám umožňuje dočasně snížit počet řádků a sloupců a tím vám ulehčuje a usnadňuje práci s daty.
Jak vytvořit seskupení řádků
- Nejprve vybereme data (řádky nebo sloupce), které chceme seskupit.
- Následně použijeme na kartě DATA v sekci Datové nástroje možnost Seskupit.
- Tím se označené řádky seskupí dohromady, což poznáme podle toho, že se vlevo před čísly přidala tato lišta, kde máme znaménko plus nebo mínus.
- Pokud potřebujeme seskupené hodnoty skrýt, tak klikneme na znaménko mínus.
- Seskupovat můžeme už i seskupené řádky až do osmi úrovní.
Jak vytvořit seskupení sloupců
Princip funguje stejně jako u řádků:
- Označíme první tři měsíce, neoznačujeme souhrnný sloupec pro první čtvrtletí a na kartě Data vybereme Seskupit.
- Stejná ovládací lišta se přidala i nad sloupce s písmeny.
Klávesová zkratka pro seskupení a oddělení
Pro seskupení a oddělení existuje i klávesová zkratka.
- Pro seskupení: Označíme sloupce, které chceme seskupit a použijeme klávesovou kombinaci SHIFT+ALT a šipka doprava.
- Pro oddělení: Označíme sloupce a zmáčkneme SHIFT+ALT a šipku doleva.
Rozbalování a sbalování seskupených dat
Seskupené řádky můžeme sbalit či rozbalit buď přes plus/minus na okrajích listu, nebo čísla 1,2 atd. v levém horním rohu okna.
Čtěte také: Jak připnout soubory na hlavní panel ve Windows
V levé liště se kromě znamének plus a mínus objevila i tato čísla. Ta označují počet seskupených kategorií. Když klikneme na číslo 2, tak se všechny řádky najednou rozbalí. Když klikneme na jedničku, tak se všechny řádky naráz sbalí po kategoriích.
Odstranění seskupení
- Seskupení odstraníte tak, že označíte řádky nebo sloupce ve skupině a na kartě Data vyberete Oddělit.
- Hromadné odstranění seskupení: Pokud bychom se chtěli zbavit všech vytvořených skupin najednou, tak to nemusíme dělat ručně, ale na kartě Data vybereme Oddělit a zde vybereme Vymazat přehled.
Automatické seskupení
Existuje i možnost automatického seskupení. Na kartě Data vybereme Seskupit a zde Automatický přehled. Tím Excel sám vytvoří skupiny dat. Tato seskupení jsou na základě excelových funkcí. Excel v tomto případě najde všechny funkce jako jsou SUMA a SUBTOTAL a rozdělí data do seskupení podle těchto vzorců.
Dynamické doplňování dat
Dynamické doplňování funguje na principu „umělé inteligence Excelu“. Do nového sloupce vedle vašich dat zadejte do první buňky vzor, tedy čeho chcete docílit.
Na kartě DATA v sekci Datové nástroje klikněte na Dynamické doplňování.
Kopírování a vkládání seskupených hodnot
Seskupení se vám může hodit i ve chvíli, kdy chcete někomu poslat pouze souhrnné výpočty bez detailních hodnot.
Čtěte také: Instalace Android SDK z lokálního souboru
- Seskupení řádky tak, aby byly vidět pouze celkové výnosy a náklady a ve sloupcích byly vidět kvartální data.
- Data označíme a na kartě Domů klikneme na Najít a vybrat a zde Přejít na jinak.
- Vybereme pouze viditelné buňky, buňky zkopírujeme a vložíme je do sešitu kam potřebujeme.
Zabezpečení seskupených hodnot
Co když ale nechceme, aby měl někdo náhled na detailní data? Máme dvě možnosti:
- Zamknutí listu: Na kartě Revize vybereme Zamknout list, nastavíme heslo a list zamkneme.
- Skrytí ovládacích tlačítek: Na kartě Soubor vybereme Možnosti a zde Upřesnit a sjedeme až skoro dolů a najdeme sekci Možnosti zobrazení pro tento list, kde odškrtneme Zobrazit symboly přehledu v případě jeho použití. Po potvrzení zmizí ovládací tlačítka, nicméně tato možnost pouze skryla tlačítka a v podstatě skryla řádky a sloupce.
5 způsobů, jak seskupit a agregovat data v Excelu
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. Každý způsob má svá pro i proti a každý se hodí použít v trochu jiném případě.
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.
1. Základní funkce (SUMIF/SUMIFS)
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).
- K tomu teď dopočítáme celkové tržby. K tomu použijeme funkci SUMIF nebo SUMIFS.
Výhodou tohoto postupu je, že je velmi jednoduchý a funguje ve všech verzích Excelu. 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.
Čtěte také: jak na dropdown menu v Excelu
2. Kontingenční tabulka
Další možností, pravděpodobně druhou nejjednodušší, kterou můžete použít pro seskupení dat je kontingenční tabulka.
- 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.
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.
3. Dynamické funkce (UNIQUE, SUMIFS s křížkem)
Ti z vás, kteří mají přístup k dynamickým funkcím mohou použít i následující postup. 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ů.
- Následně použijeme funkci SUMIF nebo SUMIFS.
- Abychom ale zajistili, že tabulka bude dynamická a reagovat na nově přidané produkty, tak za kritérium napíšeme křížek (#).
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.
4. Funkce SESKUPITPODLE (GROUPBY)
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 (včetně záhlaví).
- Následuje parametr hodnoty, což jsou hodnoty, které chceme agregovat, tedy sloupec s tržbami (opět včetně záhlaví).
- Následuje výpočet (např. SUMA) a v parametru záhlaví vybereme trojku, jelikož chceme záhlaví zobrazit.
Výhodou je, že se jakákoliv změna okamžitě propíše do souhrnné tabulky, včetně nových produktů.
5. Power Query
Poslední možností je použití nástroje Power Query.
- 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.
- 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.
- V poli operace si vybereme jaký výpočet chceme provést (např. součet) a v poli sloupec vybereme sloupec, který chceme sčítat, což jsou tržby.
- Teď stačí tabulku poslat zpátky do Excelu (Zavřít a načíst) a vybereme si, že chceme tabulku načíst na existující list vedle zdrojových dat.
Užitečné tipy pro práci s přehledy
- Pokud máte seznam dat, která chcete seskupit a shrnout, můžete vytvořit osnovu až osmi úrovní.
- Ujistěte se, že každý sloupec dat, který chcete vytvořit přehled, má v prvním řádku popisek.
- Příkaz Souhrn vloží funkci SUBTOTAL bezprostředně pod nebo nad každou skupinu řádků podrobností a automaticky vytvoří přehled.
- Můžete také oddělit oddíly osnovy, aniž byste odebrali celou úroveň.
- Pokud zrušíte seskupení osnovy, zatímco jsou data podrobností skrytá, řádky/sloupce podrobností můžou zůstat skryté. Data můžete zobrazit přetažením ukazatele myši přes čísla viditelných řádků nebo písmena viditelných sloupců sousedících se skrytými řádky a sloupci.
- Pro přehled vytvořený z řádků používá Microsoft Excel styly, jako třeba úroveň_řádku_1 a úroveň_řádku_2. Pro přehled vytvořený ze sloupců používá Excel styly, jako třeba úroveň_sloupce_1 a úroveň_sloupce_2.
Tyto techniky vám pomohou efektivněji spravovat a analyzovat data v Excelu, šetřit čas a vytvářet přehledné reporty.
tags: #seskupovani #souboru #excel #na #liste #návod