Kako izgraditi dijagram vodopada

Sve češće se susrećem u izvješćima različitih tvrtki i čujem zahtjeve polaznika da objasne kako se gradi kaskadni dijagram odstupanja – to je također „vodopad“, to je također „vodopad“, to je također „most“ ”, također je i “most” itd. . Izgleda otprilike ovako:

Iz daljine stvarno izgleda kao kaskada slapova na planinskoj rijeci ili viseći most – tko što vidi 🙂

Osobitost takvog dijagrama je da:

• Jasno vidimo početnu i konačnu vrijednost parametra (prvi i zadnji stupac).
• Pozitivne promjene (rast) prikazane su jednom bojom (obično zelena), a negativne (opadanje) drugima (obično crvena).
  
• Ponekad grafikon može sadržavati i stupce međuzbroja (sivasletio na stupce x-osi).
  

U svakodnevnom životu takvi se dijagrami obično koriste u sljedećim slučajevima:

• Visual prikaz dinamike bilo koji proces u vremenu: novčani tijek (cash-flow), investicije (ulažemo u projekt i iz toga dobivamo dobit).
• Vizualizacija provedba plana (krajnji lijevi stupac u dijagramu je činjenica, krajnji desni stupac je plan, cijeli dijagram odražava naš proces kretanja prema željenom rezultatu)
• Kada trebate vizualno pokazati faktorekoji utječu na naš parametar (faktorska analiza dobiti – od čega se sastoji).
  

Postoji nekoliko načina za izradu takvog grafikona - sve ovisi o vašoj verziji programa Microsoft Excel.

Metoda 1: Najlakša: Ugrađeni tip u programu Excel 2016 i novijim

Ako imate Excel 2016, 2019 ili noviji (ili Office 365), izgradnja takvog grafikona nije teška – ove verzije Excela već imaju ovu vrstu ugrađenu prema zadanim postavkama. Bit će potrebno samo odabrati tablicu s podacima i odabrati na kartici umetak (Umetnuti) naredba kaskadno (Slap):

Kao rezultat, dobit ćemo gotovo gotov dijagram:

Možete odmah postaviti željene boje ispune za pozitivne i negativne stupce. Najlakši način da to učinite je da odaberete odgovarajuće retke Povećati и smanjiti izravno u legendi i desnim klikom na njih odaberite naredbu Popuniti (ispuniti):

Ako grafikonu trebate dodati stupce s podzbrojevima ili konačni zbroj stupaca, najprikladnije je to učiniti pomoću funkcija MEDUZBURNICI (SUBTOTALS) or JEDINICA (SKUPNO). Oni će izračunati akumulirani iznos od početka tablice, dok će iz nje isključiti slične ukupne iznose koji se nalaze iznad:

U ovom slučaju, prvi argument (9) je šifra matematičke operacije zbrajanja, a drugi (0) uzrokuje da funkcija zanemari već izračunate ukupne iznose za prethodna tromjesečja u rezultatima.

Nakon dodavanja redaka sa zbrojevima, preostaje odabrati stupce zbrojeva koji su se pojavili na dijagramu (napraviti dva uzastopna jednokratna klika na stupac) i desnim klikom miša odabrati naredbu Postavi kao ukupno (Postavi kao ukupno):

Odabrani stupac sletjet će na x-os i automatski promijeniti boju u sivu.

To je zapravo sve - dijagram vodopada je spreman:

Metoda 2. Univerzalni: nevidljivi stupci

Ako imate Excel 2013 ili starije verzije (2010, 2007 itd.), tada vam gore opisana metoda neće raditi. Morat ćete ići okolo i izrezati vodopadni grafikon koji nedostaje iz uobičajenog naslaganog histograma (zbrajajući stupce jednu na drugu).

Trik je ovdje u korištenju prozirnih stupaca za podizanje crvenih i zelenih redova podataka na ispravnu visinu:

Da bismo izgradili takav grafikon, izvornim podacima moramo dodati još nekoliko pomoćnih stupaca s formulama:

• Prvo, moramo podijeliti naš izvorni stupac odvajanjem pozitivnih i negativnih vrijednosti u zasebne stupce pomoću funkcije IF (AKO).  
• Drugo, morat ćete dodati stupac ispred stupaca dude, gdje će prva vrijednost biti 0, a počevši od druge ćelije, formula će izračunati visinu tih vrlo prozirnih potpornih stupova.
  

