Kako napraviti grafikon vodopada

Sve češće se susrećem u izvještavanju različitih kompanija i čujem zahtjeve polaznika da objasne kako se gradi kaskadni dijagram odstupanja – to je i „vodopad“, to je i „vodopad“, to je i „most“. “, to je i “most” itd. . To izgleda otprilike ovako:

Iz daljine zaista izgleda kao slap vodopada na planinskoj rijeci ili viseći most – ko šta vidi 🙂

Posebnost takvog dijagrama je da:

• Jasno vidimo početnu i konačnu vrijednost parametra (prvi i posljednji stupac).
• Pozitivne promjene (rast) se prikazuju u jednoj boji (obično zelen), a negativne (odbijaju) prema drugima (obično crven).
  
• Ponekad grafikon može sadržavati i kolone međuzbroja (sive bojesletio na stubove x-ose).
  

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

• vizuelan prikaz dinamike bilo koji proces u vremenu: novčani tok (cash-flow), investicije (ulažemo u projekat i dobijamo profit od toga).
• vizualizacija implementacija plana (krajnji lijevi stupac na dijagramu je činjenica, krajnji desni stupac je plan, cijeli dijagram odražava naš proces kretanja prema željenom rezultatu)
• Kada vam treba vizuelno pokazati faktorekoji utiču na naš parametar (faktorska analiza profita – od čega se sastoji).
  

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

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

Ako imate Excel 2016, 2019 ili noviji (ili Office 365), onda izrada takvog grafikona nije teška – ove verzije Excela već imaju ugrađen ovaj tip prema zadanim postavkama. Bit će potrebno samo odabrati tabelu sa podacima i odabrati na kartici Ubacite (umetnuti) naredba kaskadno (vodopad):

Kao rezultat, dobit ćemo gotovo gotov dijagram:

Možete odmah postaviti željene boje ispune za pozitivne i negativne kolone. Najlakši način da to učinite je da odaberete odgovarajuće redove Povećati и smanjenje direktno u legendi i desnim klikom na njih odaberite naredbu Ispunite (Popuni):

Ako trebate dodati stupce sa međuzbrojima ili konačni zbroj stupaca na grafikon, tada je najpogodnije to učiniti pomoću funkcija SUBTOTALS (SUBZUVI) or UNIT (AGREGAT). Oni će izračunati iznos akumuliran s početka tabele, isključujući iz nje slične zbrojeve koji se nalaze iznad:

U ovom slučaju, prvi argument (9) je kod operacije matematičkog zbrajanja, a drugi (0) uzrokuje da funkcija ignorira već izračunate ukupne vrijednosti za prethodna kvartala u rezultatima.

Nakon što dodate redove sa ukupnim iznosima, ostaje da odaberete kolone ukupnog broja koji su se pojavili na dijagramu (napravite dva uzastopna pojedinačna klika na kolonu) i desnim klikom miša odaberete naredbu Postavi kao ukupno (Postavi kao ukupno):

Odabrana kolona će sletjeti na x-osu i automatski promijeniti boju u sivu.

To je, zapravo, sve - dijagram vodopada je spreman:

Metoda 2. Univerzalno: nevidljivi stupci

Ako imate Excel 2013 ili starije verzije (2010, 2007, itd.), gore opisana metoda neće raditi za vas. Moraćete da obiđete i izrežete grafikon vodopada koji nedostaje iz redovnog naslaganog histograma (sabirajući trake jedna na drugu).

Trik je da koristite prozirne kolone za podupiranje da podignete naše crvene i zelene redove podataka na ispravnu visinu:

Da bismo napravili takav grafikon, moramo izvornim podacima 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, moraćete da dodate kolonu ispred kolona dude, gdje će prva vrijednost biti 0, a počevši od druge ćelije, formula će izračunati visinu tih vrlo transparentnih potpornih stupova.
  

Nakon toga, ostaje odabrati cijelu tablicu osim izvorne kolone Flow i kreirajte regularan naslagani histogram poprečno Inset — Histogram (Insert — Stupačni grafikon):

Ako sada odaberete plave kolone i učinite ih nevidljivima (desni klik na njih – Format reda – Ispuna – Bez popunjavanja), onda samo dobijamo ono što nam treba. 

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

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

Nažalost, prethodni metod radi adekvatno samo za pozitivne vrijednosti. Ako barem na nekom području naš vodopad zađe u negativno područje, onda se složenost zadatka značajno povećava. U ovom slučaju bit će potrebno izračunati svaki red (lažni, zeleni i crveni) posebno za negativne i pozitivne dijelove sa formulama:

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

Metoda 4. Egzotično: trake gore-dole

Ova metoda se zasniva na upotrebi posebnog malo poznatog elementa ravnih grafikona (histograma i grafikona) – Gore-dole trake (Tablice gore-dolje). Ove trake povezuju tačke dva grafikona u parovima da bi se jasno pokazalo koja je od dve tačke viša ili niža, što se aktivno koristi kada se vizualizuje plan-činjenica:

Lako je zaključiti da ako uklonimo linije grafikona i ostavimo samo trake gore-dolje na grafikonu, onda ćemo dobiti isti „vodopad“.

Za takvu konstrukciju, našoj tablici moramo dodati još dvije dodatne kolone s jednostavnim formulama koje će izračunati poziciju dva potrebna nevidljiva grafa:

Da biste kreirali "vodopad", morate odabrati kolonu s mjesecima (za potpise duž X ose) i dvije dodatne kolone Schedule 1 и Schedule 2 i napravite običan graf za početak Insert – Graf (Umetnuti — linijski grafikon):

Sada dodajmo gore-dolje bendove na naš grafikon:

• U programu Excel 2013 i novijim, ovo mora biti odabrano na kartici konstruktor naredba Dodajte element grafikona — Opseg povećanja-smanjenje (Dizajn — Dodaj element grafikona — trake gore-dolje)
• U programu Excel 2007-2010 – idite na karticu Izgled – trake unapred-dekrement (Izgled — trake gore-dolje)

Grafikon će tada izgledati otprilike ovako:

Ostaje odabrati grafikone i učiniti ih transparentnim tako što ćete kliknuti na njih redom desnom tipkom miša i odabrati naredbu Format serije podataka (Format serije). Slično, možete promijeniti standardne, prilično otrcane crno-bijele boje traka u zelenu i crvenu kako biste na kraju dobili ljepšu sliku:

U najnovijim verzijama Microsoft Excel-a, širina traka se može promijeniti klikom na jedan od prozirnih grafikona (ne na trake!) desnim gumbom miša i odabirom naredbe Format serije podataka – bočni razmak (Serija formata — širina razmaka).

U starijim verzijama Excela, morali ste koristiti naredbu Visual Basic da to popravite:

1. Označite izgrađeni dijagram
2. Pritisnite prečicu na tastaturi alt+F11da uđete u Visual Basic Editor
3. Pritisnite tastersku prečicu Ctrl+Gda otvorite panel za direktni unos komandi i debug odmah (obično se nalazi na dnu).

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

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

• Kako napraviti bullet grafikon u Excelu za vizualizaciju KPI-a  
• Šta je novo u grafikonima u programu Excel 2013
• Kako napraviti interaktivni "živi" grafikon u Excelu