somme sans doublons avec conditions

[peg63]

j'ai un tableau de 4 colonnes.
colonne1: vendeurs avec doublons (le même nom peut se répéter plusieurs fois;colonne 2 :chiffre d'affaire par vendeur;colonne 3:région de vente; colonne 4 tranche age
dans une cellule je voudrais avoir une formule qui me donne le nom du meilleur vendeur dans la tranche d'age et région que j'aurai choisies
je pense que c'est une formule matricielle avec index et equiv et grande valeur mais je n'y arrive pas
je ne veux pas utiliser de TCD ou filtre élaboré ni vba
merci de votre réponse

 

[peg63]

Re : somme sans doublons avec conditions

le pari est lancé celui qui trouve sera un crack!
astiquez les meninges!!!
bonne nuit les petits (je vous rassure je suis à la peine aussi)
merci à tous pour votre aide

 

[CISCO]

Re : somme sans doublons avec conditions

Bonjour à tous

Peg63, au passage, combien as tu de lignes dans ton fichier réel ?

@ plus

 

[Monique]

Re : somme sans doublons avec conditions

Bonjour,

Voir si ça marche à tous les coups.

 

[CISCO]

Re : somme sans doublons avec conditions

Bonsoir

Bravo, Monique

Et comme d'habitude, après coup, cela parait tellement logique.

En fait, la formule que tu proposes, c'est :

INDEX(liste des noms; EQUIV(MAX (tests pour trouver la liste des totaux du ca par nom répondant aux conditions région et âge);tests pour trouver la liste des totaux du ca par nom répondant aux conditions région et âge;0)))

et qui fonctionne comme suit :

liste des totaux des ca par individu : {7;7;11;4;6...}
tests trouve la liste des totaux des ca par individu répondant aux conditions région et âge : {7;7;11;0;0....}
MAX recherche le max de la liste des totaux du ca par nom répondant..., ex 11
EQUIV recherche et renvoie la position de ce max dans cette liste, ex 3
INDEX renvoie le 3ème nom de la liste des noms, ex franck.

J'avais trouvé la liste des totaux du ca par nom répondant aux conditions région et âge, mais je n'avais pas pensé à cette double recherche, celle du max, puis celle de la position de ce max, pour ensuite appliquer le résultat à la liste des noms.

@ plus

 

[peg63]

Re : somme sans doublons avec conditions

félicitation Monique!!!!,ça marche; un grand merci aussi à cisco pour son aide sans oublier les autres....
j'espère vous retrouver bientôt sur ce forum d'excel;vous êtes des bons
portez vous bien
a+
peg63

 

[peg63]

Re : somme sans doublons avec conditions

j'ai parlé trop vite ,vous êtes toujours des bons mais dans le tableau transmis par monique quand les valeurs des deux premiers sont trés proches à 2 ou 3 points du ca le premier qui est sur la liste verif (elle qui est toujours juste) n'est pas premier sur la cellule résultat,on s'en aperçoit en changeant les ca dans le tableau de données.
alors si vous avez encore un peu de courage......
à demain

 

[CISCO]

Re : somme sans doublons avec conditions

Bonjour

Et m....

J'avais cru que la formule proposée par Monique donnait la bonne liste des totaux de ca.

J'ai l'impression que la partie SOMME.SI(Nom;Nom;Ca) pose problème car elle renvoie la liste des totaux par nom, donc le total du ca pour alain, puis celui de franck..., mais elle ne fait pas la différence entre danielEST et danielNORD par exemple.

Avec les valeurs de mon précédent fichier bisbis, avec SOMME.SI(Nom;Nom;Ca) on obtient {7;7;10;10;1;5;10;6;6;7...} alors qu'il faut obtenir {7;7;2;8;1;5;8;1;5;7....} (2+8 =10 et 1+5 = 6). Pas de problème pour alain (7), mais un problème pour franck a qui une méthode attribue un ca de 10, alors qu'en fait c'est 2 pour franckNORD et 8 pour franckEST...

Bien sûr, les tests suivants SI(Region=F$3;SI(Age=F$4 éliminent les valeurs ne correspondant pas aux critères F$3 et F$4, les valeurs conservées sont au bon endroit dans la liste, mais, malheureusement, ne sont pas les bonnes.

Avec EST et jeune, on obtient finalement avec les tests et SOMME.SI(Nom;Nom;Ca) la liste {7;7;FAUX;FAUX;FAUX;FAUX;FAUX;6;7;....} alors qu'on devrait avoir {7;7;FAUX;FAUX;FAUX;FAUX;FAUX;5;7;....}. Cela ne pose pas de problème pour EST et jeune, puisque ce qui nous intéresse ici, c'est le max, 7 dans les deux cas.

Par contre, pour NORD et jeune, cela pose un problème, puisqu'avec les tests et SOMME.SI(nom;nom;ca) on obtient {FAUX;FAUX;FAUX;FAUX;5;FAUX;6;FAUX;FAUX...} alors qu'on aurait dû avoir {FAUX;FAUX;FAUX;FAUX;FAUX;5;FAUX;1;FAUX;FAUX....}. On obtient avec SOMME.SI un max égale à 6, donc daniel, alors qu'on devrait avoir 5, donc hélène.

Bien sûr, ce problème n'existe que si des noms (ou des personnes ayant le même nom) travaillent sur plusieurs régions. Même remarque par rapport à l'âge.

En fait, ce qui serait bien, c'est une formule du style :
SOMME.SI(nom&region&age=nom&region&age;ca;0))
mais cela ne passe pas.

En conclusion, Peg63, deux questions :
1) Peux tu avoir des situations avec des danielEST, danielNORD... ou des julieESTjeune et julieESTsenior?

