L'ajout d'une mise en forme conditionnelle dans Excel vous permet d'appliquer différentes options de mise en forme à une cellule ou à une plage de cellules répondant à des conditions spécifiques que vous avez définies. Les options de formatage ne sont appliquées que lorsque les cellules sélectionnées remplissent ces conditions.
Les options de formatage pouvant être appliquées incluent les modifications de couleur de la police et de l’arrière-plan, les styles de police, les bordures de cellules et l’ajout du formatage numérique aux données.
Excel propose un certain nombre d'options intégrées pour les conditions couramment utilisées, telles que la recherche de nombres supérieurs ou inférieurs à une certaine valeur ou la recherche de nombres supérieurs ou inférieurs à la valeur moyenne.
Outre ces options prédéfinies, il est également possible de créer des règles de mise en forme conditionnelles personnalisées à l'aide de formules Excel pour tester des conditions spécifiées par l'utilisateur.
01 sur 04Appliquer plusieurs règles dans Excel
Plusieurs règles peuvent être appliquées aux mêmes données pour tester différentes conditions. Par exemple, des conditions peuvent être définies dans les données budgétaires pour appliquer des modifications de mise en forme lorsque certains niveaux, tels que 50%, 75% et 100% du budget total sont dépensés.
Dans ce cas, Excel détermine d’abord si les différentes règles sont en conflit et, le cas échéant, le programme respecte un ordre de priorité défini pour déterminer quelle règle de mise en forme conditionnelle est appliquée aux données.
Recherche de données dépassant les augmentations de 25% et 50%
Dans l’exemple suivant, deux règles de mise en forme conditionnelle personnalisées seront appliquées à la plage de valeurs. cellules B2 à B5.
- La première règle vérifie si les données contenues dans cellules A2 à A5 est supérieure à la valeur correspondante dans B2 à B5 de plus de 25%.
- La deuxième règle vérifie si les mêmes données dans A2: A5 dépasse la valeur correspondante en B2: B5 de plus de 50%.
Comme on peut le voir dans l’image ci-dessus, si l’une des conditions ci-dessus est vraie, la couleur de fond de la cellule ou des cellules situées dans la plage B1: B4 changera.
- Pour les données où la différence est supérieure à 25%, la couleur d'arrière-plan de la cellule passe au vert.
- Si la différence est supérieure à 50%, la couleur d'arrière-plan de la cellule passera au rouge.
Les règles utilisées pour accomplir cette tâche seront entrées à l'aide de la mise en forme conditionnelle. Nouvelle règle de formatage boite de dialogue. Commencez par saisir les données de l'échantillon dans les cellules A1 à C5, comme indiqué dans l'image ci-dessus.
dans le troisième étape du tutoriel va ajouter des formules aux cellules C2: C4 qui montrent la différence de pourcentage exacte entre les valeurs dans les cellules A2: A5 et B2: B5 afin de vérifier l'exactitude des règles de mise en forme conditionnelle.
02 sur 04Définition de règles de mise en forme conditionnelles
Comme mentionné, les règles de mise en forme conditionnelle qui vérifient les deux conditions seront entrées à l'aide de la mise en forme conditionnelle. Nouvelle règle de formatage boite de dialogue. Commençons par définir notre mise en forme conditionnelle personnalisée:
Mise en forme conditionnelle pour trouver une augmentation supérieure à 25%
= (A2-B2) / A2> 25%
- Surligner cellules B2 à B5 dans la feuille de calcul.
- Clique sur le Onglet Accueil du ruban.
- Clique sur le Mise en forme conditionnelle icône dans le ruban pour ouvrir la liste déroulante.
- Choisir Nouvelle règle ouvrir le Nouvelle règle de formatage boite de dialogue.
- Du Style: case, sélectionnez la Classique option.
- Dans la deuxième liste déroulante, cliquez sur la dernière optionUtilisez une formule pour déterminer les cellules à formater.
- Tapez le formule fourni ci-dessus dans l'espace prévu.
- Clique sur le Format avec: liste déroulante puis sélectionnez le Remplissage rouge clair avec texte rouge foncé option.
- Cliquez sur D'accord pour fermer les boîtes de dialogue et revenir à la feuille de calcul.
- À ce stade, la couleur de fond de cellules B3 et B5 devrait être vert.
Mise en forme conditionnelle pour rechercher une augmentation supérieure à 50%
= (A2-B2) / A2> 50%
Surligner cellules B2 à B5 dans la feuille de calcul.
- Clique sur le Onglet Accueil du ruban.
- Clique sur le Mise en forme conditionnelle icône dans le ruban pour ouvrir la liste déroulante.
- Choisir Nouvelle règle ouvrir le Nouvelle règle de formatage boite de dialogue.
- Du Style: case, sélectionnez la Classique option.
- Dans la deuxième liste déroulante, cliquez sur la dernière optionUtilisez une formule pour déterminer les cellules à formater.
- Tapez le formule fourni ci-dessus dans l'espace prévu.
- Clique sur le Format avec: liste déroulante puis sélectionnez le Remplissage vert clair avec texte vert foncé option.
- Cliquez sur D'accord pour fermer les boîtes de dialogue et revenir à la feuille de calcul.
La couleur de fond de cellule B3 devrait toujours être vert indiquant que la différence en pourcentage entre les chiffres de cellules A3 et B3 est supérieur à 25% mais inférieur ou égal à 50%. La couleur de fond de cellule B5 devrait changer en rouge indiquant que la différence en pourcentage entre les chiffres de cellules A5 et B5 est supérieur à 50 pour cent.
03 sur 04Vérification des règles de mise en forme conditionnelles
Pour vérifier que les règles de mise en forme conditionnelles entrées sont correctes, nous pouvons entrer des formules dans des cellules C2: C5 qui va calculer la différence exacte en pourcentage entre les nombres dans les plages A2: A5 et B2: B5.
= (A2-B2) / A2
- Cliquer sur cellule C2 pour en faire la cellule active.
- Tapez la formule ci-dessus et appuyez sur la touche Entrer touche du clavier.
- La réponse 10% devrait apparaître dans cellule C2, indiquant que le numéro en cellule A2 est 10% plus grand que le nombre dans cellule B2.
- Il peut être nécessaire de modifier le formatage sur cellule C2 afin d'afficher la réponse sous forme de pourcentage.
- Utilisez le poignée de remplissage copier la formule de cellule C2 à cellules C3 à C5.
- Les réponses pour cellules C3 à C5 devrait être de 30%, 25% et 60%.
Les réponses dans ces cellules montrent que les règles de mise en forme conditionnelle créées sont correctes, car la différence entre cellules A3 et B3 est supérieur à 25 pour cent et la différence entre cellules A5 et B5 est supérieur à 50 pour cent.
Cellule B4 n'a pas changé de couleur parce que la différence entre cellules A4 et B4 correspond à 25% et notre règle de mise en forme conditionnelle spécifiait qu'un pourcentage supérieur à 25% était requis pour que la couleur d'arrière-plan devienne verte.
04 sur 04Ordre de priorité pour la mise en forme conditionnelle
Lorsque plusieurs règles sont appliquées à la même plage de données, Excel détermine d'abord si les règles sont en conflit. Les règles en conflit sont celles pour lesquelles les options de formatage choisies pour chaque règle ne peuvent pas être appliquées aux mêmes données.
Dans l'exemple utilisé dans ce didacticiel, les règles sont en conflit car les deux règles utilisent la même option de formatage, à savoir la modification de la couleur de la cellule d'arrière-plan.
Dans le cas où la deuxième règle est vraie (la différence de valeur est supérieure à 50% entre deux cellules), la première règle (la différence de valeur étant supérieure à 25%) est également vraie.
Ordre de préséance d'Excel
Puisqu'une cellule ne peut pas avoir à la fois un arrière-plan rouge et vert, Excel doit savoir quelle règle de mise en forme conditionnelle elle doit appliquer.
La règle à appliquer est déterminée par l'ordre de priorité d'Excel, qui indique que la règle située en haut de la liste dans la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle est prioritaire.
Comme le montre l'image ci-dessus, la deuxième règle utilisée dans ce didacticiel est en haut de la liste et a donc priorité sur la première règle. En conséquence, la couleur d'arrière-plan de la cellule B5 est modifiée en rouge.
Par défaut, les nouvelles règles sont ajoutées en haut de la liste et ont donc une priorité plus élevée. Pour changer l’ordre de priorité, utilisez les touches Haut et bas boutons fléchés dans la boîte de dialogue, comme indiqué dans l'image ci-dessus.
Appliquer des règles non conflictuelles
Si deux règles de mise en forme conditionnelles ou plus ne sont pas en conflit, les deux sont appliquées lorsque la condition testée par chaque règle devient vraie.
Si la première règle de mise en forme conditionnelle dans notre exemple a formaté la plage de cellules B2: B5 avec une bordure verte au lieu d'une couleur d'arrière-plan verte, les deux règles de mise en forme conditionnelles ne seraient pas en conflit puisque les deux formats peuvent être appliqués sans interférer avec l'autre.
Par conséquent, cellule B5 aurait à la fois une bordure verte et une couleur de fond rouge, car la différence entre les nombres de cellules A5 et B5 est supérieur à 25 et 50 pour cent.
Formatage conditionnel ou formatage standard
En cas de conflits entre les règles de mise en forme conditionnelles et les options de mise en forme appliquées manuellement, la règle de mise en forme conditionnelle est toujours prioritaire et sera appliquée à la place des options de mise en forme ajoutées manuellement.
Si une couleur de fond jaune a été initialement appliquée à cellules B2 à B5 dans l'exemple, une fois les règles de mise en forme conditionnelles ajoutées, cellules B2 et B4 resterait jaune. Parce que les règles de mise en forme conditionnelles entrées s'appliquent aux cellules B3 et B5, leurs couleurs de fond passeraient respectivement du jaune au vert et au rouge.
