Hoe maak je een watervalgrafiek in Excel

Deze zelfstudie laat zien hoe u een watervaldiagram maakt in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

Een watervalgrafiek (ook wel een bruggrafiek, vliegende stenengrafiek, cascadegrafiek of Mario-grafiek genoemd) is een grafiek die visueel het cumulatieve effect weergeeft dat een reeks opeenvolgende positieve of negatieve waarden hebben bijgedragen aan het uiteindelijke resultaat.

Conventioneel vertegenwoordigen de eerste en laatste kolommen de begin- en eindwaarden, gescheiden door tussentijdse aanpassingen die de opeenvolging van stappen weergeven die nodig zijn om van punt A naar punt B te komen.

Dit type diagram wordt veel gebruikt door professionals in alle belangrijke sectoren voor het uitvoeren van kwantitatieve analyses, variërend van het decoderen van financiële overzichten tot het bewaken van winkelvoorraad.

Overbruggingsgrafieken werden alleen in Excel 2016 geïntroduceerd als een ingebouwd grafiektype, wat betekent dat gebruikers van eerdere Excel-versies geen andere keuze hebben dan de grafiek vanaf de grond af aan te maken.

Om die reden leer je in deze tutorial hoe je deze Mario-kaart kunt maken, zowel handmatig vanaf het begin als door je te wenden tot het oorspronkelijke kaarttype dat al het vuile werk voor je doet.

Beginnen

Meteen uit de poort, we moeten wat onbewerkte gegevens in de foto gooien. Laten we dus, ter illustratie, aannemen dat u de driemaandelijkse nettokasstroom van een fictief bedrijf wilde analyseren.

Houd met dat alles in gedachten de volgende gegevens in overweging:

Om u te helpen het diagram aan uw eigen behoeften aan te passen, gaan we het hebben over de tabel in meer detail:

• Punt uit: Deze kolom vertegenwoordigt een reeks positieve en negatieve waarden die in de grafiek moeten worden uitgezet.
• Netto geldstroom: Deze kolom bestaat uit de bijbehorende waarden. De eerste (B2) en als laatste (B13) cellen bevatten de begin- en eindwaarden, terwijl de resterende getallen ertussen staan ​​voor de kwantitatieve veranderingen in de tijd.

Nu u de essentie van de grafiek kent, kunnen we verder gaan met de kern van de zaak.

Een watervalgrafiek maken in Excel 2016+

Laat me u eerst een oplossing tonen voor diegenen die de weg van de minste weerstand zoeken.

Door de ingebouwde, kant-en-klare sjabloon te gebruiken, mits u Excel 2016 of 2022 gebruikt, zou het niet meer dan een paar eenvoudige stappen moeten kosten om een ​​watervaldiagram op te zetten.

Het komt echter allemaal met de prijs van beperkte aanpassingsopties en mogelijke compatibiliteitsproblemen; oudere versies van Excel kunnen eenvoudigweg geen recentere native grafiektypen verwerken.

Bottom line: wanneer u een grafiek rondstuurt, is het altijd een veiligere gok om deze handmatig te bouwen.

Stap 1: maak een watervalgrafiek.

Maak om te beginnen een standaardwatervaldiagram op basis van uw werkelijke gegevens. Het mooie van deze methode is dat je door geen enkele hoepel hoeft te springen:

1. Markeer alle gegevens (A1:B13).
2. Ga naar de Invoegen tabblad.
3. Selecteer de "Waterval-, trechter-, voorraad-, oppervlakte- of radardiagram invoegen" knop.
4. Kiezen "Waterval."

Onmiddellijk verschijnt je vliegende stenen-kaart:

Stap 2: Stel de subtotaal- en totaalkolommen in.

Onze volgende stap is om de begin- en eindwaarden te scheiden van de 'vliegende stenen'.

Dubbelklik en klik vervolgens met de rechtermuisknop op de kolommen die de gegevenspunten vertegenwoordigen "Overdrachtsaldo" en "Huidig ​​saldo” en kies “Instellen als Totaal.”

Technisch gezien wil je misschien daar stoppen en de grafiek laten zoals hij is. Maar deze cascadegrafiek vertelt ons niets over de gegevens die erop zijn uitgezet.

Waarom genoegen nemen met minder? In plaats daarvan kun je je nieuw gemaakte Mario-kaart aanzienlijk verbeteren door hier en daar een paar dingen te veranderen.

Stap 3: Verander het kleurenschema.

Een effectieve manier om uw grafiek te verbeteren, is door het kleurenschema voor u te laten werken en niet andersom. Kleur de gegevensreeks opnieuw in op een manier die helpt om over te brengen waar de grafiek over gaat.

1. Dubbelklik en klik vervolgens met de rechtermuisknop op het legenda-item dat staat voor Serie "Verlagen."
2. Selecteer de "Vullen" knop.
3. Kies rood uit het kleurenpalet.

