V dnešním videu si ukážeme, jak můžeme v Excelu vytvořit vodopádový graf. Tedy graf, který zobrazuje přírůstky a úbytky včetně počátečních a koncových hodnot. Od verze Excelu 2016 je vodopádový graf zahrnutý mezi standardní grafy, nicméně v dnešním videu si ukážeme, proč není tento standardní graf vždy nejlepší volbou. Rovněž je tento způsob vytvoření grafu, který si dnes ukážeme, jedinou možností, jak vytvořit vodopádový graf, pro ty z vás, kteří používají verze Excelu před verzí 2016.
Excelový soubor ke stažení:
Vodopádový graf ve verzích Excelu od 2016
Nejprve si v rychlosti ukážeme, jak můžeme vodopádový graf vytvořit ve verzích Excelu od 2016.
V příkladu máme malou zdrojovou tabulku s počáteční hodnotou. Následně máme v tabulce přírůstky nebo poklesy za měsíce. Ve vodopádovém grafu chceme zobrazit počáteční, koncovou hodnotu a v mezidobí přírůstky a poklesy za vybrané měsíce.
Označíme zdrojovou tabulku a na kartě Vožení vybereme v sekci grafy vodopádový graf.
Graf se vloží do sešitu. Posuneme ho trochu stranou a podle potřeby zvětšíme.
Vodopádový graf se vložil v základu s dvěma barevnými sloupci. Jedna barva pro kladné hodnoty a jedna barva pro záporné hodnoty. Na první pohled vidíme, že poslední sloupec s konečnou hodnotou není správně usazený, jelikož by měl začínat dole na ose x stejně jako počáteční hodnota. To vyřešíme tak, že klikneme dvakrát na poslední sloupec, tak aby se označil pouze tento koncový sloupec a v nabídce pravého tlačítka myši vybereme Nastavit jako součet.
Tím se sloupec posune dolů na osu x a rovněž se změní jeho barva.
To samé můžeme udělat s počáteční hodnotou. Označíme dvojitým poklepáním sloupec s počáteční hodnotou, pravé tlačítko myši a Nastavit jako součet.
Teď první sloupec vyjadřuje počáteční hodnotu, oranžové sloupce vyjadřují úbytek a modré sloupce přírůstek a poslední konečná hodnota je součtem počáteční hodnoty a všech ostatních hodnot.
Tento vodopádový graf, který si můžeme vybrat v nabídce grafů má několik nevýhod. První nevýhodou je, že nemůžeme kontrolovat barvu záporných a kladných sloupců. Pokud chceme změnit barvy sloupců, tak musíme každý sloupec změnit zvlášť. Druhá nevýhoda je, že se popisky zobrazí vždy u hrany sloupce, tedy u kladných sloupců nahoře a u záporných sloupců dole.
Jak vodopádový graf netvořit
Pokud chcete trochu více flexibility a nebo používáte Excel před verzí 2016, tak si teď ukážeme, jak vodopádový graf vytvořit. Než si ale ukážeme nejlepší způsob, jak vodopádový graf vytvořit, tak s ukážeme, jak ho netvořit. Velmi často se totiž tvorba vodopádového grafu obchází tak, že se vytvoří ze skládaného sloupcového grafu. To máme i v ukázce. Základem tohoto grafu jsou pomocné sloupce, kde odlišíme kladné a záporné hodnoty a následně vytvoříme základnu, kterou nakonec ale v grafu skryjeme, takže dostaneme iluzi vodopádového grafu.
Nevýhodou tohoto přístupu ale je, že pokud změníme nějakou zápornou hodnotu na vyšší hodnotu, tak se celý graf rozhodí, jelikož všechny hodnoty, a to i ty záporné, jsou v tomto grafu ukázané jako kladné, jelikož pracujeme se skládaným sloupcovým grafem. To je velká nevýhoda toho, když chcete vodopádový graf vytvořit ze sloupcového grafu.
Vodopádový graf před verzemi Excel 2016
Teď když jsme si ukázali, jak vodopádový graf netvořit, tak se teď vrhneme konečně na tvorbu vodopádového grafu.
Začneme se stejnou zdrojovou tabulkou. Nejprve se podíváme, jak vodopádový graf vypadá a vysvětlíme si, jaké typy grafu zde máme použité. I přesto, že se zdá, že i v tomto grafu pracujeme se sloupcovým grafem, tak se jedná jen o iluzi. Ve skutečnosti se jedná o několik různých typů grafů, které dohromady tvoří kombinovaný graf. Sloupce počátek a konec jsou opravdu sloupcové grafy. Sloupce úbytku a přírůstku jsou ale ve skutečnosti spojnicové grafy zamaskované jako sloupcové.
Výhodou tohoto grafu je, že pokud změníme nějakou hodnotu na mínusovou, tak se vodopádový graf posune do mínusu a přizpůsobí se rovněž popisky, jelikož popisky se zobrazí vždy nad sloupcem.
Začneme tím, že vytvoříme pomocné sloupce pro graf. Nejprve vytvoříme sloupec pro počáteční a koncovou hodnotu. Pro počáteční hodnotu se odkážeme na počáteční hodnotu z původního sloupce. Jako koncová hodnota bude součet hodnot ve sloupci.
A teď musíme vytvořit pomocné sloupce pro přírůstky a úbytky. Jak jsme si řekli před chvílí, ve skutečnosti se jedná o spojnicové grafy, které jsou naformátované jako sloupcové grafy. Takže musíme stanovit hodnotu, kde končí předchozí sloupec a hodnotu, kde začíná následný sloupec a to nakonec vytvoří iluzi těchto sloupců. Takže vytvoříme dva pomocné sloupce, které nazveme Před a Po.
Ve sloupci před bude vzorec SUMA, kde vytvoříme dynamické rozpětí z hodnot v původním zdrojovém sloupci.
A ve sloupci Po vytvoříme dynamické rozpětí z prvních dvou hodnot ve funkci SUMA. Teď první hodnota před začíná v hodnotě, kde končí počáteční hodnota a hodnota po ukazuje kam hodnota klesne po úbytku. Na dalším řádku začíná hodnota v bodě, kde předchozí hodnota končila a končí tam, kam hodnota vzrostla po přírůstku atd.
Označíme sloupce, které potřebujeme dostat do grafu, což je první sloupec s popisky a tři pomocné sloupce. Na kartě Vložení vybereme grafy a vložíme třeba sloupcový graf.
Musíme změnit typ grafu. Klikneme na graf pravým tlačítkem myši a vybereme Změnit typ grafu. V nabídce grafů vybereme dole Kombinovaný graf. Sloupce Počátek a Konec necháme jako sloupcový graf a sloupce Před a Po změníme na spojnicový graf.
Z grafu smažeme pomocné vodicí čáry a naformátujeme spojnicový graf tak, aby vypadal jako sloupcový. Označíme jednu ze spojnicových linií, je jedno kterou, a rozklikneme ikonu plus u grafu. V nabídce vybereme Sloupce vzrůstu a poklesu. Tím se do grafu vloží sloupce.
Klikneme na jednu z linií a ve formátu datové řady vybereme Možnosti řady, kde nastavíme požadovanou šířku mezery. Tím se rozšíří sloupce vzrůstu a poklesu. To samé nakonec uděláme i pro sloupce počátek a konec a nastavíme stejnou šířku mezery.
Změníme barvu u sloupců. Stačí označit sloupce počátek a konec a kliknout na ně pravým tlačítkem myši a vybereme barevnou výplň. To samé uděláme pro další dvě datové sady.
V dalším kroku smažeme linie, které nepotřebujeme, jelikož máme v grafu sloupce vzrůstu a poklesu. Klikneme na linii a v nastavení formátu vybereme v sekci Čára a vybereme bez čáry.
Smažeme legendu z grafu, kterou v grafu nepotřebujeme a můžeme smazat i osu y.
Teď musíme přidat popisky dat do grafu. Abychom ale měli popisky vždy nahoře nad sloupcem, tak musíme do zdrojové tabulky přidat další pomocný sloupec, který bude určovat umístění popisků. To uděláme pomocí funkce KDYŽ, kde podmínkou je, že pokud je zdrojová hodnota nižší než nula, tak chceme vrátit hodnotu Před, jinak chceme vrátit hodnotu Po.
V dalším kroku přidáme tento sloupec s popisky do grafu. Klikneme do grafu pravým tlačítkem myši a vybereme Vybrat data. Datovou sadu přidáme do grafu a když to uděláme, tak se graf zdeformuje. A je to tím, že máme vybraný patný graf u této poslední sady.
Klikneme do grafu a vybereme Změnit typ grafu. Datovou sadu popisky změníme na bodový graf.
K této datové sadě přidáme datové popisky. Popisky se vloží k bodům, ale vloží se doprava. Označíme popisky a vybereme formát popisků dat. Popisky umístíme nahoru. Teď ale máme špatné popisky u bodů. Jelikož hodnoty nevyjadřují skutečné hodnoty, ale umístění popisků. Takže vybereme Hodnota z buněk, kde vybereme zdrojové hodnoty a odebereme Hodnoty y.
Teď skryjeme body z grafu. Označíme body a v možnostech značky vybereme žádná značka.
Následně přidáme popisky k počátečnímu a koncovému sloupci.
Poslední, co zbývá je přidat spojnice mezi sloupci. To vyřešíme pomocí chybových úseček. A chybové úsečky vytvoříme pro kumulativní součet hodnot. Takže přidáme do zdrojové tabulky ještě pomocný sloupec Kumulativní. Zde bude funkce SUMA s dynamickým rozpětím.
Tento poslední pomocný sloupec vložíme do grafu.
K této nové datové sadě Kumulativní musíme vložit chybové úsečky. Abychom ale správnou datovou sadu označili, tak ve formátu vybereme v horní nabídce správnou datovou sadu.
Když máme datovou sadu označenou, tak přes ikonu plus u grafu vybereme chybové úsečky.
Svislé chybové úsečky nepotřebujeme, tak je označíme a smažeme. Označíme vodorovné úsečky a ve formátu vybereme kladný směr, bez zakončení a pevná hodnota 1.
Poslední, co můžeme udělat je, že orámujeme sloupce tenkou linií, aby sloupce splynuly s chybovými úsečkami.
Vodopádový graf, který si můžete vytvořit ve všech verzích Excelu je vytvořený.
Výhodou tohoto grafu je, že pokud se změní hodnota do mínusu, tak se vodopádový graf správně přizpůsobí do mínusových hodnot.
