PrecedentSuivant

Excel - Exercices de mise en forme conditionnelle

Sur cette page

Introduction
Avant de commencer
Mise en forme conditionnelle
Copier la mise en forme conditionnelle
La barre de données
Nuances de couleurs
Les jeux d'icônes
Mise en forme conditionnelle selon le contenu d'une autre cellule ou d'une formule

Introduction

Excel peut changer la présentation d’une cellule selon des règles que vous avez établies. Le contenu peut changer de couleur de la cellule, la couleur, la taille et le style de la police de caractère, l'encadrement de la cellule et plusieurs autres options de présentation pour vous avertir d’un changement important qui nécessite votre attention. Les mises en forme conditionnelles peuvent aussi être utilisées sous d’autres formes. Microsoft a grandement amélioré cette option avec Excel 2007. Vous avez maintenant accès à de nouvelles options qui étaient seulement disponibles en utilisant des logiciels spécialisés.

Avant de commencer

Pour suivre cet exercice, vous pouvez aller chercher le classeur demos-excel-3.xlsx qui se trouve sur la page des fichiers de démonstrations.

*Sélectionnez la feuille de calcul Mise en forme.

Mise en forme conditionnelle

Le prochain exercice vous démontre comment utiliser la mise en forme conditionnelle dans une situation de suivi d’inventaire.
L’exercice consiste à s’assurer d’un certain niveau d’inventaire. Certaines règles de mise en forme conditionnelles seront appliquées pour vous informer du niveau d’inventaire.


Données de base A1:C6

La première colonne indique le nom du produit. La seconde colonne indique le minimum de produit à conserver. Il faut en commander d’autres unités lorsque la quantité disponible, la troisième colonne, est inférieur au minimum. La commande mise en forme conditionnelle sera utilisée pour afficher en rouge lorsque la quantité disponible sera au niveau critique.

Excel - Menu mise en forme conditionnelle

*Placez le curseur dans la cellule C4.
*Allez à l’onglet Accueil.
*Appuyez sur le bouton Mise en forme conditionnelle.
*Sélectionnez les options Règles de mise en surbrillance des cellules et Inférieur à …

Excel - Mise en forme conditionnelle - inférieur à =b4

 


*Assurez-vous que la cellule sélectionnée soit =B4 (et non =$B$4 qui bloquerait la copie de cette règle).
*Pour cet exemple, assurez-vous que le style de présentation choisi soit Remplissage rouge clair avec texte rouge foncé.
*Appuyez sur le bouton OK.
*Changez la valeur de la cellule C4 à 3.

Excel - Mise en forme conditionnelle - Résultat de mise en forme sur cellule B4

La règle est activée puisque la valeur de la cellule est inférieure au minimum. On peut aussi changer les couleurs ou la présentation. Le prochain exemple va demander de changer la présentation au jaune si la valeur de la cellule C4 s’approche du seuil minimale. Dans ce cas, si la valeur est entre 1 à 5 unités de plus que le seuil minimal.

*Placez le curseur dans la cellule C4.
*Sélectionnez les options Règles de mise en surbrillance des cellules et Entre …

Excel - Mise en forme conditionelle - règle Entre =b4 et =b4+4

*Dans la première case, entrez =B4.
*Dans la seconde case, entrez =B4+4.
*De la liste des styles de présentation, sélectionnez Remplissage jaune avec texte jaune foncé.
*Appuyez sur le bouton OK.
*Changez la valeur de la cellule C4 à 9.

Excel - Mise en forme conditionelle - resultat entre =b4 et =b4+4

*Entrez d'autres valeurs dans la cellule C4 pour voir le résultat.

Copier la mise en forme conditionnelle

Il est possible de copier les règles de mise en forme conditionnelle que vous avez instaurées sur une cellule à d’autres. C’est d’ailleurs plus pratique que de les refaire !

*Placez le curseur dans la cellule C4.

Excel - Reproduire la mise en forme

*Sous l’onglet Accueil, dans le groupe Presse-papier, sélectionnez la commande Reproduire la mise en forme.
*Copiez la mise en forme aux cellules C5 et C6.

Excel - mise en forme conditionnelle - Résultat de copier la mise en forme

La mise en forme conditionnelle va fonctionner seulement si vous avez retiré les « $ » des adresses des cellules. Sinon, la règle reproduite sera toujours =$B$4 et non =B4. Au lieu de comparer les quantités minimales des produits, la règle va comparer les quantités disponibles au seuil minimum du produit 100. Comme pour les formules, les règles dans la mise en forme conditionnelle se préoccupent aussi des références relatives et absolues.

Barre de données

Une nouvelle option de la mise en page conditionnelle consiste à représenter les valeurs sous forme de barre horizontale. Il est donc possible de comparer le contenu de plusieurs cellules juste en comparant leurs barres de données. Le prochain exercice consiste à comparer les moyennes des cinq élèves.

Excel - Mise en forme conditionnelle - Données de base pour les barres de données

*Sélectionnez les cellules G14 à G18.
*De l’onglet Accueil, sélectionnez la commande Mise en forme conditionnelle et l’option Barres de données.

Excel - mise en forme conditionnelle - barre de données

*Sélectionnez la couleur des barres de votre choix.

Excel - Mise en forme conditioennelle - résultat

Cette option vous permet de représenter des chiffres sous formes de barres. Et vous n’avez même pas besoin de créer un graphique !

*Élargissez la colonne et regardez le résultat.

