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




Tumblr StumbleUpon Reddit LinkedIn AIM Digg Evernote WhatsApp Mijn ruimte Kindle Het

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

Een collega van mij had onlangs een budgettering oefening toegewezen aan hem waar hij moest de P & L cijfers voor het volgend 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 maakt gebruik van de inheemse stapel grafieken.) Nu hebben we niet met name 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 tot onze teleurstelling, dat was iets wat we niet vinden. Wat we vinden, maar er waren een groot aantal grafieken 'part-waterval-part-bar' die goed zou werken met een specifieke set van waarden, maar overgaan 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 ...... goed ...... 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 vervolgens toegevoegd vier extra stel formules - een voor elk van de werkelijke aantal, de padding (of offset), voor crossover (wanneer de nummers bewegen over de X-as en ten slotte het referentiepunt -., Die ik, het nulpunt). De werkelijke waterval grafiek eigenlijk gebruikt alleen de eerste drie als een serie, terwijl de datum is alleen voor de ontwikkelaar om bij te houden. (Op de kosten van de extra complexiteit, kun je eigenlijk oplossen het referentiepunt in 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 diagram in excel .

Vier verschillende onderdelen van de Waterval Grafiek:
1. De Padding: Aangezien elk van bars in een waterval grafiek niet beginnen bij 0, moet je ze (te verheffen of druk) gecompenseerd door een zekere marge. Hoe veel te compenseren, wordt bepaald door de datum.
2. De Plot: De waarde te plotten. De truc is hier om ervoor te zorgen dat als de reële waarde kan perceel is ve maar er is nog niet onder nul (omdat de vorige figuur 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 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 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 en een aantal hieronder. De oplossing is om de plot waarde te gebruiken (zie punt 2) tot de helft van de werkelijke waarde plot en gebruik de crossover waarde aan het resterende deel te plotten. Dit is een cruciale test voor een waterval grafiek - het kruis 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: De datum is het niveau dat het uitgangspunt voor de volgende waarde wordt aan het zijn van. De truc is om ervoor te zorgen dat wanneer twee opeenvolgende waarden van 100 en 10, de derde bar om van 110. Maar als de waarden 100 en -10, de derde balk moet 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, het perceel en de crossover - en plaats een gestapelde grafiek in Excel. De resulterende zal iets als dit één zijn:

Verwijder de grafiek rommel. Dubbelklik op de serie die padding 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 Mijn ruimte Kindle Het

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

Wat Denk Jij?


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>


Commentaren en Trackbacks

  1. Jon Peltier schreef:

    "[W] e deed een beetje googlen om te zien of het wiel reeds eerder was uitgevonden ..."

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

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

    Ik heb zelfs bouwde een hulpprogramma om waterval grafieken te maken met een klik op 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 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, "... .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 een fout, de eerste en derde banden verschijnen als de eerste twee schakels terwijl voor "waterval chart '. Maar ze vallen kort presenteren alle stappen met betrekking tot de creatie van een complete waterval grafiek. De tweede link, die je hebt genoemd, vertoont de complete stap voor stap proces, niet voorkomt in de zoekopdracht. Je zou het erover eens dat een aantal van de andere links die voortvloeien uit deze bevraging, doen wijzen op andere pogingen die de verwachtingen vallen.

    Wat ik heb geprobeerd om hier te zien is aan het proces van de oprichting van een dergelijke grafiek te vereenvoudigen door het gebruik van slechts 4 eenvoudige maar consistente formules die eenvoudig kan worden uitgebreid in heel nde rijen op wil en die ook het giswerk betrokken bij weten welke cellen te houden elimineren blanco 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 bij de presentatie van het concept als een nieuwe ontdekking. Groet,

  3. MatthewR schreef:

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

  4. Ajay schreef:

    Matthew - Blij dat ik kon helpen en welkom heten om da tabblad

  5. Biman schreef:

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

  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 te gebruiken, met het voordeel dat de verklaring is vrij duidelijk.

    Big Ups!

  8. Ajay schreef:

    lernr - Bedankt en Welcome to da Tab!

  9. Federico schreef:

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

  10. Ajay schreef:

    Federico - U kunt de volgende code te 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 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

    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.
    Oorzaak in mijn geval heb ik twee variabelen een op "x" as en een op "y" as en ik moet beide ingesteld.
    Kunt u mij helpen op deze manier?
    Alvast bedankt
    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 "x" as en een op "y" as) te krijgen. : Roll: Ik vond de waterval diagram slechts één type bars -hetzij horizontale (langs de x-as) of verticale (langs de y-as). Help me begrijpt dit beter.
    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 te wijzigen) en vermelden hoe je zou willen eindelijk uitzien. Laat me eens kijken.

    Groet,

  15. Federico schreef:

    Ik heb het verzonden nu !!!!

    Dank u voor uw antwoord!

    Federico

  16. Kerri schreef:

    Je bent geweldig!

  17. Ajay schreef:

    Kerri - Thanks. U schommelt ook :-)

  18. Zidane schreef:

    Mijn baas sharted zijn broek toen ik bezorgd deze schoonheid ... Cheers mate

  19. Alek schreef:

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

  20. Albert schreef:

    Zeer nuttig, leunen veel

  21. Hugh schreef:

    Cheers, heeft me gered 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 artikelen?

  23. m schreef:

    geweldig spul, net 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 yearsâ geleden en vond niets, maar dit is precies wat ik zocht dankzij

  25. Jay schreef:

    Grote sjabloon voor de grafiek zelf, met één grote tekortkoming. Ik zie niet in hoe je kunt onder andere 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:

    Dank Ajay. Probeerde dit te doen door mezelf, maar hield vast komen te zitten. Dit echt geholpen 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 getallen. Maar het is geweldig om te kunnen om de formules te gebruiken voor onze eigen doeleinden.

    Gejuich.

  31. Edo schreef:

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

    value = is de waarde te plotten, niet te selecteren voor de grafiek!
    open = sluiten [-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:

    Zeer snel en pijnloos ... nice one jongens.

  33. Carel Viljoen schreef:

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

  34. Carel Viljoen schreef:

    Drie kleine trucjes wrt Edo De 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 + Value zijn;

    3 - In Excel 2007/10/13, om de breedte tussen de spijlen te verminderen - dat wil zeggen dezelfde look als de gewijzigde kolomdiagram in het oorspronkelijke voorbeeld te bereiken - maak de grafiek, wijzig het diagram om praalgraf, wijziging van de kloof tussen de kolommen zoals gewenst 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 serie) in twee verschillende kleuren, zoals één kleur voor positieve en een andere kleur voor negatieve ..
    Vooruitblikkend om te horen van u.

  37. MLH schreef:

    Dit doet niet correct label de datapunten. Negatieve waarden worden weergegeven als positief.

  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
    Krik

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

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

Abonneren

Blijf op de hoogte van de nieuwste verhalen - direct 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