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

Créer un graphique d'écart entre budget et réel dans Excel

Dans Excel, un graphique Budget vs Variance réelle est utilisé pour comparer deux ensembles de données de série et afficher la différence ou la variance des deux séries de données. Si les différences sont des valeurs négatives, des barres ou des colonnes colorées sont affichées, si des valeurs positives, d'autres barres ou colonnes colorées sont affichées comme ci-dessous. Cet article, je vais parler de la façon de créer le graphique de variance de comparaison et un mini graphique de variance de comparaison de cellules dans le classeur Excel.


Créer un graphique d'écart entre le budget et le réel dans Excel

Pour créer le graphique des écarts entre le budget et le réel, procédez comme suit:

1. Entrez la formule ci-dessous dans la cellule D2 pour calculer la différence entre le budget et les valeurs réelles, puis faites glisser la poignée de remplissage vers les cellules dont vous avez besoin, voir capture d'écran:

=C2-B2

2. Ensuite, sélectionnez les données dans la colonne A et la colonne D, puis cliquez sur insérer > Insérer une colonne ou un graphique à barres > Colonne groupée, voir capture d'écran:

3. Et un histogramme est inséré, vous pouvez ensuite supprimer les éléments indésirables du graphique selon vos besoins, tels que supprimer le titre du graphique, l'axe vertical, le quadrillage, et vous obtiendrez le résultat comme ci-dessous les captures d'écran:

4. Ensuite, cliquez avec le bouton droit sur l'axe horizontal et choisissez Format Axe option du menu contextuel, voir capture d'écran:

5. Dans le ouvert Format Axe volet, sous le Options d'axe onglet, cliquez sur Etiquettes pour développer cette section, puis sélectionnez Faible du Position de l'étiquette liste déroulante, et l'axe horizontal sera placé en bas du graphique, voir capture d'écran:

6. Et puis, faites un clic droit sur l'une des barres et choisissez Formater la série de données option, voir capture d'écran:

7. Dans le ouvert Formater la série de données volet, sous le Remplir et ligne onglet, à partir de Niveau de remplissage section, veuillez effectuer les opérations suivantes :

  • Sélectionnez Remplissage solide l'option;
  • Visite Inverser si négatif case à cocher;
  • Spécifiez deux couleurs pour les valeurs positives et négatives séparément du Couleur menu déroulant.

8. Après avoir défini les couleurs des barres, vous devez ajouter les étiquettes de données, cliquez pour sélectionner les barres dans le graphique, puis cliquez sur Éléments du graphique icône, puis vérifiez Étiquettes de données du Éléments du graphique zone de liste, voir capture d'écran:

9. Les barres de colonne sont si étroites qu'elles ne sont pas belles, dans ce cas, vous pouvez ajuster la largeur de la barre en fonction de vos besoins, veuillez cliquer avec le bouton droit sur l'une des barres, puis choisissez Formater la série de données, Dans le Formater la série de données volet, sous le Options de série onglet, modifiez le Largeur de l'espace à votre besoin, ce cas, je le changerai en 80 % , voir capture d'écran:

Conseils:

Après avoir créé le graphique, vous souhaiterez parfois afficher les étiquettes de données avec différentes couleurs, par exemple, les étiquettes de données positives dans la couleur verte et les étiquettes de données négatives dans la couleur orange, procédez comme suit:

Sélectionnez les données dans la colonne Variance de la table de données d'origine, puis appuyez sur Ctrl + 1 ouvrir le Format de cellule boîte de dialogue, dans la Format de cellule boîte de dialogue, cliquez sur Applications du Catégories zone de liste, puis tapez le code ci-dessous dans le Catégorie zone de texte:

[Color10]General;[Color53] -General

Puis clique OK bouton, et vous obtiendrez le résultat dont vous avez besoin, voir capture d'écran:

Notes: Si vous avez besoin d'autres couleurs, veuillez consulter le tableau d'index des couleurs suivant:


Créer un graphique mini budget vs variance réelle dans les cellules

Si vous souhaitez insérer un graphique mini budget vs variance réelle dans les cellules, les étapes suivantes peuvent vous rendre service:

1. Entrez la formule ci-dessous dans la cellule D2 pour calculer la différence entre le budget et les valeurs réelles, puis faites glisser la poignée de remplissage vers les cellules dont vous avez besoin, voir capture d'écran:

=C2-B2

