Comptage sans doublons sur double filtre

[Résolu/Fermé]
Signaler
Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
-
Messages postés
589
Date d'inscription
mardi 7 avril 2015
Statut
Membre
Dernière intervention
23 juillet 2015
-
Bonjour le forum !

Je mouline un peu pour trouver la bonne formule ... et j'aurais besoin d'un peu d'aide ...

L'objectif dans le fichier joint est de compter le nombre de Clients différents pour chaque commercial ...

Avec une formule matricielle type {=SOMMEPROD(SI(D5:D22<>"";1/NB.SI(D5:D22;D5:D22)))} je parviens à compter le nombre de commerciaux différents.
Avec une formule matricielle type {=SOMMEPROD(SI(C5:C22<>"";1/NB.SI(C5:C22;C5:C22)))} je parviens à compter le nombre de clients différents.

... mais pas de "mélanger" les 2.

Edit 8h31 : un autre fichier plus propre :
https://www.cjoint.com/c/EAxiNGj3cT8

Merci d'avance pour vos idées !


14 réponses

Messages postés
10320
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
31 août 2021
1 184
Bonjour,
Juste au passage, le filtre avancé sur la plage [C4 :D22] copier vers autre emplacement [K26 :L26] et cocher extraction sans doublon et pour terminer un tableau croisé dynamique avec base de données, la plage [K26 :L20]

1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
17505
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
31 août 2021
4 704
Re,

L'informatique est logique, c'est nous qui cherchons les limites, regarde ton fichier, ajoute des noms pour tester jusqu'à la ligne 40 et on en reparle

https://www.cjoint.com/c/EAxwoYKkqtq
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
17505
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
31 août 2021
4 704
Re,

Ce n'est pas très compliqué, mais j'ai pas trouvé plus court.
Commence par nommer tes plages, dans l'exemple la plage C5:C41 je l'ai nommée Client et la plage D5:D41 nommée Nom à adapter sur tes plages plus importantes

ensuite il faut créer la formule pour extraire les clients correspondant à un nom, ces formules peuvent être créées dans des colonnes masquées dans l'exemple la première en R17. Cette formule est matricielle , c'est à dire qu'il faut les confirmer en cliquant en même temps sur les trois touches du clavier Ctrl, Shift et Entrée, si tu fais bien la formule se placera entre ce type d'accolade {}

=SI(LIGNES($1:1)+COLONNE()-18<=NB.SI(Nom;$H$17);INDEX(Client;PETITE.VALEUR(SI(Nom=$H$17;LIGNE(INDIRECT("1:"&LIGNES(Nom))));LIGNES($1:1)+COLONNE()-18));"")

