Jak vypočítat procentní změnu pomocí kontingenčních tabulek v Excelu

Kontingenční tabulky jsou úžasným integrovaným nástrojem pro vytváření sestav v Excelu. I když se obvykle používají k sumarizaci dat se součty, můžete je také použít k výpočtu procenta změny mezi hodnotami. Ještě lepší: Je to jednoduché.

Tuto techniku ​​můžete použít k provádění nejrůznějších věcí – prakticky všude, kde byste chtěli vidět, jak se jedna hodnota srovnává s druhou. V tomto článku použijeme jednoduchý příklad výpočtu a zobrazení procenta, o které se mění celková hodnota prodeje měsíc po měsíci.

Zde je list, který použijeme.

Je to docela typický příklad prodejního listu, který ukazuje datum objednávky, jméno zákazníka, obchodního zástupce, celkovou hodnotu prodeje a několik dalších věcí.

Abychom to všechno udělali, nejprve naformátujeme náš rozsah hodnot jako tabulku v Excelu a poté vytvoříme kontingenční tabulku, abychom mohli provádět a zobrazovat naše výpočty procentuální změny.

Formátování rozsahu jako tabulky

Pokud váš rozsah dat ještě není zformátován jako tabulka, doporučujeme vám tak učinit. Data uložená v tabulkách mají mnoho výhod oproti datům v oblastech buněk listu, zejména při použití kontingenčních tabulek (přečtěte si více o výhodách používání tabulek).

Chcete-li naformátovat oblast jako tabulku, vyberte oblast buněk a klepněte na Vložit > Tabulka.

Zkontrolujte, zda je rozsah správný, zda máte v prvním řádku tohoto rozsahu záhlaví, a poté klikněte na „OK“.

Rozsah je nyní formátován jako tabulka. Pojmenování tabulky usnadní budoucí odkazování při vytváření kontingenčních tabulek, grafů a vzorců.

Klikněte na kartu „Návrh“ v části Nástroje tabulky a zadejte název do pole na začátku pásu karet. Tato tabulka byla pojmenována „Prodej“.

Pokud chcete, můžete zde také změnit styl stolu.

Vytvořte kontingenční tabulku pro zobrazení procentuální změny

Nyní pojďme k vytvoření kontingenční tabulky. V nové tabulce klikněte na Vložit > Kontingenční tabulka.

Zobrazí se okno Vytvořit kontingenční tabulku. Automaticky rozpozná váš stůl. V tomto okamžiku však můžete vybrat tabulku nebo rozsah, který chcete pro kontingenční tabulku použít.

Seskupte data do měsíců

Poté přetáhneme datové pole, podle kterého chceme seskupit, do oblasti řádků kontingenční tabulky. V tomto příkladu má pole název Datum objednávky.

Od Excelu 2016 jsou hodnoty data automaticky seskupeny do let, čtvrtletí a měsíců.

Pokud to vaše verze Excelu nedělá nebo chcete jednoduše změnit seskupení, klikněte pravým tlačítkem na buňku obsahující hodnotu data a vyberte příkaz „Skupina“.

Vyberte skupiny, které chcete použít. V tomto příkladu jsou vybrány pouze roky a měsíce.

Rok a měsíc jsou nyní pole, která můžeme použít pro analýzu. Měsíce jsou stále pojmenovány jako Datum objednávky.

Přidejte pole hodnot do kontingenční tabulky

Přesuňte pole Rok z Řádků do oblasti Filtr. To umožňuje uživateli filtrovat kontingenční tabulku po dobu jednoho roku, než aby kontingenční tabulku zahlcovala příliš velkým množstvím informací.

Dvakrát přetáhněte pole obsahující hodnoty (v tomto příkladu celková hodnota prodeje), které chcete vypočítat a prezentovat změnu, do oblasti Hodnoty.

Možná to zatím nevypadá. To se ale velmi brzy změní.

Obě pole hodnot budou mít výchozí hodnotu součtu a aktuálně nemají žádné formátování.

Hodnoty v prvním sloupci bychom chtěli zachovat jako součty. Vyžadují však formátování.

Klikněte pravým tlačítkem na číslo v prvním sloupci a z místní nabídky vyberte „Formátování čísla“.

V dialogovém okně Formát buněk vyberte formát „Účetnictví“ s 0 desetinnými místy.

Kontingenční tabulka nyní vypadá takto:

Vytvořte sloupec Procentuální změny

Klikněte pravým tlačítkem na hodnotu ve druhém sloupci, ukažte na „Zobrazit hodnoty“ a poté klikněte na možnost „% rozdíl od“.

Jako základní položku vyberte „(Předchozí)“. To znamená, že aktuální hodnota měsíce je vždy porovnána s hodnotou předchozích měsíců (pole Datum objednávky).

Kontingenční tabulka nyní zobrazuje hodnoty i procentuální změnu.

Klikněte na buňku obsahující štítky řádků a jako záhlaví tohoto sloupce zadejte „Měsíc“. Poté klikněte do buňky záhlaví pro druhý sloupec hodnot a napište „Variance“.

Přidejte nějaké variační šipky

Abychom tuto kontingenční tabulku skutečně vylepšili, chtěli bychom lépe vizualizovat procentuální změnu přidáním několika zelených a červených šipek.

Ty nám poskytnou krásný způsob, jak vidět, zda byla změna pozitivní nebo negativní.

Klikněte na kteroukoli z hodnot ve druhém sloupci a poté klikněte na Domů > Podmíněné formátování > Nové pravidlo. V okně Upravit pravidlo formátování, které se otevře, proveďte následující kroky:

Vyberte možnost „Všechny buňky zobrazující hodnoty „Variance“ pro datum objednávky“.
V seznamu Styl formátu vyberte „Sady ikon“.
Vyberte červený, oranžový a zelený trojúhelník ze seznamu Styl ikon.
Ve sloupci Typ změňte možnost seznamu na „Číslo“ místo Procenta. Tím se změní sloupec Hodnota na 0. Přesně to, co chceme.

Klikněte na „OK“ a podmíněné formátování se použije na kontingenční tabulku.

Kontingenční tabulky jsou neuvěřitelným nástrojem a jedním z nejjednodušších způsobů, jak zobrazit procentuální změnu hodnot v čase.