Pfeilvarianz-Diagramm in Excel erstellen

In Excel ist das Pfeilvarianz- oder Differenzdiagramm eine Art gruppiertes Säulen- oder Balkendiagramm mit aufwärts- oder abwärtsgerichteten Pfeilen, um die prozentuale Zunahme oder Abnahme von zwei Datensätzen anzuzeigen.

Zum Beispiel, wenn Sie die Verkaufsvarianz im Vergleich zum Vorjahr anzeigen möchten, können Sie grüne und rote Pfeile verwenden, um die prozentuale Veränderung wie im folgenden Screenshot zu zeigen. In diesem Artikel werde ich darüber sprechen, wie man diesen komplexen Diagrammtyp in Excel erstellt.

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

Pfeilvarianz- oder Differenzdiagramm in Excel erstellen

Um ein Pfeilvarianzdiagramm zu erstellen, das die prozentuale Änderung des Jahres 2020 im Vergleich zum Jahr 2019 vergleicht, sollten Sie zunächst einige Hilfsspalten einfügen, um die grundlegenden Daten 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 die Formel bis Zelle D10, siehe Screenshot:

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

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

4. Geben Sie dann in Zelle G2 die folgende Formel ein und ziehen Sie sie bis Zelle G10, um die Formel auszufüllen, siehe Screenshot:

5. Geben Sie dann bitte die folgende Formel in Zelle H2 ein und ziehen Sie die Formel bis Zelle H10, siehe Screenshot:

6. Geben Sie dann die folgende Formel in Zelle I2 ein und ziehen Sie den Ausfüllkursor bis Zelle I10, siehe Screenshot:

Zweitens, Pfeilvarianzdiagramm basierend auf den Hilfsspalten-Daten erstellen

7. Nachdem Sie die Hilfsdaten erstellt haben, wählen Sie die Daten in Spalte A, B, C, D, E aus und klicken Sie auf Einfügen > Gruppierte Säule einfügen > Gruppierte Säule, siehe Screenshot:

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

9. Klicken Sie dann auf das Diagramm, um es auszuwählen, und wählen Sie dann „Daten auswählen“ aus dem Kontextmenü, siehe Screenshot:

10. Wählen Sie im aufgepoppten Dialogfeld „Datenquelle auswählen“ in der Liste „Bereich der Reihennamen“ die Reihe „Max Links“ aus und klicken Sie dann auf den Pfeil nach oben, um diese Datenreihe nach oben zu verschieben, siehe unten stehende Demo:

11. Klicken Sie dann auf die Schaltfläche OK, um das Dialogfeld zu schließen, und klicken Sie dann mit der rechten Maustaste auf die Datenreihe „Max Links“, klicken Sie auf die Schaltfläche Diagrammelemente, um das Listenfeld zu erweitern, und aktivieren Sie im Listenfeld die Fehlerbalken, siehe Screenshot:

12. Klicken Sie weiterhin mit der rechten Maustaste auf die Datenreihe „Max Rechts“ und klicken Sie dann auf die Schaltfläche Diagrammelemente, um das Listenfeld zu erweitern, und aktivieren Sie im Listenfeld die Fehlerbalken, siehe Screenshot:

13. Klicken Sie dann mit der rechten Maustaste auf die Y-Fehlerbalken „Max Links“ und wählen Sie „Fehlerbalken formatieren“ aus dem Kontextmenü, siehe Screenshot:

14. Dann, im geöffneten Bereich „Fehlerbalken formatieren“, unter dem Tab „Optionen für Fehlerbalken“, im Abschnitt „Vertikale Fehlerbalken“, konfigurieren Sie bitte die folgenden Operationen:

