Exceller

La cellule équivaut à l'une des nombreuses choses

Cell Equals One Many Things

Formule Excel: la cellule équivaut à l'une des nombreuses chosesFormule générique
= SUMPRODUCT (--(A1=things))>0
Résumé

Si vous souhaitez tester une cellule pour voir si elle équivaut à l'une de plusieurs choses, vous pouvez le faire avec une formule qui utilise la fonction SOMMEPROD.

excel mise en forme conditionnelle 3 pourcentage d'échelle de couleurs

Le contexte

Supposons que vous ayez une liste de chaînes de texte dans la plage B5: B11 et que vous souhaitiez tester chaque cellule par rapport à une autre liste d'éléments de la plage E5: E9. En d'autres termes, pour chaque cellule de B5: B11, vous voulez savoir: cette cellule est-elle égale à l'une des choses de E5: E9?



Vous pouvez commencer à construire une grande formule basée sur instructions IF imbriquées , mais une formule matricielle basée sur SUMPRODUCT est une approche plus simple et plus propre.



Solution

La solution consiste à créer une formule qui testera plusieurs valeurs et retournera une liste de valeurs VRAI / FAUX. Une fois que nous avons cela, nous pouvons traiter cette liste (un tableau, en fait) avec SUMPRODUCT.

La formule que nous utilisons ressemble à ceci:



comment multiplier sur excel
 
= SUMPRODUCT (--(B5=things))>0
Explication

La clé est cet extrait:

 
--(B5=things)

qui compare simplement la valeur de B5 à chaque valeur de la plage nommée «choses». Parce que nous comparons B5 à un tableau (c'est-à-dire la plage nommée `` choses '', E5: E11), le résultat sera un tableau de valeurs TRUE / FALSE comme ceci:

{TRUEFALSEFALSEFALSEFALSE}



Si nous avons même un VRAI dans le tableau, nous savons que B5 est égal à au moins une chose dans la liste, donc, pour forcer les valeurs VRAI / FAUX à 1 et 0, nous utilisons un double négatif (-, également appelé un double unaire). Après cette coercition, nous avons ceci:

{10000}

verrouiller la ligne et la colonne dans Excel

Nous traitons maintenant le résultat avec SUMPRODUCT, qui ajoutera les éléments du tableau. Si nous obtenons un résultat différent de zéro, nous avons au moins une correspondance, nous utilisons donc> 1 pour forcer un résultat final de TRUE ou FALSE.

Avec une liste codée en dur

Il n'est pas nécessaire que vous utilisiez une plage pour votre liste de choses. Si vous ne recherchez qu'un petit nombre d'éléments, vous pouvez utiliser une liste au format tableau, appelée constante de tableau. Par exemple, si vous recherchez uniquement les couleurs rouge, bleu et vert, vous pouvez utiliser {'red', 'blue', 'green'} comme ceci:

 
--(B5={'red','blue','green'})

Gérer les espaces supplémentaires

Si les cellules que vous testez contiennent de l'espace supplémentaire, elles ne correspondent pas correctement. Pour supprimer tout espace supplémentaire, vous pouvez modifier la formule pour utiliser la fonction TRIM comme suit:

 
= SUMPRODUCT (--( TRIM (A1)=things))>0
Auteur Dave Bruns


^