Note: The other languages of the website are Google-translated. Back to English

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

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 Etiketten 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:

Tipps:

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 Kategorien 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:

Hinweis: 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))
Hinweis: 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)
Hinweis: 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 Start > 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

Sie möchten Ihre tägliche Arbeit schnell und perfekt erledigen? Kutools for Excel bietet 300 leistungsstarke erweiterte Funktionen (Arbeitsmappen kombinieren, nach Farbe summieren, Zelleninhalte teilen, Datum konvertieren usw.) und 80% Zeit für Sie sparen.

  • Entwickelt für 1500 Arbeitsszenarien, hilft Ihnen, 80 % der Excel-Probleme zu lösen.
  • Reduzieren Sie täglich Tausende von Tastatur- und Mausklicks und entlasten Sie Ihre müden Augen und Hände.
  • Werden Sie in 3 Minuten ein Excel-Experte. Sie müssen sich keine schmerzhaften Formeln und VBA-Codes mehr merken.
  • 30 Tage unbegrenzte kostenlose Testversion. 60 Tage Geld-zurück-Garantie. Kostenloses Upgrade und Support für 2 Jahre.
Excel-Multifunktionsleiste (mit installiertem Kutools für Excel)

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 Registerkarten in Office (einschließlich Excel), genau wie Chrome, Firefox und New Internet Explorer.
Screenshot von Excel (mit installiertem Office Tab)
Kommentare (1)
Noch keine Bewertungen. Bewerten Sie als Erster!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
hallo,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
wenden
Es sind noch keine Kommentare vorhanden
Hinterlassen Sie Ihre Kommentare
Als Gast posten
×
Bewerte diese Nachricht:
0   Figuren
Vorgeschlagene Standorte

Folgen Sie uns

Copyright © 2009 - www.extendoffice.com. | Alle Rechte vorbehalten. Unterstützt von ExtendOffice. | Sitemap
Microsoft und das Office-Logo sind Marken oder eingetragene Marken der Microsoft Corporation in den USA und / oder anderen Ländern.
Geschützt durch Sectigo SSL