Hvordan man bygger et vandfaldsdiagram

Oftere og oftere mødes jeg i forskellige virksomheders rapportering og hører forespørgsler fra praktikanter om at forklare, hvordan et kaskadediagram over afvigelser er opbygget – det er også et "vandfald", det er også et "vandfald", det er også en "bro". ”, det er også en “bro” osv. . Det ser sådan ud:

På afstand ligner det virkelig en kaskade af vandfald på en bjergflod eller en hængebro – hvem ser hvad 🙂

Det særlige ved et sådant diagram er, at:

• Vi ser tydeligt den indledende og endelige værdi af parameteren (den første og sidste kolonne).
• Positive ændringer (vækst) vises i én farve (normalt grøn), og negative (fald) til andre (normalt rød).
  
• Nogle gange kan diagrammet også indeholde subtotalkolonner (grålandede på x-aksens søjler).
  

I hverdagen bruges sådanne diagrammer normalt i følgende tilfælde:

• Visuel dynamik display enhver proces i tid: cash flow (cash-flow), investeringer (vi investerer i et projekt og får overskud fra det).
• Visualisering planens gennemførelse (søjlen længst til venstre i diagrammet er et faktum, kolonnen længst til højre er en plan, hele diagrammet afspejler vores proces med at bevæge os mod det ønskede resultat)
• Når du har brug for visuelt vise faktorerder påvirker vores parameter (faktoranalyse af profit – hvad det består af).
  

Der er flere måder at bygge et sådant diagram på - det hele afhænger af din version af Microsoft Excel.

Metode 1: Letteste: Indbygget type i Excel 2016 og nyere

Hvis du har Excel 2016, 2019 eller nyere (eller Office 365), så er det ikke svært at bygge et sådant diagram – disse versioner af Excel har allerede denne type indbygget som standard. Det vil kun være nødvendigt at vælge en tabel med data og vælge på fanen indsatte (Indsæt) Kommando cascading (Vandfald):

Som et resultat får vi et næsten færdigt diagram:

Du kan straks indstille de ønskede fyldfarver for de positive og negative kolonner. Den nemmeste måde at gøre dette på er at vælge de relevante rækker Forøg и falde direkte i forklaringen og ved at højreklikke på dem, vælg kommandoen Fyld (Fylde):

Hvis du skal tilføje kolonner med subtotaler eller en sidste kolonnetotal til diagrammet, så er det mest praktisk at gøre dette ved hjælp af funktioner SUBTOTALER (SUBTOTALER) or ENHED (SAMLET). De vil beregne det akkumulerede beløb fra begyndelsen af ​​tabellen, mens de ekskluderer de tilsvarende totaler placeret ovenfor:

I dette tilfælde er det første argument (9) koden for den matematiske summeringsoperation, og det andet (0) får funktionen til at ignorere allerede beregnede totaler for tidligere kvartaler i resultaterne.

Efter tilføjelse af rækker med totaler, er det tilbage at vælge de samlede kolonner, der er dukket op på diagrammet (foretag to på hinanden følgende enkeltklik på kolonnen), og ved at højreklikke med musen vælges kommandoen Indstil som total (Angiv som total):

Den valgte kolonne lander på x-aksen og skifter automatisk farve til grå.

Det er faktisk alt - vandfaldsdiagrammet er klar:

Metode 2. Universal: usynlige søjler

Hvis du har Excel 2013 eller ældre versioner (2010, 2007 osv.), vil metoden beskrevet ovenfor ikke fungere for dig. Du bliver nødt til at gå rundt og klippe det manglende vandfaldsdiagram ud fra et almindeligt stablet histogram (summering af søjlerne oven på hinanden).

Tricket her er at bruge gennemsigtige prop-kolonner til at hæve vores røde og grønne datarækker til den korrekte højde:

For at bygge et sådant diagram skal vi tilføje et par ekstra kolonner med formler til kildedataene:

• Først skal vi opdele vores oprindelige kolonne ved at adskille de positive og negative værdier i separate kolonner ved hjælp af funktionen IF (HVIS).  
• For det andet skal du tilføje en kolonne foran kolonnerne sutter, hvor den første værdi vil være 0, og startende fra den anden celle, vil formlen beregne højden af ​​disse meget gennemsigtige støttesøjler.
  

Derefter er det tilbage at vælge hele tabellen undtagen den oprindelige kolonne Flow og opret et almindeligt stablet histogram på tværs Indsat — Histogram (Indsæt — kolonnediagram):

Hvis du nu vælger de blå kolonner og gør dem usynlige (højreklik på dem – Rækkeformat – Fyld – Ingen fyld), så får vi bare det, vi har brug for. 

Fordelen ved denne metode er enkelheden. I minusser - behovet for at tælle hjælpekolonner.

Metode 3. Hvis vi går i minus, er alt sværere

Desværre virker den tidligere metode kun tilstrækkeligt til positive værdier. Hvis i det mindste i et eller andet område vores vandfald går ind i et negativt område, så øges opgavens kompleksitet betydeligt. I dette tilfælde vil det være nødvendigt at beregne hver række (dummy, grøn og rød) separat for de negative og positive dele med formler:

For ikke at lide meget og for ikke at genopfinde hjulet, kan en færdiglavet skabelon til en sådan sag downloades i titlen på denne artikel.

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

Denne metode er baseret på brugen af ​​et særligt lidt kendt element af flade diagrammer (histogrammer og grafer) - Op-ned bånd (Op-Ned søjler). Disse bånd forbinder punkterne på to grafer i par for klart at vise, hvilket af de to punkter der er højere eller lavere, hvilket bruges aktivt, når man visualiserer plan-faktumet:

Det er nemt at finde ud af, at hvis vi fjerner linjerne på diagrammet og kun lader op-ned-båndene være på diagrammet, så får vi det samme "vandfald".

For en sådan konstruktion skal vi tilføje yderligere to kolonner til vores tabel med simple formler, der beregner placeringen af ​​de to nødvendige usynlige grafer:

For at oprette et "vandfald" skal du vælge en kolonne med måneder (til signaturer langs X-aksen) og to yderligere kolonner Skema 1 и Skema 2 og opbyg en almindelig graf til at begynde med at bruge Indsæt – Graf (Indsæt — Linje Сhart):

Lad os nu tilføje op-ned-bånd til vores diagram:

• I Excel 2013 og nyere skal dette vælges på fanen Constructor Kommando Tilføj diagramelement — Bånd af stigning-fald (Design — Tilføj diagramelement — op-ned søjler)
• I Excel 2007-2010 – gå til fanen Layout – Advance-Decrement Bars (Layout — op-ned-bjælker)

Diagrammet vil så se nogenlunde således ud:

Det er tilbage at vælge graferne og gøre dem gennemsigtige ved at klikke på dem på skift med højre museknap og vælge kommandoen Dataserieformat (Format serie). På samme måde kan du ændre standardfarverne, temmelig lurvede sorte og hvide striber til grøn og rød for at få et pænere billede i sidste ende:

I de seneste versioner af Microsoft Excel kan bjælkernes bredde ændres ved at klikke på en af ​​de gennemsigtige grafer (ikke bjælkerne!) med højre museknap og vælge kommandoen Dataserieformat – Sideklaring (Formatserie — Spaltebredde).

I ældre versioner af Excel skulle du bruge Visual Basic-kommandoen til at rette dette:

1. Fremhæv det indbyggede diagram
2. Tryk på tastaturgenvejen andre+F11for at komme ind i Visual Basic Editor
3. Tryk på tastaturgenvej Ctrl+Gfor at åbne den direkte kommandoinput og fejlfindingspanelet umiddelbar (normalt placeret i bunden).

4. Kopier og indsæt følgende kommando der: ActiveChart.ChartGroups(1).GapWidth = 30 og tryk Indtast:

Du kan selvfølgelig lege med parameterværdien, hvis du ønsker det. GapWidthfor at opnå den ønskede frigang:

• Sådan bygger du et punktdiagram i Excel for at visualisere KPI  
• Hvad er nyt i diagrammer i Excel 2013
• Sådan opretter du et interaktivt "live" diagram i Excel