Pfeil-Varianzdiagramm in Excel erstellen

In Excel ist das Pfeil-Varianz- oder Differenzdiagramm eine spezielle Form des gruppierten Säulen- oder Balkendiagramms, das mithilfe von Aufwärts- oder Abwärtspfeilen die prozentuale Zunahme bzw. Abnahme zwischen zwei Datensätzen veranschaulicht.

Möchten Sie beispielsweise die Umsatzabweichung im Vergleich zum Vorjahr veranschaulichen, können Sie grüne und rote Pfeile nutzen, um die prozentuale Veränderung – wie im folgenden Screenshot dargestellt – anschaulich hervorzuheben. In diesem Artikel zeige ich Ihnen Schritt für Schritt, wie Sie diese anspruchsvolle Diagrammart in Excel erstellen.

• Pfeil-Varianz- oder Differenzdiagramm in Excel erstellen
• Pfeil-Varianz- oder Differenzdiagramm in Excel mit einer praktischen Funktion erstellen
• Beispieldatei für Pfeil-Varianz- oder Differenzdiagramm herunterladen
• Video: Pfeil-Varianz- oder Differenzdiagramm in Excel erstellen

Pfeil-Varianz- oder Differenzdiagramm in Excel erstellen

Um ein Pfeil-Varianzdiagramm zur Darstellung der prozentualen Differenzveränderung des Jahres 2020 im Vergleich zum Jahr 2019 zu erstellen, sollten Sie zunächst einige Hilfsspalten einfügen, um die Basisdaten wie im folgenden Screenshot gezeigt zu berechnen:

Erstellen Sie zunächst die Hilfsspalten-Daten

1. Geben Sie in Zelle D2 neben den Originaldaten die folgende Formel ein, und ziehen Sie sie bis Zelle D10 herunter – siehe Screenshot:

2. Geben Sie in Zelle E2 die folgende Formel ein und kopieren Sie sie bis Zelle E10 – siehe Screenshot:

3.Geben Sie anschließend die folgende Formel in Zelle F2 ein, und kopieren Sie die Formel bis Zelle F10, siehe Screenshot:

4. Geben Sie anschließend in Zelle G2 die folgende Formel ein und ziehen Sie sie bis zu Zelle G10, um die Formel automatisch auszufüllen – siehe Screenshot:

5. Geben Sie danach folgende Formel in Zelle H2 ein und ziehen Sie sie bis Zelle H10 herunter – siehe Screenshot:

6. Geben Sie danach die folgende Formel in Zelle I2 ein, und ziehen Sie den Ausfüllknauf bis zur Zelle I10 – siehe Screenshot:

Zweitens: Erstellen Sie das Pfeil-Varianzdiagramm basierend auf den Hilfsspalten-Daten

7. Markieren Sie nach dem Erstellen der Hilfsdaten die Daten in den Spalten A, B, C, D und E, und klicken Sie anschließend auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Säulen. Siehe Screenshot:

8. Es wird ein gruppiertes Säulendiagramm eingefügt, wie im folgenden Screenshot gezeigt:

9. Klicken Sie dann auf das Diagramm und wählen im Kontextmenü Daten auswählen aus – siehe Screenshot:

10. Wählen Sie im erscheinenden Dialogfeld Daten auswählen – Quelle im Listenfeld Bereich der Reihennamen die Datenreihe Max. links aus, und klicken Sie auf die Schaltfläche mit dem nach oben zeigenden Pfeil, um diese Datenreihe nach oben zu verschieben – siehe untenstehende Demo:

11. Klicken Sie anschließend auf die Schaltfläche OK, um das Dialogfeld zu schließen. Klicken Sie dann mit der rechten Maustaste auf die Datenreihe Max. links und anschließend auf die Schaltfläche Diagrammelemente, um das Listenfeld zu erweitern. Aktivieren Sie im Listenfeld das Kontrollkästchen Fehlerindikatoren – siehe Screenshot:

12. Klicken Sie weiterhin mit der rechten Maustaste auf die Datenreihe Max. rechts und anschließend auf die Schaltfläche Diagrammelemente, um das Listenfeld zu erweitern. Aktivieren Sie im Listenfeld das Kontrollkästchen Fehlerindikatoren – siehe Screenshot:

13. Klicken Sie danach mit der rechten Maustaste auf die vertikalen Fehlerindikatoren Max. links Y und wählen Sie im Kontextmenü Fehlerindikatoren formatieren aus, siehe Screenshot:

14. Wählen Sie im geöffneten Bereich Fehlerindikatoren formatieren unter der Registerkarte Optionen für Fehlerindikatoren im Abschnitt Vertikale Fehlerindikatoren die folgenden Einstellungen vor:

• Wählen Sie Minusunter dem Abschnitt Richtungaus;
• Wählen Sie anschließend Keine Endkappeim Abschnitt Endstilaus;
• Wählen Sie danach im Abschnitt Fehlerbetrag die Option Benutzerdefiniert aus, klicken Sie auf die Schaltfläche Wert festlegen, und geben Sie im erscheinenden Dialogfeld Benutzerdefinierte Fehlerindikatoren im Textfeld Positiver Fehlerwert nichts ein. Wählen Sie stattdessen im Textfeld Negativer Fehlerwert die Daten aus der Spalte „Aufwärts-Pfeil“ aus, und klicken Sie anschließend auf OK, um das Dialogfeld zu schließen.

15. Lassen Sie den Bereich Fehlerindikatoren formatieren geöffnet und klicken Sie dann im Diagramm auf die vertikalen Fehlerindikatoren Max. rechts Y. Wählen Sie unter der Registerkarte Optionen für Fehlerindikatoren im Abschnitt Vertikale Fehlerindikatoren die folgenden Einstellungen vor:

• Wählen Sie Minusunter dem Abschnitt Richtungaus;
• Wählen Sie anschließend Keine Endkappeim Abschnitt Endstilaus;
• Wählen Sie danach im Abschnitt Fehlerbetrag die Option Benutzerdefiniert aus, klicken Sie auf die Schaltfläche Wert festlegen, und geben Sie im erscheinenden Dialogfeld Benutzerdefinierte Fehlerindikatoren im Textfeld Positiver Fehlerwert nichts ein. Wählen Sie stattdessen im Textfeld Negativer Fehlerwert die Daten aus der Spalte „Abwärts-Pfeil“ aus, und klicken Sie anschließend auf OK, um das Dialogfeld zu schließen.

16. Formatieren Sie anschließend die Datenreihen „Max. links“ und „Max. rechts“ als unsichtbar. Klicken Sie dazu mit der rechten Maustaste auf den Datenbalken „Max. links“ und wählen Sie Datenreihe formatieren. Wählen Sie im geöffneten Bereich Datenreihe formatieren unter der Registerkarte Füllung & Linie im Abschnitt Füllung die Option Keine Füllung aus – siehe Screenshot:

17. Führen Sie denselben Vorgang durch, um die Datenbalken „Max. rechts“ auszublenden – siehe Screenshot:

18. Nachdem Sie die Datenbalken „Max Left“ und „Max Right“ ausgeblendet haben, klicken Sie mit der rechten Maustaste auf die Max Left Y-Fehlerindikatoren und wählen im Kontextmenü Fehlerindikatoren formatieren aus. Führen Sie im Bereich Fehlerindikatoren formatieren unter der Registerkarte Füllung & Linie bitte die folgenden Schritte durch:

• Wählen Sie die Option Durchgezogene Linieaus, und legen Sie anschließend eine beliebige Farbe für die Aufwärts-Pfeile über die Dropdown-Liste Farbefest;
• Passen Sie anschließend die Linienbreite nach Bedarf auf 1,25 ptim Abschnitt Breitean;
• Wählen Sie danach aus der Dropdown-Liste Pfeil am Anfang das Symbol Pfeil aus.

19. Lassen Sie den Bereich Fehlerindikatoren formatieren weiterhin geöffnet und klicken Sie dann auf die Max. Right Y-Fehlerindikatoren. Führen Sie im Bereich Fehlerindikatoren formatieren unter der Registerkarte Füllung & Linie bitte die folgenden Schritte durch:

• Wählen Sie die Option Durchgezogene Linieaus, und legen Sie anschließend eine andere beliebige Farbe für die Abwärts-Pfeile über die Dropdown-Liste Farbefest;
• Passen Sie anschließend die Linienbreite nach Bedarf auf 1,25 ptim Abschnitt Breitean;
• Wählen Sie danach aus der Dropdown-Liste Pfeil am Ende das Symbol Pfeil aus.

