Hoe wordt 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 van 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 waarbij 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 template, om 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 stack grafieken.) Nu hebben we niet in het bijzonder als 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, maar er waren een groot aantal grafieken 'part-waterval-part-bar' 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 wordt hier getoond.

wrong-waterfall-chart

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

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

We vervolgens toegevoegd vier extra set van formules - een voor het werkelijke aantal, de padding (of offset), 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 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 onderdelen van de Waterval Grafiek:
1. De Vulling: Aangezien elk van bars in een waterval grafiek niet beginnen op 0, moet je ze (te verheffen of te drukken) gecompenseerd door een zekere marge. Hoeveel te compenseren, wordt bepaald door de datum.
2. The Plot: De waarde om te plotten. De truc is hier om ervoor te zorgen dat als de werkelijke waarde op perceel is ve maar er is nog niet onder nul (omdat de vorige cijfer was veel te positief), het cijfer wordt gebruikt voor het daadwerkelijk het plotten van de bar heeft om boven de as, maar bewegende zuidwaarts. En vice versa. In dergelijke gevallen is de bekleding moet worden aangepast.

3. De crossover Soms zullen we een situatie waarin de plot waarde zodanig dat deze beweegt over de X-as tegen. Bijvoorbeeld, het eerste cijfer was 1000 en het tweede cijfer (degene die we nodig hebben om plot) is -2.000. 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) tot de helft van de werkelijke waarde plot en gebruik de crossover-waarde voor de resterende deel plot. Dit is een belangrijke test voor een waterval chart - het kruis over serie automatisch aanpassen wanneer de volgende waarde veroorzaakt de beweging over de as van de onderste helft op het bovenste of vice-versa.
4. De datum: De datum is het niveau dat het uitgangspunt voor de volgende waarde wordt aan het zijn uit. De truc is om ervoor te zorgen dat wanneer twee opeenvolgende waarden 100 en 10, de derde balk moet worden van 110. Maar als de waarden 100 en -10, de derde balk moet beginnen vanaf 90.

De formules voor dat we voor ieder 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 steek een gestapelde grafiek in Excel. De resulterende zal iets als dit één zijn:

Verwijder de grafiek rommel. Dubbelklik op de reeks die padding en maken het transparant en VIOLA vertegenwoordigt ... ..your waterval grafiek is klaar 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>


Opmerkingen en Trackbacks

  1. Jon Peltier schreef:

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

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

    Een tweede les behandelt gevallen waarin de bars steken de horizontale as:
    http://peltiertech.com/Excel/Charts/waterfallcrossing.html

    Ik heb zelfs bouwde een hulpprogramma om de waterval grafieken 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 oprichting van een compleet waterval grafiek. De tweede link, die u hebt gezegd, niet tonen de complete stap voor stap proces, niet in het zoeken. Je zou het erover eens dat sommige van de andere links die het gevolg zijn van een dergelijk onderzoek, doe punt naar andere pogingen die de verwachtingen valt.

    Wat ik heb geprobeerd om hier laten zien is om 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 over n rijen op wil en die ook het giswerk betrokken bij weten welke cellen aan het houden van te 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 zeker niet een poging bij de presentatie van het concept van 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 tabblad

  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 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 de breedte van de kolommen?
    Is het mogelijk?
    Dankjewel!!!

  10. Ajay schreef:

    Federico - U kunt de volgende code gebruiken om de breedte van de kolommen van de waterval grafiek veranderen. U kan nodig zijn om de code te hacken een beetje maar aan 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

    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 moeten 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) krijgen. : Roll: Ik vond de waterval diagram slechts één type bars -hetzij horizontale (langs de x-as) of verticale (langs de y-as). Help mij te 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 bevestiging (de waterval grafiek die u probeert te wijzigen) en vermelden hoe je zou willen eindelijk uitzien. Laat me een kijkje nemen.

    Met vriendelijke groet,

  15. Federico schreef:

    Ik heb het direct 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:

    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 de kleur van de kolommen voor de lasten te wijzigen in een andere kleur dan de kolommen voor de inkomsten items?

  23. km schreef:

    geweldige dingen, maar herhaald in excel en gebruikte mijn eigen gegevens, deze 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 u kunt opnemen labels op de standplaatsen. Het zal niet tonen 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:

    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.

    Proost.

  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-close":

    value = is de waarde om te plotten, niet 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. Probeer.

  32. Carel Viljoen schreef:

    Zeer snel en pijnloos ... nice one jongens.

  33. Carel Viljoen schreef:

    Edo - net geprobeerd jou ook. Mooi in zijn eenvoud.

  34. Carel Viljoen schreef:

    Drie kleine trucjes tov 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 + Value zijn;

    3 - In Excel 2007/10/13, om de breedte tussen de staven te verminderen - dat wil zeggen dezelfde look als de gewijzigde kolomdiagram in de originele voorbeeld te bereiken - maak de grafiek wijzigt het diagramtype te bundelpijler, wijziging van de kloof tussen de kolommen zoals gewenst en terug in op de Open-High-Low-Sluiten formaat.

  35. Di Kelly schreef:

    Schitterend - dank u!

  36. tomtom schreef:

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

  37. MLH schreef:

    Dit betekent niet juist etiket de datapunten. Negatieve waarden worden weergegeven als positief.

  38. Jack schreef:

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

  39. David Herrington schreef:

    Ajay,

    Goed gedaan. Mooi en schoon. Helaas, ik ben niet een gevorderde gebruiker en terwijl de download is eenvoudig te gebruiken, het is juist de helft van een project Ik probeer om te voltooien. Ik zou graag in staat zijn om de stroom van de waterval keren, te beginnen bij het cumulatieve totaal en eindigt bij 0. De bedoeling is om 2 grafieken weer gebruiken om een ​​back als een vergelijkende analyse van de kosten van verkochte goederen gebaseerd op 2 afzonderlijke processen. Elke kans op een richting of een hand op deze?

    David

  40. Dan schreef:

    Het probleem dat ik heb nu met deze is het toevoegen van data labels. Het bedrag dat wordt ingevoerd is de plot bedrag ten opzichte van de werkelijke bedrag.

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

    [...] De data punt dat u wilt zien. 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-diagram. Tijdens het gebruik van een grafiek in uw Excel [...]

Abonneren

Blijf op de hoogte van de nieuwste verhalen - recht geleverd aan uw 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

LAATSTE BERICHTEN