Umsatzanalyse inklusive Abweichungen

Säulendiagramme sind hervorragend dazu geeignet um Daten miteinander zu vergleichen. Wenn Du die Abweichungen der einzelnen Säulen visuelle hervorheben möchtest, dann füge einfach weitere Datenreihen für die Abweichungsanalyse hinzu. Mit dem Hinzufügen von einer Datenreihe für die Zunahme und Abnahme der Werte kann der Betrachter sehr leicht die Veränderung von einem Wert zum anderen visuell erfassen.

In Excel lässt sich so ein Diagramm mit ein paar trickreichen Kniffen sehr einfach dynamisch erstellen. Das bedeutet, dass die Pfeile keine eingefügten Symbole sind. Es handelt sich dabei um eigene Datenreihen, d.h. wenn sich die Daten ändern, dann verändern sich auch die Pfeile entsprechend. Kein manuelles anpassen des Diagramms ist somit notwendig. Wir wollen ja nicht, dass uns Excel Arbeit macht, sondern das Excel für uns arbeitet.

Also dann, es folgt eine Schritt für Schritt Anleitung anhand einer Fallstudie. Viel Spaß beim Nachmachen.

Du kannst Dir hier das Musterbeispiel runterladen.

Fallstudie: Immobilienumsätze pro Monat inklusive Abweichungen

Der Makler Brainy möchte die Umsätze der verkauften Immobilien vom letzten Jahr analyisieren. Er möchte sowohl den generellen Trend sowie auch einen Vergleich der Umsätze pro Monat sehen. 

Folgende Daten liegen zugrunde:

Abbildung 1: Immobilienumsätze pro Monat

Brainy möchte ein Säulendiagramm erstellen welches nicht nur den Umsatz pro Monat darstellt, sondern auch die Veränderung von einem Monat zum anderen. Dazu muss er seinen Datenbereich zuerst mit den Abweichungsdaten erweitern.

Beim Erstellen der Abweichungsdaten geht er folgendermaßen vor:

1. In Zelle D2 schreibt er folgende Überschrift: Abweichung in Tsd. €
2. In Zelle D3 folgt die Formel: C4-C3. Er zieht diese Formel runter bis Zelle D13.
3. In der Zelle F2 fügt er die Überschrift ein: Abweichungsdatenreihe
4. In Zelle F3 folgt dann die Formel: =MONAT(B3)+0,5 und zieht diese runter bis Zelle D13. Mit der Funktion MONAT wird die Monatszahl aus dem Datum herausgefiltert und durch das addieren von 0,5 erreicht er, dass die Abweichungsdatenreihen genau zwischen den Umsatzsäulen platziert werden. Die Umsatzsäule vom Jänner (1) wird also auf der x-Achse bei 1 angezeigt, und bei 1,5 wird dann der Wert für die Abweichung angezeigt. Bei 2 wird der Umsatz für Februar angezeigt und bei 2,5 wieder die Abweichung usw.
5. In der Zelle G2 fügt er folgende Überschrift ein: Zunahme
6. In Zelle G3 folgt dann die Formel: =WENN(D3>0;C4;NV()) und zieht diese runter bis G13. Das definiert den Y-Wert, wenn die Abweichung positiv und somit zunehmend ist. Die Funktion NV() erzeugt den Error Wert #NV. Brainy erzeugt diesen Fehlerwert absichtlich, denn Zellen, die diesen Fehlerwert enthalten, werden im Diagramm nicht dargestellt.
7.  In Zelle H2 fügt er folgende Überschrift ein: Abnahme
8. In Zelle H3 folgt dann die Formel: =WENN(D3<0;C4;NV()) und zieht diese runter bis H 13. Das definiert den Y-Wert, wenn die Abweichung negativ und somit abnehmend ist. Weil Brainy für die zunehmende und abnehmende Abweichung eigene Datenreihen verwendet, kann er diese dann später unterschiedlich formatieren.
9. Das Zahlenformat für die Zellen C3:C14 und G2:H13 ist folgendes: € #.##0. Der Punkt nach der Null bewirkt, dass der Wert in tausend (Wert/1000) angezeigt wird. Beachte jedoch, die Zahl wird nicht wirklich durch 1.000 dividiert sondern nur in tausend formatiert. Du kannst das leicht überprüfen, indem Du eine Zelle anklickst und in die Bearbeitungsleiste schaust, dort siehst Du nach wie vor den kompletten Betrag.

Die fertige Datentabelle sollte nun wie folgt aussehen:

Abbildung 2: Fertige Datentabelle mit Abweichungsdatenreihen

Brainy hat die Daten sauber vorbereitet. Es ist nun an der Zeit, mit dem Erstellen des Diagramms zu beginnen. 

Markiere dazu den Datenbereich von B2:C14 und wähle aus dem Menü: Einfügen –> Empfohlene Diagramme –> Gruppierte Säulen und bestätige mit OK.

Abbildung 3: Gruppiertes Säulendiagramm einfügen

Die Umsatzdaten sind somit grafisch dargestellt. Im nächsten Schritt geht es daran, die Abweichungsdaten zu diesem bereits erstellten Diagramm hinzuzufügen. Dabei gehst Du folgendermaßen vor:

1. Markiere und kopiere (Strg + C) den Datenbereich F2:H13.
2. Wähle das Diagramm aus.
3. Menü: Start –> Einfügen –> Inhalte einfügen…

Abbildung 4: Abweichungsdaten einfügen 1/2

4. Wähle die Optionen wie im Screenshot unten angezeigt aus und klicke dann auf OK.

Abbildung 5: Abweichungsdaten einfügen 2/2

