Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm in Excel

In Excel dient ein Budget-gegen-Ist-Abweichungsdiagramm dazu, zwei Datenreihen miteinander zu vergleichen und ihre Differenz bzw. Abweichung anschaulich darzustellen. Negative Abweichungen werden dabei in einer Farbe angezeigt, positive in einer anderen – wie im folgenden Screenshot zu sehen. In diesem Artikel erfahren Sie, wie Sie sowohl ein solches Vergleichsabweichungsdiagramm als auch ein Mini-Vergleichsabweichungsdiagramm direkt innerhalb von Zellen in Ihrer Excel-Arbeitsmappe erstellen.

• Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm in Excel
• Erstellen Sie ein Mini-Budget-gegen-Ist-Abweichungsdiagramm in Zellen
• Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm mit einer beeindruckenden Funktion
• Laden Sie die Beispieldatei für das Budget-gegen-Ist-Abweichungsdiagramm herunter
• Video: Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm in Excel

Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm in Excel

Führen Sie zur Erstellung des Budget-gegen-Ist-Abweichungsdiagramms die folgenden Schritte aus:

1. Geben Sie in Zelle D2 die folgende Formel ein, um die Differenz zwischen dem Budget und dem tatsächlichen Wert zu berechnen, und ziehen Sie anschließend den Ausfüllknauf bis zu den gewünschten Zellen herunter (siehe Screenshot):

2. Markieren Sie anschließend die Daten in Spalte A und Spalte D, und klicken Sie dann auf Einfügen > Säule oder Balkendiagramm einfügen > Gruppierte Säulen (siehe Screenshot):

3. Es wird nun ein Säulendiagramm eingefügt. Danach können Sie unerwünschte Elemente wie den Diagrammtitel, die vertikale Achse oder die Gitternetzlinien ganz nach Bedarf entfernen. Das Ergebnis sehen Sie in den folgenden Screenshots:

4. Klicken Sie danach mit der rechten Maustaste auf die horizontale Achse und wählen Sie im Kontextmenü die Option Achse formatieren (siehe Screenshot):

5. Erweitern Sie im geöffneten Bereich Achse formatieren unter der Registerkarte Achsenoptionen den Abschnitt Beschriftungen, und wählen Sie im Dropdown-Menü bei Beschriftungsposition die Option Niedrig aus, damit die horizontale Achse am unteren Rand des Diagramms platziert wird (siehe Screenshot):

6. Klicken Sie danach mit der rechten Maustaste auf einen beliebigen Balken und wählen Sie die Option Datenreihe formatieren (siehe Screenshot):

7. Führen Sie im geöffneten Bereich Datenreihe formatieren unter der Registerkarte Füllung & Linie im Abschnitt Füllung die folgenden Schritte aus:

• Wählen Sie die Option Feste Füllung;
• Aktivieren Sie das Kontrollkästchen Bei negativem Wert umkehrenKontrollkästchen;
• Geben Sie im Dropdown-Menü Farbe zwei verschiedene Farben separat für positive und negative Werte an.

8. Nachdem Sie die Farben für die Balken festgelegt haben, fügen Sie bitte die Datenbeschriftungen hinzu. Klicken Sie dazu auf die Balken im Diagramm und anschließend auf das Symbol Diagrammelemente. Aktivieren Sie dann im Listenfeld Datenbeschriftungen unter Diagrammelemente (siehe Screenshot):

9. Die Säulenbalken wirken aufgrund ihrer geringen Breite optisch wenig ansprechend. In diesem Fall können Sie die Balkenbreite ganz einfach anpassen: Klicken Sie dazu mit der rechten Maustaste auf einen beliebigen Balken und wählen Sie Datenreihe formatieren. Ändern Sie im Bereich Datenreihe formatieren unter der Registerkarte Reihenoptionen den Balkenabstand nach Ihren Wünschen. In diesem Beispiel wird der Wert auf 80 % gesetzt (siehe Screenshot):

Hinweis: Falls Sie andere Farben benötigen, beachten Sie bitte die folgende Farbcode-Tabelle:

Erstellen Sie ein Mini-Budget-gegen-Ist-Abweichungsdiagramm in Zellen

Wenn Sie ein Mini-Budget-gegen-Ist-Abweichungsdiagramm direkt in Zellen einfügen möchten, helfen Ihnen die folgenden Schritte weiter:

1. Geben Sie die folgende Formel in Zelle D2 ein, um die Differenz zwischen dem Budget und Tatsächlicher Wert zu berechnen, und ziehen Sie anschließend den Ausfüllknauf bis zu den gewünschten Zellen herunter (siehe Screenshot):

2. Um die negativen Abweichungswerte anzuzeigen, wenden Sie folgende Formel in Zelle E2 an und kopieren Sie sie anschließend in die weiteren benötigten Zellen (siehe Screenshot):

3. Um die positiven Abweichungswerte anzuzeigen, geben Sie folgende Formel in Zelle F2 ein und kopieren Sie sie anschließend in alle weiteren benötigten Zellen (siehe Screenshot):

4. Fügen Sie als Nächstes ein spezielles Symbol ein, das als Balken des Diagramms dient. Klicken Sie dazu auf Einfügen > Symbol. Wählen Sie im Dialogfeld Symbol im Dropdown-Menü Blockelemente (in nahezu jeder Schriftart verfügbar) unter Teilmenge aus und klicken Sie auf das Symbol Vollblock. Klicken Sie anschließend auf Einfügen, um das Symbol einzufügen (siehe Screenshot):

5. Nachdem Sie das spezielle Symbol eingefügt haben, wenden Sie die folgende Formel in Zelle G2 an, ziehen Sie sie auf die anderen Zellen und erhalten so das unten stehende Ergebnis:

6. Wenden Sie nun die folgende Formel in Zelle H2 an, ziehen Sie sie auf die anderen Zellen und erhalten so das unten stehende Ergebnis:

7. Markieren Sie anschließend die Zellen in Spalte G und klicken Sie auf Start > Rechtsbündig, um die Daten rechtsbündig in den Zellen auszurichten (siehe Screenshot):

8. Abschließend können Sie die Schriftfarbe der beiden Spalten individuell nach Ihren Wünschen formatieren und erhalten so das im folgenden Screenshot gezeigte Ergebnis:

Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm mit einer beeindruckenden Funktion

Laden Sie die Beispieldatei für das Budget-gegen-Ist-Abweichungsdiagramm herunter

Video: Erstellen Sie ein Budget-gegen-Ist-Abweichungsdiagramm in Excel