Formule générique
=COUNTIF(list,A1)=0
Sommaire
Pour comparer des listes et mettre en évidence des valeurs qui existent dans l'une mais pas dans l'autre, vous pouvez appliquer une mise en forme conditionnelle avec une formule basée sur la fonction COUNTIF. Par exemple, pour mettre en évidence les valeurs A1: A10 qui n'existent pas C1: C10, sélectionnez A1: A10 et créez une règle de mise en forme conditionnelle basée sur cette formule:
=COUNTIF($C$1:$C$10,A1)=0
Remarque: avec la mise en forme conditionnelle, il est important de saisir la formule relative à la "cellule active" dans la sélection, qui est supposée être A1 dans ce cas.
Explication
Cette formule est évaluée pour chacune des 10 cellules de A1: D10. A1 passera à l'adresse de la cellule en cours d'évaluation, tandis que C1: C10 est entré comme adresse absolue, donc cela ne changera pas du tout.
La clé de cette formule est le = 0 à la fin, qui "retourne" la logique de la formule. Pour chaque valeur de A1: A10, NB.SI renvoie le nombre de fois où la valeur apparaît dans C1: C10. Tant que la valeur apparaît au moins une fois dans C1: C10, COUNTIF renverra un nombre différent de zéro et la formule renverra FALSE.
Mais lorsqu'une valeur n'est pas trouvée dans C1: C10, le COUNTIF renvoie zéro et, puisque 0 = 0, la formule retournera TRUE et le formatage conditionnel sera appliqué.
Plages nommées pour une syntaxe simple
Si vous nommez la liste que vous recherchez (C1: C10 dans ce cas) avec une plage nommée, la formule est plus simple à lire et à comprendre:
=COUNTIF(list,A1)=0
Cela fonctionne car les plages nommées sont automatiquement absolues.
Version sensible à la casse
Si vous avez besoin d'un décompte sensible à la casse, vous pouvez utiliser une formule comme celle-ci:
=SUMPRODUCT((--EXACT(A1,list)))=0
La fonction EXACT effectue une évaluation sensible à la casse et SUMPRODUCT comptabilise le résultat. Comme avec le COUNTIF, cette formule retournera lorsque le résultat est zéro. Le test étant sensible à la casse, «apple» apparaîtra comme manquant même si «Apple» ou «APPLE» apparaît dans la deuxième liste. Voir cette page pour une explication plus détaillée.