5. Standardmäßig werden jetzt für die Abweichungsdaten auch gruppierte Säulen eingefügt. Um das zu ändern, klicke mit der rechten Maustaste auf irgendeine Säule und wähle Datenreihen-Diagrammtyp ändern…

Es erscheint folgendes Menü. Dort wählst Du für die Datenreihen Zunahme und Abnahme jeweils den Diagrammtyp Punkt (XY). Achte darauf, dass bei Sekundärachse die Haken weg sind. Klick jetzt auf OK.

Abbildung 6: Diagrammtyp für Abweichungsdaten ändern auf Punkt (XY).

Das Diagramm sollte nun wie unten aussehen. Die Abweichungsdaten werden jetzt zwischen den existierenden Umsatzdaten als Punkt angezeigt und die Datenreihen für die Zunahme und Abnahme haben eine unterschiedliche Farbe (Orange und Grau).

Jeder Y-Wert für die Abweichungsdaten ist nun genau auf dem gleichen Level wie die jeweilig darauffolgenden Umsatzdaten. 

Abbildung 7: Diagramm mit Abweichungsdaten

Jetzt verwenden wir die Diagrammelemente Fehlerindikatoren, Farbe, Pfeile und Datenbeschriftung um die Formatierung der Abweichungsdaten abzuschließen.

1. Wähle die Datenreihe für die Zunahme aus (oranger Punkt) und selektiere dann aus den Diagrammelementen “Fehlerindikatoren“. Excel fügt beide, vertikale und horizontale Fehlerindikatoren hinzu. Das Diagramm sollte jetzt wie folgt aussehen.

Abbildung 8: Diagramm mit vertikalem und horizontalem Fehlerindikator für die Datenreihe “Zunahme”.

2. Wähle irgendeine horizontale Fehlerindikator-Linie aus und drücke die Entf-Taste. Es sollte jetzt nur die vertikale Linie übrig sein. 

3. Rechtsklick auf eine vertikale Linie –> Fehlerindikatoren formatieren…

4. Linienende: Ohne Abschluss

5. Fehlerbetrag: Benutzerdefiniert

    Positiver Fehlerwert: ={0}

    Negativer Fehlerwert: =’3_Setup_Abweichungspfeile’!$D$3:$D$13

6. Klick OK.

Abbildung 9: Fehlerindikatoren formatieren

Das Diagramm sieht wie folgt aus.

Abbildung 10: Fehlerindikatoren formatieren – Datenreihe Zunahme

7. Ändere die Farbe des Fehlerindikators auf grün und die Stärke auf 1,25 Pt.

8. Ändere den Startpfeiltyp auf Pfeil.

Abbildung 11: Fehlerindikator Pfeil formatieren – Datenreihe Zunahme 

9. Wähle den Datenpunkt für Zunahme aus und drücke dann Strg + 1 (öffnet Formatierungseinstellungen). Danach –> Füllung und Linie –> Markierung –> Markierungsoptionen –> Keine.

Abbildung 12: Datenreihe Punkt “Zunahme” unsichtbar machen

Abbildung 13: Fertig formatierter Fehlerindikator-Pfeil für die Datenreihe “Zunahme”

Wiederhole jetzt die Schritte zur Formatierung des Fehlerindikators 1 – 9 exakt gleich für die Datenreihe “Abnahme”, aber wähle für den Pfeil die Farbe rot.

HINWEIS: Der benutzerdefinierte Fehlerindikatoren-Dialog ist identisch mit dem Dialog für die Datenreihe Zunahme. Beide verwenden nur negative Fehlerwerte und das Vorzeichen der Zahl in Spalte D bestimmt, in welche Richtung die Pfeile gehen. Aus diesem Grund verwende den Startpfeiltyp für beide Fehlerindikatoren.

Das Diagramm mit beiden Fehlerindikator-Pfeilen sollte jetzt wie folgt aussehen.

Abbildung 14: Fertig formatierter Fehlerindikator-Pfeile für beide Datenreihen “Zunahme” und “Abnahme”.

Jetzt geht es nur mehr darum, die 3 Regeln für effektive Datenvisualisierung anzuwenden. 

Regel 1: Halte es einfach. Alles aus der Visualisierung entfernen, was nicht notwendig ist.

Regel 2: Die Aufmerksamkeit des Publikums in der Visualisierung lenken, d.h. das was wichtig ist muss hervorgehoben werden.

Regel 3: Verständliches Design, der Feinschliff. Die Visualisierung muss auf den ersten Blick verständlich sein. Wir fügen also einen aussagekräftigen Titel, Datenbeschriftungen und erläuternde Texte ein.

Das finale Diagramm sieht folgendermaßen aus.

Abbildung 15: Finales Diagramm “Immobilienumsätze pro Monat inklusive Abweichungen”

Brainy hat sich wieder einmal selbst übertroffen. Das Diagramm bietet nicht nur einen Überblick über die Umsätze sondern auch einen schnellen Einblick über die Veränderungen pro Monat.

Zudem hat es Brainy geschafft, dieses Diagramm nachhaltig zu gestalten. Bei jeder Aktualisierung der Daten ändern sich die Balken, Pfeile und Datenbeschriftungen vollautomatisch entsprechend der neuen Werte.

So soll es sein, automatisch und zeitsparend, schwärmt Brainy im Gedanken vor sich hin und spürt deutlich das Aufkommen einer gesteigerten Zuneigung zu Excel in diesem Moment. 

Brainy überlegt schon, was er mit der künftig gewonnen Zeit anfangen sollte. Er überlegt ob er in das abenteuerliche Geschäft mit…, aber das ist eine andere Geschichte.