2) Pour voir si le problème que tu nous signales vient aussi de SOMME.SI(nom;nom;ca), ne pourrais tu mettres un exemple, construit avec juste quelques lignes, comme mon premier fichier, en pièce jointe ?

@ plus

 

[peg63]

Re : somme sans doublons avec conditions

salut cisco,
pour répondre à ta demande
1) je peux effectivement avoir toutes les situations
2) le tableau de base de monique suffit à lui même il est vraiment adapté a ce que je cherche
j'ai trouvé cette formule
=INDEX(nom;EQUIV(MAX(SOMME.SI(nom;nom;ca));SOMME.SI(nom;nom;ca);0)) d'un collègue sur ce forum elle ne marche pas dés qu'il y a une condition sinon je pensais, s'il était possible d' inclure dans la formule, le tri conditionnel sur un index supplémentaire pour que l'index de la formule ne retienne que les lignes sélectionées
je joins le fichier
a+

 

[CISCO]

Re : somme sans doublons avec conditions

Bonsoir à tous,

Je répète ma question : STP, peg63, combien as tu de lignes, environ, dans ton vrai fichier ?

@ plus

 

[peg63]

Re : somme sans doublons avec conditions

cisco,
j'ai 200 lignes dans mon fichier
a+

 

[CISCO]

Re : somme sans doublons avec conditions

Bonsoir

Personne pour proposer une idée...

J'ai trouvé une solution.. à rallonge, en répétant presque la même formule autant de fois qu'il y a de ligne. Bien sûr, elle peut paraitre très simple en utilisant des fonctions nommées, mais, ce n'est qu'une apparence...

@ plus

PS : Peg63, tu as aussi une solution très simple : Utiliser ma seconde ou ma troisième proposition, et masquer la colonne intermédiaire.😉

@ plus

 

[CISCO]

Re : somme sans doublons avec conditions

Bonjour à tous

Encore deux questions, Peg63 :
Combien il y a t'il de vendeurs ?
Quelle version d'excel utilises tu ?

@ plus

 

[CISCO]

Re : somme sans doublons avec conditions

Rebonjour à tous

En attendant de trouver mieux, deux possibilités en pièces jointes, sans colonne intermédiaire, construites sur le même principe :

Dresser la liste des noms différents (alain, daniel...)
Prendre le premier
Faire la somme des ca correspondant à ce nom, à la région choisie et à l'âge choisi.
Prendre le second nom
Faire la somme des ca correspondant à ce nom, à la région choisie et à l'âge choisi.
....
Prendre le 10ème nom
Faire la somme des ca correspondant à ce nom, à la région choisie et à l'âge choisi.

Prendre le max de ces sommes.

Ce qui fait qu'on écrit 10 fois presque la même formule.

Pour le moment, cela ne fonctionne :
* que si on regroupe les noms identiques les uns après les autres, ce qui peut facilement être fait avec le filtre.
* que si il y a au maximum 10 noms différents.

Cela fonctionne aussi sans regrouper les noms identiques, mais dans ce cas, avec moins de 10 noms différents.

Bien sûr, il y a des modifications à faire en fonction du nombre de lignes du fichier réel...

Avec cette méthode, on trouve bien les max par région et âge, mais pour ce qui est de trouver le nom correspondant à ce max...

@ plus

 

[CISCO]

Re : somme sans doublons avec conditions

Bonsoir

Une autre possibilité, lourde, très lourde, car il faut faire le test sur toutes les cellules de la plage A3:A13.

MAX(SOMME(($A$4:$A$13&$C$4:$C$13&$D$4:$D$13=$A$[COLOR="Red"][B]4[/B][/COLOR]&F3&$E3)*$B$4:$B$13);
SOMME(($A$4:$A$13&$C$4:$C$13&$D$4:$D$13=$A$[COLOR="red"][B]5[/B][/COLOR]&F3&$E3)*$B$4:$B$13);
SOMME(($A$4:$A$13&$C$4:$C$13&$D$4:$D$13=$A$[COLOR="red"][B]6[/B][/COLOR]&F3&$E3)*$B$4:$B$13);.....

jusqu'à A13


Si quelqu'un trouve une astuce pour transformer cette formule en quelque chose de plus simple... il est le bienvenu.

Bon début de W.E.

@ plus

 

[Monique]

Re : somme sans doublons avec conditions

Bonjour,

Avec 1 tableau intermédiaire
1er tableau : tous les noms selon critères région et âge, dans l'ordre de la liste
2ème tableau : le meilleur vendeur (avec possibilité d'ex aequo)

Appuyer sur F9 pour changer les noms, les régions, les âges et le CA