La mise en forme conditionnelle est un moyen fantastique de visualiser rapidement les données dans une feuille de calcul. Avec la mise en forme conditionnelle, vous pouvez, par exemple, mettre en évidence les dates des 30 prochains jours, signaler les problèmes de saisie de données, mettre en évidence les lignes contenant les meilleurs clients, afficher les doublons, etc.
Excel est livré avec un grand nombre de « préréglages » qui facilitent la création de nouvelles règles sans formules. Cependant, vous pouvez également créer des règles avec vos propres formules personnalisées. En utilisant votre propre formule, vous prenez en charge la condition qui déclenche une règle et pouvez appliquer exactement la logique dont vous avez besoin. Les formules vous offrent un maximum de puissance et de flexibilité.
Par exemple, en utilisant le préréglage « Égal à », il est facile de mettre en surbrillance les cellules égales à « pomme ».
Mais que se passe-t-il si vous souhaitez mettre en évidence des cellules égales à « pomme » ou « kiwi » ou « citron vert » ? Bien sûr, vous pouvez créer une règle pour chaque valeur, mais c'est beaucoup de problèmes. Au lieu de cela, vous pouvez simplement utiliser une règle basée sur une formule avec le OU fonction :
Voici le résultat de la règle appliquée à la plage B4:F8 dans cette feuille de calcul :
Voici la formule exacte utilisée :
= OR (B4='apple',B4='kiwi',B4='lime')
Démarrage rapide
Vous pouvez créer une règle de mise en forme conditionnelle basée sur une formule en quatre étapes simples :
1. Sélectionnez les cellules que vous souhaitez formater.
2. Créez une règle de mise en forme conditionnelle et sélectionnez l'option Formule
3. Saisissez une formule qui renvoie VRAI ou FAUX.
4. Définissez les options de formatage et enregistrez la règle.
Les Fonction ISODD ne renvoie VRAI que pour les nombres impairs, déclenchant la règle :
comment additionner les catégories dans Excel
Vidéo: Comment appliquer une mise en forme conditionnelle avec une formule
Nous proposons également formation vidéo sur ce sujet .Logique de formule
Les formules qui appliquent une mise en forme conditionnelle doivent renvoyer VRAI ou FAUX, ou des équivalents numériques. Voici quelques exemples:
exemple de référence de cellule mixte
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')
Les formules ci-dessus renvoient toutes VRAI ou FAUX, elles fonctionnent donc parfaitement comme déclencheur pour la mise en forme conditionnelle.
Lorsque la mise en forme conditionnelle est appliquée à une plage de cellules, entrez les références de cellule par rapport à la première ligne et colonne de la sélection (c'est-à-dire la cellule supérieure gauche). L'astuce pour comprendre le fonctionnement des formules de mise en forme conditionnelle est de visualiser la même formule appliquée à chaque cellule de la sélection , avec les références de cellules mises à jour comme d'habitude. Imaginez que vous ayez entré la formule dans la cellule supérieure gauche de la sélection, puis que vous ayez copié la formule sur l'ensemble de la sélection. Si vous rencontrez des difficultés, consultez la section sur Formules factices au dessous de.
Exemples de formules
Vous trouverez ci-dessous des exemples de formules personnalisées que vous pouvez utiliser pour appliquer une mise en forme conditionnelle. Certains de ces exemples peuvent être créés à l'aide des préréglages intégrés d'Excel pour mettre en évidence les cellules, mais les formules personnalisées peuvent aller bien au-delà des préréglages, comme vous pouvez le voir ci-dessous.
Regarde aussi: Plus de 30 formules de formatage conditionnelMettre en évidence les commandes du Texas
Pour mettre en évidence les lignes qui représentent les commandes du Texas (en abrégé TX), utilisez une formule qui verrouille la référence à la colonne F :
=$F5='TX'
Pour plus de détails, voir cet article : Mettez en surbrillance les lignes avec une mise en forme conditionnelle .
Vidéo: Comment mettre en évidence des lignes avec une mise en forme conditionnelle
Mettez en surbrillance les dates des 30 prochains jours
Pour mettre en évidence les dates se produisant dans les 30 prochains jours, nous avons besoin d'une formule qui (1) s'assure que les dates sont dans le futur et (2) s'assure que les dates sont à 30 jours ou moins à partir d'aujourd'hui. Une façon de le faire est d'utiliser le ET fonction avec le Fonction MAINTENANT comme ça:
= AND (B4> NOW (),B4<=( NOW ()+30))
Avec une date actuelle du 18 août 2016, la mise en forme conditionnelle met en évidence les dates comme suit :
Les Fonction MAINTENANT renvoie la date et l'heure actuelles. Pour plus de détails sur le fonctionnement de cette formule, consultez cet article : Mettez en surbrillance les dates des N prochains jours .
Mettre en évidence les différences entre les colonnes
Étant donné que deux colonnes contiennent des informations similaires, vous pouvez utiliser une mise en forme conditionnelle pour repérer des différences subtiles. La formule utilisée pour déclencher le formatage ci-dessous est :
=$B4$C4 
Voir également: une version de cette formule qui utilise la fonction EXACT pour effectuer une comparaison sensible à la casse .
Mettre en évidence les valeurs manquantes
Pour mettre en évidence les valeurs d'une liste qui manquent dans une autre, vous pouvez utiliser une formule basée sur le Fonction COUNTIF :
= COUNTIF (list,B5)=0
Cette formule vérifie simplement chaque valeur dans Liste A par rapport aux valeurs de la plage nommée 'list' (D5:D10). Lorsque le nombre est égal à zéro, la formule renvoie VRAI et déclenche la règle, qui met en évidence les valeurs dans Liste A qui manquent à Liste B .
Vidéo: Comment trouver les valeurs manquantes avec COUNTIF
Mettez en surbrillance les propriétés avec plus de 3 chambres à moins de 350 000 $
Pour trouver des propriétés dans cette liste qui ont au moins 3 chambres mais qui coûtent moins de 300 000 $, vous pouvez utiliser une formule basée sur la fonction ET :
= AND ($C5<350000,$D5>=3)
Les signes dollar ($) verrouillent la référence aux colonnes C et D, et le ET fonction est utilisé pour s'assurer que les deux conditions sont VRAI. Dans les lignes où la fonction AND renvoie TRUE, la mise en forme conditionnelle est appliquée :
Mettez en surbrillance les valeurs les plus élevées (exemple dynamique)
Bien qu'Excel ait des préréglages pour les « valeurs supérieures », cet exemple montre comment faire la même chose avec une formule et comment les formules peuvent être plus flexibles. En utilisant une formule, nous pouvons rendre la feuille de calcul interactive - lorsque la valeur dans F2 est mise à jour, la règle répond instantanément et met en évidence de nouvelles valeurs.
La formule utilisée pour cette règle est :
comment insérer un tableau dans Excel
=B4>= LARGE (data,input)
Où 'data' est la plage nommée B4:G11 et 'input' est la plage nommée F2. Cette page a des détails et une explication complète .
diagrammes de Gantt
Croyez-le ou non, vous pouvez même utiliser des formules pour créer des diagrammes de Gantt simples avec une mise en forme conditionnelle comme celle-ci :
Cette feuille de calcul utilise deux règles, une pour les barres et une pour l'ombrage du week-end :
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends
Cet article explique la formule des barres , et cet article explique la formule pour l'ombrage du week-end .
Champ de recherche simple
Une astuce intéressante que vous pouvez faire avec la mise en forme conditionnelle consiste à créer un champ de recherche simple. Dans cet exemple, une règle met en surbrillance les cellules de la colonne B qui contiennent du texte tapé dans la cellule F2 :
La formule utilisée est :
= ISNUMBER ( SEARCH ($F,B2))
Pour plus de détails et une explication complète, voir :
- Article: Comment mettre en évidence les cellules qui contiennent du texte spécifique
- Article: Comment mettre en surbrillance les lignes contenant du texte spécifique
- Vidéo: Comment créer un champ de recherche pour mettre en évidence les données
Dépannage
Si vous ne parvenez pas à déclencher correctement vos règles de mise en forme conditionnelle, il y a probablement un problème avec votre formule. Tout d'abord, assurez-vous que vous avez commencé la formule avec un signe égal (=). Si vous oubliez cette étape, Excel convertira silencieusement votre formule entière en texte, la rendant inutile. Pour résoudre ce problème, supprimez simplement les guillemets doubles Excel ajoutés de chaque côté et assurez-vous que la formule commence par égal (=).
Si votre formule est entrée correctement, mais ne déclenche pas la règle, vous devrez peut-être creuser un peu plus. Normalement, vous pouvez utiliser la touche F9 pour vérifier les résultats d'une formule ou utiliser la fonction Évaluer pour parcourir une formule. Malheureusement, vous ne pouvez pas utiliser ces outils avec des formules de mise en forme conditionnelle, mais vous pouvez utiliser une technique appelée « formules factices ».
Formules factices
Les formules factices sont un moyen de tester vos formules de mise en forme conditionnelle directement sur la feuille de calcul, afin que vous puissiez voir ce qu'elles font réellement. Cela peut vous faire gagner beaucoup de temps lorsque vous avez du mal à faire fonctionner correctement les références de cellules.
En un mot, vous entrez la même formule dans une plage de cellules qui correspond à la forme de vos données. Cela vous permet de voir les valeurs renvoyées par chaque formule, et c'est un excellent moyen de visualiser et de comprendre comment fonctionne la mise en forme conditionnelle basée sur une formule. Pour une explication détaillée, voir cet article .
Vidéo: Tester la mise en forme conditionnelle avec des formules factices
Limites
La mise en forme conditionnelle basée sur des formules présente certaines limitations :
- Vous ne pouvez pas appliquer d'icônes, d'échelles de couleurs ou de barres de données avec une formule personnalisée. Vous êtes limité au formatage de cellule standard, y compris les formats de nombre, la police, la couleur de remplissage et les options de bordure.
- Vous ne pouvez pas utiliser certaines constructions de formules telles que les unions, les intersections ou les constantes matricielles pour les critères de mise en forme conditionnelle.
- Vous ne pouvez pas référencer d'autres classeurs dans une formule de mise en forme conditionnelle.
Vous pouvez parfois travailler autour de #2 et #3. Vous pourrez peut-être déplacer la logique de la formule dans une cellule de la feuille de calcul, puis vous référer à cette cellule dans la formule à la place. Si vous essayez d'utiliser une constante matricielle, essayez plutôt de créer une plage nommée.
Plus de ressources de formule CF
- Plus de 30 exemples de formules de mise en forme conditionnelle
- Formation vidéo avec fiches pratiques
