Formule générique
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Sommaire
Pour tester une cellule pour l'une des nombreuses chaînes, tout en excluant d'autres, vous pouvez utiliser une formule basée sur les fonctions SEARCH, ISNUMBER et SUMPRODUCT. Dans l'exemple illustré, la formule en C5 est:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
où "inclure" est la plage nommée E5: E9 et "exclure" est la plage nommée G5: G6.
Explication
Au fond, cette formule utilise la fonction RECHERCHE pour rechercher plusieurs chaînes dans une cellule. Dans le SUMPRODUCT de gauche, SEARCH recherche toutes les chaînes de la plage nommée "include".
Dans le SUMPRODUCT de droite, SEARCH recherche toutes les chaînes de la plage nommée "exclude".
Dans les deux parties de la formule, SEARCH renvoie des positions numériques lorsque des chaînes sont trouvées et des erreurs dans le cas contraire. La fonction ISNUMBER convertit les nombres en TRUE et les erreurs en FALSE, et le double négatif convertit les valeurs TRUE FALSE en 1 et 0.
Le résultat à ce stade ressemble à ceci:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Ensuite:
=(1>0)*(0=0) =TRUE*TRUE =1
Remarque: cette formule renvoie 1 ou zéro, qui sont traités comme TRUE et FALSE dans les formules, la mise en forme conditionnelle ou la validation des données.
