Formule Excel: compter les cellules qui ne contiennent pas beaucoup de chaînes -

Table des matières

Formule générique

(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))

Sommaire

Pour compter les cellules qui ne contiennent pas beaucoup de chaînes différentes, vous pouvez utiliser une formule assez complexe basée sur la fonction MMULT. Dans l'exemple illustré, la formule en F5 est:

(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))

où «données» est la plage nommée B5: B14 et «exclure» est la plage nommée D5: D7.

Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée

Préface

Cette formule est compliquée par l'exigence «contient». Si vous avez juste besoin d'une formule pour compter les cellules qui ne sont pas * égales * à beaucoup de choses, vous pouvez utiliser une formule plus simple basée sur la fonction MATCH. De plus, si vous avez un nombre limité de chaînes à exclure, vous pouvez utiliser la fonction COUNTIFS comme ceci:

=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")

Cependant, avec cette approche, vous devez entrer une nouvelle paire d'arguments de plage / critères pour chaque chaîne à exclure. En revanche, la formule expliquée ci-dessous peut gérer un grand nombre de chaînes à exclure entrées directement dans la feuille de calcul.

Enfin, cette formule est complexe. Faites-moi savoir si vous avez une formule plus simple à proposer :)

Explication

Le noyau de cette formule est ISNUMBER et SEARCH:

ISNUMBER(SEARCH(TRANSPOSE(exclude),data))

Ici, nous transposons les éléments dans la plage nommée «exclure», puis nous transmettons le résultat à RECHERCHE en tant que «recherche de texte», avec «données» en tant que «dans le texte». La fonction SEARCH renvoie un tableau 2D de valeurs TRUE et FALSE, 10 lignes par 3 colonnes, comme ceci:

(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)

Pour chaque valeur dans "données", nous avons 3 résultats (un par chaîne de recherche) qui sont soit des erreurs #VALUE, soit des nombres. Les nombres représentent la position d'une chaîne de texte trouvée et les erreurs représentent des chaînes de texte introuvables. À propos, la fonction TRANSPOSE est nécessaire pour générer le tableau 10 x 3 de résultats complets.

Ce tableau est introduit dans ISNUMBER pour obtenir des valeurs TRUE FALSE, que nous convertissons en 1 et 0 avec un opérateur double négatif (-). Le résultat est un tableau comme celui-ci:

(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)

qui entre dans la fonction MMULT en tant que array1. En suivant les règles de multiplication matricielle, le nombre de colonnes dans array1 doit être égal au nombre de lignes dans array2. Pour générer array2 , nous utilisons la fonction ROW comme ceci:

ROW(exclude)^0

Cela donne un tableau de 1s, 3 lignes par 1 colonne:

(1;1;1)

qui entre dans MMULT en tant que array2 . Après la multiplication du tableau, nous avons un tableau dimensionné pour correspondre aux données d'origine:

(2;1;0;0;1;1;0;0;0;2)

Dans ce tableau, tout nombre différent de zéro représente une valeur où au moins une des chaînes exclues a été trouvée. Les zéros indiquent qu'aucune chaîne exclue n'a été trouvée. Pour forcer toutes les valeurs non nulles à 1, nous utilisons supérieur à zéro:

(2;1;0;0;1;1;0;0;0;2)>0

qui crée encore un autre tableau ou des valeurs TRUE et FALSE:

(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)

Notre objectif final est de ne compter que les valeurs de texte pour lesquelles aucune chaîne exclue n'a été trouvée, nous devons donc inverser ces valeurs. Nous faisons cela en soustrayant le tableau de 1. Ceci est un exemple de logique booléenne. L'opération mathématique contraint automatiquement les valeurs TRUE et FALSE à 1s et 0s, et nous avons enfin un tableau à retourner à la fonction SUM: