Jak vytvořit vodopádový graf

Stále častěji se setkávám v reportingu různých společností a slyším požadavky stážistů na vysvětlení, jak se staví kaskádový diagram odchylek – je to také „vodopád“, je to také „vodopád“, je to také „most “, je to také „most“ atd. . Vypadá to nějak takto:

Z dálky to opravdu vypadá jako kaskáda vodopádů na horské řece nebo visutý most – kdo co vidí 🙂

Zvláštností takového diagramu je, že:

• Jasně vidíme počáteční a konečnou hodnotu parametru (první a poslední sloupec).
• Pozitivní změny (růst) jsou zobrazeny jednou barvou (obvykle zelená) a negativní (odmítnout) na ostatní (obvykle červený).
  
• Někdy může graf obsahovat také sloupce mezisoučtu (šedápřistál na sloupcích osy x).
  

V každodenním životě se takové diagramy obvykle používají v následujících případech:

• Vizuální zobrazení dynamiky jakýkoli proces v čase: cash flow (cash-flow), investice (investujeme do projektu a máme z něj zisk).
• Vizualizace implementaci plánu (sloupec zcela vlevo v diagramu je skutečnost, sloupec zcela vpravo je plán, celý diagram odráží náš proces směřování k požadovanému výsledku)
• Když potřebujete vizuální ukázat faktorykteré ovlivňují náš parametr (faktoriální analýza zisku – z čeho se skládá).
  

Existuje několik způsobů, jak vytvořit takový graf – vše závisí na vaší verzi aplikace Microsoft Excel.

Metoda 1: Nejjednodušší: Vestavěný typ v Excelu 2016 a novějších

Pokud máte Excel 2016, 2019 nebo novější (nebo Office 365), pak vytvoření takového grafu není obtížné – tyto verze Excelu mají tento typ již ve výchozím nastavení zabudován. Bude nutné pouze vybrat tabulku s daty a vybrat na kartě Vložit (Vložit) Příkaz kaskádové (Vodopád):

V důsledku toho získáme téměř hotový diagram:

Můžete okamžitě nastavit požadované barvy výplně pro kladné a záporné sloupce. Nejjednodušší způsob, jak toho dosáhnout, je vybrat příslušné řádky Navýšit и snížení přímo v legendě a kliknutím na ně pravým tlačítkem vyberte příkaz Vyplnit (Vyplnit):

Pokud potřebujete do grafu přidat sloupce s mezisoučty nebo konečný sloupec-součet, pak je nejpohodlnější to udělat pomocí funkcí Mezisoučty (MEDZISOUČTY) or JEDNOTKA (AGREGÁT). Vypočítají částku nashromážděnou od začátku tabulky, přičemž z ní vyloučí podobné součty uvedené výše:

V tomto případě je první argument (9) kód operace matematického součtu a druhý (0) způsobí, že funkce bude ve výsledcích ignorovat již vypočítané součty za předchozí čtvrtletí.

Po přidání řádků se součty zbývá vybrat celkové sloupce, které se objevily v diagramu (proveďte dvě po sobě jdoucí kliknutí na sloupec) a kliknutím pravým tlačítkem myši vyberte příkaz Nastavit jako celkem (Nastavit jako celkem):

Vybraný sloupec přistane na ose x a automaticky změní barvu na šedou.

To je ve skutečnosti vše – vodopádový diagram je připraven:

Metoda 2. Univerzální: neviditelné sloupce

Pokud máte Excel 2013 nebo starší verze (2010, 2007 atd.), pak vám výše popsaná metoda nebude fungovat. Chybějící graf vodopádu budete muset obejít a vystřihnout z pravidelného skládaného histogramu (sečtením sloupců na sobě).

Trik je v tom, že pomocí průhledných podpěrných sloupců zvýšíte naše červené a zelené datové řádky do správné výšky:

Abychom takový graf sestavili, musíme do zdrojových dat přidat několik dalších pomocných sloupců se vzorci:

• Nejprve musíme rozdělit náš původní sloupec oddělením kladných a záporných hodnot do samostatných sloupců pomocí funkce IF (LI).  
• Za druhé, budete muset přidat sloupec před sloupce dudlíky, kde první hodnota bude 0, a počínaje druhou buňkou vzorec vypočítá výšku těchto velmi průhledných nosných sloupců.
  

