Ako vytvoriť vodopádový graf

Čoraz častejšie sa stretávam v reportingu rôznych firiem a počúvam požiadavky stážistov na vysvetlenie, ako sa vytvára kaskádový diagram odchýlok – je to tiež „vodopád“, je to aj „vodopád“, je to aj „most“ “, je to tiež „most“ atď. Vyzerá to asi takto:

Z diaľky to naozaj vyzerá ako kaskáda vodopádov na horskej rieke alebo visutý most – kto čo vidí 🙂

Zvláštnosťou takéhoto diagramu je, že:

• Jasne vidíme počiatočnú a konečnú hodnotu parametra (prvý a posledný stĺpec).
• Pozitívne zmeny (rast) sú zobrazené jednou farbou (zvyčajne zelená) a negatívne (klesať) na iné (zvyčajne červená).
  
• Niekedy môže graf obsahovať aj stĺpce medzisúčtu (šedápristál na stĺpoch osi x).
  

V každodennom živote sa takéto diagramy zvyčajne používajú v nasledujúcich prípadoch:

• Vizuálny zobrazenie dynamiky akýkoľvek proces v čase: cash flow (cash-flow), investície (investujeme do projektu a máme z neho zisk).
• Vizualizácia plán implementácie (stĺpec úplne vľavo v diagrame je skutočnosť, stĺpec úplne vpravo je plán, celý diagram odráža náš proces smerovania k požadovanému výsledku)
• Keď potrebujete vizuál ukázať faktoryktoré ovplyvňujú náš parameter (faktorová analýza zisku – z čoho pozostáva).
  

Existuje niekoľko spôsobov, ako vytvoriť takýto graf – všetko závisí od vašej verzie programu Microsoft Excel.

Metóda 1: Najjednoduchší: Vstavaný typ v Exceli 2016 a novšom

Ak máte Excel 2016, 2019 alebo novší (alebo Office 365), zostavenie takéhoto grafu nie je zložité – tieto verzie Excelu už majú tento typ predvolene zabudovaný. Bude potrebné iba vybrať tabuľku s údajmi a vybrať na karte Vložiť (Vložiť) povel kaskádové (vodopád):

V dôsledku toho dostaneme takmer hotový diagram:

Okamžite môžete nastaviť požadované farby výplne pre kladné a záporné stĺpce. Najjednoduchší spôsob, ako to urobiť, je vybrať príslušné riadky Zvýšenie и zníženie priamo v legende a kliknutím na ne pravým tlačidlom myši vyberte príkaz Vyplniť (vyplniť):

Ak potrebujete do grafu pridať stĺpce s medzisúčtami alebo konečným súčtom stĺpcov, potom je najvhodnejšie to urobiť pomocou funkcií MEDZISÚČET (MEDZISÚČET) or UNIT (AGREGÁCIA). Vypočítajú sumu nahromadenú od začiatku tabuľky, pričom z nej vylúčia podobné súčty uvedené vyššie:

V tomto prípade je prvý argument (9) kód operácie matematického súčtu a druhý (0) spôsobí, že funkcia vo výsledkoch ignoruje už vypočítané súčty za predchádzajúce štvrťroky.

Po pridaní riadkov so súčtom zostáva vybrať stĺpce súčtu, ktoré sa objavili v diagrame (urobte dva po sebe idúce jednotlivé kliknutia na stĺpec) a kliknutím pravým tlačidlom myši vyberte príkaz Nastaviť ako celkovú (Nastaviť ako súčet):

Vybraný stĺpec pristane na osi x a automaticky zmení farbu na sivú.

To je v skutočnosti všetko – diagram vodopádu je pripravený:

Metóda 2. Univerzálne: neviditeľné stĺpce

Ak máte Excel 2013 alebo staršie verzie (2010, 2007 atď.), potom vám vyššie popísaná metóda nebude fungovať. Budete musieť ísť okolo a vystrihnúť chýbajúci vodopádový graf z bežného skladaného histogramu (sčítaním stĺpcov na sebe).

Trik je v tom, že pomocou priehľadných podperných stĺpcov zvýšite naše červené a zelené dátové riadky na správnu výšku:

Na zostavenie takéhoto grafu musíme do zdrojových údajov pridať niekoľko ďalších pomocných stĺpcov so vzorcami:

• Najprv musíme rozdeliť náš pôvodný stĺpec oddelením kladných a záporných hodnôt do samostatných stĺpcov pomocou funkcie IF (AK).  
• Po druhé, budete musieť pridať stĺpec pred stĺpce cumlíky, kde prvá hodnota bude 0 a počnúc druhou bunkou vzorec vypočíta výšku týchto veľmi priehľadných nosných stĺpcov.
  

