Mise en Forme Conditionnelle Basée sur une Formule – Excel et Google Sheets
Ce tutoriel explique comment appliquer une mise en forme conditionnelle basée sur une formule dans Excel et Google Sheets.
La mise en forme conditionnelle dans Excel s’accompagne de nombreuses règles prédéfinies qui vous permettent de mettre en forme rapidement les cellules en fonction de leur contenu. Ces règles prédéfinies ont toutefois des limites, et c’est là que l’utilisation d’une formule pour déterminer la mise en forme d’une cellule entre en jeu. Vous disposez ainsi d’une plus grande souplesse dans la mise en forme conditionnelle.
Règles des Formules de Mise en Forme Conditionnelle
Égal à
- Sélectionnez les cellules dans lesquelles vous souhaitez définir la mise en forme conditionnelle, puis, dans le ruban, sélectionnez Accueil > Mise en forme conditionnelle > Nouvelle règle.
-
- Sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et saisissez la formule :
=$C4="Ouest"
Vous devez toujours commencer votre formule par un signe égal.
La formule teste si le résultat est VRAI ou FAUX, tout comme l’instruction SI traditionnelle d’Excel, mais dans le cas de la mise en forme conditionnelle, vous n’avez pas besoin d’ajouter les conditions VRAI et FAUX. Si la formule renvoie VRAI, la mise en forme définie dans la partie Format de la règle est appliquée aux cellules concernées.
Vous devez utiliser une référence mixte dans cette formule ($C4) afin de verrouiller la colonne (la rendre absolue) mais de rendre la ligne relative. Cela permet au formatage de formater la ligne entière au lieu d’une seule cellule répondant aux critères.
Lorsque la règle est évaluée pour toutes les cellules de la plage, la ligne change, mais pas la colonne. La règle ignore alors les valeurs des autres colonnes et se concentre uniquement sur les valeurs de la colonne C. Si la colonne C de cette ligne contient le mot Ouest, le résultat de la formule est VRAI et la mise en forme est appliquée à l’ensemble de la ligne.
- Cliquez sur Format et sélectionnez la mise en forme souhaitée.
- Cliquez sur OK, puis à nouveau sur OK pour afficher le résultat.
Les lignes où la colonne C est Ouest sont mises en surbrillance, tandis que les autres lignes ne le sont pas. Dans l’exemple ci-dessus, seules deux lignes sont mises en surbrillance.
Différent
Si vous souhaitez créer une règle différent de, vous pouvez utiliser le symbole <>(différent de) au lieu de =.
=$C4<>"Ouest"
Le résultat est inverse : La plupart des lignes sont maintenant en surbrillance !
Supérieur à
Vous pouvez également tester la valeur supérieure à l’aide d’une formule. Par exemple, vous pouvez utiliser une formule pour vérifier si les valeurs de la colonne D sont supérieures à 23 000 €.
=$D4>23000
Cette règle ne met en surbrillance que les lignes dont la valeur de la colonne D est supérieure à 23 000 €.
Supérieur ou égal à
De la même manière, vous pouvez mettre en surbrillance les lignes où la valeur de la colonne D est supérieure ou égale à 23 000 € à l’aide de la formule ci-dessous :
=$D4>=23000
Le résultat de cette formule sera légèrement différent : Les lignes où la valeur du vin rouge (colonne D) est exactement 23 000 € sont également mises en évidence (ici, la ligne 5).
Inférieur à
En remplaçant le signe > par un signe <, on recherche les valeurs inférieures à 23 000 €.
=$D4<23000
Désormais, seules les lignes où le vin rouge (colonne D) est inférieur à 23 000 € sont mises en surbrillance.
Inférieur ou égal à
Pour modifier le test afin d’inclure la valeur cible (par exemple, 23 000 €), utilisez <=.
=$D4<=23000
Désormais, toutes les lignes dans lesquelles la colonne D est égale à 23 000 € sont également mises en surbrillance.
Créer des Règles avec OU, ET
Formule OU
Vous pouvez créer une règle plus complexe en utilisant la formule OU d’Excel pour étendre la formule et rechercher deux valeurs ou plus.
-
- Saisissez la formule :
=OU($C4="Ouest";$C4="Est")
- Cliquez sur le bouton Format et sélectionnez la mise en forme souhaitée, puis cliquez sur OK pour revenir à Excel.
Cette formule met en surbrillance toutes les lignes où la région est ouest ou est.
Formule ET
De la même manière, vous pouvez créer une une règle à l’aide de la formule ET. Il s’agit alors de vérifier les valeurs de deux colonnes ou plus.
La formule ci-dessus vérifie si le texte de la colonne C est Nord et si la valeur de la ligne correspondante de la colonne D est supérieure à 23 000 €.
Cette règle de mise en forme conditionnelle ne met en surbrillance que les lignes pour lesquelles les deux conditions sont remplies.
Formule SI
Vous ne devez pas nécessairement ajouter une instruction SI à la formule lorsque vous la créez dans une règle de mise en forme conditionnelle, car la mise en forme conditionnelle appliquera toujours la règle si la formule que vous avez créée renvoie une valeur vraie.
Ainsi, par exemple, cette formule :
=SI($C4="Ouest";VRAI;FAUX)et cette formule
$C4="Ouest"appliquent toutes deux la même mise en forme conditionnelle à votre feuille de calcul.
Fonctions Excel Intégrées
De nombreuses fonctions Excel intégrées peuvent être utilisées dans la mise en forme conditionnelle, telles que ESTVIDE, ESTERREUR, EST.PAIR, ESTNUM, etc.
Fonction ESTVIDE
Vous pouvez utiliser la fonction ESTVIDE pour créer une règle permettant de trouver les lignes qui contiennent des cellules vides dans la feuille de calcul.
-
- Sélectionnez la plage souhaitée, puis, dans le ruban, sélectionnez Accueil > Mise en forme conditionnelle > Nouvelle règle.
- Saisissez la formule :
=ESTVIDE($E3)
- Cliquez sur le bouton Format et sélectionnez la mise en forme souhaitée, puis cliquez sur OK pour revenir à Excel.
ESTVIDE avec OU
Vous pouvez étendre cette règle pour inclure les cellules vides ou les cellules contenant une erreur.
-
- Tapez la formule suivante
=OU(ESTVIDE($E3);ESTERREUR($E3))
- Cliquez sur le bouton Format et sélectionnez la mise en forme souhaitée, puis cliquez sur OK pour revenir à Excel.
Autres Fonctions de Mise en Forme Conditionnelle
Plusieurs autres fonctions peuvent être utilisées pour créer des règles de mise en forme conditionnelle basées sur des formules.
- NB.SI – compte les cellules qui remplissent une certaine condition (utile pour mettre en surbrillance les doublons)
- GRANDE.VALEUR – recherche la nième valeur la plus élevée
- MAX – recherche le plus grand nombre
- MIN – recherche le plus petit nombre
- MOD – renvoie le reste après la division (utile pour mettre en surbrillance une ligne sur deux)
- NON – transforme VRAI en FAUX et vice versa
- MAINTENANT – renvoie la date et l’heure actuelles (utile pour mettre en surbrillance les dates et les heures de manière conditionnelle)
- LIGNE – renvoie le nombre de lignes d’un tableau
- CHERCHE – recherche une chaîne de texte dans une autre chaîne (utile pour trouver un texte spécifique)
- AUJOURDHUI – renvoie la date du jour (utile pour appliquer un formatage conditionnel aux dates)
- RECHERCHEV – renvoie le résultat d’une recherche verticale
- JOURSEM – renvoie le jour de la semaine
- RECHERCHEX – une nouvelle fonction de recherche plus puissante
Par exemple, vous pouvez utiliser la fonction MAX pour mettre en surbrillance la ligne qui contient la valeur maximale dans une colonne donnée.
=$D4=MAX($D:$D)
Désormais, seule la ligne contenant la valeur maximale dans la colonne D est mise en surbrillance.
Si vous avez du mal à faire fonctionner une formule lors de la création d’une règle de mise en forme conditionnelle, il est conseillé de tester d’abord la formule dans votre feuille Excel. Une fois que vous l’avez trouvée, vous pouvez l’incorporer dans votre règle.
Mise en Forme Conditionnelle Basée sur une Formule dans Google Sheets
La mise en forme conditionnelle fonctionne de la même manière dans Google Sheets que dans Excel.
- Sélectionnez les cellules dans lesquelles vous souhaitez définir la mise en forme conditionnelle, puis, dans le menu, sélectionnez Format > Mise en forme conditionnelle.
- Assurez-vous que l’option Appliquer à la plage est correcte, puis sélectionnez La formule personnalisée est dans la liste déroulante Règles de mise en forme. Saisissez la formule.
- Sélectionnez ensuite votre style de mise en forme et cliquez sur OK.
Vous pouvez également utiliser les commandes Supérieur(e) à, Supérieur(e) ou égal à, Inférieur(e) à et Inférieur(e) ou égal à dans Google Sheets.
ET et OU fonctionnent également, de même que les autres fonctions énumérées ci-dessus pour Excel (à l’exception de RECHERCHEX, qui n’existe pas dans Google Sheets).