Nakon toga ostaje odabrati cijelu tablicu osim izvornog stupca Protok i stvoriti pravilan naslagani histogram poprijeko Umetak — Histogram (Umetni — grafikon stupaca):

Ako sada odaberete plave stupce i učinite ih nevidljivima (desnom tipkom miša kliknite na njih – Format retka – Ispuna – Bez ispune), tada dobivamo samo ono što nam treba. 

Prednost ove metode je jednostavnost. U minusima - potreba za brojanjem pomoćnih stupaca.

Metoda 3. Ako idemo u minus, sve je teže

Nažalost, prethodna metoda radi adekvatno samo za pozitivne vrijednosti. Ako barem u nekom području naš vodopad prijeđe u negativno područje, tada se složenost zadatka značajno povećava. U tom slučaju bit će potrebno izračunati svaki red (dummy, zeleni i crveni) zasebno za negativne i pozitivne dijelove s formulama:

Kako ne biste puno patili i ne biste ponovno izumili kotač, gotov predložak za takav slučaj možete preuzeti u naslovu ovog članka.

Metoda 4. Egzotična: trake gore-dolje

Ova se metoda temelji na korištenju posebnog malo poznatog elementa ravnih grafikona (histograma i grafikona) – Trake gore-dolje (Trake gore-dolje). Ove vrpce povezuju točke dvaju grafikona u parovima kako bi se jasno pokazalo koja je od dvije točke viša ili niža, što se aktivno koristi pri vizualizaciji plana-činjenice:

Lako je shvatiti da ako uklonimo linije grafikona i ostavimo samo trake gore-dolje na grafikonu, tada ćemo dobiti isti "vodopad".

Za takvu konstrukciju, trebamo dodati još dva dodatna stupca u našu tablicu s jednostavnim formulama koje će izračunati položaj dva potrebna nevidljiva grafikona:

Za izradu "vodopada" trebate odabrati stupac s mjesecima (za potpise duž X osi) i dva dodatna stupca Raspored 1 и Raspored 2 i izgradite regularni grafikon za početak Umetanje – Grafikon (Umetni — linijski grafikon):

Dodajmo sada trake gore-dolje na naš grafikon:

• U programu Excel 2013 i novijim, ovo mora biti odabrano na kartici Konstruktor naredba Dodaj element grafikona — Pojasi povećanja-smanjenja (Dizajn — Dodajte element grafikona — Gore-dolje trake)
• U programu Excel 2007-2010 – idite na karticu Raspored – trake za napredovanje i smanjenje (Raspored — trake gore-dolje)

Grafikon će tada izgledati otprilike ovako:

Preostaje označiti grafikone i učiniti ih transparentnima redom klikom na njih desnom tipkom miša i odabirom naredbe Format serije podataka (Format serije). Slično tome, možete promijeniti standardne, prilično otrcane crno-bijele boje pruga u zelenu i crvenu kako biste na kraju dobili ljepšu sliku:

U najnovijim verzijama programa Microsoft Excel širinu stupaca moguće je promijeniti tako da desnom tipkom miša kliknete na jedan od prozirnih grafikona (ne stupce!) i odaberete naredbu Format serije podataka – bočni razmak (Serija formata — širina razmaka).

U starijim verzijama Excela morali ste koristiti naredbu Visual Basic da biste to popravili:

1. Označite izgrađeni dijagram
2. Pritisnite tipkovnički prečac drugo+F11da biste ušli u Visual Basic Editor
3. Pritisnite tipkovni prečac Ctrl+Gza otvaranje ploče za izravni unos naredbi i otklanjanje pogrešaka Neposredan (obično se nalazi na dnu).

4. Tamo kopirajte i zalijepite sljedeću naredbu: ActiveChart.ChartGroups(1).GapWidth = 30 i pritisnite ući:

Možete se, naravno, poigrati s vrijednošću parametra ako želite. GapWidthza postizanje željenog razmaka:

• Kako izgraditi grafički grafikon u Excelu za vizualizaciju KPI-ja  
• Što je novo u grafikonima u programu Excel 2013
• Kako stvoriti interaktivni "živi" grafikon u Excelu