Valeurs distinctes avec valeurs vides

[lagire]

Bonjour,

avec cette formule qui compte le nombre de valeurs distinctes dans la colonne E en fonction s'il existe une valeur numérique dans la colonne V, elle ne fonctionne plus s'il y a des valeurs vides dans la colonne E.

SOMME(SI(ESTNUM(ALBUMS!V8644:V8652);1/NB.SI(ALBUMS!E8644:E8652;ALBUMS!E8644:E8652)))

Merci pour vos solutions.

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Bonsoir à tous, bonsoir R@chid

Ne pourrais tu être un peu plus précis pour que nous comprenions bien ton besoin

Bonjour, Rachid

mes explications et résultats attendus sont dans le fichier joint.

... s'il y a une valeur numérique dans la colonne Q dans l'exemple.

Dans ton fichier, il n'y a pas de valeur dans la colonne Q !!!

@ plus

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Re

Que veut dire "il n'y aura pas dans F" en français ?

@ plus

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Re

Que représentent les nombres dans les colonnes R et W ?

@ plus

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Re

Doit on tenir compte de USA ?

@ plus

 

[lagire]

Re : Valeurs distinctes avec valeurs vides

Bonjour,

je ne vois comment je peux être plus précis que dans l'exemple.
CISCO je me suis trompé dans un de mes précédents message, c’était s'il n y a pas de valeur dans la colonne R.

Je me réexplique,
nous avons 2 colonnes E et F avec des abréviations de pays.
il faut les valeurs distinctes entre ces 2 colonnes, s'il y a une valeur numérique dans la colonne R (de R1 à R9)
Même chose s'il y a une valeur numérique dans la colonne W.

Pour la colonne R, nous avons des valeurs num de R1 à R7. il faut donc compter les valeurs distinctes correspondantes sur les colonnes E et F.
sur la colonne E, on retrouve donc BRA ALL FRA SUI ITA et une nouvelle fois FRA qu'il ne faut pas compter.
Donc pour l'instant, ça fait 5.
sur la colonne F, on retrouve SUI ALL ITA, qu'on retrouve aussi sur la colonne E, donc aucun ne doit être compter en plus.
Mais si je rajoute par ex TCH en F1 et SUI en E5, ça doit ne m'en compter que 1 en +, car SUI existe déjà.
il faut que cela soit dynamique et pas de macro car je n'ai que starter.

Même opération à réaliser avec la colonne W.
Merci pour vos réponses.

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Bonsoir

Si je comprend bien, tu veux compter le nombre de termes différents dans la plage E1:F9, placés sur des lignes contenant aussi un nombre en colonne R. Même calcul par rapport à la colonne W. Si c'est ça, cf. deux propositions en I11 et J11 (toutes deux très proches l'une de l'autre) pour ce qui est du calcul par rapport à la colonne R.

En I11, cela donne

SOMMEPROD(ESTNUM(R1:R9)*(SIERREUR(EQUIV(E1:E9;E1:E9;0);0)=LIGNE(1:9)))+SOMMEPROD(ESTNA(EQUIV(SI(ESTNUM(R1:R9);F1:F9);SI(ESTNUM(R1:R9);E1:E9);0))*(SIERREUR(EQUIV(F1:F9;F1:F9;0);0)=LIGNE(1:9)))

en matriciel, donc à valider avec Ctrl+maj tempo+entrer.

ESTNUM(R1:R9) permet de ne prendre en compte que les lignes contenant une valeur numérique dans la plage R1:R9.
(SIERREUR(EQUIV(E1:E9;E1:E9;0);0)=LIGNE(1:9)) renvoie FAUX pour les doublons de la plage E1:E9.
Et en combinant les deux expressions précédentes, cela donne
SOMMEPROD(ESTNUM(R1:R9)*(SIERREUR(EQUIV(E1:E9;E1:E9;0);0)=LIGNE(1:9)))
qui permet de calculer le nombre de termes différents dans E1:E9 sur des lignes contenant des valeurs numériques dans la plage R1:R9.

