Exceller

Mise en forme conditionnelle avec des formules (10 exemples)

Conditional Formatting With Formulas

Démarrage rapide | Exemples | Dépannage | Formation

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 faire des choses comme mettre en évidence les dates dans les 30 prochains jours, signaler les problèmes de saisie de données, mettre en évidence les lignes contenant les principaux 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 reprenez la condition qui déclenche une règle et pouvez appliquer exactement la logique dont vous avez besoin. Les formules vous offrent une puissance et une flexibilité maximales.



Par exemple, en utilisant le préréglage «Égal à», il est facile de mettre en évidence les cellules égales à «pomme».



Mais que faire 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 cela pose beaucoup de problèmes. Au lieu de cela, vous pouvez simplement utiliser une règle basée sur une formule avec le OU fonction :

Une règle pour mettre en évidence x, y ou z



Voici le résultat de la règle appliquée à la plage B4: F8 dans cette feuille de calcul:

Mise en forme conditionnelle avec la fonction OR

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.

Sélectionnez les cellules à formater

2. Créez une règle de mise en forme conditionnelle et sélectionnez l'option Formule

Sélectionnez l'option de formule

3. Entrez une formule qui renvoie VRAI ou FAUX.

Entrez la formule relative à la cellule active

4. Définissez les options de mise en forme et enregistrez la règle.

Définir les options de formatage

Le Fonction ISODD renvoie seulement TRUE pour les nombres impairs, déclenchant la règle:

comment additionner les catégories dans Excel

La fonction ISODD renvoie TRUE pour les nombres impairs, déclenchant la règle

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 TRUE ou FALSE, elles fonctionnent donc parfaitement comme déclencheur de 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 consiste à visualiser la même formule appliquée à chaque cellule de la sélection , avec les références de cellule mises à jour comme d'habitude. Imaginez que vous avez entré la formule dans la cellule supérieure gauche de la sélection, puis copié la formule sur toute la sélection. Si vous éprouvez des difficultés, consultez la section sur Formules factices en 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 la mise en évidence des 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 mise en forme conditionnelle

Mettre en évidence les commandes du Texas

Pour mettre en surbrillance les lignes qui représentent les commandes du Texas (TX abrégé), utilisez une formule qui verrouille la référence à la colonne F:

 
=$F5='TX'

Utilisez une formule pour mettre en évidence les lignes où état =

Pour plus de détails, consultez cet article: Mettre en évidence les lignes avec une mise en forme conditionnelle .

Vidéo: Comment mettre en évidence des lignes avec une mise en forme conditionnelle

Mettre en évidence les dates dans les 30 prochains jours

Pour mettre en évidence les dates survenant 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 faire est d'utiliser le ET fonction avec la Fonction MAINTENANT comme ça:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

La date actuelle étant le 18 août 2016, la mise en forme conditionnelle met en évidence les dates comme suit:

Mise en forme conditionnelle pour mettre en évidence les dates dans les 30 prochains jours

Le Fonction MAINTENANT renvoie la date et l'heure actuelles. Pour plus de détails sur le fonctionnement de cette formule, consultez cet article: Mettre en évidence les dates dans les N prochains jours .

Mettre en évidence les différences de colonne

Étant donné deux colonnes contenant des informations similaires, vous pouvez utiliser la mise en forme conditionnelle pour repérer les différences subtiles. La formule utilisée pour déclencher la mise en forme ci-dessous est:

 
=$B4$C4

Mise en forme conditionnelle pour comparer des colonnes

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 sont absentes d'une autre, vous pouvez utiliser une formule basée sur le Fonction COUNTIF :

 
= COUNTIF (list,B5)=0

Mettre en évidence les valeurs manquantes avec une mise en forme conditionnelle

Cette formule vérifie simplement chaque valeur dans Liste A par rapport aux valeurs de la plage nommée «liste» (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 de Liste B .

Vidéo: Comment trouver les valeurs manquantes avec COUNTIF

Mettez en valeur les propriétés de 3 chambres et plus à moins de 350 000 $

Pour rechercher des propriétés dans cette liste qui ont au moins 3 chambres mais sont inférieures à 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 TRUE. Dans les lignes où la fonction AND renvoie TRUE, la mise en forme conditionnelle est appliquée:

Mise en forme conditionnelle pour mettre en évidence les listes de propriétés

Mettre en évidence les principales valeurs (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 les nouvelles valeurs.

Mise en forme conditionnelle dynamique pour les valeurs supérieures

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 détails et une explication complète .

Les diagrammes de Gantt

Croyez-le ou non, vous pouvez même utiliser des formules pour créer de simples diagrammes de Gantt avec une mise en forme conditionnelle comme celle-ci:

Utilisation de la mise en forme conditionnelle pour créer un diagramme de Gantt

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 de l'ombrage du week-end .

Champ de recherche simple

Une astuce intéressante que vous pouvez faire avec le formatage conditionnel consiste à créer un champ de recherche simple. Dans cet exemple, une règle met en évidence les cellules de la colonne B qui contiennent du texte saisi dans la cellule F2:

Champ de recherche de mise en forme conditionnelle

La formule utilisée est:

 
= ISNUMBER ( SEARCH ($F,B2))

Pour plus de détails et une explication complète, voir:

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 toute votre formule en texte, la rendant inutile. Pour corriger, 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 saisie correctement, mais ne déclenche pas la règle, vous avez peut-être creusé un peu plus. En temps normal, vous pouvez utiliser la touche F9 pour vérifier les résultats dans 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 cellule.

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 le fonctionnement de la mise en forme conditionnelle basée sur une formule. Pour une explication détaillée, voir cet article .

Utiliser des formules factices pour vérifier les formules de mise en forme conditionnelle

Vidéo: Tester la mise en forme conditionnelle avec des formules factices

Limites

La mise en forme conditionnelle basée sur une formule présente certaines limitations:

  1. 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.
  2. Vous ne pouvez pas utiliser certaines constructions de formule telles que les unions, les intersections ou les constantes de tableau pour les critères de mise en forme conditionnelle.
  3. Vous ne pouvez pas référencer d'autres classeurs dans une formule de mise en forme conditionnelle.

Vous pouvez parfois contourner les n ° 2 et 3. Vous pourrez peut-être déplacer la logique de la formule dans une cellule de la feuille de calcul, puis faire référence à cette cellule dans la formule à la place. Si vous essayez d'utiliser une constante de tableau, essayez plutôt de créer une plage nommée.

Plus de ressources sur les formules CF

  • Plus de 30 exemples de formules de mise en forme conditionnelle
  • Formation vidéo avec fiches de travail
Auteur Dave Bruns


^