Kako zgraditi diagram slapa

Vse pogosteje se srečam v poročilih različnih podjetij in slišim prošnje pripravnikov, naj pojasnijo, kako je zgrajen kaskadni diagram odstopanj – je tudi »slap«, je tudi »slap«, je tudi »most«. «, je tudi »most« itd. Videti je nekako takole:

Od daleč res izgleda kot kaskada slapov na gorski reki ali viseči most – kdor kaj vidi 🙂

Posebnost takšnega diagrama je, da:

• Jasno vidimo začetno in končno vrednost parametra (prvi in ​​zadnji stolpec).
• Pozitivne spremembe (rast) so prikazane v eni barvi (običajno Zelene), negativne (upad) drugim (običajno Modra in rdeča.).
  
• Včasih lahko grafikon vsebuje tudi stolpce vmesnih seštevkov (sivapristal na stolpcih osi x).
  

V vsakdanjem življenju se takšni diagrami običajno uporabljajo v naslednjih primerih:

• Vizualni prikaz dinamike kateri koli proces v času: denarni tok (cash-flow), investicije (vlagamo v projekt in od tega dobimo).
• Vizualizacija izvajanje načrta (skrajno levi stolpec v diagramu je dejstvo, skrajno desni stolpec je načrt, celoten diagram odraža naš proces premikanja k želenemu rezultatu)
• Ko potrebujete vizualno kažejo dejavnikiki vplivajo na naš parameter (faktorska analiza dobička – iz česa je sestavljen).
  

Obstaja več načinov za izdelavo takšnega grafikona - vse je odvisno od vaše različice programa Microsoft Excel.

1. način: Najlažji: Vgrajeni tip v Excelu 2016 in novejšem

Če imate Excel 2016, 2019 ali novejšo različico (ali Office 365), potem izdelava takšnega grafikona ni težavna – te različice Excela imajo ta tip že privzeto vgrajen. Treba bo samo izbrati tabelo s podatki in izbrati na zavihku Vstavi (Vstavi) Ukaz kaskadno (Slap):

Kot rezultat bomo dobili skoraj pripravljen diagram:

Takoj lahko nastavite želene barve polnila za pozitivne in negativne stolpce. Najlažji način za to je, da izberete ustrezne vrstice Povečajte и zmanjša neposredno v legendi in z desnim klikom na njih izberite ukaz Izpolnite (izpolni):

Če morate v grafikon dodati stolpce z vmesnimi seštevki ali končno vsoto stolpcev, je to najprimerneje storiti s funkcijami Vmesni VSEŠTVI (Vmesni seštevek) or ENOTA (SKUPNO). Izračunali bodo znesek, zbran od začetka tabele, pri čemer bodo iz nje izključili podobne vsote, ki se nahajajo zgoraj:

V tem primeru je prvi argument (9) koda operacije matematičnega seštevanja, drugi (0) pa povzroči, da funkcija v rezultatih prezre že izračunane vsote za prejšnja četrtletja.

Po dodajanju vrstic s seštevki ostane še, da izberete skupne stolpce, ki so se pojavili na diagramu (naredite dva zaporedna enojna klika na stolpec) in z desnim klikom na miško izberite ukaz Nastavi kot skupno (Nastavi kot skupno):

Izbrani stolpec bo pristal na osi x in samodejno spremenil barvo v sivo.

To je pravzaprav vse - diagram slapa je pripravljen:

Metoda 2. Univerzalno: nevidni stolpci

Če imate Excel 2013 ali starejše različice (2010, 2007 itd.), potem zgoraj opisana metoda za vas ne bo delovala. Morali boste iti naokoli in izrezati manjkajoči diagram slapa iz običajnega zloženega histograma (seštevanje palic eno na drugo).

Trik je v tem, da uporabimo prosojne prop stolpce za dvig naših rdečih in zelenih podatkovnih vrstic na pravilno višino:

Za izdelavo takšnega grafikona moramo izvornim podatkom dodati še nekaj pomožnih stolpcev s formulami:

• Najprej moramo razdeliti naš prvotni stolpec tako, da ločimo pozitivne in negativne vrednosti v ločene stolpce s funkcijo IF (ČE).  
• Drugič, dodati boste morali stolpec pred stolpce dude, kjer bo prva vrednost 0, od druge celice pa bo formula izračunala višino tistih zelo preglednih podpornih stolpcev.
  

