XL 2019 Répartition d'une liste de montant en nombre et part égal

chac10 · 28 Février 2021

Bonjour à tous,

Je cherche désespérément un code VBA qui permet de distribuer les montants d'une liste (dossiers de clients) entre plusieurs collaborateurs
De manière à ce que chacun d'entre eux ait un nombre de dossier et de somme identique ou du moins très proche.
Ceci est utile notamment pour les commerciaux.

C'est un travail qui revient trop souvent et qui nous fait perdre un temps précieux !!
Si quelqu'un peut m'aider , par un vba ou autre méthode efficace !!
Merci par avance

chac 10

mapomme · 28 Février 2021

Bonjour chac10, bienvenue sur XLD ,

A priori rien n’indique qu'on puisse le faire. Tout dépend du nombre de dossier, du profil des montants et du nombre de vendeurs.

Un cas pour montrer que ce n'est pas forcément possible : 4 dossiers de montant 2000, 200, 150, 100 à répartir sur trois vendeurs.

Donc joindre un fichier anonymisé, qui reflète le nombre de dossiers à répartir, le profil des montants et le nombre moyen de vendeur.

Après on pourra travailler et fournir sans doute quelques méthodes aboutissant à un "bon résultat" sans que ce soit le meilleur. C'est en général tout ce qu'on peut faire.

J'ai déjà vu pas mal de discussion à ce sujet sur XLD. Il suffit peut-être d'utiliser l'outil de recherche de XLD pour trouver.

job75 · 28 Février 2021

Bonjour chac10, mapomme, le forum,

Il y a toujours une solution.

Elle consiste à déterminer la répartition qui minimise l'écart-type par rapport à la moyenne.

Cela dit pour la trouver c'est loin d'être évident...

A+

mapomme · 28 Février 2021

job75 à dit:
Il y a toujours une solution.

Bonjour @job75 ,

Je voulais aussi dire une solution avec un temps de traitement "raisonnable".
Et ça, comme tu le dis, peut se révéler très coton.

La solution qui minimise l'écart-type existe mais n'est pas forcément ce que recherche le demandeur : il désire un nombre équivalent de dossiers pour chaque vendeur avec un montant à peu près équivalent. Si les dossiers sont assez nombreux (par rapport au nombre de vendeurs) et si les montants des dossiers sont à peu près uniformément répartis alors oui une ou plusieurs solutions existent pour répondre à la demande.
C'est pour ça que j'ai cité mon exemple simpliste.

job75 · 28 Février 2021

On se servira du nombre de dossiers pour diminuer le nombre de répartitions à étudier.

Par exemple avec 100 dossiers et 5 vendeurs on étudiera celles où les vendeurs ont 18 19 20 21 22 dossiers.

chac10 · 3 Mars 2021

Bonjour à tous,

Vous êtes excellent, je vais y réfléchir.

Chac10

chac10 · 6 Mars 2021

Bonjour,

j'ai essayé de répondre

mapomme à dit:
Bonjour chac10, bienvenue sur XLD ,

A priori rien n’indique qu'on puisse le faire. Tout dépend du nombre de dossier, du profil des montants et du nombre de vendeurs.

Un cas pour montrer que ce n'est pas forcément possible : 4 dossiers de montant 2000, 200, 150, 100 à répartir sur trois vendeurs.

Donc joindre un fichier anonymisé, qui reflète le nombre de dossiers à répartir, le profil des montants et le nombre moyen de vendeur.

Après on pourra travailler et fournir sans doute quelques méthodes aboutissant à un "bon résultat" sans que ce soit le meilleur. C'est en général tout ce qu'on peut faire.

J'ai déjà vu pas mal de discussion à ce sujet sur XLD. Il suffit peut-être d'utiliser l'outil de recherche de XLD pour trouvBon

chac10 · 6 Mars 2021

Bonjour mapomme

J'ai utilisé une méthode pas trop longue, qui consiste à affecter un rang a la liste des sommes à répartir selon le nombre de personnes souhaites. Tout d'abord, je tri ma liste du plus grand au plus petite ensuite , si j'ai 3 personnes , j'affecte 1 au premier montant , 2 au second et 3 au troisième. Pour équilibrer les montants finaux, j'inverse le rang pour les montants qui suit donc de 3 à 1. Et je redémarre sur le sens inversé de nouveau. ca me donne un rang comme suit 1 2 3 3 2 1 1 2 3 3 2 1 etc jusqu'à la fin.

Ainsi, chaque personne se retrouve en première et dernière position jusqu' a la fin de ma liste de montant.
Le résultat final est intéressant, car le nombre est respecté, le montant doit être réajuste quelque peu en changeant quelques montants.

Cette solution est intéressante mais j'aimerais savoir si vous en avez une meilleure et sinon si vous savez comment automatiser celle ci ?

Merci beaucoup;

Ma fraise lol

mapomme à dit:
Bonjour chac10, bienvenue sur XLD ,

A priori rien n’indique qu'on puisse le faire. Tout dépend du nombre de dossier, du profil des montants et du nombre de vendeurs.

Un cas pour montrer que ce n'est pas forcément possible : 4 dossiers de montant 2000, 200, 150, 100 à répartir sur trois vendeurs.

Donc joindre un fichier anonymisé, qui reflète le nombre de dossiers à répartir, le profil des montants et le nombre moyen de vendeur.

Après on pourra travailler et fournir sans doute quelques méthodes aboutissant à un "bon résultat" sans que ce soit le meilleur. C'est en général tout ce qu'on peut faire.

J'ai déjà vu pas mal de discussion à ce sujet sur XLD. Il suffit peut-être d'utiliser l'outil de recherche de XLD

mapomme · 6 Mars 2021

Bonjour @chac10 ,

Je vous avais oublié. Dim 28 fév 2021, j'avais fait un fichier avec exactement la méthode que vous avez décrite (avec en plus un petit peu de hasard à la fin pour tenter d'approcher un peu plus une bonne répartition) puis...
j'ai oublié de publier le fichier

.

Bon le voilà. Il faut savoir que cette méthode peut à peu près fonctionner mais pas dans tous les cas.
Comme, il y a un peu de hasard, on peut refaire plusieurs fois la ventilation pour voir si on améliore l'écart type.

Une constante (Nfois) au début du code indique combien de fois, on fait appel à un peu de hasard.
Si vous mettez Nfois à zéro, alors on retrouve votre méthode sans appel à un peu de hasard.
On peut suivre les tentatives dans la barre de statut (par tranche de 1000).

nota : comme le fichier avait tardé à venir , j'ai utiliser "un mien" mais qui ressemble au votre.

Errata : je viens de m'apercevoir que ce n'est pas le bon fichier. Je recherche le bon et reviens le joindre.
nota : pas retrouvé le bon fichier, je le reconstruis...
Rem : C'est fait !

mapomme · 6 Mars 2021

Bonjour à tous,

Une ventilation qui aboutit à un bien meilleur résultat que ma précédente version et qui de surcroit est plus rapide. On agit sur le nombre de dossiers par vendeurs. Le nombre de dossiers autorisés par vendeur varie dans un intervalle modifiable par une constante en début de code. Le nombre de tentatives d'optimisation est ajustable via une constante en début de code.

Le code est dans le module de la Feuille "Feuil1". Il est commenté.

Cliquez sur le bouton Hop!

nota : pour retrouver la méthode suggérée par @chac10, il faut donner à la constante Nfois la valeur 1 ou donner à la constante EcartNbrDossier la valeur 0.

rem : ce n'est pas parce qu'on prend un large éventail de nombre de dossier possible que le résultat utilise tout l’éventail du nombre de dossier possible (constante EcartNbrDossier) - On peut donc essayer avec un grand EcartNbrDossier (par exemple 9) et voir ce que ça donne.

edit: 6 mars 2121 17h07 : modifié fichier joint

chac10 · 7 Mars 2021

B

mapomme à dit:
Bonjour @chac10 ,

Je vous avais oublié. Dim 28 fév 2021, j'avais fait un fichier avec exactement la méthode que vous avez décrite (avec en plus un petit peu de hasard à la fin pour tenter d'approcher un peu plus une bonne répartition) puis...
j'ai oublié de publier le fichier .

Bon le voilà. Il faut savoir que cette méthode peut à peu près fonctionner mais pas dans tous les cas.
Comme, il y a un peu de hasard, on peut refaire plusieurs fois la ventilation pour voir si on améliore l'écart type.

Une constante (Nfois) au début du code indique combien de fois, on fait appel à un peu de hasard.
Si vous mettez Nfois à zéro, alors on retrouve votre méthode sans appel à un peu de hasard.
On peut suivre les tentatives dans la barre de statut (par tranche de 1000).

nota : comme le fichier avait tardé à venir , j'ai utiliser "un mien" mais qui ressemble au votre.

Errata : je viens de m'apercevoir que ce n'est pas le bon fichier. Je recherche le bon et reviens le joindre.
nota : pas retrouvé le bon fichier, je le reconstruis...
Rem : C'est fait

Bonjour Mapomme !!!

Je vous adore !!! C'est top, vraiment je vous remercie beaucoup.
C'est magique , je vais regarder avec attention votre code vba.

Je pense maintenant à générer les liste automatiquement dans des onglets différents pour chaque personne, je vais essayer de le faire mais comme je suis débutant, c'est vrai que votre aide me serait d'une grande utilité si vous le pouvez. Sinon , je ne vous en voudrais pas du tout

Vous êtes vraiment au top.

Encore merci mille fois !!!

et bien sur votre oublie est largement pardonné !!!!

chac 10

mapomme · 7 Mars 2021

chac10 à dit:
Je pense maintenant à générer les liste automatiquement dans des onglets différents pour chaque personne

Bonsoir @chac10 ,

Voir le fichier joint.
Après répartition des dossiers, on ventile les dossiers de chaque vendeur dans une feuille au nom de chaque vendeur.

chac10 · 8 Mars 2021

mapomme à dit:
Bonsoir @chac10 ,

Voir le fichier joint.
Après répartition des dossiers, on ventile les dossiers de chaque vendeur dans une feuille au nom de chaque vendeur.

Merci bien ! Et si je souhaite l'adapter a un fichier , cela en revanche ne sera pas possible je pense .
J'aimerais pour voir dire, " je veux repartir la colonne R ( en montant) et écrire le nom des agent en colonne AB . ceci serait utile puisque le fichier est construit de cette façon , mais si ma colonne a repartir change de position ?

Bravo en tous en si peu de temps, je suis bluffé.

mapomme · 8 Mars 2021

chac10 à dit:
J'aimerais pour voir dire, " je veux repartir la colonne R ( en montant) et écrire le nom des agent en colonne AB . ceci serait utile puisque le fichier est construit de cette façon , mais si ma colonne a repartir change de position ?

Si c'est possible (très certainement) mais dans ce cas, j'ai besoin d'un fichier représentatif qui bien sûr sera anonymisé.
On pourra demander à l'utilisateur où sont les données à répartir, utiliser une feuille auxiliaire pour la répartition puis ventiler dans les feuilles vendeurs. Il faudra aussi demander si on veut effacer les précédents dossiers des vendeurs ou bien les ajouter à la fin des dossiers ventilés précédemment.

mapomme · 8 Mars 2021

Re,

J'ai bien peur que votre fichier ne soit pas anonymisé et qu'il comporte des informations de "vrais gens".

Modifiez rapidement votre dernier message en en supprimant le fichier joint car actuellement nous (c'est à dire vous mais aussi le site XLD) sommes en infraction notable avec le RGPD.

D'avance, merci.

XL 2019 Répartition d'une liste de montant en nombre et part égal

chac10

XLDnaute Junior

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

chac10

XLDnaute Junior

chac10

XLDnaute Junior

chac10

XLDnaute Junior

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

chac10

XLDnaute Junior

mapomme

XLDnaute Barbatruc

Pièces jointes

chac10

XLDnaute Junior

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Discussions similaires