7. Mise en forme conditionnelle


La mise en forme conditionnelle permet de définir une mise en forme (essentiellement des couleurs de police ou d’arrière-plan de cellules…) qui sera appliquée aux cellules répondant à une condition. Cela a pour intérêt de mettre en évidence des cellules qui répondent à certains critères ou de montrer des variations ou des tendances.

Un grand avantage avec cette technique est qu’après avoir défini une mise en forme conditionnelle, Excel met à jour automatiquement la mise en forme, chaque fois que vous opérez des mises à jour au niveau de votre feuille.

Pour définir une mise en forme conditionnelle, nous utiliserons :

  • Soit la commande Mise en forme conditionnelle de l’onglet Accueil du ruban
  • Soit le bouton Analyse rapide

Nous allons vous donner une présentation de ce bouton Analyse rapide. Ensuite, nous verrons une multitude d’exemples concrets de mise en forme conditionnelle.

7.1. Le bouton Analyse rapide

Ce bouton introduit à partir de la version 2013 d’Excel, apparait dès que vous sélectionnez une plage de cellules… au coin inférieur droit de la sélection : Bouton Analyse rapide

Cliquez sur le bouton Analyse rapide, pour voir apparaître des options de mise en forme conditionnelle.

Si les cellules sélectionnées contiennent uniquement du texte, les options qui apparaissent sont :

  • Cellules contenant le texte
  • Valeurs en double
  • Valeurs uniques
  • Contenu égal à
  • Annuler la mise en forme
Bouton Analyse rapide Texte

Si les cellules sélectionnées contiennent des nombres, les options qui apparaissent sont :

  • Barres de données
  • Echelle de couleurs
  • Jeux d’icônes
  • Supérieure à
  • 10 % des valeurs les plus élevées
  • Annuler la mise en forme
 Bouton Analyse rapide Nombres

D’autres commandes de mise en forme conditionnelle sont disponibles à partir de la commande Mise en forme conditionnelle du groupe Styles de l’onglet Accueil du ruban.

7.2. Valeurs inférieures à, supérieures à, comprises entre

Soit l’extrait suivant d’une feuille de calcul :

Exemple Mise en forme conditionnelle 

Appliquons une mise en forme pour les valeurs de la colonne B comme suit :

  • Valeurs supérieures à 8000 en vert
  • Valeurs comprises entre 4000 et 8000 en jaune
  • Valeurs inférieures à 4000 en rouge

Sélectionnez la plage B2:B14 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Règles de mise en surbrillance des cellules, puis choisissez Supérieurs à :

 Mise en forme conditionnelle Supérieurs à

Au niveau de la boite de dialogue Supérieur à qui apparaît, entrez la valeur 8000 et sélectionnez Remplissage vert avec texte vert foncé :

Boite de dialogue Supérieurs à 

Gardez la plage B2:B14 sélectionnée et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Règles de mise en surbrillance des cellules, puis choisissez Entre :

Mise en forme conditionnelle Entre 

Au niveau de la boite de dialogue Entre, entrez la valeur 4000, la valeur 8000 et sélectionnez Remplissage jaune avec texte jaune foncé :

Boite de dialogue Entre 

Sélectionnez encore la plage B2:B14 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Règles de mise en surbrillance des cellules, puis choisissez Inférieurs à :

 Mise en forme conditionnelle Inférieurs à

Au niveau de la boite de dialogue Inférieur à, entrez la valeur 4000 et sélectionnez Remplissage rouge clair avec texte rouge foncé :

 Boite de dialogue Inférieurs à

Voilà le résultat :

 Exemple Mise en forme conditionnelle

Modifions une valeur ; par exemple au niveau de la ligne 4, mettons la valeur 2200 et validons par la touche Entrer du clavier. Remarquez que la cellule B4 prend automatiquement la couleur rouge :

Exemple Mise en forme conditionnelle

7.3. Cellules contenant le texte

Soit l’extrait de feuille de calcul suivante :

 Exemple Mise en forme conditionnelle

Supposons que nous voulons montrer les cas habitants au Cours Victor Hugo. Procédons comme suit :

Sélectionnez la plage C4:C16. Le bouton Analyse rapide Bouton Analyse rapide apparait en bas de la sélection. Cliquez sur le Bouton Analyse rapide puis sur le bouton Cellules contenant le texte:

Cellules contenant le texte

La boite de dialogue Texte qui contient apparait :

Texte qui contient 

