Hoe te waterval grafiek in Excel in 2 minuten te creëren




Tumblr StumbleUpon Reddit LinkedIn AIM Digg Evernote WhatsApp MySpace Kindle Het

Een waterval grafiek wordt gebruikt om een ​​reeks cijfers als ze allemaal invloed hetzelfde afgeleid nummer vertegenwoordigen. Een waterval diagram helpt om de individuele waarden van 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 ook Winst in dit geval).

Een collega van mij had onlangs een budgettering oefening toegewezen aan hem waar hij moest de P & V-cijfers voor het volgende jaar te projecteren en ze vervolgens te 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 geraden, een waterval grafiek in Excel gebruikt de inheemse stapel grafieken.) Nu hebben we niet bijzonder als wat we kregen en we deden een beetje googlen om te zien of het wiel had eerder uitgevonden ...... het had geweest en bij tal van gelegenheden, maar we waren op zoek naar een concreet voorbeeld in excel en onze teleurstelling, dat was iets wat we niet vinden. Wat we vinden, maar er waren een groot aantal grafieken 'part-waterval-deel-bar' die goed zou werken met een specifieke set van waarden, maar delegeren naar normale staafdiagrammen (of soms zelfs gewoon afbreken), zodra de waarden werden verplaatst te veel.

Een voorbeeld daarvan is hier te zien.

wrong-waterfall-chart

Dus wat is een dimwit te doen ...... en ...... we maakten 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 set van formules - een voor elk van het werkelijke aantal, de padding (of compensatie), voor crossover (wanneer de nummers bewegen over de X-as en ten slotte het referentiepunt -. Die ik, het nulpunt). De eigenlijke waterval grafiek eigenlijk gebruikt alleen de eerste drie als een serie, terwijl de datum is alleen voor de ontwikkelaar om bij te houden. (Ten koste van extra complexiteit, kun je eigenlijk oplossen het referentiepunt in op de eerste drie waarden en volledig laten vallen.)

Je mag eigenlijk sla 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 Padding: Aangezien elk van bars in een waterval grafiek niet beginnen bij 0, je moet ze (verheffen of druk), gecompenseerd door een zekere marge. Hoe veel te compenseren, wordt bepaald door de datum.
2 The Plot: De waarde om te plotten. De truc hier is om ervoor te zorgen dat als de reële waarde kan plot 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 verplaatsen naar het zuiden. En vice-versa. In dergelijke gevallen, de padding dienovereenkomstig worden aangepast.

3 De crossover: Soms zullen we een situatie waarin de plot waarde zodanig is dat het beweegt over de X-as tegen. 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 balk boven X-as weer lager. De oplossing is om de plot waarde gebruiken (in punt 2) aan de helft van de werkelijke waarde plotten en gebruik de crossover waarde om het resterende deel te plotten. Dit is een cruciale test voor een waterval diagram - het oversteken serie automatisch aangepast wanneer de volgende waarde veroorzaakt de beweging over de as van de onderste helft aan de bovenste of vice-versa.
4 De datum: De datum is het niveau dat het uitgangspunt voor de volgende waarde wordt aan het zijn van. De truc hier is om ervoor te zorgen dat wanneer twee opeenvolgende waarden van 100 en 10, de derde bar moet zijn vanaf 110 Maar als de waarden 100 en -10, de derde bar heeft om te beginnen vanaf 90.

De formules voor dat 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 plotten. Dit wordt nu de brongegevens voor de waterval grafiek. Wat blijft er nu als om gewoon halen de eerste drie van de opvulling, de plot en de crossover - en plaats een gestapelde grafiek in Excel. De resulterende zal iets als dit een zijn:

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

waterfall-chart-template

Tumblr StumbleUpon Reddit LinkedIn AIM Digg Evernote WhatsApp MySpace Kindle Het

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

Wat denk je?


