Hvordan bygge et fossefalldiagram

Stadig oftere møtes jeg i rapporteringen fra ulike selskaper og hører forespørsler fra traineer om å forklare hvordan et kaskadediagram av avvik er bygget opp – det er også en “foss”, det er også en “foss”, det er også en “bro”. ”, det er også en “bro” osv. . Det ser omtrent slik ut:

På avstand ser det virkelig ut som en kaskade av fossefall på en fjellelv eller en hengebro – hvem ser hva 🙂

Det særegne ved et slikt diagram er at:

• Vi ser tydelig den første og siste verdien av parameteren (den første og siste kolonnen).
• Positive endringer (vekst) vises i én farge (vanligvis grønn), og negative (nedgang) til andre (vanligvis rød).
  
• Noen ganger kan diagrammet også inneholde subtotalkolonner (grålandet på x-aksesøylene).
  

I hverdagen brukes slike diagrammer vanligvis i følgende tilfeller:

• Visual dynamikkvisning enhver prosess i tid: kontantstrøm (kontantstrøm), investeringer (vi investerer i et prosjekt og får fortjeneste på det).
• Visualisering plangjennomføring (kolonnen lengst til venstre i diagrammet er et faktum, kolonnen lengst til høyre er en plan, hele diagrammet gjenspeiler prosessen vår med å bevege seg mot ønsket resultat)
• Når du trenger visuell vise faktorersom påvirker parameteren vår (faktoranalyse av profitt – hva den består av).
  

Det er flere måter å bygge et slikt diagram på - alt avhenger av din versjon av Microsoft Excel.

Metode 1: Enkleste: Innebygd type i Excel 2016 og nyere

Hvis du har Excel 2016, 2019 eller nyere (eller Office 365), er det ikke vanskelig å bygge et slikt diagram – disse versjonene av Excel har allerede denne typen innebygd som standard. Det vil kun være nødvendig å velge en tabell med data og velge på fanen innfelt (Sett inn) Kommando brus (Foss):

Som et resultat vil vi få et nesten ferdig diagram:

Du kan umiddelbart angi de ønskede fyllfargene for de positive og negative kolonnene. Den enkleste måten å gjøre dette på er å velge de riktige radene Øke и redusere direkte i forklaringen og ved å høyreklikke på dem, velg kommandoen Fyll (Fylle):

Hvis du trenger å legge til kolonner med delsummer eller en siste kolonnetotal til diagrammet, er det mest praktisk å gjøre dette ved hjelp av funksjoner SUBTOTALER (DELSUMMER) or ENHET (AGGREGAT). De vil beregne beløpet som er akkumulert fra begynnelsen av tabellen, mens de ekskluderer de tilsvarende summene ovenfor:

I dette tilfellet er det første argumentet (9) koden for den matematiske summeringsoperasjonen, og det andre (0) får funksjonen til å ignorere allerede beregnede totaler for tidligere kvartaler i resultatene.

Etter å ha lagt til rader med totaler, gjenstår det å velge de totale kolonnene som har dukket opp på diagrammet (gjør to påfølgende enkeltklikk på kolonnen), og ved å høyreklikke med musen velger du kommandoen Sett som total (Angi som totalt):

Den valgte kolonnen vil lande på x-aksen og automatisk endre farge til grå.

Det er faktisk alt - fossefallsdiagrammet er klart:

Metode 2. Universal: usynlige kolonner

Hvis du har Excel 2013 eller eldre versjoner (2010, 2007, etc.), vil metoden beskrevet ovenfor ikke fungere for deg. Du må gå rundt og klippe ut det manglende fossefalldiagrammet fra et vanlig stablet histogram (summere søylene oppå hverandre).

Trikset her er å bruke gjennomsiktige støttekolonner for å heve våre røde og grønne datarader til riktig høyde:

For å bygge et slikt diagram, må vi legge til noen flere hjelpekolonner med formler til kildedataene:

• Først må vi dele den opprinnelige kolonnen vår ved å skille de positive og negative verdiene i separate kolonner ved å bruke funksjonen IF (HVIS).  
• For det andre må du legge til en kolonne foran kolonnene smokker, hvor den første verdien vil være 0, og fra den andre cellen vil formelen beregne høyden på de veldig gjennomsiktige støttesøylene.
  