2. Ensuite, affichez les valeurs négatives de la variance, appliquez la formule suivante dans la cellule E2, puis copiez la formule dans d'autres cellules dont vous avez besoin, voir capture d'écran:

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

3. Affichez les valeurs positives de la variance, tapez la formule ci-dessous dans la cellule F2, puis copiez la formule dans les autres cellules dont vous avez besoin, voir capture d'écran:

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

4. Et puis, vous devez insérer un symbole spécifique qui sera utilisé comme barres du graphique, veuillez cliquer insérer > Symboleet dans le Symbole boîte de dialogue, sélectionnez Éléments de bloc que vous pouvez trouver dans presque toutes les polices de Sous-ensemble liste déroulante, puis cliquez sur le Bloc complet symbole, puis cliquez sur insérer pour insérer le symbole, voir capture d'écran:

5. Après avoir inséré le symbole spécial, appliquez la formule ci-dessous dans la cellule G2 et faites glisser cette formule vers d'autres cellules, et vous obtiendrez le résultat ci-dessous:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Notes: Dans la formule ci-dessus, E2 la cellule contient-elle la valeur de variance négative, A16 la cellule contient-elle le symbole spécifique que vous avez inséré et le nombre 100 est une donnée variable qui est utilisée pour calculer le numéro du symbole en fonction de la valeur de la variance, vous pouvez la changer pour la vôtre.

6. Continuez à appliquer la formule ci-dessous dans la cellule H2, puis faites glisser cette formule vers d'autres cellules, et vous obtiendrez le résultat ci-dessous:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Notes: Dans la formule ci-dessus, F2 la cellule contient-elle la valeur de variance positive, A16 la cellule contient-elle le symbole spécifique que vous avez inséré et le nombre 100 est une donnée variable qui est utilisée pour calculer le numéro du symbole en fonction de la valeur de la variance, vous pouvez la changer pour la vôtre.

7. Et puis, sélectionnez les cellules de la colonne G, puis cliquez sur Accueil > Aligner à droite pour aligner les données directement sur les cellules, voir capture d'écran:

8. Enfin, vous pouvez formater les couleurs de police pour les deux colonnes séparément selon vos besoins, et vous obtiendrez le résultat comme ci-dessous:


Créez un graphique des écarts entre le budget et le réel avec une fonctionnalité incroyable

Kutools pour Excel fournit des dizaines de types spéciaux de graphiques dont Excel ne dispose pas, tels que Graphique à puces, Graphique cible et réel, Diagramme de flèche de différence etc. Avec son outil pratique Tableau de comparaison des différences, vous pouvez créer rapidement et facilement un graphique des écarts entre le budget et la réalité dans une feuille de calcul ou un mini-budget et un graphique de la variance réelle dans les cellules. Cliquez pour télécharger Kutools for Excel pour un essai gratuit!


Télécharger le fichier d'exemple de graphique d'écart entre budget et réel


Vidéo: créer un graphique d'écart entre le budget et le réel dans Excel


Les meilleurs outils de productivité de bureau

Kutools for Excel - vous aide à vous démarquer de la foule

Souhaitez-vous terminer votre travail quotidien rapidement et parfaitement? Kutools for Excel apporte 300 fonctionnalités avancées puissantes (Combiner les classeurs, somme par couleur, diviser le contenu des cellules, convertir la date, etc.) et gagner 80% de temps pour vous.

  • Conçu pour 1500 scénarios de travail, vous aide à résoudre 80% des problèmes Excel.
  • Réduisez les milliers de clics de clavier et de souris chaque jour, soulagez vos yeux et vos mains fatigués.
  • Devenez un expert Excel en 3 minutes. Plus besoin de se souvenir des formules douloureuses et des codes VBA.
  • Essai gratuit illimité de 30 jours. Garantie de remboursement de 60 jours. Mise à jour et assistance gratuites pendant 2 ans.
Ruban d'Excel (avec Kutools for Excel installé)

Onglet Office - Activer la lecture et la modification par onglets dans Microsoft Office (y compris Excel)

  • Une seconde pour basculer entre des dizaines de documents ouverts!
  • Réduisez chaque jour des centaines de clics de souris, dites adieu à la souris.
  • Augmente votre productivité de 50% lors de l'affichage et de l'édition de plusieurs documents.
  • Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Firefox et le nouvel Internet Explorer.
Capture d'écran d'Excel (avec Office Tab installé)
Commentaires (1)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
merci
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés