Mise en forme conditionnelle dans les feuilles de calcul Excel 2007 et 2010 à l’aide de formules et de jeux d’icônes

Robbie écrit principalement sur Skyrim, mais met également occasionnellement en lumière les chances des applications Microsoft telles qu’Excel et Outlook.

La mise en forme conditionnelle vous permet d’afficher visuellement les différences dans vos données pour vous permettre de les analyser plus facilement afin d’illustrer les exceptions et de mettre en évidence les tendances. Il est également précieux pour présenter vos données ; il est particulièrement utile pour créer des documents Red Amber Green (appelés RAG) à partir de vos données.

Vous pouvez appliquer une variété de formats différents à l’aide de la mise en forme conditionnelle dans Excel 2007 et 2010, notamment :

• Couleurs dégradées ou échelles de couleurs
• Bars à rencontre
• Ensembles d’icônes

Dans l’article d’aujourd’hui, nous allons travailler sur un certain nombre d’exemples d’utilisation de la mise en forme conditionnelle.

• Tout d’abord, nous utiliserons un jeu d’icônes pour illustrer si un club de football (soccer) a augmenté ou diminué ou est resté le même dans un tableau de classement par rapport à la semaine dernière.
• Enfin, nous utiliserons une formule pour mettre en évidence les cellules en fonction de leurs valeurs. Pour cet exemple, nous allons coder par couleur les données de visite Web et de consultation de page en fonction d’une clé (vert pour plus de 100, orange pour plus de 150 et jaune pour plus de 250).

Vous pouvez voir un certain nombre d’exemples d’utilisation de la mise en forme conditionnelle ci-dessous en utilisant :

• Jeux d’icônes (en haut à gauche)
• Formules (en haut à droite)
• Cases à cocher (en bas à gauche)
• Zones de liste déroulante (en bas à droite)

Mise en forme conditionnelle à l’aide de jeux d’icônes

Dans notre premier exemple, nous utiliserons la mise en forme conditionnelle sur un tableau de ligue de football (soccer) pour indiquer si une équipe a fait mieux, moins bien ou est restée la même par rapport à sa position dans la ligue dans le tableau de la semaine dernière.

Le tableau de classement est créé à l’aide de la MATCH fonction. Pour savoir exactement comment je l’ai créé, mon article se trouve ici.

Pour déterminer si une équipe a augmenté, diminué ou est restée la même par rapport à la semaine dernière, j’ai utilisé MATCH pour trouver la position des équipes dans le tableau de cette semaine, (Ligne Q) et le tableau de la dernière semaine (Ligne R). j’ai alors utilisé SI déclarations pour donner à une équipe un 3 si le montant a augmenté, 2 s’il est resté le même et un 1 s’il a baissé dans le tableau.

Pour en savoir plus sur l’utilisation SI déclarations dans Excel, j’ai écrit un excellent article qui explique la fonction SI de manière beaucoup plus détaillée, que vous pouvez trouver ici.

Passons maintenant à la mise en forme conditionnelle elle-même. J’ai copié le contenu de S3 à S26 à A3:A26. Je les ai cachés en utilisant une astuce que vous pouvez utiliser dans Excel pour masquer le contenu d’une cellule. Pour y parvenir:

Faites défiler pour continuer

• clic-droit sur une cellule et choisissez Formater les cellules
• Sélectionner Douane
• dans le Type de champ Entrer ;;;
• Cliquez sur D’ACCORD

Le contenu de la cellule disparaîtra de la vue !

Pour appliquer une mise en forme de condition :

• Sélectionnez A3: A26
• Clique le Mise en forme conditionnelle bouton dans le modes groupe sur le Maison languette
• Sélectionner Nouvelle règle
• Choisir Mettre en forme toutes les cellules en fonction de leurs valeurs
• En dessous de Style de mise en formeprendre Ensembles d’icônes
• Configurez chaque icône comme dans la figure ci-dessous
• Enfin, le Style d’icône devrait être 3 flèches (colorées)

avant de cliquer D’ACCORD pour appliquer la mise en forme, votre boîte de dialogue doit ressembler à la mienne ci-dessous.

Excel formatera automatiquement les cellules sélectionnées et ressemblera à mon tableau ci-dessous.

Mise en forme conditionnelle à l’aide de formules simples

La possibilité d’utiliser des formules en conjonction avec la mise en forme conditionnelle vous offre une grande flexibilité dans votre mise en forme. Dans mon exemple, je veux des cellules qui contiennent :

• Un nombre supérieur à 100 mais inférieur à 149 à remplir en vert.
• Si Excel trouve un nombre supérieur à 150 mais inférieur à 249, la cellule doit être remplie en orange.
• Toutes les cellules contenant un nombre supérieur à 250 doivent être remplies en jaune.

La première étape consiste à créer notre clé qui explique le jeu de couleurs aux utilisateurs de notre feuille de calcul. Pour y parvenir :

• Entrez le texte dans les cellules B1 à B3 et remplissez une cellule pour chaque ligne dans la couleur appropriée dans C1 à C3.
• Dans chacune des cellules remplies, ajoutez la limite inférieure de la, donc pour le vert entrez 100, l’orange 150 et le jaune 250.
• Cachez le contenu de chacune de ces cellules en utilisant le ;;; méthode décrite ci-dessus pour compléter la clé.

Passons ensuite à la mise en forme conditionnelle.

• Sélectionner la plage de cellules que vous souhaitez formater dans mon exemple C5 à D40
• Clique sur le Mise en forme conditionnelle bouton
• Choisir Nouvelle règle
• Dans la liste, cliquez sur Utiliser une formule pour déterminer les cellules à formater
• Nous ferons d’abord la formule pour le jaune, mais vous pouvez faire les formules dans n’importe quel ordre pour les trois couleurs
• La formule est =C6>=$C$3

Ce que cela demande à Excel de faire, c’est:

• Comparez C6 (la première cellule de notre gamme) avec C3 (la cellule qui contient 250 qui est le bas de notre troisième bande).
• S’il est supérieur à 250, formatez la cellule selon la règle que nous avons définie.

Noter: les signes $ dans la formule sont essentiels ou Excel n’utilisera pas systématiquement C3 pour la comparaison et la mise en forme conditionnelle ne fonctionnera pas.

Maintenant, nous appliquons le formatage pour cette règle.

• Cliquer sur format
• Sélectionnez le remplir onglet et choisissez la couleur (dans ce cas jaune)
• Cliquez deux fois sur OK pour revenir à Gestionnaire de règles de mise en forme conditionnelle

Répétez les deux ensembles d’instructions ci-dessus (pour créer la formule et également sélectionner le formatage de votre choix) jusqu’à ce que vous ayez quelque chose de similaire au mien ci-dessous.

La dernière étape consiste à s’assurer que l’ordre des règles est correct.

Pour vous assurer qu’Excel n’applique pas les règles de manière incorrecte, vous devez disposer du jaune règle top, alors orange alors vert.

En effet, 173, par exemple, est supérieur à 100 et 150, donc si vous avez les règles dans le mauvais ordre (par exemple vert au-dessus d’orange), 173 deviendra vert. La figure ci-dessous illustre cela, comme vous pouvez le voir, il n’y a pas de cellules remplies d’orange dans la plage.

Noter: Utilisez les flèches bleues vers le haut et vers le bas dans la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle pour classer correctement vos règles.

Avec les règles correctement ordonnées, la mise en forme conditionnelle fonctionne parfaitement et comme prévu.

Utilisation de MAX et MIN et de la mise en forme conditionnelle pour illustrer les valeurs les plus petites et les plus grandes d’une plage

En plus d’utiliser des formules simples supérieures et inférieures à pour la mise en forme conditionnelle, nous pouvons également utiliser des fonctions. Dans cet exemple, nous utiliserons MIN et MAX pour trouver les valeurs les plus petites et les plus grandes de notre gamme.

Comme nous l’avons fait ci-dessus, nous devons créer une nouvelle règle basée sur une formule.

Nous créons une nouvelle règle en utilisant MAX pour trouver le plus grand nombre et aussi un en utilisant MIN pour trouver le plus petit nombre.

La formule que nous utiliserons est :

=MAX($C$8:$D$42)=C8

Ce que cela demande à Excel, c’est si C8 est la plus grande valeur de la plage C8 à D42. Il vérifiera ensuite toutes les cellules de la plage une par une pour trouver la plus grande et elle sera formatée avec un remplissage rouge.

MIN fonctionne de manière très similaire, cette fois en recherchant la plus petite valeur, la formule étant :

=MIN($C$8:$D$42)=C8

Noter: Assurez-vous que les règles sont ordonnées correctement avec le MIN et MAX règles en haut pour s’assurer qu’elles ne sont pas écrasées (comme le MAX sera également supérieure à 250 et serait autrement remplie en jaune).

Si vous souhaitez en savoir plus sur les deux MIN et MAX fonctions, j’ai un article qui les couvre à la fois plus en profondeur et présente les PETIT, GRAND INDEX et MATCH les fonctions. J’utilise cette suite de fonctions pour créer des listes dynamiques de top 10 et des classements. Cet article peut être trouvé ici.

conclusion

La mise en forme conditionnelle dans Excel est un excellent moyen de permettre à vos données de raconter visuellement une histoire à l’utilisateur de vos feuilles de calcul. Dans mes exemples d’aujourd’hui, j’ai illustré les performances des équipes de football (soccer) cette semaine par rapport à cette semaine et j’ai regroupé les données de visite du site Web pour illustrer la performance du site Web ce jour-là. De plus, vous pouvez l’utiliser pour :

• Illustrer une tendance
• Pour signaler les exceptions

J’espère que vous avez trouvé cet article utile et informatif et que vous cherchez maintenant à l’utiliser dans vos feuilles de calcul pour les rendre encore plus utiles qu’elles ne l’étaient auparavant pour l’utilisateur final. N’hésitez pas à laisser vos commentaires ci-dessous.

Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.

André le 06 novembre 2016 :

Est-il possible de publier la feuille de calcul réelle dans vos exemples au lieu d’images ? De cette façon, les gens comme moi peuvent copier et coller la feuille de données dans Excel pour s’entraîner sans avoir à recréer la feuille de données. THX

Robbie C. Wilson (auteur) le 07 juin 2013 :

Un grand merci pour vos aimables paroles Zubair. Je suis heureux que vous ayez trouvé mon hub utile et informatif.

Zubair Ahmed le 07 juin 2013 :

Merci de partager ces informations. Ces fonctionnalités sont vraiment utiles. J’ai commencé à les utiliser assez récemment et je les trouve si bonnes lorsque vous essayez de formater des chiffres.

Je vais devoir vous suivre pour développer mes compétences sur l’utilisation des fonctionnalités d’Excel.

ta