• Wählen Sie die Option Minus im Abschnitt Richtung aus;
• Wählen Sie dann Kein Ende aus dem Abschnitt Endstil aus;
• Und wählen Sie dann Benutzerdefiniert aus dem Fehlerwert Abschnitt aus, klicken Sie dann auf die Schaltfläche Wert angeben, im aufgepoppten Dialogfeld „Benutzerdefinierte Fehlerbalken“ lassen Sie das Feld Positiver Fehlerwert leer und wählen Sie die Daten aus der Spalte „Aufwärtspfeil“ im Feld Negativer Fehlerwert aus und klicken Sie dann auf OK, um das Dialogfeld zu schließen.

15. Lassen Sie den Bereich „Fehlerbalken formatieren“ geöffnet und klicken Sie dann auf die Y-Fehlerbalken „Max Rechts“ im Diagramm, unter dem Tab „Optionen für Fehlerbalken“, im Abschnitt „Vertikale Fehlerbalken“, konfigurieren Sie bitte die folgenden Operationen:

• Wählen Sie die Option Minus im Abschnitt Richtung aus;
• Wählen Sie dann Kein Ende aus dem Abschnitt Endstil aus;
• Und wählen Sie dann Benutzerdefiniert aus dem Abschnitt Fehlerwert aus, klicken Sie dann auf die Schaltfläche Wert angeben, im aufgepoppten Dialogfeld Benutzerdefinierte Fehlerbalken lassen Sie das Feld Positiver Fehlerwert leer und wählen Sie die Daten aus der Spalte „Abwärtspfeil“ im Feld Negativer Fehlerwert aus und klicken Sie dann auf OK, um das Dialogfeld zu schließen.

16. Formatieren Sie dann die Datenreihenbalken „Max Links“ und „Max Rechts“ unsichtbar, klicken Sie mit der rechten Maustaste auf den Datenbalken „Max Links“ und wählen Sie die Option Datenreihe formatieren, im geöffneten Bereich Datenreihe formatieren, unter dem Tab Füllung & Linie, wählen Sie Keine Füllung aus dem Abschnitt Füllung aus, siehe Screenshot:

17. Führen Sie dieselbe Operation aus, um die Datenbalken „Max Rechts“ auszublenden, siehe Screenshot:

18. Nachdem Sie die Datenbalken „Max Links“ und „Max Rechts“ ausgeblendet haben, klicken Sie nun mit der rechten Maustaste auf die Y-Fehlerbalken „Max Links“ und wählen Sie dann „Fehlerbalken formatieren“ aus dem Kontextmenü, im Bereich „Fehlerbalken formatieren“, unter dem Tab „Füllung & Linie“, führen Sie bitte die folgenden Operationen aus:

• Wählen Sie die Option Durchgezogene Linie und geben Sie dann eine Farbe Ihrer Wahl für die Aufwärtspfeile aus der Dropdown-Liste Farbe an;
• Passen Sie dann die Linienbreite auf 1,25 pt an, wie Sie es benötigen, aus dem Abschnitt Breite;
• Wählen Sie dann das Pfeilsymbol aus der Dropdown-Liste Beginn-Pfeiltyp aus.

19. Lassen Sie den Fehlerbalken formatieren Bereich weiterhin geöffnet, klicken Sie dann auf die Y-Fehlerbalken „Max Rechts“, im Fehlerbalken formatieren Bereich, unter dem Füllung & Linie Tab, führen Sie bitte die folgenden Operationen aus:

• Wählen Sie die Option Durchgezogene Linie und geben Sie dann eine andere Farbe Ihrer Wahl für die Abwärtspfeile aus der Dropdown-Liste Farbe an;
• Passen Sie dann die Linienbreite auf 1,25 pt an, wie Sie es benötigen, aus dem Abschnitt Breite;
• Wählen Sie dann das Pfeilsymbol aus der Dropdown-Liste End-Pfeiltyp aus.

20. Und Sie erhalten das Diagramm wie im folgenden Screenshot gezeigt:

