Excel-Tipp: Wasserfall-Chart mit Abweichungslinien

Premium

Wenn in Excel Abweichungen analysiert werden sollen, ist hier das Wasserfall-Diagramm ein nützliches Werkzeug.
Dieses aufzubauen ist schon eine gewisse Herausforderung. Komplizierter wird es, wenn Sie die Gesamtabweichung in dem Diagramm visualisieren möchten. Umsetzen können Sie dies mit Pfeilen und Linien. Aber der Reihe nach.

Grundgerüst Wasserfalldiagramm
Zunächst bauen wir das klassische Wasserfall-Diagramm für die Abweichungsanalyse von 10 Produkten auf.

Tragen Sie 10 Produktnamen (hier Produkt 1 bis 10) untereinander in eine Excel Tabelle ein.
Daneben schreiben Sie die Ist- und VJ- bzw. Plan-Werte
Nachdem Sie diese Werte eingetragen haben, kommen die absoluten und prozentualen Abweichungen hinzu.
Den Abschluss bildet eine Hilfsspalte. Verwenden Sie hier folgende Formel:

=F7-ZEILE()/1000000

Die Zelle F7 bezieht sich hier auf den absoluten Abweichungswert. Diese Daten brauchen wir später für die Sortierung der Abweichungen.



Erstellen Sie nun rechts von der Eingabe-Tabelle eine zweite Tabelle. Beginnen Sie mit Produkt. Da drunter schreiben Sie VJ bzw. Plan. Lassen Sie nun 10 Zeilen frei und schreiben Sie jetzt “IST“ in die Tabelle. Rechts vom VJ/Plan und IST verknüpfen Sie die Zellen mit den Werten aus der Eingabe-Tabelle.



Unter dem VJ/Plan schreiben Sie nun folgende Formel (in diesem Beispiel)

=INDEX($C$7:$C$16;VERGLEICH(KGRÖSSTE($H$7:$H$16;ZEILE(H1));$H$7:$H$16;0))

Mit dieser Formel wird erreicht, dass Ihnen das Produkt mit der höchsten positiven Abweichung als erstes angezeigt wird. Wenn Sie die Formel nun bis zum IST hinunterkopieren, werden Ihnen die Produkte mit den absteigenden Abweichungswerten angezeigt.



Lassen Sie neben den Produkten eine Spalte frei und ziehen Sie sich die Abweichungswerte mithilfe der SVERWEIS-Formel pro Produkt.



Rechts neben dem Produkt, welches an erster Stelle steht, hier Produkt 7, schreiben Sie folgende Formel:

=WENN(L8>0;K7;K7+L8)

Mit dieser Formel wird geprüft, ob es sich bei dem ersten Abweichungswert um eine positive Zahl handelt, dann kann der VJ/Plan Wert übernommen werden. Sollte es sich um einen negativen Abweichungswert handeln (tritt ein, wenn alle Produkte das Ziel verfehlten) wird hier die Differenz aus dem VJ/Plan-Wert und erstem Abweichungswert ermittelt.

Für die nächste Formel wird es etwas komplizierter:

=WENN(UND(L8>0;L9>0);K8+L8;WENN(UND(L9>0;L8<0);K8;WENN(UND(L9<0;L8<0);K8+L9;L9+K8+L8)))

Gehen wir einmal die möglichen Variablen durch:

1. der erste und folgende Abweichungswert sind beide positiv
2. der folgende Wert ist positiv und der erste Wert ist negativ
3. der erste und folgende Wert sind beide negativ
4. der erste Wert ist positiv und der folgende Wert ist negativ

Diese vier Variablen sind nun in der Formel eingebaut.
Hintergrund ist, dass es sich bei dem Diagramm um ein stufenartiges bzw. gestapeltes Balken-Diagramm handelt, damit alles funktioniert.

Zunächst benötigen wir in der Tabelle allerdings noch die Info, ob es sich um einen positiven bzw. negativen Abweichungswert handelt und, dass wenn ein negativer Abweichungswert vorliegt, dieser positiv angezeigt wird. Dies ist notwendig, damit im Diagramm der rote Balken im positiven und nicht im negativen Bereich der Achse angezeigt wird.

Für die negativen Abweichungswerte verwenden Sie folgende Formel, damit Ihnen die Werte absolut dargestellt werden:

=WENN(L8<0;ABS(L8);0)

Und bei den positiven Werten diese Formel

=WENN(L8>0;L8;0)



Erstellung des  Diagramms

Verwenden Sie ein gestapeltes Balken-Diagramm. Die erste Datenreihe nennen Sie Kalkulation. Als Datenquelle verwenden Sie, in diesem Beispiel die Zellen K7 bis K22 Sie nehmen nach dem letzten Wert (IST) noch 4 freie Zeilen mit, die keine Werte enthalten.

Entfernen Sie im Diagramm die X-Achse, die Legende und den Titel. Bei der Y-Achse entfernen Sie die Werte und den Hauptstrich-Typ. Färben Sie die Y-Achse auf schwarz und setzen Sie die Stärke auf 3,0.
Im Anschluss lassen Sie sich die Werte in umgekehrter Reihenfolge anzeigen.
Machen Sie einen Doppelklick auf die Balken und stellen Sie die Abstandsbreite auf 0%.

Das Diagramm sieht jetzt wie folgt aus:



Bis auf den ersten und letzten Balken stellen Sie die Füllfarbe auf “Keine Füllung“

Den VJ/Plan Wert markieren Sie mit einer grauen und den IST-Wert mit einer schwarzen Farbe.
Als nächstes fügen Sie die 2. Datenreihe hinzu und verwenden die positiven Abweichungswerte. Fügen Sie auch wieder die leeren Zeilen hinzu.
Die Farbe der positiven Balken setzen Sie auf ein dunkles grün.
Die dritte Datenreihe bezieht sich auf die negativen Abweichungswerte. Benutzen Sie hier eine rote Farbe.

(9 Seiten)

Letzte Änderung W.V.R am 13.09.2019
Autor(en): Kristoffer Ditz