Direkt zum Inhalt

Erstellen Sie ein Diagramm für Budget und tatsächliche Abweichung in Excel

Autor: Xiaoyang Letzte Änderung: 2020-12-17

In Excel wird ein Diagramm Budget gegen tatsächliche Abweichung verwendet, um zwei Sätze von Seriendaten zu vergleichen und die Differenz oder Varianz der beiden Datenreihen anzuzeigen. Wenn die Unterschiede negative Werte sind, werden einfarbiger Balken oder Spalten angezeigt. Wenn positive Werte angezeigt werden, werden andere farbige Balken oder Spalten angezeigt (siehe Abbildung unten). In diesem Artikel werde ich darüber sprechen, wie das Vergleichsvarianzdiagramm und ein Mini-In-Zellen-Vergleichsvarianzdiagramm in der Excel-Arbeitsmappe erstellt werden.


Erstellen Sie in Excel ein Diagramm mit Budget und tatsächlicher Abweichung

Führen Sie die folgenden Schritte aus, um das Diagramm Budget gegen tatsächliche Abweichung zu erstellen:

1. Geben Sie die folgende Formel in Zelle D2 ein, um die Differenz zwischen dem Budget und den tatsächlichen Werten zu berechnen, und ziehen Sie dann den Füllpunkt nach unten auf die gewünschten Zellen (siehe Abbildung):

=C2-B2

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

3. Wenn ein Säulendiagramm eingefügt wird, können Sie die unerwünschten Elemente des Diagramms nach Bedarf löschen, z. B. den Diagrammtitel, die vertikale Achse und die Gitterlinien. Das Ergebnis wird wie folgt angezeigt: Screenshots:

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

5. In der geöffneten Achse formatieren Scheibe, unter der Achsenoptionen Tab, klicken Sie auf Label um diesen Abschnitt zu erweitern, und wählen Sie dann Niedrig von dem Etikettenposition Dropdown-Liste, und die horizontale Achse wird am unteren Rand des Diagramms platziert, siehe Screenshot:

6. Klicken Sie dann mit der rechten Maustaste auf eine der Leisten und wählen Sie Datenreihen formatieren Option, siehe Screenshot:

7. In der geöffneten Datenreihen formatieren Scheibe, unter der Füllen & Linie Registerkarte, von der Füllen Abschnitt, bitte führen Sie die folgenden Operationen aus:

  • Auswählen Feste füllung Option;
  • Shau Invertieren, wenn negativ Kontrollkästchen;
  • Geben Sie zwei Farben für die positiven und negativen Werte getrennt von der an Farbe Dropdown-Liste.

8. Nachdem Sie die Farben für die Balken festgelegt haben, sollten Sie die Datenbeschriftungen hinzufügen. Klicken Sie auf, um die Balken im Diagramm auszuwählen, und klicken Sie dann auf Diagrammelemente Symbol, dann überprüfen Datenaufkleber von dem Diagrammelemente Listenfeld, siehe Screenshot:

9. Die Spaltenleisten sind so schmal, dass sie nicht schön aussehen. In diesem Fall können Sie die Balkenbreite an Ihre Bedürfnisse anpassen. Klicken Sie mit der rechten Maustaste auf eine der Balken und wählen Sie dann Datenreihen formatierenIn der Datenreihen formatieren Scheibe, unter der Serienoptionen Registerkarte, ändern Sie die Spaltbreite In diesem Fall werde ich es in Ihr Bedürfnis ändern 80%, siehe Screenshot:

Tips:

Nach dem Erstellen des Diagramms möchten Sie manchmal die Datenbeschriftungen mit unterschiedlichen Farben anzeigen, z. B. die positiven Datenbeschriftungen in grüner Farbe und die negativen Datenbeschriftungen in orange Farbe. Gehen Sie dazu folgendermaßen vor:

Wählen Sie die Daten in der Spalte Varianz aus der ursprünglichen Datentabelle aus und drücken Sie dann Strg + 1 öffnen Format Cells Dialogfeld, in der Format Cells Dialogfeld, klicken Sie auf Maßgeschneidert von dem Kategorie Listenfeld, und geben Sie dann den folgenden Code in das Feld ein Typ Textfeld:

[Color10]General;[Color53] -General

Dann klick OK Klicken Sie auf die Schaltfläche, und Sie erhalten das gewünschte Ergebnis, siehe Screenshot:

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


Erstellen Sie ein Mini-Budget im Vergleich zum tatsächlichen Varianzdiagramm in Zellen