ESTNA(EQUIV(SI(ESTNUM(R1:R9);F1:F9);SI(ESTNUM(R1:R9);E1:E9);0)) permet de ne prendre en compte que les lignes contenant une valeur numérique dans la plage R1:R9 et contenant dans E1:E9 des termes non présents dans F1:F9
SOMMEPROD(ESTNA(EQUIV(SI(ESTNUM(R1:R9);F1:F9);SI(ESTNUM(R1:R9);E1:E9);0))*(SIERREUR(EQUIV(F1:F9;F1:F9;0);0)=LIGNE(1:9))
permet de calculer le nombre de termes différents dans la plage F1:F9 placées sur une ligne contenant une valeur numérique dans R1:R9 et pas dans la plage E1:E9 sur les mêmes lignes.

@ plus

 

[lagire]

Re : Valeurs distinctes avec valeurs vides

Merci CISCO,

Je crois que tu as bien compris ce que je voulais.
c'est déjà beaucoup mieux, mais ça ne fonctionne pas tout à fait.
si je rajoute par ex ITA en F8, il ne me compte pas un en plus en valeur distinctes W.

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Bonsoir

Effectivement, la formule ne donnait pas le bon résultat dans tous les cas. Dans la foulée, j'ai même vu que la première partie, celle qui calcule le nombre de termes différents dans la colonne E, n'était pas parfaite (cf la pièce jointe lagire2bis). Je l'ai donc aussi modifiée.

Cf. donc en pièce jointe. A vérifier bien sûr.

Le principe :
EQUIV(x cellules;x cellules;0)=LIGNE(1:x) renvoie FAUX pour les doublons (parce que, pour un doublon y, EQUIV(y;x cellules;0) renvoie la position du 1er y dans les x cellules, et pas celle du 2ème doublon dans ces x cellules. Ex : EQUIV({"AA";"BB";"AA"};{"AA";"BB";"AA"};0) donne {1;2;1} et pas {1;2;3})
SOMMEPROD(1*EQUIV(x cellules;x cellules;0)=LIGNE(1:x)) donne le nombre de valeurs différentes, puisque les doublons comptent pour FAUX, et que 1*FAUX= 0.

Le problème ici, c'est de lister les x cellules à prendre en compte, une première fois sur la plage E1:E9, une seconde fois sur F1:F9.

En I11, pour la première partie, pour la colonne E
ESTNUM(R1:R9)*(E1:E9<>"");E1:E9;"") garde les termes différents de "" de E1:E9 sur les lignes contenant des nombres dans la colonne R, et met "" dans les autres cellules.
SI(ESTNUM(R1:R9);E1:E9);0) garde les termes de E1:E9 sur les lignes contenant des nombres dans la colonne R, et met des 0 dans les autres cellules (et pas des "" dans les autres cellules).
EQUIV(SI(ESTNUM(R1:R9)*(E1:E9<>"");E1:E9;"");SI(ESTNUM(R1:R9);E1:E9);0);0) renvoie la position des termes du premier groupe dans le second groupe.
SOMMEPROD(1*(SIERREUR(EQUIV(SI(ESTNUM(R1:R9)*(E1:E9<>"");E1:E9;"");SI(ESTNUM(R1:R9);E1:E9);0);0)=LIGNE(1:9))) compte le nombre de termes différents dans la plage E1:E9 sur les lignes contenant des nombres dans la plage R1:R9

Toujours en I11, mais pour la seconde partie, pour la colonne F, c'est la même méthode, mais il faut faire un test en plus pour comparer F1:F9 à E1:E9, sous la forme SI(ESTNA qui choisit les termes intéressants dans la plage F1:F9 non présents dans la plage E1:E9, termes différents de "", et placés sur les bonnes lignes.

SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;"") garde les termes de la plage F1:F9 sur les lignes contenant des nombres dans la colonne R, différents (à cause du ESTNA) des termes de la plage E1:E9 sur les mêmes lignes, et met des "" dans les autres cellules.

SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;0), idem, mais met des 0 dans les autres cellules.

EQUIV(SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;"");SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;0);0) renvoie la position des termes du premier groupe dans le second.

Ensuite, on procède de même, avec un test =LIGNE(1:9) pour éliminer les doublons et on les somme avec un SOMMEPROD(....) matriciel à cause des ESTNUM(...), des (...<>"") qui font intervenir des plages de plusieurs cellules, et non d'une seule cellule.


Si quelqu'un trouve plus court...

@ plus

 

[lagire]

Re : Valeurs distinctes avec valeurs vides

Merci CISCO,

cela fonctionne.

à plus tard, j'aurais surement d'autres demandes + tard.

 

[lagire]

Re : Valeurs distinctes avec valeurs vides

Bonjour,

je reviens à nouveau vers vous car je voudrais modifier mon résultat final.
Toujours le même principe, il faut les doublons mais non plus sur 2 mais 4 colonnes, toujours en fonction d'une valeur num en R ou W.
Et autre point, je voudrais non plus compter les doublons mais retranscrire chaque doublon(texte) sur une même ligne.
Ex pour les valeurs distinctes R, mettre en I1>ALL J1>SUI K1>FRA etc...

Merci pour votre retour.

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Bonjour

David84 avait traité un sujet similaire là https://www.excel-downloads.com/threads/liste-triee-par-ordre-alpha-extraite-dun-tableau.147738/. Sa méthode donnait la liste, en colonne et dans l'ordre alphabétique, des termes différents initialement répartis dans un tableau, mais sans tenir compte d'une autre colonne contenant des valeurs. Je vais voir si je peux adapter son travail à ton besoin... Pas évident.

@ plus

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Re

Cf. en pièce jointe la proposition de David84 une fois modifiée pour tenir compte d'une colonne supplémentaire contenant des nombres. Ne reste plus qu'à adapter cela à ton cas.

@ plus

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Re

Et hop, c'est fait. Cf. en pièce jointe.
La plage zone est définie dans le gestionnaire de noms. Si tu veux y incorporer encore plus de colonnes, il suffit de la modifier dans ce gestionnaire. Si tu veux en modifier le nombre de lignes, il faudra changer la définition de la plage zone, et le contenu de la formule (R1:R9 à remplacer par...).

@ plus

 

[CISCO]

Re : Valeurs distinctes avec valeurs vides

Re

Idem, mais cette fois-ci, si tu veux rajouter des colonnes et/ou des lignes à la plage zone, il te suffira de modifier la définition de la plage zone dans le gestionnaire de noms, à condition que tu ailles toujours chercher les nombres en colonnes R ou W..

@ plus

 

[lagire]

Re : Valeurs distinctes avec valeurs vides

super Cisco et merci, ça fonctionne parfaitement