Comptage sans doublons sur double filtre
Résolu
touroul
Messages postés
509
Date d'inscription
Statut
Membre
Dernière intervention
-
touroul Messages postés 509 Date d'inscription Statut Membre Dernière intervention -
touroul Messages postés 509 Date d'inscription Statut Membre Dernière intervention -
A voir également:
- Comptage sans doublons sur double filtre
- Double ecran - Guide
- Whatsapp double sim - Guide
- Photo filtre 7 gratuit - Télécharger - Retouche d'image
- Doublons photos - Guide
- Double driver - Télécharger - Pilotes & Matériel
18 réponses
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]
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]
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
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
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.
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.
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.
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.
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
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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
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
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
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
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.
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.
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
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
Bonsoir,
Pour terminer la soirée, voir la feuille [Feuil2] : https://www.cjoint.com/?3AyxBKryokW
Pour terminer la soirée, voir la feuille [Feuil2] : https://www.cjoint.com/?3AyxBKryokW
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
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
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
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
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 :
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
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
Bonjour,
Avec filtre avancé et TCD : https://www.cjoint.com/?3Aywn7iONO9
Avec filtre avancé et TCD : https://www.cjoint.com/?3Aywn7iONO9
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
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
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.
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.
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
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
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:
- créer une deuxième colonne cachée (ex : N) contenant cette formule par exemple :
- 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 :
Je remercie vivement tous ceux qui ont participé à résoudre ce problème difficile !
Bonne soirée
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
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
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
Bonsoir Eric
C'est un vieux sujet, mais je m'en sers toujours ...
Sur le Post du #25 janv. 2015 à 10:53, dans le fichier CJoint, tu me proposes une formule matricielle en I9.
Puis-je te demander pourquoi tu l'as mis en matricielle stp ?
J'obtiens les mêmes résultats sans matricielle.
Merci par avance pour ton aide
Bonjour,
à propos de doublons, au risque que des cuistres t'accusent t'en créer un, tu aurais peut-être dû faire un nouveau fil: un certain nombre d'intervenants ne viennent plus régulièrement et d'autres plus du tout, c'est dommage.
Excel 365 va "matricialiser" automatiquement et la question ne se posera pas.
Sinon, sans formule matricielle, la formule en I9 ne calcule pas la matrice, elle ne rend que 1 divisé par le nombre d’occurrences du premier commercial en tête de liste, dans les cas d'espèce 1/(B5+B6+B8+B9)=0,25 au lieu de 3.
Bonjour à tous,
Puisque la discussion est rouverte, une solution avec excel 365 :
=LET(tbl;PRENDRE(UNIQUE(C5:D22);;-1);GROUPER.PAR(tbl;SEQUENCE(LIGNES(tbl);;1;0);SOMME;;0;;tbl<>0))
Mieux vaut tard que jamais !
Daniel
Bonjour Brucine, Daniel,
Effectivement Eriiic a quitté le navire, il faisait partie des plus actifs.
Merci pour vos réponses, je teste ça en rentrant ce soir.
Bon, j'ai dû "wikiyer" cuistre, j'ai appris un mot !
A plus tard