pour que cette formule fonctionne horizontalement j'ai rajouté la syntaxe COLONNE() de sorte LIGNES($1:1) - COLONNE() soit égale à 1 puis à 2 dans la deuxième colonne etc ...
la première formule est en colonne R soit la 18éme colonne et COLONNE() donne bien 18, pour avoir LIGNE($1:1)+COLONNE() donnerai LIGNE($1:19) donc pour avoir LIGNE($1:1) il faut donc écrire (
LIGNES($1:1)+COLONNE()-18

ensuite on va compter le nombre de commercial avec NB.SI(Nom;$H$17)

Ensuite on va extraire les données à partir de la première, puis de la deuxième etc ...
INDEX(Client;PETITE.VALEUR(SI(Nom=$H$17;LIGNE(INDIRECT("1:"&LIGNES(Nom))));LIGNES($1:1)+COLONNE()-18))

une fois la formule écrite on la valide en matricielle
Pour la deuxième ligne en R18 on copie la formule et on change l'adresse de la cellule critère H$17 en H$18 et on confirme en matricielle puis à la ligne du dessous en R19 modifier l'adresse cellule critère H$19 et on confirme
etc...

ensuite on surbrille les cellules contenant les formules et on incrémente vers la droite en fonction du nombre maximum de client pouvant être rencontré, ne pas hésiter de mettre quelques incrémentations supplémentaires

et pour finir en I17, cette formule pour compter les données des formules matricielles une seule fois

=SOMMEPROD(1/NB.SI(R17:AF17;R17:AF17))-1

modifier cette formule pour I18, puis I19 etc ...

A toi de jouer

A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Je t'ai donné beaucoup de mal, j'en suis confus ..un grand merci à toi.
Je vais tester tout ça, mais tes explications me paraissent claires.
Après, il suffit de pondre les formules une fois, le reste se fait tout seul, donc ça devrait aller.
Après j'évaluerai la pertinence des résultats.
Je te fais un reply demain.
Encore merci

Bonne soirée à toi.
Messages postés
17505
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
31 août 2021
4 704
Re,

tu peux tester chaque partie de la formule, exemple surbrille cette partie LIGNES($1:1)+COLONNE()-18 et clic sur la touche de fonction F9 le résultat est bien [1]puis Echap pour sortir

cette partie NB.SI(Nom;$H$17) te donnera [6]

cette partie INDEX(Client;PETITE.VALEUR(SI(Nom=$H$17;LIGNE(INDIRECT("1:"&LIGNES(Nom))));LIGNES($1:1)+COLONNE()-18)) te donne le premier client Les Bonzami

si tu change le nombre de ligne ou colonne soit LIGNES($1:2)+COLONNE()-18 ou

LIGNES($1:1)+COLONNE()-17

INDEX(Client;PETITE.VALEUR(SI(Nom=$H$17;LIGNE(INDIRECT("1:"&LIGNES(Nom))));LIGNES($1:1)+COLONNE()-18)) te donnera le deuxième client etc ...

n'oublie pas pour sortir du test touche Echap
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
OK.
J'étais en train justement.
Mais je suis pas rapide et je fais les tests pas à pas.
Quel niveau !
Par contre je dois l'adapter à un tableau contenant 30 commerciaux et 900 clients, alors c'est chaud ...
A plus
Messages postés
10320
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
31 août 2021
1 184
Bonjour,
Eh bien il serait encore temps de mettre un fichier exemple qui aie la même structure que l'original sans quoi tout le monde va se retrouver à Pâques... !


Salutations.
Le Pingou
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
24161
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
13 août 2021
6 873
Bonjour à tous,

Une proposition avec 2 colonnes intermédiaires, et avec formules pas gourmandes.
https://www.cjoint.com/c/EAyxxdtw5sI

eric

En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Bonjour Eric

Merci pour cette proposition.
J'étais en train de faire une méthode basée sur l'utilisation de "&" et la proportionnalité par rapport aux nombres de clients, comme toi ...
Par contre il y a une erreur Colonne E : les lignes vierges comptent pour 0,2, ce qui fait une somme des commerciaux à 8 au lieu de 7.

Bravo pour l'idée, pardon aux méninges.
Messages postés
24161
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
13 août 2021
6 873
Bonjour,

Je ne me suis attaché qu'au décompte des couples commercial-client et ça n'y a aucune influence.
Tu peux ajouter un test pour y mettre 0 mais ça ne t'aidera pas à compter les commerciaux que tu comptes déjà très bien en I9.
A moins que je n'ai pas compris...
J'avais laissé des cellules avec des essais, elles ne servent à rien. Le fichier nettoyé : https://www.cjoint.com/c/EAzleFW6K4a

eric
Messages postés
10320
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
31 août 2021
1 184
Bonsoir,
Pour terminer la soirée, voir la feuille [Feuil2] : https://www.cjoint.com/?3AyxBKryokW

1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
24161
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
13 août 2021
6 873
Salut le pingou,

regarde ma proposition.
Retour aux fondamentaux après être parti dans tous les sens aussi ;-)
eric
Messages postés
10320
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
31 août 2021
1 184
Salut eriiic,
Je l'ai vu mais après que j'ai posté mon message.
C'est une super solution, bravo.
Il me semble que tu as déjà posté une solution similaire dans le courant de 2014 ....!
Mes amitiés.
Le Pingou
Messages postés
26146
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
31 août 2021
6 032
Bonjour
avant de trouver une formule qu'on va continuer à chercher, vous pouvez créer une colonne qui concatène les nom du commercial et le nom du client et appliquer la formule sur ce champ.
crdlmnt
Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Bonjour Vaucluse et Mike

Désolé pour le retard de réponse, mais le boulot ne m'a pas lâché aujourd'hui.

J'ai testé la formule de Mike, il m'a fallu un peu de temps pour la vérifier en raison de la complexité du tableau.

Même si la formule de Mike semble logique (mais je ne comprends par pourquoi "ARRONDI"), elle me surestime mes valeurs d'environ 15%.
Je suis en train de fouiller le problème : il peut s'agir de beaucoup de choses : erreurs dans le format, communauté de clients entre commerciaux, autre ...
Si cela ne vous dérange pas, je veux bien que vous m'expliquiez pourquoi vous utilisez la fonction ARRONDI (le reste j'ai compris).

Demain je vais essayer en concaténant au moyen d'une esperluette, pour voir si les chiffres concordent, comme conseillé par Vaucluse.

C'est vraiment un casse-tête ce truc, j'y ai déjà passé plusieurs heures avant d'ouvrir un sujet.

En tout cas merci pour la réflexion, je vous tiens au courant.