Wenn Sie ein Mini-Budget-Diagramm für die tatsächliche Varianz in Zellen einfügen möchten, können die folgenden Schritte Ihnen einen Gefallen tun:

1. Geben Sie die folgende Formel in Zelle D2 ein, um die Differenz zwischen dem Budget und den tatsächlichen Werten zu berechnen, und ziehen Sie dann den Füllpunkt nach unten auf die gewünschten Zellen (siehe Abbildung):

=C2-B2

2. Zeigen Sie dann die negativen Werte der Varianz an, wenden Sie die folgende Formel in Zelle E2 an und kopieren Sie die Formel in andere benötigte Zellen, siehe Screenshot:

=IF(D2<0,D2,"")

3. Zeigen Sie die positiven Werte der Varianz an, geben Sie die folgende Formel in Zelle F2 ein und kopieren Sie die Formel in andere benötigte Zellen (siehe Abbildung):

=IF(D2>0,D2,"")

4. Und dann sollten Sie ein bestimmtes Symbol einfügen, das als Diagrammleiste verwendet wird. Klicken Sie auf Insert > Symbol, und in der Symbol Dialogfeld auswählen Blockelemente das finden Sie in fast jeder Schriftart aus dem Teilmenge Dropdown-Liste und klicken Sie auf Voller Block Symbol, dann klicken Sie auf Insert Zum Einfügen des Symbols siehe Screenshot:

5. Wenden Sie nach dem Einfügen des speziellen Symbols die folgende Formel in Zelle G2 an und ziehen Sie diese Formel auf andere Zellen. Sie erhalten das folgende Ergebnis:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Note: In der obigen Formel E2 ist die Zelle enthält den negativen Varianzwert, A16 Ist die Zelle enthält das spezifische Symbol, das Sie eingefügt haben, und die Nummer 100 ist eine Variable Daten, die verwendet wird, um die Nummer des Symbols basierend auf dem Varianzwert zu berechnen, Sie können es in Ihre eigenen ändern.

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

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Note: In der obigen Formel F2 ist die Zelle enthält den positiven Varianzwert, A16 Ist die Zelle enthält das spezifische Symbol, das Sie eingefügt haben, und die Nummer 100 ist eine Variable Daten, die verwendet wird, um die Nummer des Symbols basierend auf dem Varianzwert zu berechnen, Sie können es in Ihre eigenen ändern.

7. Wählen Sie dann die Zellen in Spalte G aus und klicken Sie auf Startseite > Rechts ausrichten Informationen zum Ausrichten der Daten direkt auf den Zellen finden Sie im Screenshot:

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


Erstellen Sie ein Diagramm aus Budget und tatsächlicher Varianz mit einer erstaunlichen Funktion

Kutools for Excel bietet Dutzende spezieller Diagrammtypen, über die Excel nicht verfügt, z Aufzählungszeichen, Ziel- und Istdiagramm, Differenzpfeiltabelle und so weiter. Mit seinem handlichen Werkzeug- DifferenzvergleichstabelleSie können schnell und einfach ein Diagramm zwischen Budget und tatsächlicher Abweichung im Arbeitsblatt oder ein Diagramm mit Mini-Budget und tatsächlicher Abweichung in Zellen erstellen. Klicken Sie hier, um Kutools for Excel kostenlos herunterzuladen!


Laden Sie die Beispieldatei Budget vs Actual Variance Chart herunter


Video: Erstellen Sie ein Diagramm für das Budget und die tatsächliche Abweichung in Excel


Die besten Tools für die Office-Produktivität

Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate  |  Leere Zeilen löschen  |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren  |  Runde ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrfachwert  |  Über mehrere Blätter hinweg  |  Unscharfe Suche...
Adv. Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl Dropdown-Liste...
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  Spalten vergleichen mit Wählen Sie Gleiche und Unterschiedliche Zellen ...
Ausgewählte Funktionen: Rasterfokus  |  Designansicht  |  Große Formelleiste  |  Arbeitsmappen- und Blattmanager | Ressourcen (Autotext)  |  Datumsauswahl  |  Arbeitsblätter kombinieren  |  Zellen verschlüsseln/entschlüsseln  |  Senden Sie E-Mails nach Liste  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen ...)  |  50+ Chart Typen (Gantt-Diagramm ...)  |  40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag ...)  |  19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion ...)  |  7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Teilen Sie Excel-Zellen ...)  |  ... und mehr

Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...

Beschreibung


Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).

  • Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
  • Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
  • Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
  • Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
bonjour,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
merci
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations