XL 2016 NB.SI excluant des critères

[Phillip]

Bonjour,

J'ai un fichier exemple joint, ou je veux compter des mots clés (tata, titi, tutu...)...J'ai utilisé la formule nb.si qui marche très bien. Les cellules peuvent contenir plusieurs mots clés.

Jusqu'ici tout va bien. J'ai des mots clés autres que titi, tata, tutu...Ici, pour l'exemple, j'ai mis arbre, maison, chemin.

Je voudrais compter les "autres", c'est à dire ceux qui ne sont pas titi, tata, tutu. (Je ne sais pas à l'avance que ça va être arbre, maison, chemin, donc je ne peux pas le mettre en dur dans la formule), et la soustraction du total moins les tutu, tata, titi.... ne marche pas, comme j'ai parfois plusieurs mots clés dans une cellule (ça me donne des valeurs négatives)

Ma formule =NB.SI($A$1:$A$16;OU("<>*toto*";"<>*tata*";"<>*titi*";"<>*tutu*";"<>*tyty*";"<>""")), donne 0 et je ne comprends pas pourquoi....

Merci de votre aide !

Cordialement

Phillip

 

[Jocelyn]

Bonjour le Forum,
Bonjour Phillip,

Un essai avec une formule un peu capillotractée mais qui semble fonctionner

Cordialement

 

[Phillip]

Bonjour,

Ca marche avec le fichier exemple, mais pas avec le fichier réel...Je ne comprends pas par ailleurs comment ça marche !

Je rectifie, ça marche moyen...

Cordialement

 

[Jocelyn]

re,

Pour t'expliquer la démarche le NBVAL(A1:A16) va compter le nombre de cellules non vide, ensuite on retire à cela la somme des chiffres des critères SOMME(F1:F5) et comme il y a cellule contenant plusieurs valeurs séparées par des virgules on ajoute le nombre de virgules puisqu'a priori chaque virgule est suivi d'un texte pour cela on compte le nombre de caractères présents dans la plage A1:A16 auquel on soustrait le nombre de caractère de la meme plage en ayant dans cette plage remplacé les , par rien (SOMMEPROD((NBCAR(A1:A16)))-SOMMEPROD((NBCAR(SUBSTITUE(A1:A16;",";"")))))

ensuite sans voir ton fichier original difficile de dire pourquoi cela ne fonctionne pas tes termes sont ils séparés par des virgules ?

Cordialement

 

[Phillip]

Oui, virgules, mais j'ai des termes avec des espaces...genre Tata,tutu toto...C'est sans doute ça le problème !

 

[JHA]

Bonjour à tous,
Bonjour Jocelyn

Une autre façon de voir les choses mais il faut effectuer plusieurs manipulations.

JHA

 

[Phillip]

Dans ta formule, je rajoute un sommeprod avec substitution des espaces par "rien" et en ajoutant 2 fois le nombre de caractères et ça marche. Je ne comprends toujours pas la relation mathématique qu'il peut y avoir entre le nombre de virgules ou espaces et le nombre de mots "autres". En revanche, ça ne marche pas pour mon fichier original, et là, je ne comprends pas non plus...

 

[Jocelyn]

Bonjour L'ami JHA,

@ Phillip c'est plus une relation de logique qu'une relation mathématique même si au final cela ce rejoint, le but est de savoir en fin de compte combien il y a de terme dans toute la plage le NBVAL donne le nombre de cellule non vide qu'elles aient un ou plusieurs termes sachant que chaque terme est séparé d'une virgule a priori si il y a une virgule un terme la suit donc en comptant le nombre de virgule nous avons le nombre de termes à ajouter au nombre de cellules non vide 1 par cellule non vide plus un par virgule suivie d'un terme

après les soucis sur ton fichier origina je ne sais pas comme ca sans voir

@ JHA , oui bonne solution aussi mais comme d'hab moi je dis pourquoi faire simple quand je peux faire compliqué et pourtant j'essai de me soigner

 

[Jocelyn]

Pour le Fun une formule qui passe par un seul sommeprod et des substitues

=SOMMEPROD((SUPPRESPACE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1:A16;E1;"");E2;"");E3;"");E4;"");E5;"");",";))<>"")*1)

Cordialement

 

[Phillip]

Ayé, j'ai compris la logique (ça tourne pas vite, là-haut!). Maintenant, il faut que je trouve tout seul pourquoi ça ne fonctionne pas sur mon fichier original ! Merci de toutes ces explications !

Cordialement

PS : J'ai trouvé...En fait, j'ai termes du genre "herbe de machin en cours"...Ca me compte donc 5 termes. J'ai donc rajouté une macro (car oui, j'ai une macro en plus !) qui remplace les espaces par le trait du 8....

Phillip

 

[Exl-Noob]

Salut,

Il y a plus simple qu'une macro je pense
L'angle d'attaque est de compter le nombre de virgule par cellule.
De là, on sait le nombre de mot clefs. Soit 1 s'il n'y a pas de virgule dans la cellule, soit nb virgule + 1.
Le problème de la formule NB.SI c'est qu'elle ne donne que 0 ou 1. La formule s'arrête de compter le nombre de virgule dans une cellule au delà de 1. Gênant.

Donc pour compter les virgules il faut une formule inutilement compliquée à inclure malheureusement à chaque ligne :

=NBCAR(A1)-NBCAR(SUBSTITUE(A1;",";""))

Ensuite le total de mots clefs différents, n'est que la somme des mots clefs...

=SOMME(C1:C20)+NBVAL(A1:A20)

à laquelle on retranche la somme des autres mots clefs identifiés

=SOMME(C1:C20)+NBVAL(A1:A20)-SOMME(F1:F5)

Ce fichier marche avec tous les cases qui contiennent des mots clefs seuls, ou nombreux mais séparés par une virgule, avec ou sans espace.
Par contre les cellules remplies avec une espace vont troubler le calcul, j'aime autant prévenir.

J'ai mis le bon résultat en Orange, petit clin d'œil

Bonne soirée,