Hoe te waterval grafiek maken in Excel in 2 minuten




Een waterval grafiek wordt gebruikt om een ​​reeks cijfers vertegenwoordigen als ze allemaal invloed hebben op de zelfde afgeleid nummer. Een waterval diagram helpt om de individuele waarden tot een geheel koppelen. Een winst-en verliesrekening is een goed voorbeeld waar de omvang van elke figuur heeft een impact op een afgeleid getal (die toevallig winst in dit geval).

Een collega van mij had onlangs een budgettering oefening toegewezen aan hem waar hij moest de P & V-cijfers projecteren voor het volgende jaar en dan laten zien als een waterval grafiek. De bestaande sjabloon te beginnen, hadden waarschijnlijk rond 7 of 8 serie, elk zijn gebruikt om een ​​reeks van grafieken genereren. (Zoals je misschien al hebt geraden, een waterval grafiek in Excel gebruikt de inheemse stapel grafieken.) Nu zijn we niet bijzonder leuk wat we kregen en we deden een beetje googlen om te zien of het wiel eerder had uitgevonden ...... het had geweest en bij tal van gelegenheden, maar we waren op zoek naar een concreet voorbeeld in excel en tot onze teleurstelling, dat was iets wat we niet vinden. Wat we vonden, waren echter een groot aantal 'part-waterval-part-bar' grafieken die goed zou werken met een specifieke set van waarden, maar delegeren in de normale staafdiagrammen (of soms zelfs gewoon afbreken) zodra de waarden werden verplaatst te veel.

Een voorbeeld hiervan is hier te zien.

wrong-waterfall-chart

Dus wat is een dimwit te doen ...... en ...... we onze eigen versie van de waterval grafiek.
waterfall chart

Naar de waterval grafiek te maken, we zijn begonnen met een dummy set van nummers voor de P & L.

We voegde vier extra stel formules - een voor het werkelijke aantal, de padding (of compensatie), voor crossover (wanneer de nummers bewegen over de X-as en ten slotte het referentiepunt -. Die ik noemde de datum). De werkelijke waterval grafiek daadwerkelijk gebruikt alleen de eerste drie als een serie, terwijl het nulpunt is alleen voor de ontwikkelaar om bij te houden. (Ten koste van extra complexiteit, kun je eigenlijk oplossen het referentiepunt in de eerste drie waarden en volledig laten vallen.)

Je mag eigenlijk slaat u de rest als alles wat je nodig hebt om te grijpen is een snelle kopie van de waterval grafiek in Excel .

Vier verschillende componenten van de Waterval Grafiek:
. 1 De Vulling: Aangezien elk van staven in een waterval grafiek niet beginnen bij 0, je moet ze compenseren (verheffen of drukken) door een zekere marge. Hoeveel te compenseren wordt bepaald door de datum.
. 2 The Plot: De waarde te plotten. De truc is hier om ervoor te zorgen dat als de werkelijke waarde te plotten is-ve, maar het werkt nog steeds niet onder nul (omdat de vorige cijfer was veel te positief), het cijfer gebruikt voor het daadwerkelijk uitzetten van de bar heeft om boven de as, maar zuidwaarts. En vice-versa. In dergelijke gevallen, de opvulling dienovereenkomstig worden aangepast.

. 3 De crossover: Soms zullen we een situatie waarin de plot waarde zodanig is dat het beweegt over de X-as ondervindt. Bijvoorbeeld, het eerste cijfer was 1000 en de tweede figuur (degene die we nodig hebben om te plotten) is -2000. In dat geval zal een deel van de staaf boven de X-as en een aantal hieronder. De oplossing is om de plot waarde gebruiken (in punt 2) de helft van de werkelijke waarde plotten en gebruik de crossover waarde op het overige deel plotten. Dit is een cruciale test voor een waterval grafiek - de cross-over serie moet automatisch aan te passen wanneer de volgende waarde zorgt ervoor dat de beweging over de as van de onderste helft naar de bovenste of vice-versa.
4 De datum:. Het nulpunt is het niveau dat het uitgangspunt voor de volgende waarde aan het zijn van wordt. De truc hier is om ervoor te zorgen dat wanneer twee opeenvolgende waarden zijn 100 en 10, de derde bar moet zijn vanaf 110. Maar als de waarden 100 en -10, de derde balk moet beginnen vanaf 90.

De formules die we voor elk van de bovengenoemde componenten waren:
waterfall-chart-formulas

Zodra u de set van de vier formules, ze gewoon uit te breiden tot de hele serie die u wilt uitzetten. Dit wordt nu de brongegevens voor de waterval grafiek. Wat blijft er nu als om gewoon halen de eerste drie de opvulling, het perceel en de crossover - en plaats een gestapelde grafiek in Excel. De resulterende zal iets dergelijks als dit een te zijn:

Verwijder de grafiek rommel. Dubbelklik op de serie die padding vertegenwoordigt en maken het transparant en VIOLA ..... je waterval grafiek is klaar te stromen!