XHTML: U kunt deze tags gebruiken: <a href="" title=""> <abbr title = ""> <acronym 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 had eerder is uitgevonden ..."

    Ik heb een Waterval Grafiek Tutorial beschikbaar voor jaren gehad:
    http://peltiertech.com/Excel/Charts/Waterfall.html
    Dit is de eerste schakel terug als je op Google naar excel waterval charts.

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

    Ik heb zelfs bouwde een hulpprogramma om 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 je een 101%. Uw tutorials met betrekking tot de creatie van 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, "... Wij waren op zoek naar een concreet voorbeeld in excel en tot onze teleurstelling, dat was iets wat we niet vinden ....". Om precies te zijn een fout, de eerste en derde verbindingen lijken de eerste twee schakels terwijl voor "waterval chart '. Toch vallen ze kort presenteren alle stappen met betrekking tot het creëren van een complete waterval grafiek. De tweede link, die je hebt genoemd, vertoont de volledige stap voor stap proces, niet voorkomt in de zoekopdracht. Je zou eens dat sommige van de andere links die het gevolg zijn van een dergelijk onderzoek, doen punt naar andere pogingen die de verwachtingen valt.

    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 n rijen op wil en die ook het giswerk betrokken bij het 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 tot het presenteren van het concept als een nieuwe ontdekking. Met vriendelijke 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

  5. Biman schreef:

    bedankt voor de beschrijvende tips over waterval charts..truly inspirerende

  6. Ajay schreef:

    Thanks Biman. Welcome to 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 uitleg is vrij duidelijk.

    Big Ups!

  8. Ajay schreef:

    lernr - Bedankt en Welcome to da Tab!

  9. Federico schreef:

    Hoe kan ik instellen via VBA-code de breedte van de kolommen?
    Is het mogelijk?
    Dank u !!!

  10. Ajay schreef:

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

    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

    Met vriendelijke groet,

  11. Federico schreef:

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

  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 "x"-as en een op "y"-as) te krijgen. : Roll: Ik vond de waterval diagram slechts een type bars -hetzij horizontale (langs de x-as) of verticaal (langs de y-as). Me helpen begrijpen dit beter.
    Met vriendelijke 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 bijlage (de waterval grafiek die u probeert te wijzigen) en vermeld hoe je zou willen om eindelijk eruit. Laat me eens kijken.

    Met vriendelijke groet,

  15. Federico schreef:

    Ik heb het direct nu !!!!

    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 deze voor een lange tijd ... .thanks

  20. Albert schreef:

    Erg handig, leunen veel

  21. Hugh schreef:

    Cheers, redde mij hopen 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 te wijzigen in een andere kleur dan de kolommen voor de inkomsten items?

  23. m schreef:

    geweldig spul, maar gerepliceerd in excel en gebruikte mijn eigen gegevens, is dit zeker rotsen.

    Ik heb ook met behulp van VBA-codes tutorials zoals draaipunten. thanks team

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

    Hunuted voor dit voorbeeld een coupble van jaara 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 u kunt ook labels op de standplaatsen. Het zal niet geven negatieve percelen 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 bleef vastzitten. Dit echt geholpen duidelijk de basics.

  28. Jodie Hicken schreef:

    Bedankt, zeer gemakkelijk te begrijpen

  29. Manuel schreef:

    Fantastic !!! helpt veel.

  30. Brian schreef:

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

    Cheers.

  31. Edo schreef:

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

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

    Dat is het. Proberen.

  32. Carel Viljoen schreef:

    Extreem snel en pijnloos ... nice one guys.

  33. Carel Viljoen schreef:

    Edo - net geprobeerd 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 moet eigenlijk Open + Waarde zijn;

    3 - In Excel 2007/10/13, de breedte tussen de spijlen te verminderen - dat wil zeggen dezelfde look als de gewijzigde kolomdiagram in het oorspronkelijke voorbeeld te bereiken - maken de grafiek wijzigt het diagramtype te bundelpijler, wijziging van de kloof tussen de kolommen naar wens en terug in op de Open-High-Low-Close-formaat.

  35. Di Kelly schreef:

    Schitterend - dank je wel!

  36. tomtom schreef:

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

  37. MLH schreef:

    Deze corrigeert niet het etiket van de datapunten. Negatieve waarden worden weergegeven als positieve.

  38. Jack schreef:

    Geachte Tomtom,
    Om automatische kleuring van zowel positieve als negatieve effecten te krijgen, kunt u proberen deze gratis template: http://www.waterfall-chart.com !
    Hoop dat het helpt
    Jack

  1. Hoe maak je een dashboard in Excel maken | Excel en VBA - da Tab Is On schreef:

    [...] De data dat 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 van de nieuwste verhalen - recht in uw mailbox
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