Budget vs. Actual Varianz-Diagramm in Excel erstellen

In Excel wird ein Budget vs. Actual Varianz-Diagramm verwendet, um zwei Datensätze zu vergleichen und die Differenz oder Varianz zwischen den beiden Datensätzen anzuzeigen. Wenn die Unterschiede negative Werte sind, werden farbige Balken oder Säulen einer Farbe angezeigt; bei positiven Werten werden farbige Balken oder Säulen einer anderen Farbe dargestellt, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich darüber sprechen, wie man das Vergleichsvarianzdiagramm und ein Mini-Vergleichsvarianzdiagramm innerhalb von Zellen in einer Excel-Arbeitsmappe erstellt.

• Budget vs. Actual Varianz-Diagramm in Excel erstellen
• Mini-Budget vs. Actual Varianz-Diagramm in Zellen erstellen
• Budget vs. Actual Varianz-Diagramm mit einem erstaunlichen Feature erstellen
• Beispieldatei für Budget vs. Actual Varianz-Diagramm herunterladen
• Video: Budget vs. Actual Varianz-Diagramm in Excel erstellen

Budget vs. Actual Varianz-Diagramm in Excel erstellen

Um das Budget vs. Actual Varianz-Diagramm zu erstellen, gehen Sie bitte wie folgt vor:

1. Geben Sie die folgende Formel in Zelle D2 ein, um die Differenz zwischen den Budget- und Istwerten zu berechnen, und ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, die Sie benötigen, siehe Screenshot:

2. Wählen Sie dann die Daten in Spalte A und Spalte D aus, und klicken Sie auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Säule, siehe Screenshot:

3. Ein Säulendiagramm wurde eingefügt. Dann können Sie unerwünschte Elemente des Diagramms nach Bedarf löschen, z. B. den Diagrammtitel, die vertikale Achse, Gitternetzlinien, und Sie erhalten das Ergebnis, wie in den folgenden Screenshots gezeigt:

4. Klicken Sie dann mit der rechten Maustaste auf die horizontale Achse und wählen Sie die Option Achse formatieren aus dem Kontextmenü, siehe Screenshot:

5. Im geöffneten Bereich Achse formatieren, unter dem Reiter Achsenoptionen, klicken Sie auf Beschriftungen, um diesen Abschnitt zu erweitern, und wählen Sie dann Niedrig aus der Dropdown-Liste für die Beschriftungsposition aus. Die horizontale Achse wird am unteren Rand des Diagramms platziert, siehe Screenshot:

6. Klicken Sie dann mit der rechten Maustaste auf einen der Balken und wählen Sie die Option Datenreihe formatieren, siehe Screenshot:

7. Im geöffneten Datenreihe formatieren Bereich, unter dem Füllung & Linie Reiter, im Füllung Abschnitt, führen Sie bitte die folgenden Schritte aus:

• Wählen Sie die Option Volltonfarbe aus;
• Aktivieren Sie das Kontrollkästchen Negativ invertieren;
• Legen Sie zwei Farben für positive und negative Werte separat über die Dropdown-Liste Farbe fest.

8. Nachdem Sie die Farben für die Balken festgelegt haben, sollten Sie nun die Datenbeschriftungen hinzufügen. Klicken Sie dazu auf die Balken im Diagramm und dann auf das Symbol Diagrammelemente, und aktivieren Sie Datenbeschriftungen aus der Liste der Diagrammelemente, siehe Screenshot:

9. Die Säulenbalken sind so schmal, dass sie nicht schön aussehen. In diesem Fall können Sie die Balkenbreite nach Bedarf anpassen. Klicken Sie mit der rechten Maustaste auf einen der Balken und wählen Sie dann Datenreihe formatieren. Im Bereich Datenreihe formatieren, unter dem Reihenoptionen Reiter, ändern Sie die Lückenbreite nach Bedarf. In diesem Fall ändere ich sie auf 80 %, siehe Screenshot:

Hinweis: Wenn Sie andere Farben benötigen, sehen Sie sich bitte die folgende Farbindex-Tabelle an:

Mini-Budget vs. Actual Varianz-Diagramm in Zellen erstellen

Wenn Sie ein Mini-Budget vs. Actual Varianz-Diagramm in Zellen einfügen möchten, können Ihnen die folgenden Schritte helfen:

1. Geben Sie die folgende Formel in Zelle D2 ein, um die Differenz zwischen den Budget- und Istwerten zu berechnen, und ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, die Sie benötigen, siehe Screenshot:

2. Zeigen Sie dann die negativen Werte der Varianz an, indem Sie die folgende Formel in Zelle E2 einfügen und die Formel dann in die anderen benötigten Zellen kopieren, siehe Screenshot:

3. Zeigen Sie die positiven Werte der Varianz an, indem Sie die folgende Formel in Zelle F2 eingeben und die Formel dann in die anderen benötigten Zellen kopieren, siehe Screenshot:

4. Fügen Sie als nächstes ein bestimmtes Symbol ein, das als Diagrammbalken verwendet wird. Klicken Sie auf Einfügen > Symbol, und wählen Sie im Dialogfeld Symbol Blockelemente aus, die Sie in fast jeder Schriftart finden können, aus der Dropdown-Liste Teilmenge. Klicken Sie auf das Vollblock-Symbol und dann auf Einfügen, um das Symbol einzufügen, siehe Screenshot:

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

6. Wenden Sie weiterhin die folgende Formel auf Zelle H2 an und ziehen Sie diese Formel in die anderen Zellen. Sie erhalten das folgende Ergebnis:

7. Wählen Sie dann die Zellen in Spalte G aus und klicken Sie auf Start > Rechtsbündig ausrichten, um die Daten rechts in den Zellen auszurichten, siehe Screenshot:

8. Zum Schluss können Sie die Schriftfarben für die beiden Spalten separat nach Bedarf formatieren, und Sie erhalten das Ergebnis, wie im folgenden Screenshot gezeigt:

Budget vs. Actual Varianz-Diagramm mit einem erstaunlichen Feature erstellen

Beispieldatei für Budget vs. Actual Varianz-Diagramm herunterladen

Video: Budget vs. Actual Varianz-Diagramm in Excel erstellen