waterfall-chart-template


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think?


XHTML: U kunt deze tags gebruiken: <a href="" title=""> <abbr title=""> <acroniem title=""> <b> <blockquote cite=""> <cite> <code> < del datetime = ""> <em> <i> <Q cite=""> <strike> <strong>


Reacties en Trackbacks

  1. Jon Peltier schreef:

    "[W] e hebben een beetje googlen om te zien of het wiel eerder had uitgevonden ..."

    Ik heb een Waterval Grafiek Tutorial beschikbaar voor jaren:
    http://peltiertech.com/Excel/Charts/Waterfall.html
    Dit is de eerste link geretourneerd als u Google voor excel waterval grafieken.

    Een tweede les geldt in gevallen waarin de tralies steken de horizontale as:
    http://peltiertech.com/Excel/Charts/waterfallcrossing.html

    Ik heb zelfs bouwde een hulpprogramma voor waterval grafieken maken met een klik van een knop, met zowel gratis en professionele versies:
    http://peltiertech.com/Utility/WaterfallUtility.html

  2. Ajay schreef:

    John :: Ik ben het met u een 101%. Uw tutorials over de oprichting van de waterval grafieken zijn zeer informatief. Goeroes zoals jij, John Walkenbach, Debra Dalgleish, Charley Kyd, Tushar Mehta blijven een bron van inspiratie.

    Maar zoals ik al zei, ".... we waren op zoek naar een concreet voorbeeld in excel en tot onze teleurstelling, dat was iets wat we niet vinden ....". Om precies te zijn om een ​​fout, de eerste en de derde koppelingen lijken als de eerste twee schakels tijdens het zoeken naar 'waterval chart'. Maar ze vallen kort presenteren alle stappen met betrekking tot de oprichting van een compleet waterval grafiek. De tweede link, die je heb gezegd, is tonen de complete stap voor stap proces, niet in de zoekopdracht. Je zou ermee akkoord dat sommige van de andere links die voortvloeien uit deze bevraging, doen wijs andere pogingen die de verwachtingen vallen.

    Wat ik heb geprobeerd om hier te laten zien is om het proces van de oprichting van een dergelijke grafiek te vereenvoudigen met behulp van slechts 4 eenvoudige maar consistente formules die eenvoudig kan worden uitgebreid over de n rijen op wil en die ook het giswerk betrokken te weten welke cellen te houden elimineren leeg en welke te bevolken. Of dat is een verbetering ten opzichte van eerdere inspanningen ...... dat laat ik open voor discussie.

    Wat het ook is, dit is zeker niet een poging om de presentatie van het concept als een nieuwe ontdekking. Groet,

  3. MatthewR schreef:

    Zeer informatief. Bedankt voor de stapsgewijze Walkthrough ... geholpen een ton.

  4. Ajay schreef:

    @ Matthew - Blij dat ik kon helpen en welkom bij da TaB zijn

  5. Biman schreef:

    bedankt voor de beschrijvende tips over waterval grafieken .. echt inspirerend

  6. Ajay schreef:

    Thanks Biman. Welkom bij da Tab en blij dat dit was van hulp :-)

  7. lernr schreef:

    Zeer mooie Ajay

    Om een ​​of andere reden heb ik niet de gratis versie van JP's Utility op de pagina te zien.

    Dit is zeker iets wat ik kan gemakkelijk gebruik maken, met het voordeel dat de verklaring is vrij duidelijk.

    Groot UPS!

  8. Ajay schreef:

    lernr - Bedankt en Welcome to da TaB!

  9. Federico schreef:

    Hoe kan ik passen door middel van VBA-code de breedte van de kolommen?
    Is het mogelijk?
    Dank je wel!

  10. Ajay schreef:

    @ Federico - U kunt de volgende code gebruiken om de breedte van de kolommen van de waterval diagram te wijzigen. Het kan nodig zijn om de code te kraken een beetje maar naar uw wensen.

    1
    2
    3
    4
    5
    6
    Sub Waterfall_Width_Change ()
    ActiveChart.ChartGroups Voor Elke grp In ActiveChart.ChartGroups
    grp.Overlap = 100
    grp.GapWidth = 70
    Volgende grp
    End Sub

    Groet,

  11. Federico schreef:

    @ Ajay: dank u voor uw antwoord.
    Maar ik denk dat ik nedd iets anders want ik moet voor elke coloumn de breedte in te stellen.
    Want in mijn geval heb ik twee variabelen een op "x"-as en een op "y"-as en ik moeten beide ingesteld.
    Kan je me helpen op deze manier?
    Dank u bij voorbaat
    Regards

  12. Ajay schreef:

    @ Federico - Zou het mogelijk zijn voor u om mij een screenshot of een bestand dat ik kan kijken. Ik heb niet het punt over de twee variabelen (een op de "x"-as en een op "y"-as) te krijgen. : Roll: Ik vond de waterval diagram slechts een type-staven horizontaal (langs de x-as) of verticaal (langs de y-as). Help me dit beter te begrijpen.
    Groet,

  13. Federico schreef:

    Tuurlijk! hoe kan ik sturen naar u?

  14. Ajay schreef:

    @ Federico - Stuur me een e-mail op databison | bij | gmail.com met de bevestiging (de waterval grafiek die u probeert aan te passen) en vermelden hoe je zou willen eindelijk eruit. Laat me eens kijken.

    Groet,

  15. Federico schreef:

    Ik heb het gestuurd right now!!

    Dank u voor uw antwoord!

    Federico

  16. Kerri schreef:

  17. Ajay schreef:

    @ Kerri - Bedankt. You rock te :-)

  18. Zidane schreef:

    Mijn baas sharted zijn broek toen ik bezorgd dat moois ... Cheers mate

  19. Alek schreef:

    absoluut briljant en eenvoudige stap voor stap handleiding. Ik ben op zoek naar dit voor een lange tijd .... bedankt

  20. Albert schreef:

    Zeer nuttig, leunen veel

  21. Hugh schreef:

    Cheers, bespaarde me zeeen van tijd, Thanks

  22. Dan schreef:

    Bedankt voor de informatie, het was erg behulpzaam. Ik vroeg me af hoe je de kleur van de kolommen voor de lasten van een andere kleur dan de kolommen voor de inkomsten items wijzigen?

  23. m schreef:

    awesome stuff, net gerepliceerd in excel en gebruikte mijn eigen gegevens, dit zeker rotsen.

    Ik heb ook gebruik gemaakt van VBA-codes tutorials zoals draaipunten. thanks team

  24. john.caulfield @ mousetraining.co.uk schreef:

    Hunuted voor dit voorbeeld een coupble van yearsâ geleden en vond niets, maar dit is precies wat ik zocht dankzij

  25. Jay schreef:

    Grote sjabloon voor de grafiek zelf, met een grote tekortkoming. Ik zie niet in hoe je kunt onder andere labels op de standplaatsen. Het zal niet negatief percelen show met een negatief label. Youd moet ze handmatig voor zover ik kan vertellen bewerken.

  26. Filder schreef:

  27. Pritam schreef:

    Bedankt Ajay. Probeerde dit te doen door mijzelf, maar hield vast komen te zitten. Dit is echt hielp duidelijk de basics.

  28. Jodie Hicken schreef:

    Bedankt, zeer gemakkelijk te begrijpen

  29. Manuel schreef:

    Fantastisch! helpt veel.

  30. Brian schreef:

    Fantastisch, precies wat onze GM is na. Nog niet gespeeld met de nummers. Maar het is geweldig om te kunnen om de formules voor onze eigen doeleinden te gebruiken.

    Cheers.

  31. Edo schreef:

    U kunt hetzelfde resultaat bereiken (met aparte kleuren voor positieve en negatieve bijdrage!) Op een gemakkelijkere manier met de grafiek "stock open hoog-laag-slot":

    value = is de waarde om te plotten, niet te selecteren voor de grafiek!
    open = sluiten [-1]
    hoog = sluiten
    laag = sluiten [-1]
    close = waarde [-1] + waarde
    In de eerste regel waarde [1] = 0

    Dat is het. Proberen.

  32. Carel Viljoen schreef:

    Zeer snel en pijnloos ... nice one guys.

  33. Carel Viljoen schreef:

    Edo - net geprobeerd de jouwe ook. Mooi in zijn eenvoud.

  34. Carel Viljoen schreef:

    Drie kleine trucjes wrt Edo techniek ...

    1 - Doe geen moeite bevolken ofwel de hoge of lage waarden - u kunt deze kolommen leeg te laten;

    2 - The Close waarde zou eigenlijk Open + Waarde zijn;

    3 - In Excel 2007/10/13, om de breedte te verminderen tussen de spijlen - dwz dezelfde look als de gewijzigde kolom grafiek in het origineel voorbeeld te bereiken - maken de grafiek, wijzigt u het schema om bundelpijler, wijziging van de kloof tussen de kolommen naar wens en terug in op de Open-hoog-laag-slot formaat.

  35. Di Kelly schreef:

    Superb - dank u!

  36. tomtom schreef:

    Hallo grote sjabloon. Snelle vraag hoe kan ik laten zien de geplotte gegevens (een reeks) in twee verschillende kleuren, zoals een kleur voor positieve en een andere kleur voor negatieve ..
    Vooruitblikkend te horen van u.

  1. Hoe kan ik een dashboard maken in Excel | Excel & VBA - da Tab Is On schreef:

    [...] De gegevens wijzen u wilt tonen. Als u wilt dat de winst-en verliesrekening laten zien, gebruik dan een waterval grafiek (indien nodig). Als het een project tijdlijn, gebruik maken van een Gantt-grafiek. Tijdens het gebruik van een grafiek in Excel [...]

Abonneren

Blijf op de hoogte met de nieuwste verhalen - Geleverd direct in je inbox
feedburner

Vertalen

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software voor Excel