Formule Excel : Vérifier si une cellule contient l'une des plusieurs valeurs mais exclure d'autres valeurs

Supposons qu'il y ait deux listes de valeurs, et vous souhaitez vérifier si la cellule B3 contient l'une des valeurs dans la plage E3:E5, mais en même temps, elle ne doit contenir aucune des valeurs dans la plage F3:F4, comme le montre la capture d'écran ci-dessous. Ce tutoriel fournira une formule pour gérer rapidement cette tâche dans Excel et expliquera les arguments de la formule.

Formule générique :

Arguments

Valeur de retour :

La formule renvoie 1 ou 0. Lorsque la cellule contient l'une des valeurs à inclure et n'en contient aucune des valeurs à exclure, elle renvoie 1, sinon elle renvoie 0. Dans cette formule, 1 et 0 sont traités comme des valeurs logiques VRAI et FAUX.

Fonctionnement de cette formule

Supposons que vous souhaitiez vérifier si la cellule B3 contient l'une des valeurs dans la plage E3:E5, mais en même temps exclure les valeurs dans la plage F3:F4, veuillez utiliser la formule ci-dessous.

Appuyez sur Entrée pour obtenir le résultat du test.

Explication

Partie 1 : (SOMMEPROD(--ESTNUM(CHERCHE($E$3:$E$5;B3)))>0) vérifie si la cellule contient des valeurs dans E3:E5

CHERCHE : la fonction CHERCHE renvoie la position du premier caractère de la chaîne de texte à l'intérieur d'une autre. Si la fonction CHERCHE trouve le texte correspondant, elle renvoie la position relative, sinon elle renvoie une erreur #VALEUR!. Par exemple, ici la formule CHERCHE($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;12}.

Fonction ESTNUM : la fonction ESTNUM renvoie VRAI lorsqu'une cellule est un nombre. Donc, ESTNUM(CHERCHE($E$3:$E$5;B3)) renverra un résultat sous forme de tableau comme {vrai,vrai,vrai}, car la fonction CHERCHE trouve 3 nombres.

--ESTNUM(CHERCHE($E$3:$E$5;B3)) convertit la valeur VRAI en 1 et la valeur FAUX en 0, donc cette formule change le résultat du tableau en {1;1;1}.

SOMMEPROD : est utilisée pour multiplier des plages ou additionner des tableaux ensemble et renvoyer la somme des produits. La fonction SOMMEPROD(--ESTNUM(CHERCHE($E$3:$E$5;B3))) renvoie 1+1+1=3.

Enfin, comparez la formule de gauche SOMMEPROD(--ESTNUM(CHERCHE($E$3:$E$5;B3))) et 0, tant que le résultat de la formule de gauche est supérieur à 0, le résultat sera VRAI, sinon il renverra FAUX. Ici, il renvoie VRAI.

Partie 2 : (SOMMEPROD(--ESTNUM(CHERCHE($F$3:$F$4;B3)))=0) vérifie si la cellule ne contient pas de valeurs dans F3:F4

La formule CHERCHE($F$3:$F$4;B3) recherchera chaque valeur dans 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 : {#VALEUR!;#VALEUR!}.

ESTNUM(CHERCHE($F$3:$F$4;B3)) renverra un résultat sous forme de tableau comme {faux;faux}, car la fonction CHERCHE ne trouve aucun nombre.

--ESTNUM(CHERCHE($F$3:$F$4;B3)) convertit la valeur VRAI en 1 et la valeur FAUX en 0, donc cette formule change le résultat du tableau en {0;0}.

SOMMEPROD : est utilisée pour multiplier des plages ou additionner des tableaux ensemble et renvoyer la somme des produits. La fonction SOMMEPROD(--ESTNUM(CHERCHE($F$3:$F$4;B3))) renvoie 0+0=0.

Enfin, comparez la formule de gauche SOMMEPROD(--ESTNUM(CHERCHE($F$3:$F$4;B3))) et 0, tant que le résultat de la formule de gauche est égal à 0, le résultat sera VRAI, sinon il renverra FAUX. Ici, il renvoie VRAI.

Partie 3 : Multipliez les deux formules

=(SOMMEPROD(--ESTNUM(CHERCHE($E$3:$E$5;B3)))>0)*(SOMMEPROD(--ESTNUM(CHERCHE($F$3:$F$4;B3)))=0)

=VRAI*VRAI

=1

Dans cette formule, 1 et 0 sont traités comme des valeurs logiques VRAI et FAUX.

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 dans la plage B, vous pouvez utiliser une formule matricielle qui combine les fonctions NB, CHERCHE et ET dans Excel.
• Vérifier si une cellule contient l'un des nombreux éléments
  Ce tutoriel fournit une formule pour vérifier si une cellule contient l'une des plusieurs valeurs dans Excel, et explique les arguments dans la formule ainsi que son fonctionnement.
• Vérifier si une cellule contient l'un des éléments
  Supposons qu'il y ait une liste de valeurs dans la colonne E, et vous souhaitez vérifier si les cellules dans la colonne B contiennent toutes les valeurs de la colonne E, et renvoyer VRAI ou FAUX.
• 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 VRAI si la cellule contient un nombre, et FAUX si la cellule ne contient pas de nombre.