Po tem je treba izbrati celotno tabelo, razen prvotnega stolpca Pretok in ustvarite običajni zloženi histogram čez Vložek — Histogram (Vstavi — stolpčni grafikon):

Če zdaj izberete modre stolpce in jih naredite nevidne (desni klik nanje – Oblika vrstice – Polnilo – Brez polnila), potem dobimo le tisto, kar potrebujemo. 

Prednost te metode je preprostost. V minusih - potreba po štetju pomožnih stolpcev.

Metoda 3. Če gremo v minus, je vse težje

Na žalost prejšnja metoda deluje ustrezno le za pozitivne vrednosti. Če vsaj na nekem območju naš slap preide v negativno območje, se kompleksnost naloge znatno poveča. V tem primeru bo treba vsako vrstico (navidezno, zeleno in rdečo) posebej izračunati za negativne in pozitivne dele s formulami:

Da ne bi veliko trpeli in ne bi znova izumljali kolesa, lahko v naslovu tega članka prenesete že pripravljeno predlogo za tak primer.

Metoda 4. Eksotika: pasovi gor-dol

Ta metoda temelji na uporabi posebnega malo znanega elementa ravnih grafikonov (histogramov in grafov) – Trakovi gor-dol (Palice gor-dol). Ti pasovi povezujejo točke dveh grafov v parih, da jasno pokažejo, katera od obeh točk je višja ali nižja, kar se aktivno uporablja pri vizualizaciji dejstva načrta:

Preprosto je ugotoviti, da če odstranimo črte grafikonov in na grafikonu pustimo samo pasove navzgor in navzdol, potem bomo dobili enak "slap".

Za takšno konstrukcijo moramo v našo tabelo dodati še dva dodatna stolpca s preprostimi formulami, ki bodo izračunale položaj dveh zahtevanih nevidnih grafov:

Če želite ustvariti "slap", morate izbrati stolpec z meseci (za podpise vzdolž osi X) in dva dodatna stolpca Razpored 1 и Razpored 2 in zgradite običajni graf za začetnike Vstavi – Graf (Vstavi — črtni grafikon):

Zdaj pa našemu grafikonu dodamo pasove gor-dol:

• V Excelu 2013 in novejšem morate to izbrati na zavihku Konstruktor Ukaz Dodaj element grafikona — Pasovi povečanja-zmanjšanja (Oblikovanje — Dodajte element grafikona — Vrstice gor-dol)
• V Excelu 2007-2010 – pojdite na zavihek Postavitev – vrstice za napredovanje in zmanjševanje (Postavitev — vrstice gor-dol)

Grafikon bo potem videti nekako takole:

Ostaja še, da izberete grafe in jih naredite pregledne tako, da jih izmenično kliknete z desnim gumbom miške in izberete ukaz Oblika serije podatkov (Oblika serije). Podobno lahko spremenite standardne črno-bele črtne barve črno-belega videza, ki so precej zanič, v zelene in rdeče, da na koncu dobite lepšo sliko:

V najnovejših različicah programa Microsoft Excel lahko širino stolpcev spreminjamo tako, da z desno tipko miške kliknemo na enega od preglednih grafov (ne stolpcev!) in izberemo ukaz Format podatkovne serije – stranski razmik (Serija formatov — širina vrzeli).

V starejših različicah Excela ste morali to popraviti z ukazom Visual Basic:

1. Označite zgrajeni diagram
2. Pritisnite bližnjico na tipkovnici druga+F11da pridete v urejevalnik Visual Basic
3. Pritisnite bližnjico na tipkovnici Ctrl+Gda odprete neposredni vnos ukazov in ploščo za odpravljanje napak Takojšen (običajno se nahaja na dnu).

4. Tja kopirajte in prilepite naslednji ukaz: ActiveChart.ChartGroups(1).GapWidth = 30 in pritisnite Vnesite:

Seveda se lahko poigrate z vrednostjo parametra, če želite. GapWidthda dosežete želeno razdaljo:

• Kako zgraditi grafikon z oznakami v Excelu za vizualizacijo KPI  
• Kaj je novega v grafikonih v Excelu 2013
• Kako ustvariti interaktivni grafikon "v živo" v Excelu