Pour info j'ai consulté un tas de sujets dans pas mal de forums : cette question n'a jamais vraiment été résolue, et elle ne concernant qu'un simple filtrage ...

Bonne soirée
Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Bonsoir Mike

Dans le même fichier poussé jusqu'à la ligne 40, avec insertion volontaire de lignes vides, les calculs semblent exacts.
Je vois que tu les obtiens par une formule qui va taper dans des colonnes cachées qui, une fois révélées, contiennent :
=SI(LIGNES($1:1)+COLONNE()-18<=NB.SI(Nom;$H$17);INDEX(Client;PETITE.VALEUR(SI(Nom=$H$17;LIGNE(INDIRECT("1:"&LIGNES(Nom))));LIGNES($1:1)+COLONNE()-18));"")


Mais franchement je n'ai pas compris grand chose à la méthode ...
Et du coup je n'ai pas été capable de l'appliquer à mon fichier.

Je joins le fichier corrigé :
https://www.cjoint.com/c/EAytJOnz3aI

Merci pour cette aide, je sais c'est une demande compliquée, accrochons-nous !

Bonne soirée à tous
Messages postés
10320
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
31 août 2021
1 184
Bonjour,
Avec filtre avancé et TCD : https://www.cjoint.com/?3Aywn7iONO9

Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Bonsoir le Pingou

Merci bien pour cette solution, je vais essayer aussi.
Juste une question : pas bête l'idée de l'extraction sans doublon, qui peut être réalisée dans une colonne cachée, mais peut-on se passer d'un TCD ? Puisque les doublons sont éliminés par cette méthode ...

J'en bave bien avec la solution de Mike, qui est rigoureuse, mais qui m'oblige à étendre beaucoup les sous-colonnes de calculs (je n'ai pas fini).

A plus
Messages postés
10320
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
31 août 2021
1 184
Bonjour,
Mais oui, sans TCD pour le résultat vous pouvez le rechercher avec formule.
Cependant pour quoi compliqué si l'on peut faire simple ou bien ce n'est qu'une question de présentation du résultat un peu plus artistique... !
Au passage, mes salutations amicales à Mike-31 et Vaucluse.

Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Hummmm Dsl Le Pingou.
Je mouline déjà :
En fait, en réalité, entre les colonnes Commercial et Client il y a une autre colonne : du coup dans l'extraction sans doublons, j'obtiens un message d'erreur de Réf non valide, y compris quand je sélectionne seulement mes 2 colonnes avec Ctrl.
Faut-il utiliser la zone de critères ?

Merci d'avance pour l'aide
Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Bonsoir à tous

Le problème est résolu.

En résumé, pour ceux qui passeraient par ici :
Pour effectuer un calcul (sans macro) qui filtre 2 colonnes en éliminant les doublons dans une des 2 colonnes concernées, il faut :
- créer une première colonne cachée (ex : M) qui concatène les 2 colonnes concernées par le calcul : ex:
=E&G
: formule à étirer sur toutes les lignes du tableau.
- créer une deuxième colonne cachée (ex : N) contenant cette formule par exemple :
=SI(M3="";"0";1/NB.SI($M$3:$M$10000;M3))
: cette formule distribue pour chaque valeur d'une colonne la répartition parmi les valeurs de l'autre colonne contenant des doublons (ex : 2 si 1 doublon, 0,33333 si 3 doublons)
- Nommer les 2 colonnes cachées :
ex : colonne M : Gestionnaire de noms -> "concat"
colonne N : Gestionnaire de noms -> "répartition"
Dans la colonne où vous souhaitez voir apparaître le résultat :
=SOMME.SI(concat;[critère];répartition)
où [critère] représente la valeur à filtrer.

Je remercie vivement tous ceux qui ont participé à résoudre ce problème difficile !

Bonne soirée
Messages postés
24161
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
13 août 2021
6 873
Bonjour,

0 (numérique), pas "0" (texte), ça fait mieux ;-)
eric
Messages postés
589
Date d'inscription
mardi 7 avril 2015
Statut
Membre
Dernière intervention
23 juillet 2015
54
salut, c'est 0.5 si 1 doublons, 0.333 si 2 doublons etc.

Qu'appelle tu critère ? Par rapport à ton fichier, quel est ta valeur à filtrer ?

Cordialement
Messages postés
394
Date d'inscription
mardi 5 octobre 2010
Statut
Membre
Dernière intervention
21 novembre 2020
12
Salut à tous

Avec du recul et quelques semaines d'utilisation :
La solution proposée par Eric dans son post #25 janv. 2015 à 10:53 fonctionne impeccablement, aucune erreur.
Franchement c'était pas facile à trouver ...

Merci pour tout