Kumulativní součet v kontingenční tabulce | Mezisoučty a na co si dát pozor

V dnešním videu se podíváme na to, jak v kontingenční tabulce vytvořit kumulativní neboli průběžné součty. Představte si, že máte v kontingenční tabulce zobrazit tržby po měsících, ale zároveň chcete, aby se vedle zobrazily tržby jako kumulativní součet. Tedy situaci, kdy se předchozí tržba přičte k tržbě následující. Ukážeme si dva způsoby, jak to vytvořit. Jeden výpočet se vždy na konci roku vynuluje a začne se počítat zase od znovu. Druhý výpočet bude počítat kumulativní součty bez ohledu na rok. Na závěr videa si ještě ukážeme jednu důležitou věc, na kterou si u kumulativních součtů musíte dát pozor, abyste neudělali chybu. 

Excelový soubor ke stažení

Kumulativní součet v kontingenční tabulce | Mezisoučty

Pro dnešní ukázku použijeme velmi malou sadu zdrojových dat. Zdrojovou tabulku máme na kartě zdrojová data, kde máme tabulku s denními tržbami za produkty. Pomocí kontingenční tabulky chceme analyzovat tržby po měsících. Nejen to, v kontingenční tabulce bychom rádi rovněž zobrazili kumulativní součty tržeb.

Kumulativní součet v kontingenční tabulce 1
Obrázek č.1 Zdrojová tabulka

Musíme začít tím, že vytvoříme kontingenční tabulku. Klikneme do zdrojové tabulky a na kartě Vložení vybereme Kontingenční tabulka. Kontingenční tabulku vložíme na stejný list vedle zdrojových dat. Na řádky kontingenční tabulky vložíme datum a do pole hodnoty vložíme tržby. Vidíme, že datumy se automaticky seskupily po letech, kvartálech a měsících. Pro účely této kalkulace se nám hodí seskupení po letech a měsících, kvartály nepotřebujeme, tak je z tabulky vyhodíme a ostatní necháme. 

Měsíce se v kontingenční tabulce zobrazují ve formátu čísel, pokud byste chtěli, aby se měsíce zobrazili textem, jako leden, únor, březen a tak dále, tak se podívejte na video Jak v kontingenční tabulce zobrazit měsíce jako text.

Kumulativní součet v kontingenční tabulce 2
Obrázek č.2 Vytvoření kontingenční tabulky

Rovnou zformátujeme tržby, a to tak, že klikneme do sloupce tržeb a vybereme Formát čísla, kde nastavíme měnu bez desetinných míst.

Kumulativní součet v kontingenční tabulce 3
Obrázek č.3 Změna formátu čísla

Teď máme pro každý měsíc v daném roce zobrazené tržby. Vedle bychom ale chtěli vidět kumulativní součet tržeb. Musíme tedy pole tržby vložit do kontingenční tabulky ještě jednou. Opět pole rovnou zformátujeme, klikneme do sloupce a vybereme Formát čísla, měna a bez desetinných míst.

Kumulativní součet v kontingenční tabulce 4
Obrázek č.4 Druhý sloupec tržeb v kontingenční tabulce

Pro zobrazení kumulativních tržeb klikneme do druhého sloupce tržeb a buď klikneme levým tlačítkem myši na pole tržby v poli hodnoty a vybereme Nastavení polí hodnot a zde Zobrazit hodnoty jako a nebo klikneme pravým tlačítkem myši na hodnoty ve druhém sloupci a vybereme Zobrazit hodnoty jako. Tak jako tak ve výpočtu Zobrazit hodnoty jako vybereme Mezisoučet v. 

Máme na výběr základní pole, jelikož máme na řádcích datumy, tak necháme jako základní pole datum. Potvrdíme. Tržby ve druhém sloupci se změnily na kumulativní součty. Jak vidíme, lednové hodnoty se rovnají. V únoru už ale máme součet ledna a února. Březnová částka se rovná součtu částek leden, únor a březen. Prosincový kumulativní součet se rovná částce na řádku celkového součtu za vybraný rok.

Kumulativní součet v kontingenční tabulce 5
Obrázek č.5 Zobrazit hodnoty jako - Mezisoučet v

Všimněte si, že na konci každého roku se kumulativní součet vynuluje a pokračuje se znovu od ledna. Ve většině případů je to přesně to, co potřebujeme. Nicméně někdy byste rádi zobrazili i kumulativní součty od počátku do konce, bez ohledu na roky. To znamená, že by se poslední hodnota v roce 2020 přičetla k první hodnotě roku 2021 a kumulativní součty by takto pokračovali až do konce tabulky.

Obrázek č.6 Kumulativní součet v kontingenční tabulce

Pokud by vás napadlo vyhodit z kontingenční tabulky rok, tak by se výpočet stal nesmyslným, jelikož by se tím pádem sečetly všechny lednové hodnoty dohromady, všechny únorové hodnoty. To není to, co potřebujeme. Naším cílem je dosáhnout situace, kdy se prosincová hodnota z roku 2020 přičte k lednové hodnotě 2021 a bude se s kumulativním součtem pokračovat až do posledního měsíce v tabulce.

Jedním z postupů, jak toho můžete dosáhnout je, že zrušíte seskupení datumových hodnot v kontingenční tabulce. Klikneme do sloupce s datumy a vybereme Oddělit. Datumy se změnily na obyčejné denní datumy bez seskupení na měsíce a roky.

Kumulativní součet v kontingenční tabulce 7
Obrázek č.7 Odstranění seskupení z kontingenční tabulky