Op dezelfde manier opnieuw kleuren Serie “Verhogen” naar groen en Serie “Totalen” naar blauw. Eenmaal daar zou uw cascadegrafiek er als volgt uit moeten zien:

Stap 4: Stem de verticale asbereiken af ​​op uw werkelijke gegevens.

Wijzig de schaal van de verticale as om in te zoomen op de zwevende kolommen voor meer details. Klik met de rechtermuisknop op de primaire verticale as en klik op "Formaat as.”

In de Formaat as taakvenster, volgt u deze eenvoudige stappen:

1. Schakel over naar de Asopties tabblad.
2. Stel de Minimale grenzen tot "50,000.”
3. Stel de Maximale grenzen tot "110,000.”

Stap #5: Verfijn de details.

Het is logisch dat hoe meer elementen de grafiek bevat, hoe rommeliger deze eruitziet. Laten we dus de onnodige elementen verwijderen door de legenda en rasterlijnen te verwijderen.

Klik met de rechtermuisknop op de grafieklegenda en kies "Verwijderen” uit het menu dat verschijnt. Herhaal hetzelfde proces voor de rasterlijnen.

Verander ten slotte de titel van de grafiek en je kunt het een dag noemen!

Een watervalgrafiek maken in Excel 2007, 2010 en 2013

Deze tutorial zou hier eindigen als de hierboven getoonde methode compatibel was met alle versies van Excel. Helaas is dat niet het geval.

Om die reden kunt u overwegen om de grafiek handmatig vanaf nul te plotten, zelfs als u Excel 2016 of 2022 gebruikt.

Uiteraard kost het meer tijd om de grafiek op die manier samen te stellen, maar de uitbetaling is de moeite meer dan waard.

Aan het einde van dit gedeelte leert u hoe u dit aanpasbare, dynamische brugdiagram kunt maken dat compatibel is met alle versies van Excel:

Zonder verder oponthoud, laten we de bal aan het rollen krijgen.

Stap 1: bereid grafiekgegevens voor.

Eerlijk gezegd is deze stap het moeilijkst.

Simpel gezegd, onze aangepaste watervalgrafiek wordt gemaakt op basis van een gestapeld kolomdiagram. Maar om het voor elkaar te krijgen, moeten we wat extra hulpgegevens aan de vergelijking toevoegen.

Neem voor uw gemak een snelle blik op hoe uw kaartgegevens aan het einde van de voorbereidingsfase moeten worden georganiseerd.

Voor degenen die hun eerste kleine stapjes zetten op weg naar Excel-beheersing, lijkt deze tabel misschien ronduit ontmoedigend, maar in werkelijkheid kan iedereen de taak aan door de gedetailleerde instructies hieronder te volgen.

Laten we beginnen met het toevoegen van drie hulpkolommen met de naam "Onzichtbaar” (kolom B), “Toename” (kolom C), en "Verminderen” (kolom D), die de dummy-waarden zullen bevatten voor het positioneren van uw werkelijke gegevens op de toekomstige grafiek.

En als kolommen Verhogen en Verminderen zijn nogal vanzelfsprekend, wat is er aan de hand? kolom Onzichtbaar? Kortom, deze waarden vormen de onzichtbare basis van het toekomstige gestapelde kolomdiagram om de werkelijke waarden op de juiste hoogte te brengen.

Laten we omgaan met kolom Verhogen eerst: voer de volgende formule in cel in C2 en sleep het naar beneden met de vulgreep om het uit te voeren voor de resterende cellen (C3:C13):

1	=ALS(E2>0, E2, 0)

Typ nu de spiegelformule in de eerste cel van kolom Verlagen (kolom D) en kopieer het ook naar beneden:

1	=ALS(E2<=0, -E2, 0)

Twee neer, nog één te gaan. Nadat u de benodigde kaartgegevens hebt gevonden, kunt u nu instellen kolom Onzichtbaar om de cashflowschommelingen nauwkeurig in kaart te brengen door de volgende formule te typen: in de tweede cel van de kolom (B3) en kopieer het naar B12:

1

=B2+C2-D3

OPMERKING: Het is absoluut noodzakelijk dat u de eerste en laatste cellen (B2 en B13) van de kolom leeg!

Vergeet niet om alle grafiekgegevens op te maken als valuta (Home > Nummergroep > Valuta).

Op dit moment ziet uw uitgebreide tabel er als volgt uit:

Stel als laatste aanpassing nog een kolom in met de naam "Etiketten' die mooie aangepaste gegevenslabels zal bevatten.

Nadat u deze kolom hebt ingesteld, voert u deze formule in de tweede cel van de kolom Labels (F3) en kopieer het naar beneden, waarbij je de eerste cel verlaat (F2) en laatste cel (F13) leeg:

1	=IF(E3<=0, TEXT(-E3, "$#,##")&CHAR(10)&TEXT(-E3/SUM(B3:D3), "(#% ↓)"), TEXT(E3, "$#,##")&CHAR(10)&TEXT(E3/SUM(B3:D3), "(#% ↑)"))

