Note: The other languages of the website are Google-translated. Back to English

Formule Excel: vérifiez si la cellule contient certains textes mais pas d'autres

Pour vérifier si une cellule contient des textes de 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. Ici, ce didacticiel explique comment utiliser cette formule matricielle.
doc vérifier si contient certains mais pas d'autres 1

Formule générique:

=AND(COUNT(SEARCH(include,text))>0,COUNT(SEARCH(exclude,text))=0)

Arguments

Text: the text string you want to check.
Include: the values you want to check if argument text contains.
Exclude: the values you want to check if argument text does not contain.

Valeur de retour:

Cette formule renvoie une valeur logique. Si la cellule contient au moins l'un des arguments Inclure mais ne contient aucun des arguments Exclure, elle retournera TRUE ou elle retournera 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 en même temps, elle ne contient aucune valeur de la plage F3: F4, veuillez utiliser la formule ci-dessous

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

Presse Maj + Ctrl + Entrée touche pour vérifier la cellule.
doc vérifier si contient certains mais pas d'autres 2

Explication

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

Rechercher function: 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 #VALUE! Erreur. Ici, la formule SEARCH($E$3:$E$5,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. Il renverra un résultat de tableau comme suit: {1;7;#VALUE!}.

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

COUNT(SEARCH($E$3:$E$5,B3))>0, comparez le résultat de la fonction COUNT et de zéro, tant que le résultat de la fonction COUNT est supérieur à 0, le résultat retournera TRUE ou il retournera FALSE. Ici, il renvoie VRAI.
doc vérifier si contient certains mais pas d'autres 3

2 partie: COUNT(SEARCH($F$3:$F$4,B3))=0 vérifier si la cellule B3 ne contient pas l'une des valeurs de la plage F3: F4

Rechercher function: 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, il renvoie #VALUE! Erreur. Ici, la formule SEARCH($F$3:$F$4,B3) recherche chaque valeur de la plage F3: F4 dans la cellule B3 et renvoie l'emplacement de chaque chaîne de texte dans la cellule B3. Il renverra un résultat de tableau comme suit: {#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, comparez le résultat de la fonction COUNT et de zéro, tant que le résultat de la fonction COUNT est égal à 0, il retournera TRUE, ou il retournera FALSE. Ici, il renvoie VRAI.
doc vérifier si contient certains mais pas d'autres 4

3 partie: ET fonction

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

=AND(TRUE,TRUE)

=TRUE

Exemple de fichier

exemple de docCliquez pour télécharger un exemple de fichier


Formules relatives


Les meilleurs outils de productivité de bureau

Kutools for Excel - vous aide à vous démarquer de la foule

Souhaitez-vous terminer votre travail quotidien rapidement et parfaitement? Kutools for Excel apporte 300 fonctionnalités avancées puissantes (Combiner les classeurs, somme par couleur, diviser le contenu des cellules, convertir la date, etc.) et gagner 80% de temps pour vous.

  • Conçu pour 1500 scénarios de travail, vous aide à résoudre 80% des problèmes Excel.
  • Réduisez les milliers de clics de clavier et de souris chaque jour, soulagez vos yeux et vos mains fatigués.
  • Devenez un expert Excel en 3 minutes. Plus besoin de se souvenir des formules douloureuses et des codes VBA.
  • Essai gratuit illimité de 30 jours. Garantie de remboursement de 60 jours. Mise à jour et assistance gratuites pendant 2 ans.
Ruban d'Excel (avec Kutools for Excel installé)

Onglet Office - Activer la lecture et la modification par onglets dans Microsoft Office (y compris Excel)

  • Une seconde pour basculer entre des dizaines de documents ouverts!
  • Réduisez chaque jour des centaines de clics de souris, dites adieu à la souris.
  • Augmente votre productivité de 50% lors de l'affichage et de l'édition de plusieurs documents.
  • Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Firefox et le nouvel Internet Explorer.
Capture d'écran d'Excel (avec Office Tab installé)
Commentaires (3)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de combiner cet outil et d'autres pour aider avec l'exemple suivant ?
Dans la page 1, colonne A, cellule A : la somme de toutes les valeurs trouvées dans la plage de la page 2
Ce commentaire a été minimisé par le modérateur sur le site
...Page 2, plage de colonnes A15-A25, si la cellule correspondante de la page 2, plage de colonnes B15-B25 contient le mot "Pommes".
Ce commentaire a été minimisé par le modérateur sur le site
Salut, GoodEvening, je suppose que vous voulez additionner les valeurs dans la colonne A si les cellules correspondantes dans la colonne B sont le mot "pommes" comme la capture d'écran ci-dessous, si c'est le cas, vous pouvez vous référer au toturial ci-dessous, il fournit la formule pour résoudre ce problème dans les détails.https://www.extendoffice.com/excel/formulas/excel-sum-if-cell-equals-value-does-not-equal.html
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés