XL 2016 répartition par plusieurs critères

[Pimsir]

Bonjour!
Je me casse la tête depuis plusieurs jours sur un problème de répartition que j'aurais souhaité automatiser.

J'ai besoin de répartir des individus dans différents groupes en fonction de 2 variables, le poids et l'âge. Cette répartition doit être faite de manière à se que la moyenne de poids et de l'âge de chaque groupe soit approximativement la même. Les problèmes sont que d'une fois sur l'autre mon nombre d'individus et mon nombre de groupe n'est pas identique et que chaque groupe ne fait pas forcément la même taille.

ci-dessous un fichier exemple de ce que je souhaiterais

Si quelqu'un as une idée sur comment faire je suis preneur.
Merci beaucoup !

 

[sylvanu]

Bonsoir Pimsir,
Problème complexe.
J'ai fait un premier step : Trier les ID par classe de poids et classe d'age.
Mais là, à mon tour de sécher pour le prochain step.

 

[mapomme]

Bonsoir @Pimsir, @sylvanu 😉,

Une piste dans le fichier joint. La macro est une ébauche et ne s'adapte pas (pour l'instant) au nombre d'individus et les groupes ont le même nombre d'individus (à une une unité près). Mais c'est facilement intégrable.

Un certain nombre de colonnes avec formules ont été ajoutées.
La partie verte n'est que la copie en valeur d'une solution trouvée par itération.

• choisir le nombre de groupe en cellule H1 (cellule en jaune)
• puis cliquer sur le bouton Hop!

La barre d 'état indique l'avancement de la recherche pour trouver une bonne répartition.

Le code est dans le module "Module1"
La constante Nfois (modifiable) au sein de Module1 indique le nombre d'itération à effectuer

La macro n'est pas du tout optimisée puisque ce n'est qu'une piste.

A vous de vous prononcer sur l'utilité de la démarche.

 

[sylvanu]

Bonsoir Pimsir, Bonsoir MaPomme,
,
Un nouvel essai moins abouti que celui de MaPomme.

Les personnes sont triés par Age/Poids et par Poids/Age.
Mais toujours pas trouvée la formule magique pour que les groupes soient homogènes.
Cependant les tris peuvent aider.

 

[sylvanu]

Bonjour Pimsir, Bonjour MaPomme,

j'ai enfin trouvée une méthode qui n'est peut être pas l'optimum mais qui est déterministe et non manuelle. Je voulais éviter les tirages aléatoires car ils ne garantissent pas l'optimum.
Je distribue les personnes dans un repère Age/Poids.
Je fais des groupes, deux par poids, trois par age.
Puis je recherche l'optimum de ces trois limites pour que les groupes soient à peu près de même taille.

 

[sylvanu]

Re Bonjour Pimsir, Bonjour MaPomme,

Sinon il y a la solution des tris par age et par poids, et faire les groupe à la main.
Plus fastidieux mais peut être le plus optimisé.

 

[mapomme]

Bonjour @sylvanu 🙂

ils ne garantissent pas l'optimum

De nombreuses méthodes d'optimisation aboutissent à un "bon" résultat sans atteindre l'optimum. Rares sont celles qui garantissent le meilleur résultat (avec une durée de traitement raisonnable).

 

[mapomme]

Re @sylvanu,

qui est déterministe et non manuelle. Je voulais éviter les tirages aléatoires car ils ne garantissent pas l'optimum.

Je suis comme toi, mais c'est sans doute le côté cartésien (et scolaire) qui s'exprime. Même avec la physique contemporaine, le côté "cartésien scolaire" est mis en défaut.

 

[sylvanu]

Bonjour MaPomme,

J'ai tenté au début les tirages aléatoires comme vous.
Mais si on considère qu'on a 59 boules et qu'ils faut en tirer 10 pour gagner, la proba est plus faible qu'à l'Euromillions. 🙂 J'ai laissé la macro tourner pendant des heures et ça ne convergeait que très très lentement.
D'où l'idée de trouver quelque chose de plus "cartésien" et plus "déterministe".

Ce qui est génial, c'est que le résultat le plus optimum est celui ... fait à la main. ( post #6 )
On garde encore une longueur d'avance sur la machine. 😉

 

[mapomme]

On garde encore une longueur d'avance sur la machine.

Pour combien de temps ? 🙄

 

[sylvanu]

Justement, profitons en !

Faites attention quand même çà votre Webcam. 😉

 

[Pimsir]

Bonjour !
Merci beaucoup pour toutes ces solutions!! Il faut vraiment que je me mette plus sérieusement à la macro!

Sylvanu merci beaucoup pour ta méthode de distribuer les personnes dans un repère Age/Poids, pfiou c'est lourd par contre, ça a fait planter mon ordi la première fois que je l'ai fait tourner.

J'aimerais t'embêter un peu plus si possible. Dans ton post de samedi (test répartition V2) tu as fait une macro pour répartir les personnes par tranche de 10 ans et 10 kgs ce qui me serrait également très utile pour un autre projet. Est-il possible de modifier facilement cette macro pour pouvoir, d'une fois, sur l'autre modifier la tranche d'age et de poids ? (par tranche de 5 ou 10 ou 20 par exemple)

J'avoue que j'ai plus besoin d'une répartition rapide que parfaitement optimal, car je risque de toute façon de devoir remodifier les groupes par la suite dus à d'autres facteurs. Donc une répartition aléatoire comme la fait mapomme me convient tous à fait. Si elle pouvait intégrer le nombre d'individus se serait juste parfait.

Dans tous les cas je tenais à vous remercier pour votre aide vous allez me faire gagner un temps fou, sans parler du temps que je vais passer à m'amuser à essayer de décortiquer vos macros^^

 

[mapomme]

Bonjour @Pimsir, à tous,

Si elle pouvait intégrer le nombre d'individus se serait juste parfait.

Voir la version v2.

Les différences avec la v1:

• quatre noms ont été définis (voir menu Formules / Gestionnaire de noms)
• on utilise ces quatre noms à la fois dans les formules de la feuille "Individu" et dans le code VBA.
• les résultats se trouvent dans le colonnes L à U de la feuille "Individu" et dans le tableau croisé dynamique de la feuille "Résultat"
• on a accéléré le traitement dans le cas où il n'y a qu'un groupe (c'était pas le cas dans la v1)

Le code se trouve dans Module1 et dans le module de la feuille "Résultat" (pour actualiser le TCD).

nota: le code du module1 a été entièrement commenté.