Excel - mise en forme conditionelle - Résultat colonne allongée

Les barres de données s’ajustent à la nouvelle largeur de la colonne. Il devient encore plus facile de les comparer.

Nuances de couleurs

Une autre option qui a été ajoutée à Excel est de représenter les chiffres avec une palette de couleurs. Le prochain exercice consiste à placer une carte de nuances de couleurs par-dessus les notes des étudiants.

Excel - Mise en forme conditionnelle - Données de base pour les barres de données

*Sélectionnez les cellules B14 à F18.
*De l’onglet Accueil, sélectionnez la commande Mise en forme conditionnelle  et l’option Nuances de couleurs.

Excel - Mise en forme conditionelle - Nuances de couleurs

*Sélectionnez la série de couleurs de votre choix.

La couleur au bas de l'icone représente la valeur inférieure et la couleur au dessus représente la valeur maximale.

Excel - Mise en ofrme conditionelle - résultat de nuances de couleurs

Le terme anglais pour cette représentation est un « heat map ». Le niveau d’intérêt aux données est représenté par une couleur plus vive ou pâle selon le cas. C’est une autre façon de « voir » les données et d’attirer l’attention vers les valeurs qui le méritent.

Jeux d'icônes

Une autre façon de représenter les valeurs dans les cellules est en utilisant des icônes. Excel 2007 offre une bonne variété d’icônes disponibles. Le prochain exercice consiste à placer des icônes sur une série de chiffres.

Excel - Mise en forme conditionelle - donnes pour jeux d'icônes

*Sélectionnez les cellules A23 à A27.
*Sélectionnez l’onglet Accueil.
*Appuyez sur le bouton Mise en forme conditionnelle.
*De la liste des options, sélectionnez Jeux d’icônes.

Excel - Mise en forme conditionelle - Jeux d'icones

Excel offre une bonne sélection d’icônes pour représenter ce que vous désirez. L’aperçu instantané vous permet de voir quelle série d’icônes serait le plus appropriée pour vos besoins.

*De la liste des icônes disponibles, sélectionnez l’option Autres règles.

Excel - Mise en forme conditionelle - Options jeux d'icones

Vous n’êtes pas limité à utiliser les règles établies par Excel. Vous pouvez les modifier pour mieux représenter vos besoins. Essayez de changer quelques règles et constatez les résultats.

Mise en forme conditionnelle selon le contenu d'une autre cellule ou d'une formule

Les exemples précédents démontraient comment appliquer à une cellule une mise en forme conditionnelle selon son contenu. Le prochain exemple démontre comment appliquer une mise en forme conditionnelle selon le contenu d’une autre cellule ou d’une formule.

Excel - Mise en forme conditionelle - données de base pour appliquer une règle sur plusieurs cellules

*Commencez ces exemples dans la cellule B1 d’une nouvelle feuille de calcul.
*Sous l’onglet Accueil, sélectionnez les options Mise en forme conditionnelle et Nouvelle règle.

Excel - Mise en forme conditionnelle - regle à appliquer sur plusieurs cellules

La case du haut montre tous les types de mise en forme conditionnelle que vous pouvez appliquer.

*Sélectionnez l’option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

Vous devez créer une formule dont le résultat doit être soit VRAI ou FAUX. L’option VRAI activera la mise en forme conditionnelle. L’option FAUX ne l’activera pas.

*Pour cet exemple, entrez la formule =$a1>=150 .

Le résultat de cette formule devrait être soit VRAI ou FAUX. Ce sont les options requise pour activer ou pas une mise en forme conditionnelle. De plus, la formule peut être plus complexe et base sur plusieurs critères que les options offerts dans le menu de mise en forma coditionnelle. Vous auriez pu aussi utiliser la fonction OU(), ET() ou tout autre fonction tant que le résultat soit VRAI ou FAUX. La fonction SI() gère les résultats VRAI et FAUX à l’intérieur de la fonction. Mais vous pouvez aussi l’utiliser de cette façon =si(condition;VRAI ;FAUX).

Le « $ » dans la formule est important pour la suite de l’exercice. Les références relatives et absolues s’appliquent de la même façon dans la mise en forme conditionnelle que pour les formules dans les cellules. Dans ce cas ci, la formule va toujours chercher le contenu dans la colonne A.

*Appuyez sur le bouton Format.

Excel - mise en forme conditionelle - Règle sur plusieurs cellules - remplissage

Cette fenêtre vous permet de changer la présentation des valeurs, de la police de caractères, du style de bordure ou de la couleur et style du fond de la cellule.

*Sélectionnez l’onglet Remplissage.
*Changez la couleur d’arrière-plan à jaune.
*Appuyez sur le bouton OK.

*Appuyez sur le bouton OK une autre fois pour compléter la règle.

La prochaine partie consiste à répliquer cette règle aux cellules B1 à E5.

*Placez le curseur sur la cellule B1.

Excel - Reproduire la mise en forme

*Sous l’onglet Accueil, dans le groupe Presse-papier, sélectionnez la commande Reproduire la mise en forme.
*Sélectionnez les cellules B1 à E5.

*Entrez des chiffres dans les cellules A1 à A5.

Excel 2010 - Mise en forme conditionnelle - formule - résultat

Vous verrez l’impact sur les cellules de la ligne. C’est pour cette raison que le « $ » a été placé dans la formule. Il indique à la mise en forme conditionnelle de vérifier le contenu de la colonne A au lieu de la cellule qui est juste à la gauche de celle-ci.