Formule générique
=IF(logical_test,"P","")
Sommaire
Pour afficher une coche si une valeur est "autorisée" basée sur une liste existante de valeurs autorisées, vous pouvez utiliser une formule basée sur la fonction IF avec la fonction COUNTIF. Dans l'exemple, la formule en C5 est:
=IF(COUNTIF(allowed,B5),"✓","")
où autorisé est la plage nommée E5: E9.
Explication
Cette formule est un bon exemple d'imbrication d'une fonction dans une autre. À la base, cette formule utilise la fonction IF configurée pour renvoyer une coche (✓) lorsqu'un test logique renvoie TRUE:
=IF(logical_test,"✓","")
Si le test renvoie FALSE, la formule renvoie une chaîne vide (""). Pour le test logique, nous utilisons la fonction COUNTIF comme ceci:
COUNTIF(allowed,B5)
COUNTIF compte les occurrences de la valeur en B5 dans la plage nommée autorisée (E5: E9). Cela peut vous sembler «à l'envers», mais si vous y réfléchissez, cela a du sens. Si la valeur de B5 se trouve dans la liste de valeurs autorisées, COUNTIF renverra un nombre positif (dans ce cas 1). Sinon, COUNTIF renverra zéro. Excel évaluera tout nombre non nul comme VRAI, donc cela fonctionne parfaitement comme test logique pour IF.
IF ne retournera TRUE que si la valeur est trouvée dans la liste autorisée, et, si tel est le cas, le résultat final est une coche (✓). Si la valeur n'est pas trouvée dans la liste autorisée, COUNTIF renvoie zéro, qui est évalué comme FALSE. Dans ce cas, le résultat final est une chaîne vide (""), qui n'affiche rien.
Avec des valeurs fixes
L'exemple ci-dessus montre les valeurs autorisées dans une plage de cellules, mais les valeurs autorisées peuvent également être codées en dur dans les formules en tant que constante de tableau comme ceci:
=IF(COUNTIF(("red","blue","green"),B5),"✓","")
Caractère de coche (✓)
L'insertion d'une coche dans Excel peut être étonnamment difficile et vous trouverez de nombreux articles sur Internet expliquant différentes approches. Le moyen le plus simple d'obtenir le caractère de coche (✓) utilisé dans cette formule dans Excel est simplement de le copier et de le coller. Si vous copiez à partir de cette page Web, collez-le dans la barre de formule pour éviter de faire glisser dans une mise en forme indésirable. Vous pouvez également copier et coller directement à partir de la feuille de calcul jointe.
Si vous rencontrez des problèmes avec le copier-coller, essayez cette variante. Le caractère lui-même est Unicode 2713 (U + 2713), et peut également être entré dans Excel avec la fonction UNICHAR comme ceci:
=UNICHAR(10003) // returns "✓"
Ainsi, la formule originale peut être écrite comme ceci:
=IF(COUNTIF(allowed,B5),UNICHAR(10003),"")
Remarque: la fonction UNICHAR a été introduite dans Excel 2013.
Extension de la formule
L'idée de base de cette formule peut être étendue de nombreuses manières intelligentes. Par exemple, la capture d'écran ci-dessous montre une formule qui renvoie une coche uniquement lorsque tous les scores de test sont d'au moins 65:
La formule dans G5 est:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
La fonction NOT inverse le résultat de COUNTIF. Si vous trouvez cela déroutant, vous pouvez alternativement restructurer la formule IF comme ceci:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
Dans la version de la formule, la logique est plus similaire à la formule originale ci-dessus. Cependant, nous avons déplacé la coche vers l'argument value_if_false, donc la coche n'apparaîtra que si le nombre de COUNTIF est égal à zéro. En d'autres termes, la coche n'apparaîtra que si aucune valeur inférieure à 65 n'est trouvée.
Remarque: vous pouvez également utiliser la mise en forme conditionnelle pour mettre en évidence une entrée valide ou non valide, et la validation des données pour restreindre l'entrée afin de n'autoriser que les données valides.
