Exceller

La cellule contient l'un des nombreux éléments

Cell Contains One Many Things

Formule Excel: la cellule contient l'un des nombreux élémentsFormule générique
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0
Résumé

Pour tester si une cellule contient l'une des nombreuses chaînes, vous pouvez utiliser une formule basée sur le CHERCHER , NOMBRE et SOMMEPROD les fonctions. La formule en C5, copiée vers le bas, est:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

des choses est le plage nommée E5: E7.



Explication

Le but de cet exemple est de tester chaque cellule de B5: B14 pour voir si elle contient l'une des chaînes du plage nommée des choses (E5: E7). Ces chaînes peuvent apparaître partout dans la cellule, il s'agit donc d'un problème littéral de «contient». La formule en C5, copiée vers le bas, est:



 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Cette formule est basée sur une autre formule qui vérifie 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:

 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Quand le Fonction RECHERCHE trouve une chaîne, il renvoie la position de cette chaîne sous forme de nombre. Si RECHERCHE ne trouve une chaîne, elle renvoie une #VALUE! Erreur. Cela signifie que ISNUMBER retournera TRUE s'il y a une correspondance et FALSE sinon.



Dans cet exemple, le but est de vérifier plus d'une chaîne, nous donnons donc à la fonction RECHERCHE un liste de chaînes dans le plage nommée des choses . Puisqu'il y a 3 chaînes dans des choses ('rouge', 'vert' et 'bleu'), SEARCH renvoie 3 résultats dans un déployer comme ça:

somme d'une colonne dans Excel
 
{#VALUE!#VALUE!23}

Parce que «rouge» et «bleu» ne sont pas trouvés, la recherche renvoie une #VALEUR! Erreur. Cependant, comme «vert» apparaît près de la fin du texte dans la cellule B5, RECHERCHE renvoie 23 (c'est-à-dire que «vert» commence au 23e caractère).

Ce tableau est renvoyé directement au Fonction ISNUMBER , qui convertit les éléments du tableau en TRUE ou FALSE:



 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Logiquement, si nous avons même un VRAI dans le tableau, nous savons qu'une cellule contient au moins une des chaînes que nous recherchons. Le moyen le plus simple de vérifier TRUE est d'ajouter toutes les valeurs ensemble. Nous pouvons le faire avec le Fonction SOMMEPROD , mais nous devons d'abord forcer les valeurs TRUE / FALSE à 1s et 0s avec un double négation (--) comme ça:

 
--{FALSEFALSETRUE} // coerce to 1s and 0s

Cela donne un nouveau tableau contenant seulement 1 et 0:

comment changer les minuscules en majuscules dans Excel 2010
 
{001}

qui est livré directement à SUMPRODUCT:

 
= SUMPRODUCT ({001}) // returns 1

Avec un seul tableau à traiter, SUMPRODUCT additionne les éléments du tableau et renvoie un résultat. Tout résultat différent de zéro signifie que nous avons un `` hit '', donc nous ajoutons> 0 pour forcer un résultat final de TRUE ou FALSE:

 
= SUMPRODUCT ({001})>0 // returns TRUE

Notez que toute combinaison de correspondances renverra un nombre supérieur à zéro et que la formule retournera TRUE.

Avec une liste codée en dur

Il n'est pas nécessaire d'utiliser une plage pour la liste des chaînes à rechercher. Vous pouvez également utiliser un constante de tableau . Par exemple, pour rechercher «rouge», «bleu» ou «vert», vous pouvez utiliser une formule comme celle-ci:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Fonction SUM

Historiquement, SOMMEPROD apparaît souvent dans formules matricielles , car il peut gérer les tableaux de manière native, sans contrôle + shift + enter . Cela rend la formule «plus conviviale» pour la plupart des utilisateurs. Dans Excel 365 , qui gère les tableaux nativement , le Fonction SUM peut être utilisé à la place de SUMPRODUCT sans contrôle + shift + enter:

comment calculer cv dans excel
 
= SUM (-- ISNUMBER ( SEARCH (things,A1)))>0

Empêcher les fausses correspondances

Un problème avec cette approche est que vous pouvez obtenir de fausses correspondances à partir de sous-chaînes qui apparaissent à l'intérieur de mots plus longs. Par exemple, si vous essayez de faire correspondre «dr», vous pouvez également trouver «Andrea», «drink», «dry», etc. puisque «dr» apparaît à l'intérieur de ces mots. Cela se produit parce que SEARCH effectue automatiquement une correspondance «contient».

Pour un piratage rapide, vous pouvez ajouter de l'espace autour des mots de recherche (par exemple, `` dr '' ou `` dr '') pour éviter d'attraper `` dr '' dans un autre mot. Mais cela échouera si «dr» apparaît en premier ou en dernier dans une cellule, ou apparaît avec une ponctuation.

Si vous avez besoin d'une solution plus précise, une option consiste à normaliser le texte premier dans un colonne d'assistance , en prenant soin d'ajouter également un espace de début et de fin. Ensuite, vous utilisez la formule de cette page sur le texte résultant.

Pièces jointes Déposer la cellule contient l'un des nombreux éléments .xlsx Auteur Dave Bruns


^