Potom zostáva vybrať celú tabuľku okrem pôvodného stĺpca flow a vytvorte pravidelný skladaný histogram Vložka — Histogram (Vložiť – stĺpcový graf):

Ak teraz vyberiete modré stĺpce a zmeníte ich na neviditeľné (kliknite na ne pravým tlačidlom myši – Formát riadka – Výplň – Bez výplne), potom dostaneme len to, čo potrebujeme. 

Výhodou tejto metódy je jednoduchosť. V mínusoch - potreba počítať pomocné stĺpce.

Spôsob 3. Ak ideme do mínusu, všetko je ťažšie

Bohužiaľ, predchádzajúca metóda funguje adekvátne len pre kladné hodnoty. Ak sa náš vodopád aspoň v nejakej oblasti dostane do negatívnej oblasti, potom sa zložitosť úlohy výrazne zvyšuje. V tomto prípade bude potrebné vypočítať každý riadok (figurína, zelená a červená) samostatne pre zápornú a kladnú časť pomocou vzorcov:

Aby ste veľa netrpeli a nevynašli koleso, v názve tohto článku si môžete stiahnuť hotovú šablónu pre takýto prípad.

Metóda 4. Exotika: pásy hore-dole

Táto metóda je založená na použití špeciálneho málo známeho prvku plochých diagramov (histogramov a grafov) – Pásma hore-dole (Stĺpce hore-dole). Tieto pásy spájajú body dvoch grafov v pároch, aby jasne ukázali, ktorý z týchto dvoch bodov je vyšší alebo nižší, čo sa aktívne používa pri vizualizácii plánu-skutočnosti:

Je ľahké zistiť, že ak odstránime čiary grafov a ponecháme na grafe iba pásy hore-dole, dostaneme rovnaký „vodopád“.

Pre takúto konštrukciu musíme do našej tabuľky pridať ďalšie dva stĺpce s jednoduchými vzorcami, ktoré vypočítajú polohu dvoch požadovaných neviditeľných grafov:

Ak chcete vytvoriť „vodopád“, musíte vybrať stĺpec s mesiacmi (pre podpisy pozdĺž osi X) a dva ďalšie stĺpce Plán 1 и Plán 2 a vytvorte bežný graf pre začiatočníkov Vložiť – Graf (Vložiť — riadok Сhart):

Teraz pridajme do našej tabuľky pásma nahor a nadol:

• V Exceli 2013 a novších musí byť toto vybraté na karte staviteľ povel Pridať prvok grafu — Pásma nárastu a poklesu (Návrh — Pridať prvok grafu — Pruhy hore-dole)
• V Exceli 2007-2010 – prejdite na kartu Rozloženie – pruhy pre postupné znižovanie (Rozloženie – pruhy nahor a nadol)

Graf potom bude vyzerať asi takto:

Zostáva vybrať grafy a urobiť ich priehľadnými tak, že na ne postupne kliknete pravým tlačidlom myši a vyberiete príkaz Formát radov údajov (Formátovať sériu). Podobne môžete zmeniť štandardné, skôr ošúchané farby čiernobielych pruhov na zelenú a červenú, aby ste nakoniec získali krajší obrázok:

V najnovších verziách programu Microsoft Excel je možné zmeniť šírku pruhov kliknutím na jeden z priehľadných grafov (nie pruhy!) pravým tlačidlom myši a výberom príkazu Formát dátovej série – bočná vôľa (Formátovať sériu – šírka medzery).

V starších verziách programu Excel ste na opravu tohto problému museli použiť príkaz jazyka Visual Basic:

1. Zvýraznite zostavený diagram
2. Stlačte klávesovú skratku ostatné+F11sa dostanete do editora jazyka Visual Basic
3. Stlačte klávesovú skratku ctrl+Gotvorte priamy príkazový vstup a panel ladenia Bezprostredná (zvyčajne sa nachádza v spodnej časti).

4. Skopírujte a prilepte tam nasledujúci príkaz: ActiveChart.ChartGroups(1).GapWidth = 30 a stlačte vstúpiť:

Ak chcete, môžete si, samozrejme, pohrať s hodnotou parametra. GapWidthna dosiahnutie požadovanej vôle:

• Ako vytvoriť odrážkový graf v Exceli na vizualizáciu KPI  
• Čo je nové v grafoch v Exceli 2013
• Ako vytvoriť interaktívny „živý“ graf v Exceli