Formule Excel : Vérifier si une cellule contient certains textes mais pas d'autres

Pour vérifier si une cellule contient certains textes dans la plage A mais ne contient pas les textes de la plage B, vous pouvez utiliser une formule matricielle qui combine les fonctions COUNT, SEARCH et AND dans Excel. Ce tutoriel explique comment utiliser cette formule matricielle.

Formule générique :

Arguments

Valeur de retour :

Cette formule renvoie une valeur logique. Si la cellule contient au moins un des arguments Inclure mais aucun des arguments Exclure, elle renverra TRUE, sinon elle renverra FALSE.

Comment fonctionne cette formule

Supposons que vous souhaitiez vérifier si la cellule B3 contient l'une des valeurs de la plage E3:E5, mais qu'en même temps, elle ne contient aucune des valeurs de la plage F3:F4, utilisez la formule ci-dessous.

Appuyez sur Maj + Ctrl + Entrée pour vérifier la cellule.

Explication

Partie 1 : ( COUNT(SEARCH($E$3:$E$5,B3))>0 vérifie si la cellule B3 contient l'une des valeurs de la plage E3:E5

SEARCH : la fonction SEARCH renvoie la position du premier caractère de la chaîne de texte à l'intérieur d'une autre. Si la fonction SEARCH trouve le texte correspondant, elle renvoie la position relative ; sinon, elle renvoie une erreur #VALUE!. Ici, la formule SEARCH($E$3:$E$5,B3) recherchera chaque valeur de la plage E3:E5 dans la cellule B3 et renverra l'emplacement de chaque chaîne de texte dans la cellule B3. Elle renverra un résultat sous forme de tableau comme ceci : {1;7;#VALUE!}.

COUNT : la fonction COUNT compte le nombre d'éléments dans une plage ou un tableau. COUNT(SEARCH($E$3:$E$5,B3)) renvoie 2 car il y a deux éléments dans le tableau {1;7;#VALUE!}.

COUNT(SEARCH($E$3:$E$5,B3))>0, compare le résultat de la fonction COUNT avec zéro. Tant que le résultat de la fonction COUNT est supérieur à 0, le résultat sera TRUE, sinon il sera FALSE. Ici, il renvoie TRUE.

Partie 2 : COUNT(SEARCH($F$3:$F$4,B3))=0 vérifie si la cellule B3 ne contient aucune des valeurs de la plage F3:F4

SEARCH : la fonction SEARCH renvoie la position du premier caractère de la chaîne de texte à l'intérieur d'une autre. Si la fonction SEARCH trouve le texte correspondant, elle renvoie la position relative ; sinon, elle renvoie une erreur #VALUE!. Ici, la formule SEARCH($F$3:$F$4,B3) recherchera chaque valeur de la plage F3:F4 dans la cellule B3 et renverra l'emplacement de chaque chaîne de texte dans la cellule B3. Elle renverra un résultat sous forme de tableau comme ceci : {#VALUE!; #VALUE!;#VALUE!}.

COUNT fonction : la fonction COUNT compte le nombre d'éléments dans une plage ou un tableau. COUNT(SEARCH($F$3:$F$4,B3)) renvoie 0 car il n'y a aucun élément dans le tableau {#VALUE!; #VALUE!;#VALUE!}.

COUNT(SEARCH($F$3:$F$4,B3))>0, compare le résultat de la fonction COUNT avec zéro. Tant que le résultat de la fonction COUNT est égal à 0, il renverra TRUE, sinon il renverra FALSE. Ici, il renvoie TRUE.

Partie 3 : Fonction AND

=AND(COUNT(SEARCH($E$3:$E$5,B3))>0,COUNT(SEARCH($F$3:$F$4,B3))=0)

=AND(TRUE,TRUE)

=TRUE

Fichier d'exemple

Cliquez pour télécharger le fichier d'exemple

Formules relatives

• Vérifier si une cellule contient un texte spécifique
  Pour vérifier si une cellule contient certains textes dans la plage A mais ne contient pas les textes de la plage B, vous pouvez utiliser une formule matricielle qui combine les fonctions COUNT, SEARCH et AND dans Excel.
• Vérifier si une cellule contient l'une des plusieurs valeurs mais exclut d'autres valeurs
  Ce tutoriel fournira une formule pour gérer rapidement la tâche consistant à vérifier si une cellule contient une chose parmi d'autres tout en excluant d'autres valeurs dans Excel et expliquera les arguments de la formule.
• Vérifier si une cellule contient une chose parmi d'autres
  Supposons qu'il y ait une liste de valeurs dans la colonne E, et que vous souhaitiez vérifier si les cellules de la colonne B contiennent toutes les valeurs de la colonne E, et renvoyer TRUE ou FALSE.
• Vérifier si une cellule contient un nombre
  Parfois, vous voudrez peut-être vérifier si une cellule contient des caractères numériques. Ce tutoriel fournit une formule qui renverra TRUE si la cellule contient un nombre, et FALSE si elle ne contient pas de nombre.