Kontingenční tabulky v Excelu představují skvělý nástroj pro tvorbu přehledů. Ačkoli se nejčastěji využívají k sumacím dat, nabízejí i možnost vypočítat procentuální změnu mezi jednotlivými hodnotami, a to velmi jednoduchým způsobem.
Tuto techniku lze využít v mnoha situacích, kde potřebujete porovnat vývoj jedné hodnoty oproti jiné. V tomto článku si ukážeme, jak pomocí kontingenční tabulky vypočítat a zobrazit procentuální změnu celkového objemu prodeje v průběhu jednotlivých měsíců.
Níže je ukázkový list, se kterým budeme pracovat.
Jedná se o typický prodejní list, obsahující datum objednávky, jméno zákazníka, obchodního zástupce, celkovou hodnotu prodeje a další informace.
Pro dosažení našeho cíle nejprve naformátujeme rozsah dat do tabulky v Excelu. Následně vytvoříme kontingenční tabulku, která nám umožní provést a zobrazit výpočet procentuální změny.
Formátování dat jako tabulky
Pokud data nejsou dosud formátována jako tabulka, vřele doporučujeme tak učinit. Tabulky nabízejí oproti obyčejným oblastem buněk mnoho výhod, obzvláště při práci s kontingenčními tabulkami (zjistěte více o výhodách tabulek).
Pro převedení oblasti dat na tabulku, označte požadovanou oblast buněk a zvolte možnost Vložit > Tabulka.
Ověřte, zda je rozsah správný a zda má tabulka v prvním řádku záhlaví. Poté klikněte na tlačítko „OK“.
Nyní je váš rozsah naformátován jako tabulka. Pojmenováním tabulky si usnadníte budoucí odkazy při vytváření kontingenčních tabulek, grafů a vzorců.
V kartě „Návrh“, která se objeví v Nástrojích tabulky, zadejte do příslušného pole název tabulky. V tomto příkladu byla tabulka pojmenována „Prodeje“.
Můžete si zde také upravit styl tabulky.
Vytvoření kontingenční tabulky pro zobrazení procentuální změny
Přejděme k vytvoření kontingenční tabulky. V novém listě vyberte Vložit > Kontingenční tabulka.
Zobrazí se okno pro vytvoření kontingenční tabulky. Excel automaticky rozpozná vaši tabulku, ale máte možnost zvolit jinou tabulku nebo rozsah.
Seskupení dat podle měsíců
Do oblasti řádků kontingenční tabulky přetáhněte datové pole, podle kterého chcete data seskupovat. V tomto případě je to pole „Datum objednávky“.
Od verze Excel 2016 se data automaticky seskupují podle let, čtvrtletí a měsíců.
Pokud vaše verze Excelu automatické seskupení nepodporuje, nebo chcete seskupení změnit, klikněte pravým tlačítkem myši na buňku obsahující datum a vyberte možnost „Skupina“.
Zvolte skupiny, které chcete použít. V tomto příkladu jsme vybrali roky a měsíce.
Nyní máme pole pro analýzu v podobě roku a měsíce. Měsíce jsou stále označeny jako „Datum objednávky“.
Přidání polí s hodnotami do kontingenční tabulky
Přesuňte pole „Rok“ z oblasti Řádky do oblasti Filtr. Díky tomu můžete filtrovat kontingenční tabulku pro konkrétní rok a zabránit tak zahlcení tabulky nadměrným množstvím dat.
Dvakrát přetáhněte pole s hodnotami (v tomto případě „Celková hodnota prodeje“), u kterých chcete vypočítat a zobrazit procentuální změnu, do oblasti Hodnoty.
Zatím to možná nevypadá nejlépe, ale to se brzy změní.
Obě pole hodnot mají ve výchozím nastavení součet a zatím nemají žádný formát.
Hodnoty v prvním sloupci si ponecháme jako součty, ale je nutné je naformátovat.
Klikněte pravým tlačítkem myši na libovolné číslo v prvním sloupci a z kontextového menu zvolte „Formát čísel“.
V zobrazeném dialogovém okně „Formát buněk“ vyberte formát „Účetnický“ s 0 desetinnými místy.
Kontingenční tabulka nyní vypadá takto:
Vytvoření sloupce Procentuální změna
Klikněte pravým tlačítkem myši na hodnotu ve druhém sloupci, najděte „Zobrazit hodnoty jako“ a klikněte na možnost „% rozdíl od“.
Jako základní položku vyberte „(Předchozí)“. To způsobí, že se hodnota aktuálního měsíce bude vždy porovnávat s hodnotou předchozího měsíce (pole „Datum objednávky“).
Kontingenční tabulka nyní zobrazuje jak hodnoty, tak i procentuální změnu.
Klikněte na buňku se štítky řádků a zadejte do záhlaví sloupce „Měsíc“. Poté klikněte do buňky záhlaví druhého sloupce hodnot a zadejte „Odchylka“.
Přidání vizualizace odchylek
Pro vylepšení vizuální stránky tabulky přidáme grafické znázornění procentuální změny pomocí zelených a červených šipek.
Díky nim bude na první pohled zřejmé, zda je daná změna pozitivní nebo negativní.
Klikněte na libovolnou hodnotu ve druhém sloupci a v pásu karet zvolte Domů > Podmíněné formátování > Nové pravidlo. V okně pro úpravu pravidla formátování proveďte následující kroky:
Vyberte možnost „Všechny buňky zobrazující hodnoty ‚Odchylka‘ pro Datum objednávky“.
V seznamu „Styl formátu“ vyberte „Sady ikon“.
Vyberte červený, oranžový a zelený trojúhelník z nabídky „Styl ikon“.
Ve sloupci „Typ“ změňte hodnotu z „Procento“ na „Číslo“. Hodnota sloupce se tak změní na 0. To je přesně to, co chceme.
Klikněte na „OK“ a podmíněné formátování se aplikuje na kontingenční tabulku.
Kontingenční tabulky jsou skvělým nástrojem a představují jeden z nejjednodušších způsobů, jak zobrazit procentuální změnu hodnot v čase.