20. Anschließend erhalten Sie das Diagramm, wie im folgenden Screenshot dargestellt:

21. Klicken Sie als Nächstes mit der rechten Maustaste auf den Datenbalken „Max Left“ und wählen Sie im Kontextmenü Datenreihe formatieren aus. Wählen Sie im geöffneten Bereich Datenreihe formatieren unter der Registerkarte Reihenoptionen die Option Sekundäre Achse im Abschnitt Reihe darstellen auf aus. Klicken Sie anschließend auf den Datenbalken „Max Right“ und führen Sie dieselbe Aktion wie in der folgenden Demo gezeigt durch:

22. Klicken Sie danach mit der rechten Maustaste auf einen beliebigen Balken im Diagramm und wählen Sie Datenreihe formatieren. Geben Sie im Bereich Datenreihe formatieren unter der Registerkarte Reihenoptionen jeweils 0 % und 100 % in die Felder Überlappung der Reihen und Abstandsbreite ein.

Wählen Sie anschließend im Bereich Datenreihe formatieren aus der Dropdown-Liste Reihe „Jahr 2019“ oder Reihe „Jahr 2020“ unter Reihenoptionen aus und tragen Sie jeweils 0 % sowie 100 % in die Felder Überlappung der Reihen und Abstandsbreite ein. Siehe folgende Demo:

23. In diesem Schritt fügen Sie dem Diagramm Datenbeschriftungen hinzu: Klicken Sie auf die Datenreihe „Max Left“ und anschließend auf Diagrammelemente. Aktivieren Sie im erweiterten Diagrammelemente-Listenfeld die Option Datenbeschriftungen. Siehe Screenshot:

24. Klicken Sie danach mit der rechten Maustaste auf die Datenbeschriftungen und wählen Sie Datenbeschriftungen formatieren. Siehe Screenshot:

25. Wählen Sie im geöffneten Bereich Datenbeschriftungen formatieren unter der Registerkarte Beschriftungsoptionen die Option Werte aus Zellen aus. Im daraufhin erscheinenden Dialogfeld Datenbeschriftungen-Bereich markieren Sie die Datenzellen aus der Spalte „Up Data Label“ und klicken anschließend auf die Schaltfläche OK. Siehe Screenshot:

26. Bleiben Sie im Bereich Datenbeschriftungen formatieren und deaktivieren Sie die Optionen Wert sowie Hilfslinien anzeigen. Anschließend erhalten Sie das unten stehende Diagramm. Siehe Screenshot:

27. Fügen Sie danach die abwärts gerichteten Datenbeschriftungen hinzu: Klicken Sie mit der rechten Maustaste auf den Datenbalken „Max Right“ und wählen Sie Diagrammelemente, um das Listenfeld zu erweitern. Aktivieren Sie im Diagrammelemente-Listenfeld die Option Datenbeschriftungen. Siehe Screenshot:

28. Klicken Sie danach mit der rechten Maustaste auf die neu hinzugefügten Datenbeschriftungen und wählen Sie Datenbeschriftungen formatieren. Wählen Sie im geöffneten Bereich Datenbeschriftungen formatieren unter der Registerkarte Beschriftungsoptionen die Option Werte aus Zellen aus. Wählen Sie im daraufhin erscheinenden Dialogfeld Datenbeschriftungen-Bereich die Datenzellen aus der Spalte „Down Data Label“ aus und klicken Sie anschließend auf die Schaltfläche OK. Siehe Screenshot:

29. Bleiben Sie weiterhin im Bereich Datenbeschriftung formatieren, und deaktivieren Sie die Optionen Wert und Hilfslinien anzeigen. Damit wurden die prozentualen Änderungen als Datenbeschriftungen dem Diagramm hinzugefügt. Siehe Screenshot:

30. Löschen Sie abschließend nicht benötigte Diagrammelemente nach Bedarf – beispielsweise Gitternetzlinien, den Diagrammtitel und die sekundäre Achse –, um das vollständige Diagramm wie im folgenden Screenshot dargestellt zu erhalten:

Pfeil-Varianz- oder Differenzdiagramm in Excel mit einer praktischen Funktion erstellen

Beispieldatei für Pfeil-Varianz- oder Differenzdiagramm herunterladen

Video: Pfeil-Varianz- oder Differenzdiagramm in Excel erstellen