Degenen die met andere getalnotaties werken, moeten in plaats daarvan deze formule toepassen:

1	=IF(E3<=0, TEXT(-E3, "#,##")&CHAR(10)&TEXT(-E3/SUM(B3:D3), "(#% ↓)"), TEXT(E3, " #,##")&CHAR(10)&TEXT(E3/SUM(B3:D3), "(#% ↑)"))

Stap 2: Bouw een gestapeld kolomdiagram.

We hebben de bult overwonnen, dus de tijd om onze watervalkaart te bouwen is eindelijk aangebroken.

1. Selecteer alle gegevens in kolommen Periode, Onzichtbaar, Toename, en Verminderen (A1:D13).
2. Ga naar de Invoegen tabblad.
3. Klik "Kolom- of staafdiagram invoegen.”
4. Kiezen "Gestapelde kolom.”

Excel zal deze eenvoudige grafiek samenstellen die uiteindelijk zal worden omgezet in een verbluffende watervalgrafiek:

Stap 3: Verberg serie "Onzichtbaar".

Voordat we verder gaan met de rest van het diagram, verbergt u de onderliggende gegevensreeksen door de zwevende kolommen naar boven te duwen. Klik met de rechtermuisknop op een van de blauwe kolommen die staan ​​voor Serie "Onzichtbaar" en open de Gegevensreeks opmaken taakvenster.

Eenmaal daar, maak de helperkolommen onzichtbaar:

1. Ga naar de Vulling & lijn tab-de Vullen tabblad in oudere versies van Excel.
2. Onder "Vullen," selecteer "Geen vulling.”
3. Onder "Grens," Klik "Geen lijn.”

Stap 4: Pas het kleurenschema aan.

In hetzelfde Gegevensreeks opmaken taakvenster, kleurt u elke gegevensreeks opnieuw door de identieke stappen hierboven te volgen (Serie-opties > Vullen > Vaste vulling > Kleur) zodat het intuïtief in één oogopslag kan worden begrepen:

1. Voor Serie "Verlagen", kiezen rood.
2. Voor Serie "Verhogen", kiezen groente.
3. Dubbelklik bovendien op de kolommen “Overdrachtssaldo” en "Huidig ​​saldo" en kleur ze opnieuw blauw.

Dit is wat je op dit moment zou moeten hebben:

Stap #5: Wijzig de spleetbreedte in "20%".

Vergroot nu de breedte van de kolommen voor een betere visuele waarneming.

1. Open de Gegevensreeks opmaken taakvenster (klik met de rechtermuisknop op een van de gegevensreeksen en kies Gegevensreeks opmaken) en schakel over naar de Serie-opties tabblad.
2. Wijziging "Breedte spleet" tot "20%.”

Stap # 6: Pas de verticale asbereiken aan.

Nogmaals, de asschaal moet worden aangepast om nauwkeuriger in te zoomen op de gegevens. Om het proces snel samen te vatten voor degenen die het eerste deel van deze tutorial hebben overgeslagen, gaat u als volgt te werk:

1. Klik met de rechtermuisknop op de verticale as en kies "Formaat as.”
2. Open in het taakvenster de Asopties tabblad.
3. Onder "Grenzen," verandering "Minimum" tot "50,000" en "Maximaal" tot "110,000.”

Stap #7: Voeg de aangepaste gegevenslabels toe en plaats ze.

Die aangepaste gegevenslabels hebben te lang gewacht, dus laten we ze eindelijk in gebruik nemen. Klik met de rechtermuisknop op een kolom en selecteer "Voeg gegevenslabels toe.”

Onmiddellijk worden de standaard gegevenslabels die aan de helperwaarden zijn gekoppeld, aan het diagram toegevoegd:

Maar dat is niet precies wat we zoeken. U kunt dit probleem omzeilen door de standaardlabels handmatig te vervangen door de aangepaste waarden die u vooraf hebt voorbereid.

1. Dubbelklik op het gegevenslabel dat u wilt wijzigen.
2. Binnenkomen "=" in de Formule balk.
3. Markeer een corresponderende cel in kolom Labels.

Vervang de resterende gegevenslabels door hetzelfde proces te herhalen voor elke zwevende kolom.

Eenmaal daar moet u de labels naar hun respectieve posities slepen: alle positieve waarden moeten boven de respectieve groene kolommen worden geplaatst, terwijl de negatieve onder de rode kolommen moeten worden verplaatst.

Nadat je de labels met succes hebt aangepakt, zou je Mario-kaart in zoiets als dit moeten veranderen:

Stap #8: Ruim het grafiekgebied op.

Verwijder ten slotte de diagramlegenda en rasterlijnen die niets naar de tabel brengen (Klik met de rechtermuisknop > Verwijderen). Verander de titel van de grafiek en daar heb je je watervalgrafiek!