Répartition équitable

ditlaime · 25 Octobre 2015

Bonjour à tous,

je sollicite votre aide car je cherche à répartir une liste de montants en 10 colonnes, le tout de manière équitable en nombre de lignes ainsi qu'en montant total. De plus, la liste à répartir a une colonne correspondante avec le numéro de client

J'ai cherché un peu partout et j'ai presque trouvé ce qui peut m'aider, à une nuance près: le fichier proposé permet de répartir une liste de nombres en 2 colonnes équitables seulement. J'ai bien pensé utiliser la répartition en cascade (répartir en 2 puis encore en 2, etc) mais malheureusement une fois mon fichier coupé en 2 listes à répartir en 5 colonnes, je ne peux plus avancer (car je me retrouverai avec 2 listes pour 2 personnes et demi...)

La discussion sur laquelle j'ai trouvé la solution est ici : https://www.excel-downloads.com/threads/repartition-equitable.231105/

Je vous mets le fichier original en PJ et une version modifiée (mais non fonctionnelle) qui précise ce que je souhaite atteindre comme résultat. Le nombre de chiffres dans la liste peut être amené à varier, ainsi que le nombre de colonnes

Merci d'avance pour votre aide et si je ne suis pas clair, n'hésitez pas à me demander des précisions

Bonne soirée

Nicolas

CISCO · 25 Octobre 2015

Re : Répartition équitable

Bonsoir

Il y a certainement d'autres fils traitant de problèmes très proches du tien, fils que tu peux trouver en cherchant avec les mots clefs répartition équitable. Regarde là par exemple :
https://www.excel-downloads.com/threads/repartition-equitable.157308/

@ plus

ditlaime · 25 Octobre 2015

Re : Répartition équitable

Bonsoir

merci de la réponse

J'ai déjà consulté ce fil (comme tous les autres avec le même titre), mais la solution proposée n'est pas aussi efficace que celle du fil que je cite (il faut changer chaque formule au lieu d'une macro)

D’où ma recherche ici de quelqu'un qui pourrait peut-être m'aider à changer la matrice pour diviser les données en 10 et non pas en 2

Bonne soirée

CISCO · 27 Octobre 2015

Re : Répartition équitable

Bonjour

Comme je ne sais pas faire avec une macro, j'ai repris la méthode proposée dans le fil que j'ai cité dans mon précédent post. Cela donne le fichier "fichiersomme10colonnesbis".

Comme le résultat obtenu n'est pas super, j'ai essayé avec une méthode plus basique, à savoir répartir au hasard des 0 et des 1 dans 10 colonnes, en imposant deux conditions (Ne mettre qu'un 1 par ligne, mettre 0 si la somme depuis le début de la colonne est supérieure à la valeur désirée en C2 = total/10). Cf. le fichier "fichiersomme10colonnessuite".

Le résultat obtenu n'est pas forcément super, mais en faisant plusieurs tirages en appuyant sur F9, on finit par avoir des résultats intéressants. Pour visualiser facilement ces derniers, en G2, j'impose un écart max en % entre la valeur désirée (C2) et le total obtenu colonne par colonne (D5, E5...). Une MFC met en vert l'écart type en C3 lorsque tous les écarts (D4, E4...) sont inférieurs à cet écart max (G2). Il ne reste plus qu'à copier et à coller les valeurs du tableau (cf. les tableaux obtenus et mis à droite). Bien sûr, plus l'écart max G2 est petit, plus il faut faire de nombreux essais.

@ plus

P.S : Deux remarques :
* Le tableau de gauche du second fichier contient deux types de formules, l'un, en orange, utilisant les données sur sa ligne à sa gauche et dans sa colonne au dessus, l'autre, en noir, les données sur sa ligne à sa droite et dans sa colonne au dessus. J'ai fait cela pour essayer d'"équilibrer" ce tableau, les colonnes de droite étant "défavorisées" par la formule en orange (le 1 est souvent choisi dans les colonnes de gauche avec la formule orange, et donc, dans ce cas, des 0 imposés dans les colonnes de droite).
* Il y a des 0 en blanc en E6, D6...

mapomme · 27 Octobre 2015

Re : Répartition équitable

Bonsoir ditlaime, CISCO ,

Une méthode perso et totalement empirique (la vitesse a été privilégiée - pas d'utilisation de génération aléatoire - un nombre réduit de combinaisons essayées) qui donne un résultat d'autant plus honnête que, me semble-t-il

:

le nombre de valeurs initiales est grand
les valeurs sont à peu près équitablement réparties
dans le tableau final, le nombre de colonnes reste relativement petit par rapport au nombre de lignes

le choix du nombre de colonnes (de 1 à 16) se fait dans la cellule D3
la macro se lance quand on modifie le nombre de colonnes
on peut aussi lancer la macro en cliquant sur le bouton "GO..."
la cellule D13 est utilisée par la macro, ne pas la modifier
idem pour les cellules F2 à AK2
les cellules de D11 à D44 peuvent être effacées, elles ne sont présente qu'à titre d'information

NB: la méthode utilisée implique que le nombre de clients soit un multiple du nombre de colonnes de ventilation. La macro peut donc rajouter des clients fictifs tous nommés XXX et à montant nul. Ces clients se retrouvent dans le tableau final.

Rem: je suis en train penser que si les conditions que j'ai citées sont toutes vérifiées, toute distribution aléatoire devrait être une assez bonne solution ! Et donc que la seconde partie de ma macro n'est pas loin d'être inutile! (pas tout à fait malgré tout car elle améliore quand même la première ventilation)

CISCO · 27 Octobre 2015

Re : Répartition équitable

Bonsoir à tous, bonsoir mapomme

C'est sûr, il ni a pas photo... Le fichier colle exactement à la demande de ditlaime, il me semble, ça tourne assez vite... Reste plus qu'à voir sa réaction...

@ plus

ROGER2327 · 28 Octobre 2015

Re : Répartition équitable

Bonsoir à tous.

À mapomme :

Joli !

Suggestion : remplacer

Code:

  For i = 1 To nbrBloc
    For j = 1 To nbrCol
      Tablo2(i, 2 * j - 1) = Tablo1((i - 1) * nbrCol + j, 1)
      Tablo2(i, 2 * j - 0) = Tablo1((i - 1) * nbrCol + j, 2)
    Next j
  Next i

par

VB:

  For j = 1 To nbrCol
    For i = 1 To nbrBloc Step 2
      Tablo2(i, 2 * j - 1) = Tablo1((i - 1) * nbrCol + j, 1)
      Tablo2(i, 2 * j) = Tablo1((i - 1) * nbrCol + j, 2)
    Next i
    For i = 2 To nbrBloc Step 2
      Tablo2(i, 2 * j - 1) = Tablo1(i * nbrCol - j + 1, 1)
      Tablo2(i, 2 * j) = Tablo1(i * nbrCol - j + 1, 2)
    Next i
  Next j

Bonne nuit !

ℝOGER2327
#8073

Dimanche 22 Haha 143 (Résurrection de Bosse-de-Nage - fête Suprême Seconde)
5 Brumaire An CCXXIV, 9,8065h - oie
2015-W44-2T23:32:08Z

CISCO · 28 Octobre 2015

Re : Répartition équitable

Bonjour à tous, bonjour mapomme, Roger2327

@ mapomme : Pourrais tu m'expliquer la méthode mathématique que tu as utilisée pour organiser cette répartition, STP ?

@ plus

Modeste geedee · 28 Octobre 2015

Re : Répartition équitable

Bonsour®
bien vu mapomme !!

Une méthode perso et totalement empirique

- 1 : distribution séquentielle successive par colonne de la liste des valeurs triées

- 2 : on fait une rotation de chaque ligne pour voir si l'écart moyen diminue (somme par colonne)

j'avais envisagé cette méthode mais avec rotation aléatoire mais sans tri préalable des valeurs (beaucoup plus lente et pas vraiment probante)

(la vitesse a été privilégiée - pas d'utilisation de génération aléatoire )

au-delà de 3 colonnes une rotation alternée apporte une très légère amélioration...

Répartition équitable

ditlaime

XLDnaute Nouveau

Pièces jointes

CISCO

XLDnaute Barbatruc

ditlaime

XLDnaute Nouveau

CISCO

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

CISCO

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

CISCO

XLDnaute Barbatruc

Modeste geedee

XLDnaute Barbatruc

Discussions similaires