Všimněte si, že se tím výpočet ve druhém sloupci automaticky vyresetoval, a to bez jakéhokoliv upozornění. Výpočet mezisoučet se tak stal opět jen obyčejným sloupcem s tržbami. Musíme do něho tedy opět kliknout a vybrat Zobrazit hodnoty jako, mezisoučet v a potvrdit jako základní pole datum. Teď máme kumulativní součty od počátku do konce bez ohledu na to, o jaký rok se jedná.

Kumulativní součet v kontingenční tabulce 8a
Obrázek č.8 Resetování výpočtu ve sloupci kumulativní součty

Toto řešení ovšem většinou není to, co potřebujete, jelikož ve zdrojové tabulce můžete mít statisíce řádků a dní, takže by kontingenční tabulky byla dlouhá a nepodávala by vám požadovaný přehled. 

Na konci každého roku se kumulativní součet zastaví kvůli seskupení dat. Pokud chceme ukázat kumulativní součty bez ohledu na roky, musíte si pomoci pomocným sloupcem ve zdrojových datech. Přepneme se do zdrojových dat a přidáme nový sloupec, který se bude jmenovat Rok-měsíc. Zde použijeme excelovou funkci HODNOTA.NA.TEXT, kde jako hodnotu označíme datum v tabulce a jako formát použijeme v uvozovkách „rrrr-mm“). Funkci potvrdíme a stáhneme ji dolů pro všechny řádky.

Obrázek č.9 Funkce HODNOTA.NA.TEXT v pomocném sloupci

Překlikneme se do kontingenční tabulky a na kartě Analýza kontingenční tabulky musíme změnit zdroj dat, jelikož jsme do kontingenční tabulky přidali nový sloupec. V poli aktualizujeme rozsah kontingenční tabulky a potvrdíme. Do polí se vložil nový sloupec, který jsme právě vytvořili. Tento sloupec vložíme na řádky místo datumu, ve sloupcích necháme tržby. Opravíme výpočet, jelikož jsme z řádků vyhodili datumy a vložili tam nový sloupec, tak se výpočet změnil na chybovou hlášku. Klikneme do druhého sloupce tržeb a vybereme Zobrazit hodnoty jako, Mezisoučet v a opravíme základní pole, tentokrát to bude nový sloupec Rok-měsíc. Potvrdíme a teď máme mezisoučet, po měsících a letech, který se nezastaví na konci roku.

Kumulativní součet v kontingenční tabulce 10
Obrázek č.10 Kumulativní součet v kontingenční tabulce, který se nezastaví na konci roku

Při kumulativních součtech si musíte dát pozor na jednu věc. Upravíme tuto tabulku, vyhodíme z ní všechna pole a začneme od znovu. Na řádky vložíme datumy a tentokrát necháme automatické seskupení i s kvartály. Do pole tržeb vložíme tržby a to dvakrát. Opět změníme rychle u obou sloupců formát na měnu bez desetinných míst. Druhý sloupec změníme na mezisoučet v. Jako základní pole vybereme roky, jako minule. Potvrdíme a rozklikneme rok, abychom viděli detail po kvartálech. Nepřijde vám něco divného?

Obrázek č.11 Špatně zvolené základní pole v mezisoučtu

Když se podíváte na kumulativní součet ve druhém sloupci, tak čísla nedávají moc smysl. V roce 2020 jsou čísla identická s tržbami po kvartálech a v roce 2021 jsou sice sečtená, ale nejsou sečtené správné kvartály. Pokud si lámete hlavu s tím, co se to sečetlo, tak tím, že jsme vybrali jako základní pole datum, tak se sečetly kvartály napříč roky. V roce 2020 tedy zůstaly hodnoty stejné, ale v roce 2021 se sečetl první kvartál roku 2020 a první kvartál roku 2021. Atd. Což na 99 % není to, co chcete a potřebujete. Musíme tedy výpočet opravit, vybereme Zobrazit hodnoty jako a vybereme Mezisoučet v a opravíme základní pole, kde vybereme čtvrtletí. Teď už nám to počítá správně. 

Kumulativní součet v kontingenční tabulce 12
Obrázek č.12 Správně zvolené základní pole v mezisoučtu

MOHLO BY VÁS ZAJÍMAT

5 komentářů

  1. Dobrý den.
    Jsem členem Akademie Exelu. Jsem starší člověk a proto si stahuji jednotlivé kurzy do svého počítače, zejména textovou část. Prvně se podívám na video, pak si otevřu ve Wordu textovou část a exelovou tabulku a svým tempem postupuji s učením. Při snaze opět si stáhnout u kurzu “Kumulativní součet v kontingenční tabulce | Mezisoučty tabulky” text do Wordu se něco zadrhlo a text nelze uložit oproti exelové tabulky, která uložit šla. Jde o opatření z Vaší strany, nebo o jiný problém?
    Děkuji předem za Vaši odpověď
    Schlick Václav

  2. Dobrý deň, kontigenčná tabuľka mi sčituje časové údaje vo formáte h:mm. Súčty v riadkoch a stĺpcoch sú správne, ale celkový súčet je nesprávny. Ani v zdrojovej tabuľke funkcia SUM nedala správny súčet. V inej tabuľke dokonca aj jeden riadok a jeden stĺpec neboli sčítané správne. Keď vložím do zdrojovej tabuľky jednoduché čísla, kontigenčná tabuľka sčituje všetko správne. Kde hľadať chybu?

Napsat komentář

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