21. Als nächstes klicken Sie mit der rechten Maustaste auf den Datenbalken „Max Links“ und wählen Sie „Datenreihe formatieren“ aus dem Kontextmenü, im geöffneten Bereich „Datenreihe formatieren“, unter dem Tab „Reihenoptionen“, wählen Sie die Option Sekundäre Achse aus dem Abschnitt Serie plotten auf aus, klicken Sie dann auf den Datenbalken „Max Rechts“ und führen Sie dieselbe Operation wie in der unten stehenden Demo gezeigt aus:

22. Klicken Sie dann mit der rechten Maustaste auf einen beliebigen Balken im Diagramm und wählen Sie „Datenreihe formatieren“, im Bereich „Datenreihe formatieren“, unter dem Tab „Reihenoptionen“, geben Sie 0 % und 100 % in die Felder Überlappung und Abstand ein.

Gehen Sie dann im Bereich „Datenreihe formatieren“ weiterhin zur Auswahl „Jahr 2019“ oder „Jahr 2020“ aus der Dropdown-Liste Reihenoptionen und geben Sie 0 % und 100 % in die Felder Überlappung und Abstand ein. Siehe unten stehende Demo:

23. In diesem Schritt können Sie die Datenbeschriftungen für das Diagramm hinzufügen, klicken Sie auf die Datenreihe „Max Links“ und klicken Sie dann auf Diagrammelemente, im erweiterten Listenfeld Diagrammelemente aktivieren Sie die Option Datenbeschriftungen, siehe Screenshot:

24. Klicken Sie dann mit der rechten Maustaste auf die Datenbeschriftungen und wählen Sie „Datenbeschriftungen formatieren“, siehe Screenshot:

25. Im geöffneten Bereich „Datenbeschriftungen formatieren“, unter dem Tab „Beschriftungsoptionen“, aktivieren Sie die Option Wert aus Zellen und im aufgepoppten Dialogfeld „Datenbeschriftungsbereich“ wählen Sie die Datenzellen aus der Spalte „Aufwärts-Datenbeschriftung“ aus und klicken Sie dann auf OK, siehe Screenshot:

26. Bleiben Sie im Bereich „Datenbeschriftungen formatieren“, deaktivieren Sie die Optionen Wert und Führungslinien anzeigen und Sie erhalten das unten stehende Diagramm, siehe Screenshot:

27. Fügen Sie dann die Abwärts-Datenbeschriftungen hinzu, klicken Sie mit der rechten Maustaste auf den Datenbalken „Max Rechts“ und klicken Sie auf Diagrammelemente, um das Listenfeld zu erweitern, aktivieren Sie im Listenfeld Diagrammelemente die Option Datenbeschriftungen, siehe Screenshot:

28. Klicken Sie dann mit der rechten Maustaste auf die neu hinzugefügten Datenbeschriftungen und wählen Sie „Datenbeschriftungen formatieren“, im geöffneten Bereich „Datenbeschriftungen formatieren“, unter dem Tab „Beschriftungsoptionen“, aktivieren Sie die Option Wert aus Zellen und im aufgepoppten Dialogfeld „Datenbeschriftungsbereich“ wählen Sie die Datenzellen aus der Spalte „Abwärts-Datenbeschriftung“ aus und klicken Sie dann auf OK, siehe Screenshot:

29. Bleiben Sie im Bereich „Datenbeschriftungen formatieren“, deaktivieren Sie die Optionen Wert und Führungslinien anzeigen und die prozentualen Änderungsdatenbeschriftungen wurden dem Diagramm hinzugefügt, siehe Screenshot:

30. Löschen Sie abschließend die nicht benötigten Diagrammelemente wie Sie es benötigen, z.B. die Gitternetzlinien, Diagrammtitel, sekundäre Achse und Sie erhalten das vollständige Diagramm wie im folgenden Screenshot gezeigt:

Pfeilvarianz- oder Differenzdiagramm in Excel mit einer praktischen Funktion erstellen

Beispieldatei für Pfeilvarianz- oder Differenzdiagramm herunterladen

Video: Pfeilvarianz- oder Differenzdiagramm in Excel erstellen