Au niveau de cette boite de dialogue, vous pouvez saisir le texte cherché, c’est-à-dire « Victor Hugo ». Mais, puisque le texte cherché est écrit à la cellule B1, cliquez sur cette cellule. Excel ajoute la valeur « =$B$1 ». L’avantage est que, si vous voulez chercher un autre texte, il suffit de le saisir au niveau de la cellule B1, sans avoir à refaire la mise en forme.

Au niveau de la liste des formats, vous pouvez choisir un format de la liste ou bien choisir Format personnalisé…

 Texte qui contient

En choisissons Format personnalisé…, la boite de dialogue Format de cellule apparaît. Vous pouvez composer ici le format qui vous convient en utilisant les 4 onglets de cette boite de dialogue:

Format de cellule

Choisissez un format et validez en cliquant sur le bouton OK.

Cliquez ensuite sur le bouton OK de la boite de dialogue Texte qui contient. Remarquez les champs mis en évidence à l’aide du format que vous avez choisi:

Exemple Mise en forme conditionnelle 

Maintenant, modifiez le texte de la cellule B1 par « Bergeret » par exemple, et validez par la touche Entrer du clavier. Remarquez que la mise en forme est actualisée automatiquement par Excel:

Exemple Mise en forme conditionnelle

7.4. Valeurs en double

Soit l’extrait de la feuille de calcul suivante contenant au niveau de la colonne A des adresses Mail :

Exemple Mise en forme conditionnelle 

Normalement, il ne doit pas y avoir d’adresse Mail en double, sinon, il s’agit d’erreur de saisi. Utilisons donc cette technique de mise en forme conditionnelle pour retrouver et corriger les cas éventuels de doublons.

Sélectionnez la plage A2:A14 et cliquez sur le Bouton Analyse rapide puis sur le bouton Valeurs en double:

Valeurs en double 

Excel met en évidence les valeurs en double de la sélection:

Exemple Mise en forme conditionnelle 

7.5. Barres de données

Soit l’extrait suivant d’une feuille de calcul :

Exemple Mise en forme conditionnelle 

Sélectionnez la plage B2:B14 et cliquez sur le Bouton Analyse rapide puis sur le bouton Barres de données:

Barres de données 

Vous pouvez procéder autrement. Sélectionnez la plage B2:B14 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Barres de données, puis choisissez la couleur qui vous convient :

Barres de données

Excel montre l’importance des valeurs de la sélection de manière similaire à un graphique :

Exemple Mise en forme conditionnelle 

7.6. Jeux d’icônes

Soit l’extrait suivant d’une feuille de calcul :

Exemple Mise en forme conditionnelle 

Sélectionnez la plage B2:B15 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Jeux d'icônes, puis choisissez un ensemble d’icônes :

Jeux d'icônes 

Le résultat est comme suit :

Exemple Mise en forme conditionnelle 

L’inconvénient avec ce résultat est que la couleur verte indique les grands nombres d’infractions (6 et 7), alors qu’elle doit indiquer les petits nombres. D’autres part, je suppose que nous voulons une répartition différente entre les 3 couleurs, soit par exemple :

  • Couleur rouge pour 4 et plus
  • Couleur orange pour 2 et 3
  • Couleur verte pour 1

Pour avoir ce résultat, procédez comme suit :

Sélectionnez encore la plage B2:B15 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Jeux d'icônes, puis choisissez Autres règles :

 Autres règles

La boîte de dialogue Nouvelle règle de mise en forme apparaît :

Nouvelle règle de mise en forme 

Cliquez sur le bouton Ordre inverse des icônes, modifiez le Type en « Nombre » et les valeurs comme suit :

Nouvelle règle de mise en forme 

Validez à l’aide du bouton OK. Vous deviez avoir le résultat suivant :

 Exemple Mise en forme conditionnelle

7.7. Nouvelle règle de mise en forme

Soit l'extrait de la feuille de calcul suivante:

Exemple Nouvelle règle de mise en forme

Nous voulons mettre en évidence les noms des abonnées pour lesquels le don dépasse 4 000 Euros et qui habitent à Paris par exemple. Pour cela, nous allons créer une nouvelle règle de mise en forme.

  • Sélectionnez la plage de cellules à la colonne A où est inscrit les noms des abonnées. Par exemple la plage A2:A80.
  • Cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Nouvelle règle..

Exemple Nouvelle règle de mise en forme

Au niveau de la boite de dialogue Nouvelle règle de mise en forme qui apparaît :

  • Cliquez sur Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué de la liste Sélectionnez un type de règle...
  • Au champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie entrez la formule :

=ET(E2>4000;F2="Paris")

