Discussion :

[LG-69]

Bonjour,

Je cherche sur les forum depuis un moment, j'ai essayé pas mal de trucs et je suis presque au bout... mais ça marche toujours pas complètement... Tout aide sera la bienvenue, merci d'avance.

Mon problème :

- J'ai une liste de personnes en colonne B (de la ligne 5 à 181) avec potentiellement des doublons sur les noms et des cellules vides.
- Pour chaque personne, j'ai (ou pas) une saisie de valeur en colonne M

Je souhaite compter le nombre de personnes ayant une valeur saisie en colonne M (en ne comptant qu'une seule fois les personnes en doublon)

La formule que j'ai trouvée qui marche presque :

=SOMMEPROD(ESTNUM(M$5:M$181)*SI($B$5:$B$181="";0;1/NB.SI($B$5:$B$181;$B$5:$B$181)))

Si on la décortique :

Etape 1 :
SI($B$5:$B$181="";0;1/NB.SI($B$5:$B$181;$B$5:$B$181))

signifie que si le nom n'est pas renseigné en colonne B ($B$5:$B$181="") je mets dans ma matrice 0, sinon je mets 1/NB.SI($B$5:$B$181;$B$5:$B$181), ça me permet de compter le nombre de valeur sans doublon en colonne 5 en acceptant les cellules vides => ça marche (ça me donne une matrice de pondération pour chaque ligne (1 si le nom n'est pas en doublon, 0,5 si nom trouvé 2 fois, 0,3333 si nom trouvé 3 fois,...)

Etape 2 :
ESTNUM(M$5:M$181)* formule_ci_dessus

fait un produit de 2 matrices, celle de mes doublons et celle de ma colonne de saisie M
En gros si j'ai quelque chose de saisie en colonne M pour ma personne on fait x1 sinon on fait x0 => ça marche (ça me donne une matrice mettant 0 si la personne n'a pas de valeur en colonne M, la valeur de l'autre matrice ci-dessous si elle a une saisie)

Etape 3 :
SOMMEPROD()

fait la somme de tout ça => ça marche, ça me donne la somme de toutes les valeurs de ma matrice résultante

MAIS.... j'ai constaté un écart en contrôlant mes résultats et cet écart vient de :

- Si j'ai une personne en doublon, dans la première matrice je vais avoir 0,5 sur la première ligne où apparaît son nom, puis 0,5 pour la deuxième ligne
- Mais... si la personne n'a une valeur en colonne M que sur la première ligne par exemple la matrice résultante (étape 2) donnera 0,5 sur la sa première ligne mais 0 sur sa deuxième ligne

De ce fait, quand on fera la somme (etape 3) ma personne ne comptera que pour 0,5 et non pour 1, du coup ma personne n'est comptée qu'à moitié....

J'ai pensé à une solution mais je n'arrive pas à la mettre en oeuvre...
Il faudrait que je fasse ma recherche de doublon non pas sur ma colonne B mais sur une matrice ne prenant en compte que les noms des gens ayant une valeur en colonne M.... et ça je n'arrive pas à le faire.

J'ai tenté une première étape : SI(ESTNUM(L$5:L$181);B$5:B$181)) qui semble me renvoyer une matrice avec des 0 si pas de valeur en colonne L et le nom de la personne (issu de la colonne B) s'il y a une valeur en L, mais après.... comment faire pour faire un comptage sans doublon de cette matrice résultante ?

Mon cas est assez compliqué et j'ai essayé de l'expliquer de mon mieux.

en espérant que j'ai été assez clair et que quelqu'un pourra m'aider.
D'avance merci

[LG-69]

Personne ne saurait me répondre ?

J'ai quand même avancé un peu en faisant du pas à pas et en passant par moult cellules intermédiaires...