Etter det gjenstår det å velge hele tabellen bortsett fra den opprinnelige kolonnen Flow og lag et vanlig stablet histogram på tvers Innfelt — Histogram (Sett inn – kolonnediagram):

Hvis du nå velger de blå kolonnene og gjør dem usynlige (høyreklikk på dem – Radformat – Fyll – Ingen fyll), så får vi bare det vi trenger. 

Fordelen med denne metoden er enkelheten. I minusene - behovet for å telle hjelpekolonner.

Metode 3. Hvis vi går i minus, er alt vanskeligere

Dessverre fungerer den forrige metoden bare tilstrekkelig for positive verdier. Hvis i det minste i et område fossen vår går inn i et negativt område, øker kompleksiteten til oppgaven betydelig. I dette tilfellet vil det være nødvendig å beregne hver rad (dummy, grønn og rød) separat for de negative og positive delene med formler:

For ikke å lide mye og ikke finne opp hjulet på nytt, kan en ferdig mal for en slik sak lastes ned i tittelen på denne artikkelen.

Metode 4. Eksotisk: opp-ned-bånd

Denne metoden er basert på bruk av et spesielt lite kjent element av flate diagrammer (histogrammer og grafer) - Opp-ned-bånd (opp-ned stolper). Disse båndene kobler sammen punktene til to grafer i par for å tydelig vise hvilket av de to punktene som er høyere eller lavere, som brukes aktivt når du visualiserer planfakta:

Det er lett å finne ut at hvis vi fjerner linjene i diagrammene og lar bare opp-ned-båndene være på diagrammet, vil vi få den samme "fossen".

For en slik konstruksjon må vi legge til ytterligere to kolonner til tabellen vår med enkle formler som vil beregne posisjonen til de to nødvendige usynlige grafene:

For å lage en "foss", må du velge en kolonne med måneder (for signaturer langs X-aksen) og to ekstra kolonner Planlegg 1 и Planlegg 2 og bygg en vanlig graf til å begynne med å bruke Sett inn – graf (Sett inn — Line Сhart):

La oss nå legge til opp-ned-bånd til diagrammet vårt:

• I Excel 2013 og nyere må dette velges på fanen Constructor Kommando Legg til diagramelement — Bånd av økning-nedgang (Design – Legg til diagramelement – ​​søyler opp og ned)
• I Excel 2007-2010 – gå til fanen Layout – Søyler med forhåndsreduksjon (Layout – søyler opp og ned)

Diagrammet vil da se omtrent slik ut:

Det gjenstår å velge grafene og gjøre dem gjennomsiktige ved å klikke på dem etter tur med høyre museknapp og velge kommandoen Dataserieformat (Format serie). På samme måte kan du endre standard, ganske shabby utseende svart og hvit stripefarger til grønn og rød for å få et bedre bilde til slutt:

I de nyeste versjonene av Microsoft Excel kan bredden på stolpene endres ved å klikke på en av de gjennomsiktige grafene (ikke stolpene!) med høyre museknapp og velge kommandoen Dataserieformat – Sideklaring (Formatserie – Spaltebredde).

I eldre versjoner av Excel måtte du bruke Visual Basic-kommandoen for å fikse dette:

1. Marker det bygde diagrammet
2. Trykk på hurtigtasten andre+F11for å komme inn i Visual Basic Editor
3. Trykk tastaturgenvei Ctrl+Gfor å åpne direkte kommandoinndata og feilsøkingspanelet umiddelbar (vanligvis plassert nederst).

4. Kopier og lim inn følgende kommando der: ActiveChart.ChartGroups(1).GapWidth = 30 og trykk Enter:

Du kan selvfølgelig leke med parameterverdien hvis du ønsker det. GapWidthfor å oppnå ønsket klaring:

• Hvordan bygge et punktdiagram i Excel for å visualisere KPI  
• Hva er nytt i diagrammer i Excel 2013
• Hvordan lage et interaktivt "live" diagram i Excel