Tipps und Tricks rund um Excel
A | B | C | D | E | F | G | H | I/J | K |
L | M | N | O | P/Q | R | S | T | U-W | X-Z |
Wasserfall aus gestapelten Säulen
- Erstellen in Excel 2016
- Vor- und Nachteile des Wasserfalldiagramms aus Excel 2016
- Wasserfall aus gestapelten Säulen
- Wasserfall mit Linien- und Punkt Diagrammen
In allen Excel-Versionen können Wasserfalldiagramme aus gestapelten Säulen erstellt werden. Das ist der "klassische" Weg, der im Internet immer beschrieben wird.
Achtung! Dieses Vorgehen ist nur sinnvoll, wenn Werte nicht unter den Nullpunkt der Achse fallen! Wird irgendwann ein negatives Ergebnis erreicht, ist der in Kapitel 4 beschriebene Weg wesentlich komfortabler und sicherer.
|
Oben sehen Sie die Stapelung der Balken - der untere Teil bleibt später unsichtbar. Die Tabelle mitsamt dem Diagramm können Sie sich herunterladen; der Link steht oben rechts.
Ausgangspunkt ist eine Tabelle mit einem Start- und Endwert sowie den Veränderungen während des Jahres. Das ist der gleiche Aufbau wie im ersten Teil dieser Reihe. Im weiteren Verlauf der Diagrammerstellung benötigen wir:
- Den Start- und Endwert
- die Werte, die ansteigen (Gewinne)
- die Werte, die abfallen (Verluste)
- eine Hilfsberechnung für den Sockel, auf dem Gewinne und Verluste für jeden Monat aufsetzen
Schreiben Sie zuerst in Spalte B den Startwert, darunter die Zahlen für Januar bis Dezember. Verluste werden als Minuswerte erfasst. Berechnen Sie in B15 die Endsumme mit =SUMME(B2:B14)
|
|
In Spalte C werden die Werte aufgerechnet, sie werden kumuliert. Das ist eine Hilfsberechnung, die wir später für die Hilfsbalken benötigen. Beginnen Sie in C2 mit =SUMME($B$2:B2) und ziehen Sie die Formel nach unten bis C14. Im Dezember müssen Sie den Endwert erreichen. Achten Sie darauf, dass das erste B2 absolut gesetzt wird und das zweite nicht!
In Spalte D übernehmen Sie in D2 und D15 den Start- und Endwert. Tippen Sie in D2 einfach =B2 und in D15 ebenso simpel =B15.
In der Formelansicht sehen die Spalten A bis D jetzt so aus:
|
|
Nun geht es weiter mit den Spalten E und F für den Anstieg bzw. den Abfall. Wir brauchen die Werte für die Monate separiert nach Gewinn (positiv) und Verlust (negativ).
Beginnen Sie in E3 - der Zeile neben "Januar". Wir brauchen nur die positiven Zahlen für den Anstieg, also wird mit WENN abgefragt, ob die Zahl in B3 größer oder gleich Null ist. Dann soll sie übernommen werden, sonst soll eine Null geschrieben werden:
=WENN(B3>=0;B3;0)
Füllen Sie die Formel nach unten aus bis E14.
In Spalte F brauchen wir die Verluste, also alle negativen Zahlen - aber sie müssen hier unbedingt ein positives Vorzeichen haben. Wenn also in B3 eine negative Zahl steht, soll sie übernommen und mit einem positiven Vorzeichen versehen werden. Das geht mit der Multiplikation mit -1 oder durch das Vorsetzen von Minus. Minus Minus ist in der Mathematik Plus.
=WENN(B3<0;-B3;0)
Vergleichen Sie mit der ersten Abbildung, sie müssen jetzt die Zahlen getrennt nach Gewinn und Verlust bekommen. Keine Zahl hat ein Minuszeichen.
Würde ich jetzt ein Diagramm erstellen aus den Spalten A, D, E und F, so sähe das Diagramm so aus. Das ist nicht schlecht, aber noch nicht vollständig. Uns fehlen die Hilfsbalken, die alles nach oben schieben.
|
|
Diese Hilfsbalken müssen sich unter die Anstieg/Abfall-Säulen schieben und den Startpunkt für jeden Monat vorgeben. Ich nutze dafür die Spalte C mit den kumulierten Werten als Ausgangspunkt. Im Diagramm unten habe ich die Werte für die Hilfsbalken eingetragen.
Im Januar ist der Sockel 10.000, dann kommen 1.000 hinzu, so dass der Februar bei 11.000 startet. Leider verlieren wir im März und landen bei 9.000. Der neue Startpunkt für den April ist also 9.000.
In G3 steht die Formel Füllen Sie sie nach unten aus. |
Damit wird jeweils vom kumulierte Stand der Verlust des folgenden Monats abgezogen. Jetzt sind alle Vorbereiten für das Diagramm getroffen. Sie können loslegen.
Markieren Sie A1:A15, drücken Sie die STRG-Taste und markieren Sie D1:G15. Klicken Sie auf "Einfügen | Diagramme: 2D-Säulen - Gestapelte Säulen".
|
|
Sie erhalten ein Diagramm mit gestapelten Säulen - aber in der falschen Reihenfolge. Die Hilfsbalken (bei mir in gelb) sind oben, müssen aber unter die anderen Säulen.
|
|
Bleiben Sie im Diagramm und wählen Sie "Diagrammtools | Entwurf | Daten auswählen". Markieren Sie "Hilfsbalken" und klicken Sie so lange auf den Pfeil nach oben, bis die Reihe ganz oben steht. |
|
Markieren Sie anschließend im Diagramm die Hilfsbalken und wählen Sie im Aufgabenbereich rechts "Füllung: Keine Füllung" und "Rahmen: Keine Linie". Löschen Sie die Benennung "Hilfsbalken" in der Tabelle (bei mir Zelle G1)
18.11.2016