En fait il me faut une sorte de matrice virtuelle intermédiaire contenant les valeurs suivantes : SI(ESTNUM($M$5:$M$181);$B$5:$B$181;"")
Elle serait remplie par le nom de la personne si la personne a une valeur renseignée en colonne M, rien ("") si pas de valeur renseignée.
Il faudrait que j'arrive à charger ces valeurs dans une matrice virtuelle "MaMatrice"

Pour ensuite pouvoir utiliser le comptage : =SOMMEPROD(SI(MaMatrice="";0;1/NB.SI(MaMatrice;MaMatrice)))

J'ai essayé en remplaçant le texte MaMatrice par la formule SI(ESTNUM($M$5:$M$181);$B$5:$B$181;""), pas de souci pour la première et troisième "MaMatrice" (cf formule ci-dessous) mais ça marche pas pour la 2ème, ça me met alors #VALEUR dans le résultat...

Donc en gros je me dis que si j'arrivais à charger cette Matrice virtuelle MaMatrice et utiliser cette référence dans la formule SOMMEPROD.... ça devrait marcher... Mais je ne sais pas faire cette étape.

[papouclo]

Bonjour,
Essayer la formule matricielle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(SIERREUR(1/NB.SI.ENS($B$5:$B$181;$B$5:$B$181;$M$5:$M$181;">0");0)*ESTNUM($M$5:$M$181))

à valider par Ctrl + Maj + Entrée
Cordialement
Claude

[LG-69]

Bonjour,

Merci beaucoup pour cette réponse qui semble parfaitement répondre à mon besoin !
J'ai bien vérifié tous mes chiffres et je retombe sur mes pattes. Je ne connaissais pas cette formule NB.SI.ENS

Cependant, pour ma compréhension avant de passer le sujet à Résolu, comment se décortique la formule ?
J'ai l'impression "*ESTNUM($M$5:$M$181)" de la fin n'est pas utile et pourtant.... si je ne le mets pas je n'ai pas le même résultat (et le résultat devient faux).

Le "NB.SI.ENS($B$5:$B$181;$B$5:$B$181;$M$5:$M$181;">0")" nous renvoie (sous forme de matrice) le nombre de fois que chacune des valeurs de B5 à B181 est trouvée dans la plage B5 à B181 et pour lesquelles la colonne M correspondante est >0, c'est bien ça ?
Le "SIERREUR" permet de remplacer par 0 en cas d'erreur sur la formule 1/NB.SI.ENS... (ça c'est pour les cas où on a une cellule vide en colonne B et que donc NB.SI.ENS renvoie 0)

J'ai donc l'impression que le *ESTNUM n'est pas utile car on a déjà tenu compte du fait que la colonne M est numérique (le ">0")

Est-ce qu'il y a quelque chose qui m'échappe ?

[papouclo]

Bonjour,
L'image ci-dessous tente d'illustrer le problème.

La fonction NB.SI.ENS($B$5:$B$181;"a";$M$5:$M$181;">0") dénombre parfaitement qu'il n'y a que deux fois simultanément sur une même ligne le texte "a" en colonne B
et une valeur numérique positive en colonne M et renvoie l'inverse de 2 soit 0,5.
Seulement autant de fois on appellera cette fonction autant de fois elle renverra cette réponse.
Et c'est ce qui se passe sur l'image en colonne O où elle est appelée 3 fois car il y a 3 occurrences de "a" en colonne B , ce qui fait qu'elle renvoie une fois de trop la
réponse 0,5 en O6, d'où un résultat faussé si l'on additionnait les nombres de la colonne.
Le problème est corrigé si l'on écrit un produit dont le deuxième facteur est NUM($M5:$M181) ce qui, à la ligne 6, multiplie le 0,5 "ennuyeux" par ESTNUM(M6), faux donc égal
à 0 dans le calcul.
Cordialement
Claude

[LG-69]

Super !! Merci beaucoup c'est parfaitement clair. C'est le 0.5 renvoyé en O6 qui m'avait échappé.