Vai al contenuto principale

Crea un grafico Budget vs Actual Variance in Excel

In Excel, viene utilizzato un grafico Budget vs Actual Variance per confrontare due set di dati di serie e visualizzare la differenza o la varianza delle due serie di dati. Se le differenze sono valori negativi, vengono visualizzate una barra o una colonna colorata, se valori positivi, vengono visualizzate altre barre o colonne colorate come mostrato nell'immagine sottostante. In questo articolo, parlerò di come creare il grafico della varianza del confronto e un mini nel grafico della varianza del confronto delle celle nella cartella di lavoro di Excel.


Crea un grafico della varianza del budget e della varianza effettiva in Excel

Per creare il grafico del budget rispetto alla varianza effettiva, eseguire i seguenti passaggi:

1. Immettere la formula seguente nella cella D2 per calcolare la differenza tra il budget e i valori effettivi, quindi trascinare il quadratino di riempimento verso il basso sulle celle necessarie, vedere screenshot:

=C2-B2

2. Quindi selezionare i dati nella colonna A e nella colonna D, quindi fare clic su inserire > Inserisci grafico a colonne o a barre > Colonna raggruppata, vedi screenshot:

3. E viene inserito un grafico a colonne, quindi puoi eliminare gli elementi indesiderati del grafico di cui hai bisogno, ad esempio eliminare il titolo del grafico, l'asse verticale, le linee della griglia e otterrai il risultato come mostrato di seguito:

4. Quindi, fai clic con il pulsante destro del mouse sull'asse orizzontale e scegli Formato Axis opzione dal menu contestuale, vedi screenshot:

5. In aperto Formato Axis riquadro, sotto il Opzioni dell'asse scheda, fare clic per il tuo brand per espandere questa sezione, quindi selezionare Basso dal Posizione etichetta elenco a discesa e l'asse orizzontale verrà posizionato nella parte inferiore del grafico, vedi screenshot:

6. Quindi, fai clic con il pulsante destro del mouse su una delle barre e scegli Formato serie di dati opzione, vedi screenshot:

7. In aperto Formato serie di dati riquadro, sotto il Riempi e linea scheda, dal Riempire sezione, eseguire le seguenti operazioni:

  • Seleziona Riempimento solido opzione;
  • Vedi Inverti se negativo casella di controllo;
  • Specificare due colori per i valori positivi e negativi separatamente dal file Colore cadere in picchiata.

8. Dopo aver impostato i colori per le barre, è necessario aggiungere le etichette dei dati, fare clic per selezionare le barre nel grafico, quindi fare clic su Elementi del grafico , quindi seleziona Etichette dati dal Elementi del grafico casella di riepilogo, vedi screenshot:

9. Le barre delle colonne sono così strette che non sembrano belle, in questo caso, puoi regolare la larghezza della barra in base alle tue necessità, fai clic con il pulsante destro del mouse su una qualsiasi delle barre, quindi scegli Formato serie di dati, Nella Formato serie di dati riquadro, sotto il Opzioni di serie scheda, cambia il file Larghezza gap secondo le tue necessità, questo caso, lo cambierò in 80%, vedi screenshot:

Suggerimenti::

Dopo aver creato il grafico, a volte, potresti voler mostrare le etichette dei dati con colori diversi, ad esempio, le etichette dei dati positivi nel colore verde e le etichette dei dati negativi nel colore arancione, per favore fai come segue:

Selezionare i dati nella colonna Varianza dalla tabella dati originale, quindi premere Ctrl + 1 per aprire il formato celle finestra di dialogo, nel file formato celle finestra di dialogo, fare clic Custom dal Categoria casella di riepilogo e quindi digitare il codice seguente nel file Tipologia casella di testo:

[Color10]General;[Color53] -General

Quindi fare clic su OK pulsante e otterrai il risultato di cui hai bisogno, vedi screenshot:

Note:: Se hai bisogno di altri colori, visualizza la seguente tabella dell'indice dei colori:


Crea un mini budget rispetto al grafico della varianza effettiva nelle celle

Se desideri inserire un mini budget rispetto al grafico della varianza effettiva nelle celle, i seguenti passaggi potrebbero farti un favore:

1. Immettere la formula seguente nella cella D2 per calcolare la differenza tra il budget e i valori effettivi, quindi trascinare il quadratino di riempimento verso il basso sulle celle necessarie, vedere screenshot:

=C2-B2

2. Quindi, visualizza i valori negativi della varianza, applica la seguente formula nella cella E2, quindi copia la formula in altre celle che ti servono, vedi screenshot:

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

