Excel formation - Page de vente TCd - 11 Découvrez la méthode complète pour analyser les données efficacement et sans effort avec les tableaux croisés dynamiques, en cliquant ici

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - MFC sur graphique - 01

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.

Excel formation - MFC sur graphique - 02

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

Excel formation - MFC sur graphique - 03

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 :

Excel formation - MFC sur graphique - 04

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) 

Excel formation - MFC sur graphique - 05

À 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… » :

Excel formation - MFC sur graphique - 06

Ensuite, nous cliquons sur la flèche qui se trouve dans la zone de saisie de la plage de données sources :

Excel formation - MFC sur graphique - 07

Ce qui permet de venir sélectionner l’ensemble des données servant de source du graphique :

Excel formation - MFC sur graphique - 08

Nous validons ensuite en appuyant sur la touche [Entrée] du clavier.

Excel formation - MFC sur graphique - 09

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

Excel formation - MFC sur graphique - 10

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.

Excel formation - MFC sur graphique - 11

Ne reste plus qu’à modifier la couleur des barres, en effectuant un cric-droit sur celles-ci, puis en sélectionnant la couleur correspondante :

Excel formation - MFC sur graphique - 12

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 :

Excel formation - MFC sur graphique - 13

Nous pouvons bien entendu mettre en forme ces étiquettes en procédant de la manière suivante :

Excel formation - MFC sur graphique - 14

  • 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 »

Excel formation - MFC sur graphique - 15

Cette opération est à répéter pour la barre rouge :

Excel formation - MFC sur graphique - 16

 

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 :

Excel formation - MFC sur graphique - 17

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 :

Excel formation - MFC sur graphique - 18

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) 

Excel formation - MFC sur graphique - 19

Cela étant fait, nous pouvons maintenant masquer les colonnes C et D qui ne sont utilisées que pour la construction du graphique :

Excel formation - MFC sur graphique - 20

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

Excel formation - MFC sur graphique - 21

Pour les réafficher, effectuez un clic-droit sur le graphique > Sélectionner des données :

Excel formation - MFC sur graphique - 22

Ensuite dans la fenêtre de sélection des données, cliquez sur le bouton Cellules masquées et cellules vides :

Excel formation - MFC sur graphique - 23

Ne reste alors plus qu’à cocher l’option Afficher les données des lignes et colonnes masquées :

Excel formation - MFC sur graphique - 24

 

 

 

 

 

 



Articles qui pourraient vous intéresser

Comment simplifier les formules avec l’automatisation des plages nommées d’Excel
Comment ajouter automatiquement une ligne lors d’un changement de valeur dans une colonne Excel
Comment tirer une lettre aléatoire pour créer un mot de passe incrackable – La fonction CAR() d’Excel
Comment créer une zone d’impression dynamique et intelligente sur Excel
Comment supprimer les lignes vides ou avec des cellules vides d’un tableau sur Excel
Comment géolocaliser une adresse sur Google Maps en 1 clic avec Excel
Comment calculer le taux de rendement interne d’un projet sur Excel (fonction TRI)
Comment envoyer la sélection par mail en 1 clic avec VBA sur Excel
Comment afficher plusieurs onglets d'un même classeur grâce au multi-fenêtrage d'Excel
Comment convertir des CSV en fichier Excel en masse (tous les fichiers d’un répertoire) avec VBA
6 Astuces pour envoyer facilement un fichier Excel par mail (sans VBA)
3 méthodes pour transformer un texte en majuscule (ou en minuscule) sur Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.