Startseite

Outlook | Excel | Word | PowerPoint | Adobe Acrobat CD | Blog

XING | Facebook |


Pia Bork
Training, Support, Coaching für Office und Filesite


 

Impressum

Creative Commons License  

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 mit Pos./Neg. Abweichung

  1. Erstellen in Excel 2016
  2. Vor- und Nachteile des Wasserfalldiagramms aus Excel 2016
  3. Wasserfall aus gestapelten Säulen
    1. Verbindungslinien
    2. Datenbeschriftung
  4. Wasserfall mit Pos./Neg.-Abweichung
    1. Säulen und Abweichungen verbreitern

Eine sinnvolle Alternative zu den gestapelten Säulen ist die Verwendung der Pos./Neg.-Abweichung. Dabei wird zwischen den Punkten zweier Linien eine Verbindung in Form eines Rechtecks gezeichnet. Dieses Verfahren habe ich von Leila Gharani gelernt, der ich an dieser Stelle nur bestätigen kann, dass ihre Erklärungen amazing sind!

Die Tabelle wird ähnlich aufgebaut wie beim Wasserfall aus gestapelten Säulen.

 

In Spalte A und B werden die Beschriftungen für Kategorien sowie die Werte eingetragen. B2 ist der Startwert. In C wird der jeweils kumulierte Wert bis dahin berechnet. Starten Sie in C3 mit der Formel:
=SUMME($B$2:B2)
und füllen Sie sie nach unten. Achten Sie auf den absoluten Bezug!

In Zelle D3 übernehmen Sie den Startwert aus B3 oder C3 und in D10 übernehmen Sie den Endwert aus C9.

Die Spalten E und F bilden die Linien, deren Datenpunkte durch die Pos./Neg.Abweichung verbunden werden müssen.

In Spalte E steht der Wert vor der Veränderung - also aus der Zeile darüber. Starten Sie in E4 mit =C3.

In Spalte F steht der Wert nach der Veränderung - also aus der gleichen Zeile. Starten Sie in F4 mit =C4. Ziehen Sie beide Formeln nach unten aus.

 

Die Beschriftungspostion in Spalte G gibt vor, an welcher Stelle die Beschriftungen sitzen. Für den Wert "100" aus dem Jahr 2010 muss die Beschriftung auf der Position 600 sitzen; für den Wert "-200" aus dem Jahr 2011 muss die Beschriftung ebenfalls auf 600 sitzen.

Für die Beschriftungspostion erstellen Sie eine WENN-Formel. Beginnen Sie in G4:
=WENN(B4<0;E4;F4)
und ziehen Sie die Formel nach unten aus.

Markieren Sie
A2:A10 und D2:G10

Wählen Sie "Einfügen | Diagramm | Säulen- oder Balkendiagramm | Gruppierte Säulen"

Lassen Sie das Diagramm markiert und wählen Sie "Diagrammtools | Entwurf | Diagrammtyp ändern: Verbund".

 

Deaktivieren Sie für alle Reihen die "Sekundärachse".

Stellen Sie ein:

Start/Ende - gruppierte Säulen

Vor und Nach - Linie

Beschriftung-Pos. - Punkt (XY)

Alles mit OK bestätigen.

 

Markieren Sie die Vor- oder die Nach-Linie, klicken Sie auf das Pluszeichen rechts neben dem Diagramm und aktivieren Sie "Pos./Neg. Abweichung".

 

 

Zwischen jedem Punkt der Vor- und jedem Punkt der Nach-Linie wird jetzt ein Rechteck gezeichnet. Im Beispiel oben sind die Rechtecke weiß, wenn der Unterschied zwischen Vor und Nach positiv ist und schwarz, wenn der Unterschied negativ ist.

Markieren Sie nacheinander die Vor- und die Nach-Linie und formatieren Sie sie ohne Linie. Markieren Sie die positiven Abweichungskästchen (bei mir original weiß) und füllen Sie sie mit grüner Farbe; markieren Sie die negativen Abweichungskästchen (bei mir schwarz) und füllen Sie sie in rot.

Markieren Sie die Reihe "Beschriftung Pos.", das sind die kleinen orangefarbenen Punkte - wählen Sie sie am besten im Aufgabenbereich am rechten Rand aus.

Weisen Sie der Markierung keine Füllung und keine Linie zu.

 

 

Lassen Sie die Reihe "Beschriftung Pos." markiert, klicken Sie auf das Pluszeichen rechts neben dem Diagramm und aktivieren Sie die "Datenbeschriftung"

Markieren Sie die Datenbeschriftung - oder wählen Sie im rechten Aufgabenbereich die "Reihe Beschriftung Pos. Datenbeschriftungen" aus.

 

 

 

Wechseln Sie in die Rubrik "Beschriftungsoptionen". Deaktivieren Sie "Y-Wert", aktivieren Sie "Beschriftungsposition: Über". Aktivieren Sie "Wert aus Zellen" und markieren Sie B3 bis B10.

Achten Sie darauf, dass Sie Start- und Endwert (B3 und B10) mitmarkieren!

Achtung! Dieses Verfahren funktioniert erst ab Excel 2013. Diese Beschriftungen sind nicht sichtbar, wenn Sie die Arbeitsmappe in Excel 2010 oder vorher öffnen. Für ältere Excel-Versionen lassen Sie diesen letzten Schritt aus.

Markieren Sie eine Beschriftung nach der anderen (nicht IN die Beschriftung klicken!) und klicken in die Bearbeitenzeile. Verbinden Sie jede Beschriftung mit der Zelle, in der die Beschriftung steht.

 

 

Der Rest ist Kosmetik: Markieren Sie Start/Ende und fügen Sie ebenfalls die Datenbeschriftung hinzu. Löschen Sie alle überflüssigen Elemente wie Gitterlinien, Legende oder senkrechte Achse. Formatieren Sie die Säulen für Start und Ende in einer anderen Farbe. Verbreitern Sie die Säulen und die Abweichungskästchen.

19.11.2016

 

Kompatibilität

"Beschriftung aus Zellen" kann erst ab Excel 2013 angezeigt werden!

Download

Wasserfall-Diagramm mit "Pos./Neg. Abweichung" (Excel 2016, gezippt)

Quelle

Mit Dank an Leila Gharani, von der ich das gelernt habe!