3. Visualizza i valori positivi della varianza, digita la formula seguente nella cella F2, quindi copia la formula in altre celle necessarie, vedi screenshot:

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

4. E quindi, dovresti inserire un simbolo specifico che verrà utilizzato come barre del grafico, fai clic inserire > Simbolo, E nel Simbolo finestra di dialogo, selezionare Elementi di blocco che puoi trovare in quasi tutti i caratteri del file Sottoinsieme elenco a discesa e fare clic su Blocco completo simbolo, quindi fare clic su inserire per inserire il simbolo, vedi screenshot:

5. Dopo aver inserito il simbolo speciale, quindi, applica la formula seguente nella cella G2 e trascina questa formula su altre celle e otterrai il risultato seguente:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Note:: Nella formula sopra, E2 è la cella contiene il valore di varianza negativo, A16 è la cella contiene il simbolo specifico che si è inserito e il numero 100 è un dato variabile che viene utilizzato per calcolare il numero del simbolo in base al valore di varianza, puoi cambiarlo con il tuo.

6. Continua ad applicare la formula seguente nella cella H2, quindi trascina questa formula su altre celle e otterrai il risultato seguente:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Note:: Nella formula sopra, F2 è la cella contiene il valore di varianza positivo, A16 è la cella contiene il simbolo specifico che si è inserito e il numero 100 è un dato variabile che viene utilizzato per calcolare il numero del simbolo in base al valore di varianza, puoi cambiarlo con il tuo.

7. Quindi, seleziona le celle nella colonna G, quindi fai clic su Casa > Allinea a destra per allineare i dati direttamente sulle celle, vedi screenshot:

8. Alla fine, puoi formattare i colori dei caratteri per le due colonne separatamente in base alle tue esigenze e otterrai il risultato come mostrato nell'immagine sottostante:


Crea un grafico della varianza effettiva rispetto al budget con una funzionalità straordinaria

Kutools for Excel fornisce dozzine di tipi speciali di grafici che Excel non ha, come Grafico puntato, Target e grafico effettivo, Grafico a freccia di differenza e così via. Con il suo pratico strumento- Grafico di confronto delle differenze, puoi creare un grafico del budget rispetto alla varianza effettiva nel foglio di lavoro o un grafico del mini budget rispetto alla varianza effettiva all'interno delle celle in modo rapido e semplice. Fai clic per scaricare Kutools per Excel per una prova gratuita!


Scarica il file di esempio del grafico Budget vs Actual Variance


Video: crea un grafico della varianza effettiva e del budget in Excel


I migliori strumenti per la produttività in ufficio

Kutools per Excel: ti aiuta a distinguerti dalla folla

Funzioni popolari: Trova, evidenzia o identifica i duplicati  |  Elimina righe vuote  |  Combina colonne o celle senza perdere dati  |  Round senza formula ...
Super VLookup: Criteri multipli  |  Valore multiplo  |  Su più fogli  |  Ricerca fuzzy...
Avv. Menu `A tendina: Elenco a discesa facile  |  Elenco a discesa dipendente  |  Elenco a discesa a selezione multipla...
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  Confronta colonne con Seleziona Stesse celle e diverse ...
Funzionalità in primo piano: Messa a fuoco della griglia  |  Vista di progettazione  |  Grande barra delle formule  |  Gestore di cartelle di lavoro e fogli | Resource Library (Testo automatico)  |  Date picker  |  Combina fogli di lavoro  |  Crittografa/decrittografa le celle  |  Invia e-mail per elenco  |  Super filtro  |  Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi ...)  |  Più di 50 Grafico Tipi (Diagramma di Gantt ...)  |  40+ Pratico Formule (Calcola l'età in base al compleanno ...)  |  19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso ...)  |  12 Conversione Strumenti (Numeri in parole, Conversione di valuta ...)  |  7 Unisci e dividi Strumenti (Combina righe avanzate, Dividi celle di Excel ...)  |  ... e altro ancora

Kutools per Excel vanta oltre 300 funzionalità, Garantirti che ciò di cui hai bisogno sia a portata di clic...


Scheda Office: abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)

  • Un secondo per passare da una dozzina di documenti aperti all'altra!
  • Riduci ogni giorno centinaia di clic del mouse, dì addio alla mano del mouse.
  • Aumenta la produttività del 50% durante la visualizzazione e la modifica di più documenti.
  • Porta schede efficienti in Office (incluso Excel), proprio come Chrome, Edge e 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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations