Formule générique
=SUMPRODUCT(--(A1=things))>0
Sommaire
Si vous souhaitez tester une cellule pour voir si elle équivaut à l'une de plusieurs choses, vous pouvez le faire avec une formule qui utilise la fonction SOMMEPROD.
Le contexte
Supposons que vous ayez une liste de chaînes de texte dans la plage B5: B11 et que vous souhaitiez tester chaque cellule par rapport à une autre liste d'éléments de la plage E5: E9. En d'autres termes, pour chaque cellule de B5: B11, vous voulez savoir: cette cellule est-elle égale à l'une des choses de E5: E9?
Vous pouvez commencer à créer une grande formule basée sur des instructions IF imbriquées, mais une formule matricielle basée sur SUMPRODUCT est une approche plus simple et plus propre.
Solution
La solution consiste à créer une formule qui testera plusieurs valeurs et retournera une liste de valeurs TRUE / FALSE. Une fois que nous avons cela, nous pouvons traiter cette liste (un tableau, en fait) avec SUMPRODUCT.
La formule que nous utilisons ressemble à ceci:
=SUMPRODUCT(--(B5=things))>0
Explication
La clé est cet extrait:
--(B5=things)
qui compare simplement la valeur de B5 à chaque valeur de la plage nommée «choses». Parce que nous comparons B5 à un tableau (c'est-à-dire la plage nommée «choses», E5: E11), le résultat sera un tableau de valeurs VRAI / FAUX comme ceci:
(VRAI; FAUX; FAUX; FAUX; FAUX)
Si nous avons même un VRAI dans le tableau, nous savons que B5 est égal à au moins une chose dans la liste, donc, pour forcer les valeurs VRAI / FAUX à 1 et 0, nous utilisons un double négatif (-, également appelé un double unaire). Après cette coercition, nous avons ceci:
(1; 0; 0; 0; 0)
Nous traitons maintenant le résultat avec SUMPRODUCT, qui ajoutera les éléments du tableau. Si nous obtenons un résultat différent de zéro, nous avons au moins une correspondance, nous utilisons donc> 1 pour forcer un résultat final de TRUE ou FALSE.
Avec une liste codée en dur
Il n'est pas nécessaire que vous utilisiez une plage pour votre liste de choses. Si vous ne recherchez qu'un petit nombre d'éléments, vous pouvez utiliser une liste au format tableau, appelée constante de tableau. Par exemple, si vous recherchez uniquement les couleurs rouge, bleu et vert, vous pouvez utiliser ("rouge", "bleu", "vert") comme ceci:
--(B5=("red","blue","green"))
Gérer les espaces supplémentaires
Si les cellules que vous testez contiennent de l'espace supplémentaire, elles ne correspondent pas correctement. Pour supprimer tout espace supplémentaire, vous pouvez modifier la formule pour utiliser la fonction TRIM comme suit:
=SUMPRODUCT(--(TRIM(A1)=things))>0