Le résultat de cette formule est vrai lorsque les conditions 'E2>4000' et 'F2="Paris"' sont tous les deux vrais. Consultez nos rubriques sur les Fonctions Excel 2016 pour plus d'information sur la fonction ET.

  • Cliquez ensuite sur le bouton Format... pour ouvrir la boite de dialogue Format de cellule et choisissez le format qui vous convient. L'utilisation de cette boite de dialogue est mentionnée à la section 7.3. ci-dessus.
  • Valider à l'aide du bouton OK.

Boite de dialogue Nouvelle règle de mise en forme

Voici le résultat :

Exemple Nouvelle règle de mise en forme

J'attire votre attention sur le fait que dans la formule j'ai utilisé les adresses des cellules E2 et F2. Ces cellules correspondent à la 1ère cellule de la sélection. C'est à dire celle située en haut à gauche de la sélection; soit A2. La formule est appliquée aux autres cellules de la sélection de manière similaire à celle de la copie incrémentée.

Et si nous voulons mettre en évidence toute la ligne pour laquelle la condition est vraie. Comme ça :

Exemple Gestion de règles de mise en forme

Explications à la section suivante.

7.8. Consulter et modifier les règles de mise en forme

Modifions la règle définie à la section précédente afin de mettre en évidence toute la ligne pour laquelle la condition est vraie :

  • Cliquez sur une cellule de la plage A2:A80 ; là où est définie la mise en forme conditionnelle
  • Cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Gérer les règles...

Exemple Gestion de règles de mise en forme

  • La boite de dialogue Gestionnaire des règles de mise en forme conditionnelle s'ouvre

Boite de dialogue Gestionnaire des règles de mise en forme conditionnelle

Au niveau de cette boite de dialogue,

  • Vous pouvez afficher les règles de mise en forme pour :
    • La sélection actuelle
    • La feuille en cours
    • Ou chacune des feuilles du classeur en cours
  • La liste des règles est affichée au tableau en bas. A l'extrait ci-dessus, deus lignes correspondant à deux règles de mise en forme
  • Les boutons Nouvelle règle..., Modifier la règle... et Supprimer la règle... permettent comme l'indiquent leurs noms de :
    • Créer une nouvelle règle de mise en forme
    • Modifier la règle sélectionnée à la liste en bas
    • Supprimer la règle sélectionnée à la liste en bas

Je rappelle que nous voulons Modifier la règle définie à la section précédente afin de mettre en évidence toute la ligne pour laquelle la condition est vraie à savoir; don supérieur à 4000 et ville égal Paris. Pour cela, à la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle :

  • Modifier le champ S'applique à pour la règle en :

=$A$2:$F$80

NB. Remarquez que ce champ est modifiable à la boite de dialogue.

NB. Vou pouvez maintenant cliquez sur le bouton OK et voir le résultat. le reste de la ligne ne prend pas la mise en forme définie.
Pourquoi ? Je rappelle encore une fois qu'avec les références de cellule relatives E2 et F2, Excel incrémente le E et le F pour les cellules des colonnes B, C, ... C'est à dire que pour la cellule B2 par exemple, Excel vérifiera les conditions aux cellules F2 et G2...
Il faut, donc, modifier la formule de la règle en : =ET($E2>4000;$F2="Paris"). Pour cela :

  • Cliquez sur la ligne de la règle que nous voulons modifier
  • Cliquez sur le bouton Modifier la règle...
  • Au niveau de la boite de dialogue Modifier la règle de mise en forme qui apparaît, entrez au champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie la formule :

=ET($E2>4000;$F2="Paris")

Boite de dialogue Modifier la règle de mise en forme

  • Cliquez sur le bouton OK pour revenir à la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle
  • Cliquez sur le bouton OK et voyez le résultat :

Exemple Gestion de règles de mise en forme

7.9. Annuler la mise en forme conditionnelle

Pour annuler la mise en forme conditionnelle pour une plage de cellules, sélectionnez cette palge de cellule et utilisez le bouton Analyse rapide, puis le bouton Annuler la mise en forme :

 Annuler la mise en forme

NB. Vous pouvez également annuler une mise en forme conditionnelle en passant par la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle comme mentionné à la section précédente.

7.10. Copie de mise en forme conditionnelle

S’agissant d’une mise en forme, sa copie d’une plage de cellules à une autre doit se faire à l’aide du bouton Reproduire la mise en forme Bouton Reproduire la mise en forme

Mais, si vous faites une copie de contenu de cellules, par exemple, à l’aide de la technique de copie incrémentée, alors la mise en forme est copiée par Excel en même temps.

Exercice

Exercice – Recherche de doublons


<< 6. Utilisation de calculs

8. Travail avec les feuilles de calcul >>

Commentaires des lecteurs

Ajouter un commentaire