So erstellen Sie ein Wasserfalldiagramm

Immer öfter treffe ich mich in der Berichterstattung verschiedener Unternehmen und höre Anfragen von Auszubildenden, zu erklären, wie ein Kaskadendiagramm von Abweichungen aufgebaut ist – es ist auch ein „Wasserfall“, es ist auch ein „Wasserfall“, es ist auch eine „Brücke“. “, es ist auch eine „Brücke“ usw. . Es sieht in etwa so aus:

Aus der Ferne sieht es wirklich aus wie eine Kaskade von Wasserfällen an einem Gebirgsfluss oder eine Hängebrücke – wer sieht was 🙂

Die Besonderheit eines solchen Diagramms besteht darin, dass:

• Wir sehen deutlich den Anfangs- und Endwert des Parameters (die erste und letzte Spalte).
• Positive Veränderungen (Wachstum) werden einfarbig dargestellt (meist grünen) und negative (Ablehnung) für andere (normalerweise roten).
  
• Manchmal kann das Diagramm auch Zwischensummenspalten enthalten (grauauf den x-Achsen-Spalten gelandet).
  

Im Alltag werden solche Diagramme meist in folgenden Fällen verwendet:

• visuell Dynamikanzeige jeder Prozess in der Zeit: Cashflow (Cash-Flow), Investitionen (wir investieren in ein Projekt und profitieren davon).
• Visualisierung Umsetzung planen (Die Spalte ganz links im Diagramm ist eine Tatsache, die Spalte ganz rechts ist ein Plan, das gesamte Diagramm spiegelt unseren Prozess wider, uns dem gewünschten Ergebnis zu nähern.)
• Wenn Sie visuelles brauchen Faktoren zeigendie unseren Parameter beeinflussen (faktorielle Analyse des Gewinns – woraus er besteht).
  

Es gibt mehrere Möglichkeiten, ein solches Diagramm zu erstellen – alles hängt von Ihrer Version von Microsoft Excel ab.

Methode 1: Einfachste: Integrierter Typ in Excel 2016 und neuer

Wenn Sie Excel 2016, 2019 oder höher (oder Office 365) haben, ist das Erstellen eines solchen Diagramms nicht schwierig – diese Versionen von Excel haben diesen Typ bereits standardmäßig integriert. Es muss nur eine Tabelle mit Daten ausgewählt und auf der Registerkarte ausgewählt werden Insert (Einfügen) Befehl Cascading (Wasserfall):

Als Ergebnis erhalten wir ein fast fertiges Diagramm:

Sie können sofort die gewünschten Füllfarben für die positiven und negativen Spalten festlegen. Am einfachsten geht das, indem Sie die entsprechenden Zeilen markieren Erhöhung и verringern direkt in der Legende und per Rechtsklick darauf den Befehl auswählen Füllen (Füllen):

Wenn Sie Spalten mit Zwischensummen oder eine endgültige Spaltensumme zum Diagramm hinzufügen müssen, ist dies am bequemsten mit Funktionen möglich ZWISCHENSUMME (ZWISCHENSUMME) or EINHEIT (AGGREGAT). Sie berechnen den kumulierten Betrag vom Beginn der Tabelle an, wobei sie die oben aufgeführten ähnlichen Summen ausschließen:

In diesem Fall ist das erste Argument (9) der Code der mathematischen Summationsoperation, und das zweite (0) bewirkt, dass die Funktion bereits berechnete Summen für vorherige Quartale in den Ergebnissen ignoriert.

Nach dem Hinzufügen von Zeilen mit Summen müssen noch die im Diagramm erschienenen Summenspalten ausgewählt werden (zwei aufeinanderfolgende Einzelklicks auf die Spalte) und durch Klicken mit der rechten Maustaste den Befehl auswählen Als Summe festlegen (Als Summe setzen):

Die ausgewählte Spalte landet auf der x-Achse und ändert automatisch ihre Farbe in Grau.

Das ist eigentlich alles – fertig ist das Wasserfalldiagramm:

Methode 2. Universell: unsichtbare Spalten

Wenn Sie Excel 2013 oder ältere Versionen (2010, 2007 usw.) haben, funktioniert die oben beschriebene Methode nicht für Sie. Sie müssen herumgehen und das fehlende Wasserfalldiagramm aus einem normalen gestapelten Histogramm ausschneiden (wobei die Balken übereinander summiert werden).

Der Trick dabei ist, transparente Prop-Spalten zu verwenden, um unsere roten und grünen Datenzeilen auf die richtige Höhe zu bringen:

Um ein solches Diagramm zu erstellen, müssen wir den Quelldaten einige weitere Hilfsspalten mit Formeln hinzufügen:

• Zuerst müssen wir unsere ursprüngliche Spalte aufteilen, indem wir die positiven und negativen Werte mithilfe der Funktion in separate Spalten aufteilen IF (WENN).  
• Zweitens müssen Sie eine Spalte vor den Spalten hinzufügen Schnuller, wobei der erste Wert 0 ist, und ausgehend von der zweiten Zelle berechnet die Formel die Höhe dieser sehr transparenten Stützsäulen.
  

Danach bleibt die gesamte Tabelle mit Ausnahme der ursprünglichen Spalte auszuwählen Flow und erstellen Sie ein regelmäßiges gestapeltes Histogramm Einschub — Histogramm (Einfügen – Säulendiagramm):

Wenn Sie nun die blauen Spalten auswählen und unsichtbar machen (Rechtsklick darauf – Zeilenformat – Füllung – Keine Füllung), dann bekommen wir einfach das, was wir brauchen. 

Der Vorteil dieser Methode ist die Einfachheit. In den Minuspunkten – die Notwendigkeit, Hilfsspalten zu zählen.

Methode 3. Wenn wir ins Minus gehen, ist alles schwieriger

Leider funktioniert die bisherige Methode nur bei positiven Werten ausreichend. Wenn unser Wasserfall zumindest in einigen Bereichen in einen negativen Bereich geht, dann steigt die Komplexität der Aufgabe deutlich an. In diesem Fall muss jede Reihe (Dummy, Grün und Rot) separat für den negativen und positiven Teil mit Formeln berechnet werden:

Um nicht viel zu leiden und das Rad nicht neu zu erfinden, kann eine fertige Vorlage für einen solchen Fall im Titel dieses Artikels heruntergeladen werden.

Methode 4. Exotisch: Auf-Ab-Bänder

Diese Methode basiert auf der Verwendung eines speziellen, wenig bekannten Elements von flachen Diagrammen (Histogramme und Grafiken) – Auf-Ab-Bänder (Aufwärts-Abwärts-Balken). Diese Bänder verbinden die Punkte zweier Graphen paarweise, um deutlich zu zeigen, welcher der beiden Punkte höher oder niedriger ist, was bei der Visualisierung des Plan-Fakts aktiv genutzt wird:

Es ist leicht herauszufinden, dass wir den gleichen „Wasserfall“ erhalten, wenn wir die Linien der Diagramme entfernen und nur die Aufwärts-Abwärts-Bänder auf dem Diagramm belassen.

Für eine solche Konstruktion müssen wir unserer Tabelle zwei weitere zusätzliche Spalten mit einfachen Formeln hinzufügen, die die Position der beiden erforderlichen unsichtbaren Graphen berechnen:

Um einen „Wasserfall“ zu erstellen, müssen Sie eine Spalte mit Monaten (für Signaturen entlang der X-Achse) und zwei zusätzliche Spalten auswählen Planen Sie 1 ein и Planen Sie 2 ein und erstellen Sie ein regelmäßiges Diagramm für den Anfang mit Einfügen – Grafik (Einfügen — Liniendiagramm):

Jetzt fügen wir unserem Diagramm Up-Down-Bänder hinzu:

• In Excel 2013 und neuer muss dies auf der Registerkarte ausgewählt werden Bauherr Befehl Diagrammelement hinzufügen — Anstiegs-Abnahme-Bänder (Design – Diagrammelement hinzufügen – Up-Down-Balken)
• In Excel 2007-2010 – gehen Sie zur Registerkarte Layout – Vorwärts-Rückwärts-Balken (Layout – Up-Down-Balken)

Das Diagramm sieht dann etwa so aus:

Es bleibt, die Diagramme auszuwählen und transparent zu machen, indem man sie der Reihe nach mit der rechten Maustaste anklickt und den Befehl auswählt Datenreihenformat (Formatreihe). Ebenso können Sie die standardmäßigen, eher schäbig aussehenden Schwarz-Weiß-Streifenfarben in Grün und Rot ändern, um am Ende ein schöneres Bild zu erhalten:

In den neuesten Versionen von Microsoft Excel kann die Breite der Balken geändert werden, indem man mit der rechten Maustaste auf einen der transparenten Graphen (nicht die Balken!) klickt und den Befehl auswählt Datenreihenformat – Seitenabstand (Formatreihe — Lückenbreite).

In älteren Versionen von Excel mussten Sie den Visual Basic-Befehl verwenden, um dies zu beheben:

1. Markieren Sie das erstellte Diagramm
2. Drücken Sie die Tastenkombination Andere+F11um in den Visual Basic Editor zu gelangen
3. Drücken Sie die Tastenkombination Ctrl+Gum die direkte Befehlseingabe und das Debug-Panel zu öffnen unmittelbar (befindet sich normalerweise unten).

4. Kopieren Sie den folgenden Befehl und fügen Sie ihn dort ein: ActiveChart.ChartGroups(1).GapWidth = 30 und drücken Sie Enter:

Sie können natürlich mit dem Parameterwert herumspielen, wenn Sie möchten. Spaltbreiteum den gewünschten Abstand zu erreichen:

• So erstellen Sie ein Bullet-Diagramm in Excel, um KPIs zu visualisieren  
• Was ist neu in Diagrammen in Excel 2013
• So erstellen Sie ein interaktives „Live“-Diagramm in Excel