Comptage sans doublons sur double filtre Résolu/Fermé

Question

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 ! Configuration: Windows 7 / Excel 2010 / Firefox 35.0

Vaucluse · Answer

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

touroul · Answer

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

Le Pingou · Answer

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]

Mike-31 · Answer

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

touroul · Answer

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

Mike-31 · Answer

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.

Mike-31 · Answer

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

Le Pingou · Answer

Bonjour,
Avec filtre avancé et TCD : https://www.cjoint.com/?3Aywn7iONO9

Le Pingou · Answer

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.

Le Pingou · Answer

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

eriiic · Answer

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

Le Pingou · Answer

Bonsoir,
Pour terminer la soirée, voir la feuille [Feuil2] : https://www.cjoint.com/?3AyxBKryokW

touroul · Answer

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

touroul · Answer

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

Comptage sans doublons sur double filtre

14 réponses

Discussions similaires