Répartition équitable

[gourdin]

Bonjour,

Je souhaite répartir équitablement un groupe de 20 chiffres dans 3 groupes de façon à ce que le total de chaque groupe de chiffres soit le plus proche.

Vor fichier joint pour mieux comprendre

Merci

 

[KenDev]

Re : Répartition équitable

Bonjour Gourdin,

Je n'ai pas vraiment d'idée la sur le moment mais, sauf erreur, je viens de calculer qu'une approche systématique de toutes les répartitions possibles de 20 n° sur 3 colonnes (en considérant que le nombre de n° par colonne est libre) pourrait conduire à examiner jusque 769 820 418 répartitions (dans le cas ou la liste n'a pas de doublons) et a vu de nez laborieux à programmer.

As tu besoin de trouver LA meilleure ?

Sinon j'opterai pour une approche aléatoire :
- définir un critère qualitatif
- choisir une répartition au hasard (quitte à piper un peu les dés de façon à ce que, par exemple, la répartition 6 7 7 soit choisie plus souvent que la 0 0 20).
- distribuer ensuite aléatoirement les 20 n° dans cette répartition
- estimer la valeur selon tes critères de cette répartition, la garder en mémoire
- recommencer et comparer avec la valeur précédente en gardant toujours la meilleure
- laisser tourner...

Cordialement

KD

 

[ROGER2327]

Re : Répartition équitable

Bonjour à tous

Deux procédures à voir dans ce classeur.​

ROGER2327
#4927

Samedi 7 Gueules 138 (Saint Gueule, abbé, SQ)
13 Pluviôse An CCXIX
2011-W05-2T01:18:52Z

 

[KenDev]

Re : Répartition équitable

Bonjour Gourdin, bonjour Roger,

@Roger : Waoh ! Les codes sont impressionnants et je suis en train d'essayer de les comprendre. Je m'interroge sur les critères qui font sortir de la boucle (je n'en suis qu'à toto). Je me suis amusé à mettre dans le tableau un 100000 et laisser les autres, histoire d'être bien sûr que la moyenne ne pourra pas être approchée dans aucun cas de figure. Ca tourne, ça tourne.. Question : y'aurait-il beaucoup de modificatifs à faire pour avoir une réponse dans tous les cas (la meilleure possible) ?

Edit : A oui c'est v...

Je n'arrive pas à comprendre que oDat ait 2 dimension alors qu'il est initialisé sur une colonne (= [B5:B24].Value) ?

Cordialement

KD

 

[gourdin]

Re : Répartition équitable

Bonjour,

En effet les codes sont impressionnnants et tout à fait incompréhensible pour mon niveau. En tout celà fonctionne. Merci beaucoup Roger et bravo.

Sauf que je souhaitais pouvoir adapter le codage proposé dans des situations similaires (répartir dans 4, 5, 6 groupes etc. par exemple) et je serais incapable de modifier le code.

Pour l'instant et pour rester à mon niveau d'excel, voici comment j'ai essayer de répartir :

1. Trier la colonne C du plus grand au plus petit.
2. Mettre des formules simples qui placent le plus grand chiffre en haut dans le groupe A, le suivant en haut dans groupe B, le suivant en haut dans groupe C
etc. en descendant dans les groupes et en repartant plutot par le groupe C pour équilibrer les 3 groupes

Mais cela ne fonctionne pas toujours correctement si les écarts entre chiffres de la colonne C sont trop importants et nécessite donc de rectifier.

PS : ce n'est pas trop grave si ce n'est pas la meilleure solution de répartition qui est proposé (comme le fait si bien le code de Roger) mais elle doit rester assez proche de l'idéal.

Voir fichier joint

Merci

 

[hoerwind]

Re : Répartition équitable

Bonjour, salut les autres,

En pièce jointe une approche par formule.
Si le principe convient, les formules définitives (sans tableau intermédiaire) pourront être mises au point.

 

[gourdin]

Re : Répartition équitable

OK merci
J'ai compris le principe et je devrais pouvoir me charger des formules définitives.

 

[CISCO]

Re : Répartition équitable

Bonjour

Une variante en pièce jointe.

Le principe :
On calcule, colonne par colonne, la valeur moyenne à mettre dans chaque cellule. Dans l'exemple ci-joint, on doit avoir en colonne D des nombres proches de 12,24 = SOMME(B5:B24)/(3*7).

En D11, la formule recherche dans la liste des nombres B5:B24, le nombre le plus proche de 12,24, soit 12.
En D12, la formule recherche dans la liste des nombres encore disponibles (liste initiale sans les nombres en D11, E11 et F11), le nombre x tel que 12 + x soit le plus proche de 2*12,24, soit encore 12.
En D13, la formule...... le nombre y tel que 12 + x + y soit le plus proche de 3 * 12,24.
Et ainsi de suite...


Cette méthode a le défaut de rejeter en bas de colonne les plus grandes valeurs, ce qui déséquilibre totalement les sommes colonne par colonne.

Pour compenser ce défaut, j'ai mis un second tableau où ces valeurs sont imposées dans la première ligne. Ces excès sont compensés automatiquement dans les lignes suivantes. Bien sûr, en l'état, cela ne fonctionne bien que si il y peu de valeurs très loin de la moyenne.

@ plus

 

[CISCO]

Re : Répartition équitable

Rebonjour

Toujours pour le fun, un fichier dans lequel les nombres sont tirés au hasard.

@ plus

 

[CISCO]

Re : Répartition équitable

Bonsoir

Une petite simplificaton en pièce jointe.

@ plus

P.S : La simplification proposée ne fonctionnant pas bien, je supprime ma pièce jointe.

 

[CISCO]

Re : Répartition équitable

Bonjour

En pièce jointe, une petite simplification de la formule donnant nombreligne1bis dans le tableau 1 du post 8.

Dans l'ancienne version, j'utilisai

ESTNA(EQUIV(nombreligne1;Feuil1!$K$10:$K10;0))*ESTNA(EQUIV(nombreligne1;Feuil1!$L$10:$L10;0))*ESTNA(EQUIV(nombreligne1;Feuil1!$M$10:$M10;0))

et maintenant, c'est tout simplement

ESTNA(EQUIV(nombreligne1;Feuil1!$K$10:$M10;0))

@ plus

 

[gourdin]

Re : Répartition équitable

Ok Merci beaucoup

 

[CISCO]

Re : Répartition équitable

Bonsoir à tous, bonsoir Gourdin

Si c'est toujours d'actualité, jette un œil sur la proposition, très efficace, de mapomme sur ce fil :
https://www.excel-downloads.com/threads/repartition-equitable.20002192/

@ plus