Poté zbývá vybrat celou tabulku kromě původního sloupce Flow a vytvořte pravidelný skládaný histogram napříč Vložka — Histogram (Vložit — sloupcový graf):

Pokud nyní vyberete modré sloupce a zneviditelníte je (klikněte na ně pravým tlačítkem – Formát řádku – Výplň – Bez výplně), pak dostaneme jen to, co potřebujeme. 

Výhodou této metody je jednoduchost. V mínusech – nutnost počítat pomocné sloupce.

Metoda 3. Pokud jdeme do mínusu, je vše obtížnější

Bohužel předchozí metoda funguje adekvátně pouze pro kladné hodnoty. Pokud se náš vodopád alespoň v nějaké oblasti dostane do negativní oblasti, pak se složitost úkolu výrazně zvyšuje. V tomto případě bude nutné vypočítat každý řádek (fiktivní, zelený a červený) samostatně pro zápornou a kladnou část pomocí vzorců:

Abyste moc netrpěli a nevynalezli znovu kolo, v nadpisu tohoto článku je ke stažení hotová šablona pro takový případ.

Metoda 4. Exotika: pásma nahoru-dolů

Tato metoda je založena na použití speciálního málo známého prvku plochých diagramů (histogramů a grafů) – Pásma nahoru-dolů (Sloupce nahoru-dolů). Tyto pásy spojují body dvou grafů ve dvojicích, aby bylo jasné, který z těchto dvou bodů je vyšší nebo nižší, což se aktivně používá při vizualizaci plánu-skutečnosti:

Je snadné zjistit, že pokud odstraníme čáry grafů a ponecháme na grafu pouze pásy nahoru a dolů, dostaneme stejný „vodopád“.

Pro takovou konstrukci musíme do naší tabulky přidat další dva další sloupce s jednoduchými vzorci, které vypočítají polohu dvou požadovaných neviditelných grafů:

Chcete-li vytvořit „vodopád“, musíte vybrat sloupec s měsíci (pro podpisy podél osy X) a dva další sloupce Plán 1 и Plán 2 a vytvořit pravidelný graf pro začátečníky pomocí Vložit – Graf (Vložit — řádek Сhart):

Nyní do našeho žebříčku přidáme pásma nahoru-dolů:

• V Excelu 2013 a novějších musí být toto vybráno na kartě Stavitel Příkaz Přidat prvek grafu — Pásma nárůstu a poklesu (Návrh — Přidat prvek grafu — Pruhy nahoru a dolů)
• V Excelu 2007-2010 – přejděte na kartu Rozložení – pruhy pro postupné snížení (Rozvržení — pruhy nahoru a dolů)

Graf pak bude vypadat nějak takto:

Zbývá vybrat grafy a zprůhlednit je tak, že na ně postupně kliknete pravým tlačítkem myši a vyberete příkaz Formát datové řady (Formát řady). Podobně můžete změnit standardní, poněkud ošuměle vypadající černo-bílé barvy pruhů na zelenou a červenou, abyste nakonec získali hezčí obrázek:

V nejnovějších verzích aplikace Microsoft Excel lze šířku pruhů změnit kliknutím na jeden z průhledných grafů (nikoli pruhy!) pravým tlačítkem myši a výběrem příkazu Formát datové řady – boční vůle (Formátovat řadu – šířka mezery).

Ve starších verzích Excelu jste k opravě museli použít příkaz jazyka Visual Basic:

1. Zvýrazněte vytvořený diagram
2. Stiskněte klávesovou zkratku Další+F11se dostanete do editoru jazyka Visual Basic
3. Stiskněte klávesovou zkratku Ctrl+Gotevřete přímý příkazový vstup a panel ladění bezprostřední (obvykle se nachází ve spodní části).

4. Zkopírujte a vložte tam následující příkaz: ActiveChart.ChartGroups(1).GapWidth = 30 a stiskněte vstoupit:

Pokud chcete, můžete si samozřejmě s hodnotou parametru pohrát. Šířka mezeryk dosažení požadované vůle:

• Jak vytvořit odrážkový graf v Excelu pro vizualizaci KPI  
• Co je nového v grafech v Excelu 2013
• Jak vytvořit interaktivní „živý“ graf v Excelu