Een watervaldiagram maken

Steeds vaker kom ik elkaar tegen in de berichtgeving van verschillende bedrijven en hoor ik verzoeken van stagiaires om uit te leggen hoe een cascadediagram van afwijkingen wordt opgebouwd – het is ook een “waterval”, het is ook een “waterval”, het is ook een “brug” ”, het is ook een “brug”, enz. . Het ziet er ongeveer zo uit:

Van een afstand lijkt het echt een waterval van watervallen op een bergrivier of een hangende brug - wie ziet wat 🙂

De eigenaardigheid van zo'n diagram is dat:

• We zien duidelijk de begin- en eindwaarde van de parameter (de eerste en laatste kolom).
• Positieve veranderingen (groei) worden in één kleur weergegeven (meestal groen), en negatieve (afwijzen) naar anderen (meestal rood).
  
• Soms kan het diagram ook subtotaalkolommen bevatten (grijslandde op de x-as kolommen).
  

In het dagelijks leven worden dergelijke diagrammen meestal gebruikt in de volgende gevallen:

• Visual dynamische weergave elk proces op tijd: cashflow (cashflow), investeringen (we investeren in een project en halen er winst uit).
• Visualisatie plan implementatie (de meest linkse kolom in het diagram is een feit, de meest rechtse kolom is een plan, het hele diagram weerspiegelt ons proces om naar het gewenste resultaat te gaan)
• Wanneer je visueel nodig hebt factoren weergevendie onze parameter beïnvloeden (factoriële analyse van winst - waar het uit bestaat).
  

Er zijn verschillende manieren om zo'n grafiek te maken - het hangt allemaal af van uw versie van Microsoft Excel.

Methode 1: Eenvoudigste: ingebouwd type in Excel 2016 en nieuwer

Als je Excel 2016, 2019 of later (of Office 365) hebt, dan is het bouwen van zo'n grafiek niet moeilijk – deze versies van Excel hebben dit type standaard al ingebouwd. Het is alleen nodig om een ​​tabel met gegevens te selecteren en op het tabblad te selecteren Invoegen (Invoegen) commando trapsgewijze (Waterval):

Als resultaat krijgen we een bijna kant-en-klaar diagram:

U kunt direct de gewenste opvulkleuren instellen voor de positieve en negatieve kolommen. De eenvoudigste manier om dit te doen, is door de juiste rijen te selecteren Laat uw omzet и verlagen direct in de legenda en door er met de rechtermuisknop op te klikken, selecteert u de opdracht Vulling (Vullen):

Als u kolommen met subtotalen of een laatste kolomtotaal aan de grafiek moet toevoegen, dan is het het handigst om dit te doen met behulp van functies SUBTOTALEN (SUBTOTALEN) or EENHEID (TOTAAL). Ze berekenen het verzamelde bedrag vanaf het begin van de tabel, terwijl ze de vergelijkbare totalen hierboven uitsluiten:

In dit geval is het eerste argument (9) de code van de wiskundige optelbewerking en het tweede (0) zorgt ervoor dat de functie reeds berekende totalen voor voorgaande kwartalen in de resultaten negeert.

Na het toevoegen van rijen met totalen, blijft het om de totale kolommen te selecteren die in het diagram zijn verschenen (maak twee opeenvolgende enkele klikken op de kolom) en selecteer met de rechtermuisknop de opdracht Stel in als totaal (Instellen als totaal):

De geselecteerde kolom komt op de x-as terecht en verandert automatisch van kleur in grijs.

Dat is eigenlijk alles - het watervaldiagram is klaar:

Methode 2. Universeel: onzichtbare kolommen

Als u Excel 2013 of oudere versies (2010, 2007, enz.) hebt, werkt de hierboven beschreven methode niet voor u. Je zult rond moeten gaan en de ontbrekende watervalgrafiek uit een normaal gestapeld histogram moeten knippen (de balken op elkaar optellen).

De truc hier is om transparante steunkolommen te gebruiken om onze rode en groene gegevensrijen naar de juiste hoogte te brengen:

Om zo'n diagram te maken, moeten we nog een paar hulpkolommen met formules toevoegen aan de brongegevens:

• Eerst moeten we onze oorspronkelijke kolom splitsen door de positieve en negatieve waarden in afzonderlijke kolommen te scheiden met behulp van de functie IF (ALS).  
• Ten tweede moet u een kolom voor de kolommen toevoegen fopspenen, waarbij de eerste waarde 0 is, en beginnend bij de tweede cel, berekent de formule de hoogte van die zeer transparante ondersteunende kolommen.
  

