Kontingenční tabulky jsou úžasné vestavěné zprávynástroj v Excelu. I když se obvykle používají k sumarizaci dat s součty, můžete je také použít k výpočtu procenta změny mezi hodnotami. Ještě lepší: Je to snadné.
Tuto techniku byste mohli použít pro všechny druhyvěci - skoro kdekoli byste chtěli vidět, jak se jedna hodnota porovnává s jinou. V tomto článku budeme používat přímý příklad výpočtu a zobrazení procenta, o které se celková hodnota prodeje mění každý měsíc.
Zde je list, který budeme používat.
Je to docela typický příklad prodejního listu, který zobrazuje datum objednávky, jméno zákazníka, obchodní zástupce, celkovou prodejní hodnotu a několik dalších věcí.
Abychom toho všeho dosáhli, nejprve zformátujeme náš rozsah hodnot jako tabulku v Excelu a poté vytvoříme kontingenční tabulku, která provede a zobrazí výpočty procentuální změny.
Formátování rozsahu jako tabulky
Pokud váš datový rozsah již není naformátován jakou stolu, doporučujeme vám to. Data uložená v tabulkách mají více výhod oproti datům v buněčných rozsazích listu, zejména při použití kontingenčních tabulek (více o výhodách použití tabulek).
Chcete-li formátovat rozsah jako tabulku, vyberte oblast buněk a klikněte na Vložit> Tabulka.
Zkontrolujte, zda je rozsah správný, zda máte záhlaví v prvním řádku tohoto rozsahu, 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“.
Zde také můžete změnit styl tabulky.
Vytvořte kontingenční tabulku pro zobrazení změny procenta
Nyní pojďme na 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 zjistí vaši tabulku. Nyní však můžete vybrat tabulku nebo rozsah, který chcete použít pro kontingenční tabulku.
Seskupte data do měsíců
Poté přetáhneme datové pole, které chceme seskupit, do oblasti řádků kontingenční tabulky. V tomto příkladu je pole pojmenováno Datum objednávky.
Od Excelu 2016 jsou hodnoty data automaticky seskupeny do let, čtvrtletí a měsíců.
Pokud to vaše verze aplikace Excel neprovede nebo pokud chcete jednoduše seskupení změnit, klepněte pravým tlačítkem myši na buňku obsahující hodnotu data a poté 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.
Do kontingenční tabulky přidejte hodnotová pole
Přesuňte pole Rok z řádků do oblasti Filtr. To umožňuje uživateli filtrovat kontingenční tabulku po dobu jednoho roku, namísto nepořádku kontingenční tabulky s příliš velkým množstvím informací.
Přetáhněte pole obsahující hodnoty (v tomto příkladu hodnota celkového prodeje), kterou chcete vypočítat, a prezentujte změnu do oblasti Hodnoty dvakrát.
Možná to ještě příliš nevypadá. Ale to se brzy změní.
Obě pole hodnot budou mít výchozí součet a v současné době nebudou mít žádné formátování.
Hodnoty v prvním sloupci bychom chtěli zachovat jako součty. Vyžadují však formátování.
Klepněte pravým tlačítkem myši na číslo v prvním sloupci a v místní nabídce vyberte příkaz „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 pro změnu procenta
Klepněte pravým tlačítkem myši na hodnotu ve druhém sloupci, přejděte na příkaz „Zobrazit hodnoty“ a potom klepněte na možnost „Odchylka od%“.
Jako základní položku vyberte „(Předchozí)“. To znamená, že aktuální hodnota měsíce je vždy porovnává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 do buňky obsahující popisky řádků a jako záhlaví sloupce zadejte „Měsíc“. Poté klikněte do buňky záhlaví pro druhý sloupec hodnot a zadejte „Variance“.
Přidejte některé šipky variace
Chcete-li opravdu vyleštit tuto kontingenční tabulku, chtěli bychom si vizualizovat procentuální změnu lépe přidáním zelených a červených šipek.
To nám poskytne krásný způsob, jak zjistit, zda změna byla 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 stylu vyberte „Sada ikon“.
- Ze seznamu Styl ikon vyberte červené, oranžové a zelené trojúhelníky.
- Ve sloupci Typ změňte možnost seznamu a řekněte „Číslo“ namísto procenta. Tím změníte sloupec Hodnota na 0. Přesně to, co chceme.
Klikněte na „OK“ a na kontingenční tabulku se použije podmíněné formátování.
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.
