Formule générique
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Sommaire
Si vous souhaitez tester une cellule pour voir si elle contient tous les éléments d'une liste, vous pouvez le faire avec une formule qui utilise la fonction SEARCH, à l'aide des fonctions ISNUMBER, SUMPRODUCT et COUNTA.
Le contexte
Supposons que vous ayez une liste de chaînes de texte dans la plage B5: B8 et que vous souhaitiez savoir si ces cellules contiennent tous les mots d'une autre plage, E5: E7.
Vous pouvez créer une formule qui utilise des instructions IF imbriquées pour vérifier chaque élément, mais qui ne sera pas bien mise à l'échelle si vous avez beaucoup de choses à rechercher. Chaque fois que vous ajoutez un mot à rechercher, vous devrez ajouter un autre IF imbriqué et ajuster les parenthèses.
Solution
La solution est de créer une formule qui compte toutes les correspondances à la fois. Une fois que nous avons cela, nous comparons simplement ce nombre avec le nombre d'articles que nous recherchons. S'ils correspondent, nous savons qu'une cellule contient tous les éléments.
Dans l'exemple illustré, la formule que nous utilisons est:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Explication
La clé est cet extrait:
ISNUMBER(SEARCH(things,B5)
Ceci est basé sur une autre formule (expliquée en détail ici) qui vérifie simplement une cellule pour une seule sous-chaîne. Si la cellule contient la sous-chaîne, la formule renvoie TRUE. Sinon, la formule renvoie FALSE.
Cependant, si nous donnons à la même formule une liste de choses (dans ce cas, nous utilisons une plage nommée appelée "choses", E5: E7) cela nous rendra une liste de valeurs TRUE / FALSE, une pour chaque élément dans . Le résultat est un tableau qui ressemble à ceci:
(VRAI; VRAI; VRAI)
Où chaque TRUE représente un élément trouvé, et chaque FALSE représente un élément introuvable.
Nous pouvons forcer les valeurs TRUE / FALSE à 1s et 0s avec un double négatif (-, également appelé double unaire):
--ISNUMBER(SEARCH(things,B5))
ce qui donne un tableau comme celui-ci:
(1; 1; 1)
Ensuite, nous traitons ce tableau avec SUMPRODUCT, ce qui nous donnera une somme totale. Si cette somme est égale au nombre d'éléments dans la plage nommée "choses", nous savons que nous avons tout trouvé et que nous pouvons renvoyer TRUE. Pour ce faire, nous comparons directement les deux nombres. Nous obtenons un nombre de cellules non vides dans "choses" en utilisant COUNTA:
=COUNTA(things)
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
