MISE EN FORME CONDITIONNELLE SUR UN GRAPHIQUE EXCEL
Dans ce nouveau tutoriel, je vais vous montrer comment procéder pour appliquer une mise en forme conditionnelle sur un graphique de type histogramme sur Excel. Cette mise en forme conditionnelle va ici nous permettre de faire apparaître d’un seul coup d’œil les mois au cours desquelles les ventes d’une entreprise ont été les plus importantes, ou au contraire les plus faibles. Et elle sera capable de s’adapter automatiquement lors des mises à jour des données sources.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons partir d’un tableau très simple, dans lequel nous retrouvons les ventes mensuelles réalisées par une entreprise au cours des douze mois de l’année 2019 :
Ces ventes sont également représentées sur un graphique de type histogramme très simple composé de douze barres.
L’objectif va alors de faire ressortir les mois au cours desquels les ventes ont atteint leur maximum, ou au contraire lorsque celles-ci ont été les plus faibles.
L’étude des données mensuelles fait apparaître que :
- Le mois au cours duquel les ventes ont été les plus faibles est le mois de mai avec 99532€ de ventes, nous allons souhaiter faire apparaître la barre en rouge,
- Le mois au cours duquel les ventes ont été les plus importantes est le mois de septembre avec 981919€ de ventes, nous allons souhaiter faire apparaître la barre en vert,
2. Modifier les couleurs minimales et maximales
L’astuce pour parvenir à créer une mise en forme conditionnelle sur un graphique va consister à dessiner des nouvelles barres par-dessus le graphique existant.
Pour cela, nous allons tout d’abord commencer par insérer deux colonnes juste à droite du tableau dans lesquelles nous allons devoir mettre en place deux formules.
Dans la colonne Max, nous allons vouloir de faire apparaître que les ventes réalisées lors du mois ayant généré le plus de ventes (en l’occurrence, le mois septembre), tous les autres mois devront faire apparaître des ventes nulles).
Pour cela, nous allons utiliser deux fonctions Excel bien connues :
- La fonction SI() qui permet de réaliser un test afin de retourner une valeur donnée lorsque le test retourne la valeur VRAI, et une autre valeur lorsque le résultat du test est égal à FAUX,
- La fonction MAX() permet de retourner la valeur maximale au sein d’une plage de cellules.
Vous aurez donc bien compris, que nous allons demander à la fonction SI() de tester si la valeur contenue sur la même ligne est bien égale à la valeur maximale de la série.
Lorsque tel est le cas, alors nous allons récupérer la valeur correspondante au mois de la ligne en cours d’analyse, et dans le cas contraire, nous récupérerons simplement la valeur zéro.
Voici donc la formule saisie dans la cellule C8 :
=SI(B8=MAX($B$8:$B$19);B8;0)
Bien entendu, pour pouvoir décaler ensuite la formule sur les cellules du dessous, nous devons faire en sorte de « bloquer » les coordonnées de la plage des cellules sur lesquelles nous souhaitons effectuer le test.
Pour cela, juste après avoir cette plage (B8:B19), nous appuyons sur le touche [F4] du clavier afin de faire apparaître les symboles dollars.
Il est également possible d’insérer ces symboles manuellement en utilisant la touche dédiée du clavier (cliquez ici pour tout savoir sur ce symbole dollar dans Excel).
Une fois la formule saisie, nous pouvons simplement l’étendre vers le bas en utilisant la poignée de recopie (le petit carré situé en bas à droite de la cellule C8), que nous faisons glisser vers le bas (ou en double-cliquant sur celui-ci) :
Lorsque nous relâchons le bouton de la souris, automatiquement Excel affiche le résultat des douze mois, et seules les ventes réalisées en septembre apparaissent non nulles :
Ensuite, nous pouvons procéder de la même manière pour déterminer les ventes minimales au cours de cette année.
Comme vous pouvez l’imaginer, nous allons ici remplacer la fonction MAX() par la fonction MIN() qui fonctionne exactement de la même manière mais permet cette fois-ci de faire ressortir la plus petite valeur contenue dans une plage de cellules :
=SI(B8=MIN($B$8:$B$19);B8;0)
À présent, pour faire apparaître ces mois minimums et maximums sur le graphique, nous allons devoir modifier la source des données qui alimente celui-ci.
Pour cela, nous sélectionnons simplement le graphique, puis nous effectuons un clic-droit sur celui-ci afin de sélectionner l’option « Sélectionner des données… » :
Ensuite, nous cliquons sur la flèche qui se trouve dans la zone de saisie de la plage de données sources :
Ce qui permet de venir sélectionner l’ensemble des données servant de source du graphique :
Nous validons ensuite en appuyant sur la touche [Entrée] du clavier.
Excel valide alors la modification pour tenir compte de la nouvelle plage de cellules.
Nous pouvons constater deux problèmes liés à cette modification.
Tout d’abord, le titre du graphique a disparu en raison de l’ajout des deux colonnes.
Pour le réafficher, cliquez sur le bouton plus (« + ») qui apparaît à droite du graphique, lorsque celui-ci est sélectionné, puis cochez l’option « Titre du graphique » :
Ensuite, nous pouvons constater que les nouvelles barres sont affichées, non pas au-dessus des anciennes, comme nous le souhaiterions, mais à côté…
Pour régler ce second problème, sélectionnez l’une des barres du graphique afin de faire apparaître le menu « Options des séries » sur la partie droite de la fenêtre d’Excel, puis dans le champ « Superposition des séries », nous spécifions une valeur de 100% pour que les barres supérieures se place exactement au-dessus des barres d’origine.
Ne reste plus qu’à modifier la couleur des barres, en effectuant un cric-droit sur celles-ci, puis en sélectionnant la couleur correspondante :
Pour finir, si nous souhaitons afficher la valeur de ces barres minimale et maximale, nous effectuons un nouveau clic-droit afin de choisir Ajouter des étiquettes de données > Ajouter des étiquettes de données :
Nous pouvons bien entendu mettre en forme ces étiquettes en procédant de la manière suivante :
- 1 : Tout d’abord nous sélectionnons l’une des étiquettes de données,
- 2 : Puis nous cliquons sur le bouton « Options d’étiquettes »,
- 3 : Nous spécifions le format spécial « # ##0 "k€";; », qui permet de saisir les nombres en kilo euros et de masquer,
- 4 : et enfin nous validons en appuyant sur le bouton « Ajouter »
Cette opération est à répéter pour la barre rouge :
3. Mettre en avant les trois meilleurs mois
Maintenant, et pour aller un peu plus loin, imaginons que nous souhaitions, non plus mettre seulement en avant le meilleur mois de ventes, mais carrément les trois meilleurs mois (TOP 3) !
Pour cela, nous allons modifier très légèrement la fonction utilisée dans la colonne MAX.
Et nous allons utiliser la fonction RANG() qui permet de faire ressortir le classement de la valeur contenue dans une cellule parmi les valeurs contenues au sein de plusieurs cellules :
=SI(RANG(B8;$B$8:$B$19)<=3;B8;0)
Nous regardons donc ici si le rang représenté par les ventes du mois est au moins inférieur à trois.
Lorsque c’est le cas, nous pouvons reprendre les ventes réalisées au cours du mois, et dans le cas contraire nous reprenons simplement des ventes nulles :
Bien entendu, nous pouvons faire de même pour la colonne Min.
Pour inverser le résultat du classement, il suffit simplement d’utiliser l’argument facultatif Ordre :
Cet argument prend par défaut la valeur de 0 qui permet d’effectuer un tri décroissant (du plus grand au plus petit).
Pour inverser le tri, nous lui donnons simplement la valeur de 1 pour effectuer un tri croissant (du plus petit au plus grand).
=SI(RANG(B8;$B$8:$B$19;1)<=3;B8;0)
Cela étant fait, nous pouvons maintenant masquer les colonnes C et D qui ne sont utilisées que pour la construction du graphique :
(Pour cela, nous les sélectionnons, puis à l’aide du clic-droit nous choisissons Masquer)
À ce moment-là, la mise en forme conditionnelle disparaît car le graphique ne voit plus les données correspondantes :
Pour les réafficher, effectuez un clic-droit sur le graphique > Sélectionner des données :
Ensuite dans la fenêtre de sélection des données, cliquez sur le bouton Cellules masquées et cellules vides :
Ne reste alors plus qu’à cocher l’option Afficher les données des lignes et colonnes masquées :