Nb.si.ens et critère "complexe" (recherche de chaîne de carac)

Bonjour et merci pour cette jolie base de données de réponses aux questions des amateurs "pas forcément bien éclairés comme moi"

Dans une feuille excel j'ai une formule utilisant la fonction nb.si.ens qui me permet de chercher le nombre d'occurences de certains critères dans un tableau.

La formule est du type

où

"origine", "date" et "statut" sont des noms de plages verticale de cellules (tjrs la même hauteur)

A47 contient un caractère ou une chaîne de caractère (c'est lui qui me pose problème)

C47 une date

La formule me remonte le nombre d’occurrences répondant strictement aux critères et notamment celles où il FAUT que le texte de la cellule A47 soit strictement présent dans la plage "origine"

La difficulté que je ne parviens pas à lever est le cas dans lequel je veux pouvoir compter le nombre d’occurrences de la plage de cellules "origine" contenant non seulement le terme exact contenu dans A47 (exemple la lettre A), mais incluant aussi tous les textes contenant ce terme exact.

exemple : dans la plage origine j'ai A,B,C ou A,B ou A ou B ou A,C ... et je veux compter mettre dans un tableau de synthèse le même type de formule [NB.SI.ENS(origine;A47;date;">="&C47;statut;"=5")] mais avec un comptage de toutes les cellules de la plage "origine" contenant la lettre A (ou plutôt contenant le contenu de la cellule A47, qui sera A ou B ou C concrètement).

CHERCHE, TROUVE ou autre fonction ne marchent pas ou en tout cas je ne sais pas rédiger un critère de test qui soit plus intelligent que "A47" pour prendre en compte toutes les possibilités de présence de la lettre A -ou autre chaîne de caractères- contenue dans A47 dans les différentes chaînes de caractères présentes dans la plage de cellule "origine"

J'espère avoir été clair (?)

Merci d'avance pour les éléments que vous pourriez me donner

[en écrivant ce message je me disais qu'une solution serait une sommation de plusieurs formules NB.SI.ENS avec variation du critère présent sur le premier test de la plage "origine", ie =NB.SI.ENS(origine;"A";...)+NB.SI.ENS(origine;"A,B";...)+etc etc mais ce n'est guère glorieux ni intéressant puisque je veux pouvoir me raccrocher au contenu de la cellule A47 et non aux valeurs que peut prendre cette cellule]

En ces temps de confinement je ne peux pas joindre le fichier en question (il est sur mon PC boulot qui n'est pas raccordé à internet ).

bonjour

justement , tu as le temps de peaufiner une petite maquette d'une 10 aines de lignes

cordialement

Exact...

Malheureusement je ne peux pas de suite... rdv médical, boulot à faire quand même, bref je ne vais pas raconter ma vie

A noter que j'ai avancé et découvert que si je met les différentes valeurs cherchées dans une plage de cellules au lieu de mettere le caractère cherché dans A47 alors je peux utiliser sommeprod pour éviter de sommer les différents NB.SI.ENS.

Mais c'est bien la recherche de "comment transformer la chaîne de caractère présente en A47 en critère de recherche de la présence de cette chaîne de caractère parmis les différentes chaîne de caractères de la plage origine" qui m'intéresse.

re

prend ton temps

cordialement

Rebonjour, je reviens vers vous avec mon problème qui est toujours d'actualité.

Alors si la condition sine qua non pour qu'on m'aide c'est de faire un fichier j'en ai fait un mais :

• ce n'est qu'un exemple bien moins complexe que le tableau sur lequel je dois faire des indicateurs
• et surtout je n'ai pas sur mon PC perso la bonne version d'excel (2003) par rapport à celle du boulot (2016)

J'espère avoir une aide, d'avance merci

re

je ne saisi pas bien la manoeuvre ,mais je te propose matière à reflexion

cordialement

Merci,

Alors... déjà ce qui m'étonne c'est qu'en ouvrant ton fichier il y a 3 écrit, puis si je fais un copier/coller d'une partie de la formule pour comprendre alors le résultat passe à 1 (que je fasse fn+F2 ou pas).

A l'ouverture du fichier il y a conversion du format xlsx en xls, ceci expliquerait celà??

Tu as choisi de travailler sur la colonne observation alors que je travaille sur celle "origine" (la plage de cellule s'appelle origine d'ailleurs, je l'ai nommée car çà soulage mes neurones fatigués par les $D1:$D12 et autres désignations de plages).

Je me suis sans doute mal exprimé :

=> je cherche à compter le nombre de lignes respectant un certain nombre de critères (pas que la date et l'existence de A,B ou C mais aussi d'autres critères), c'est pourquoi j'utilise la fonction NB.SI.ENS (de tête SOMMEPROD s'applique pour une matrice à 2 dimensions seulement? je ne sais pas si le terme matriciel est adéquat). je ne sais pas si sommeprod permet de rajouter plusieurs conditions (sur le nom de la référence compris entre 342 et 345 per exemple ou autre).

En reprenant ton exemple je ne cherche pas à compter la présence de A dans une chaîne de caractères, mais à vérifier au sein de mon NB.SI.ENS si A est "présent ou pas" dans les cellules prises une par une dans le cadre du calcul (matriciel? je ne sais pas non plus si le terme est adéquat).

=> Finalement tu obtiens 3 là où je cherche à trouver 2 (la ligne où est BLABLA répond aux deux critères de date et de présence de A)

En reprenant ton exemple pour B j'aurais un résultat de 1 et C de 1 également.

J'espère éclairer ma recherche (je comprend mieux pk il fallait un exemple... mais je suis visiblement limité par mon excel 2003...).

En allant plus loin, MAIS ce n'est pas du tout le but initial donc cette recherche n'est pas envisagée si trop dûr (même si souhaitable si elle est possible) : cherchant les observations contenant A ou B j'aurais 2 (le cellule qui contient B contient aussi A) et cherchant les observations qui contiennent A et B j'aurais 1 (cette célèbre cellule BLABLA).

Ok, vu pour les accolades pour lesquelles il faut fait CTRL+MAJ+entrée pour valider le calcul matriciel ... mais justement étant appelé à ne pas utiliser seul le fichier je ne pourrai pas passer le mot à tous les utilisateurs potentiels pour qu'il "pensent" à faire ctrl+maj+entrée dzans une cellule à un moment donné sachant qu'il y a des centaines de cellules dans mes fichiers de travail.

Bonjour,

Pour info, à ce stade comme il n'y a visiblement pas de remède miracle permettant de chercher une chaîne de caractère dans une plage de cellule soumise à des tets NB.SI.ENS j'en suis à trier les chaînes de caractères dans une zone du tableau et à faire un test "matriciel" dans NB.SI.ENS

Dans mon fichier d'origine la formule devient SOMMEPROD(NB.SI.ENS(date;">="&"2018";référence;"<>";"";origine;M2:M8))

où M2:M8 contiennent les valeurs que je cherche dans le tableau.

Ca ne me satisfait pas vraiment étant donné que je cherchais une chaîne de caractères dans des cellules et que j'en suis à identifier les valeurs que prennent les cellules qui satisfont à la chaîne de caractère cherchée pour mettre leur contenu dans une autre zone de la feuille excel.

Je vais peut-être essaye de récupérer un Excel2008 (si çà suffit) afin de tester les formules sur mon PC perso (le télé-travail a des limites sans connexion internet).