Daarna blijft het om de hele tabel te selecteren, behalve de originele kolom Stroom en maak een regelmatig gestapeld histogram over Inzet — Histogram (Invoegen — Kolomdiagram):

Als u nu de blauwe kolommen selecteert en ze onzichtbaar maakt (klik er met de rechtermuisknop op - Rij-indeling - Opvullen - Geen opvulling), dan krijgen we gewoon wat we nodig hebben. 

Het voordeel van deze methode is de eenvoud. In de minnen - de noodzaak om hulpkolommen te tellen.

Methode 3. Als we in het rood gaan, is alles moeilijker

Helaas werkt de vorige methode alleen adequaat voor positieve waarden. Als onze waterval op zijn minst in een bepaald gebied in een negatief gebied terechtkomt, neemt de complexiteit van de taak aanzienlijk toe. In dit geval moet elke rij (dummy, groen en rood) afzonderlijk worden berekend voor de negatieve en positieve delen met formules:

Om niet veel te lijden en het wiel niet opnieuw uit te vinden, kan een kant-en-klaar sjabloon voor zo'n geval worden gedownload in de titel van dit artikel.

Methode 4. Exotisch: up-down bands

Deze methode is gebaseerd op het gebruik van een speciaal weinig bekend element van platte grafieken (histogrammen en grafieken) - Up-down banden (omhoog-omlaag-balken). Deze banden verbinden de punten van twee grafieken in paren om duidelijk te laten zien welke van de twee punten hoger of lager is, wat actief wordt gebruikt bij het visualiseren van het plan-feit:

Het is gemakkelijk om erachter te komen dat als we de lijnen van de grafieken verwijderen en alleen de opwaartse banden op de kaart laten, we dezelfde "waterval" zullen krijgen.

Voor een dergelijke constructie moeten we nog twee extra kolommen aan onze tabel toevoegen met eenvoudige formules die de positie van de twee vereiste onzichtbare grafieken berekenen:

Om een ​​"waterval" te maken, moet u een kolom met maanden selecteren (voor handtekeningen langs de X-as) en twee extra kolommen Plan 1 и Plan 2 en bouw een normale grafiek voor starters met behulp van Invoegen – Grafiek (Invoegen — Lijn Сhart):

Laten we nu opwaartse banden aan onze grafiek toevoegen:

• In Excel 2013 en nieuwer moet dit worden geselecteerd op het tabblad Constructeur commando Voeg een grafiekelement toe — Banden van toename-afname (Ontwerp — Grafiekelement toevoegen — Bars omhoog en omlaag)
• In Excel 2007-2010 – ga naar tabblad Lay-out - Bars voor het verlagen van de voortgang (Lay-out — Bars omhoog/omlaag)

De grafiek ziet er dan ongeveer zo uit:

Het blijft om de grafieken te selecteren en ze transparant te maken door er achtereenvolgens op te klikken met de rechtermuisknop en het commando te selecteren Indeling gegevensreeks (Formaat serie). Op dezelfde manier kun je de standaard, nogal armoedig ogende zwart-witte streepkleuren veranderen in groen en rood om uiteindelijk een mooier beeld te krijgen:

In de nieuwste versies van Microsoft Excel kan de breedte van de balken worden gewijzigd door met de rechtermuisknop op een van de transparante grafieken (niet de balken!) te klikken en het commando te selecteren Formaat gegevensreeks – zijwaartse speling (Formaatreeks — spleetbreedte).

In oudere versies van Excel moest u de Visual Basic-opdracht gebruiken om dit op te lossen:

1. Markeer het gebouwde diagram
2. Druk op de sneltoets anders+F11om in de Visual Basic Editor te komen
3. Druk op de sneltoets Ctrl+Gom het paneel voor directe opdrachtinvoer en foutopsporing te openen Onmiddellijk (meestal onderaan).

4. Kopieer en plak daar de volgende opdracht: ActiveChart.ChartGroups(1).GapWidth = 30 en druk op Enter:

U kunt natuurlijk met de parameterwaarde spelen als u dat wilt. GapBreedteom de gewenste speling te bereiken:

• Hoe een bullet-grafiek in Excel te bouwen om KPI te visualiseren  
• Wat is er nieuw in grafieken in Excel 2013
• Hoe maak je een interactieve "live" grafiek in Excel