Souhrny v excelových tabulkách | Výpočty v tabulkách

V dnešním videu si ukážeme, jak v excelových tabulkách používat souhrny neboli mezi výpočtové řádky. Rovněž si ukážeme, jak můžeme pomocí souhrnů lehce seskupit data v tabulce, a to i do několika různých kategorií a podkategorií. Souhrny v excelových tabulkách vám umožní rychlou analýzu dat, a to zvláště ve chvíli, kdy se vám nevyplatí data analyzovat pomocí kontingenčních tabulek.

Excelový soubor ke stažení

Souhrny v excelových tabulkách

V dnešním příkladu máme tabulku dat, ve které máme kromě produktů, jejich tržeb, nákladů a marže i rozdělení produktů dle typu a pobočky. Řekněme, že bychom pod každý typ produktu chtěli vložit řádek mezisoučtu. Chceme tedy v tabulce vidět mezisoučet pro oblečení, obuv a doplňky.

Souhrny v excelových tabulkách 1
Obrázek č.1 Zdrojová tabulka

Klikneme do tabulky a nejprve data seřadíme tak, aby byly jednotlivé typy produktů pod sebou. Klikneme do sloupce s typem produktů a na kartě Data vybereme Seřadit. Chceme seřadit sloupec Typ a to podle hodnot od A do Z. Potvrdíme a hodnoty tabulce se seřadily. Teď máme pod sebou jednotlivé typy produktů.

Obrázek č.2 Seřazení dat

Teď chceme do tabulky vložit mezisoučtové řádky. Klikneme opět do tabulky a na kartě Data vybereme Souhrn. Zde nastavíme, pro jaké sloupce souhrny chceme. Chceme souhrn pro typ produktu, takže vybereme typ pro každou změnu ve sloupci. Jako výpočet chceme součet a které sloupce chceme sečíst? Chceme sečíst jak tržby, náklady, tak marže. Vše ostatní necháme tak, jak je. Potvrdíme a do tabulky se vložily mezisoučtové řádky. 

Obrázek č.3 Souhrn pro sloupec typ

Kromě mezisoučtových řádků se do tabulky vložil i řádek celkového součtu. Navíc se mezisoučty rovnou vložily i se seskupením, o kterém jsme měli nedávno na Akademii Excelu video. Máme tak možnost jednotlivé typy produktů sbalovat a rozbalovat podle potřeby.

Obrázek č.4 Souhrny v tabulce

Klikneme do jednoho z výpočtů a vidíme, že funkce, která hodnoty sčítá je funkce SUBTOTAL. Na řádku celkového součtu je rovněž vložená funkce SUBTOTAL. Všimněte si, že funkce SUBTOTAL na celkovém řádku označuje ve druhém parametru funkce celý sloupec hodnot, a to včetně ostatních mezisoučtů. Výpočet je ale přesto správný a důvodem je to, že funkce SUBTOTAL ignoruje ve výpočtu ostatní funkce SUBTOTAL ve sloupci.

Obrázek č.5 Funkce SUBTOTAL na celkovém řádku souhrnu

Mezisoučty můžeme v tabulce kombinovat. Řekněme, že kromě typů produktů potřebujeme ještě mezisoučty pro pobočky. Opět musíme nejprve pobočky seřadit tak aby byly jednotlivé pobočky pod sebou. Následně klikneme do tabulky a vybereme Souhrn. Tentokrát chceme souhrn pro sloupec Pobočka. Typ výpočtu je součet a chceme sečíst stejné položky jako u typu. Potvrdíme a do tabulky se vložily mezisoučty pro pobočky, ale zmizely nám mezisoučty pro typ produktů. A je to proto, protože jsme zapomněli na jedno políčko.

Tip: Nejlepší je před vložením souhrnů nejprve seřadit všechny sloupce, do kterých budeme chtít vložit souhrny. 

Souhrny v excelových tabulkách 6
Obrázek č.6 Druhý souhrn v tabulce

 Vrátíme se zpátky so bodu, kdy jsme měli v tabulce součty pro typ produktů a ukážeme si, kde jsme udělali chybu. Klikneme do tabulky, karta Data a Souhrn. Opět vybereme jako sloupec pobočku, vše necháme tak jak je, ale musíme odškrtnout pole Nahradit aktuální souhrny. Potvrdíme a teď máme v tabulce mezisoučtový řádek jak pro typ produktu, tak pro jednotlivé pobočky.

Souhrny v excelových tabulkách 7
Obrázek č.7 Nahradit aktuální souhrny

Pokud bychom chtěli mezi výpočtové řádky z tabulky odstranit, tak buď můžeme odstranit pouze vybrané mezi výpočty, a to tak, že řádek s vybraným mezi výpočtem označíme a jednoduše smažeme. Můžeme rovněž naráz označit více mezi výpočtů, a to pomocí označování řádků a držení klávesy CTRL. Následně na jeden z řádků klikneme a vybereme Odstranit. Pokud chceme odstranit všechna seskupení v tabulce včetně všech mezi výpočtových řádků, tak klikneme do tabulky a na kartě Data vybereme Souhrn a zde dole vybereme Odebrat vše. Tím se z tabulky odstraní všechny mezi výpočty a seskupení.

Obrázek č.8 Odstranění souhrnů v tabulce

To není vše, jednotlivé mezisoučty nebo spíše mezi výpočty můžeme v tabulce kombinovat. Vrátíme se k tabulce, kde jsme měli mezisoučty jen pro typy produktů. Řekněme, že nás kromě součtů zajímají i průměrné hodnoty. Klikneme do tabulky a na kartě Data vybereme Souhrn. Chceme průměr spočítat podle typů produktů, takže vybereme sloupec Typ. Výpočet změníme ze součtu na průměr. Opět vybereme pro které sloupce chceme průměrné hodnoty spočítat a nesmíme zapomenout na to, že pokud chceme mít v tabulce uvedené jak součty, tak průměry, tak že nesmíme mít zaškrtnuté pole Nahradit aktuální souhrny. Potvrdíme a do tabulky se vložil nový výpočet s průměrem. Takto tedy můžete kupit jednotlivé výpočty pro zvolené sloupce. Pokud byste chtěli výpočet součtu nahradit průměrem, tak byste u průměru naopak zaškrtli pole Nahradit aktuální souhrny.

Obrázek č.9 Kombinace několika souhrnů

Jamile do tabulky přidáte více mezi výpočtových řádků, tak se vám stejné výpočty automaticky vloží i do řádků celkových výpočtů pro celou tabulku.

Souhrny v excelových tabulkách 10
Obrázek č.10 Více souhrnů na řádku celkového součtu

Použití mezisoučtů se hodí pro rychlou analýzu dat. Nevýhodou jejich používání je, že se vám mění struktura tabulky. Z tabulkového formátu se vlivem mezisoučtů rozložení tabulky změnilo a do tabulky se přidaly mezisoučty i řádek celkového součtu. Kdybychom teď na tato data chtěli použít jiné excelové funkce mimo tabulku, jako funkce SUMIF nebo COUNTIF, tak budeme mít problém, jelikož jsme si rozbili tabulkové rozložení tabulky. Pro dlouhodobější a flexibilnější práci je tak lepší používat pro účely mezisoučtů kontingenčních tabulek. Ty nenaruší původní strukturu tabulky a poskytují větší flexibilitu u práce s daty. Nicméně pro rychlou přehledovou analýzu dat se vám funkce souhrnů může hodit.

MOHLO BY VÁS ZAJÍMAT

Jedna odpověď

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *