XL 2013 repartition aleatoire de candidat dans un groupe

[duc441]

Bonjour, est ce qu'il y aurait parmi vous quelqu'un qui pourrait m'aider, sur la création d'un fichier Excel , pour une répartition aléatoire d'une liste de candidat dans des groupes composé de deux jury . La contrainte est de faire en sorte que les candidats ne soient pas aves les jury qu'ils connaissent. En vous remerciant par avance .

 

[crocrocro]

Bonjour,
en pj une solution qui utilise le Solveur.
Tout est décrit dans la feuille.
mon exemple utilise 3 jurys, il faudra faire quelques petites adaptations si le nombre est différent.
Je peux détailler

 

[bsalv]

re, salut crocro,
une solution avec une macro

 

[crocrocro]

Bonsoir @bsalv ,
il me semble qu'on s'est déjà croisés sur ce type de demande avec des propositions Solveur VS Macro
@duc441 , j'ai fait les 2 hypothèses suivantes :
1- Les groupes doivent avoir le même nombre de candidats (sans découper un candidat si la division ne tombe pas juste)
2- Si un candidat est connu de tous les jurys, on va tout de même l'affecter à un jury.
La dernière hypothèse est, je suppose prioritaire par rapport à la 1ère. J'ai utilisé des coefficients de pondération dans mon fichier qui permettent de prioriser à votre convenance.

 

[duc441]

Bonsoir @bsalv ,
il me semble qu'on s'est déjà croisés sur ce type de demande avec des propositions Solveur VS Macro
@duc441 , j'ai fait les 2 hypothèses suivantes :
1- Les groupes doivent avoir le même nombre de candidats (sans découper un candidat si la division ne tombe pas juste)
2- Si un candidat est connu de tous les jurys, on va tout de même l'affecter à un jury.
La dernière hypothèse est, je suppose prioritaire par rapport à la 1ère. J'ai utilisé des coefficients de pondération dans mon fichier qui permettent de prioriser à votre conv

 

[duc441]

Bonjour,

Merci pour votre réponse mais je recherche plus une solution sous ce format , deux jury pour un groupe , avec les mêmes contraintes , les candidats ne doivent pas connaitre leur jury , du même établissement. En vous remerciant par avance

 

[bsalv]

il me semble qu'on s'est déjà croisés sur ce type de demande avec des propositions Solveur VS Macro

@crocrocro , c'est possible, je ne me rappèle pas. Mais je crains que pour 160 integers et 9 groupes, Solver ne sera pas un outil pratique, oubien trop grand oubien trop lent, je ne le sais pas. Pour m'amuser, je l'ai transformé en 2 TS et dans "Tabel1", à partir de la ligne 40, j'ai ajouté des noms supplémentaires pour créer une situation plus complexe/impossible (avec des noms connu dans les 9 groupes). Alors en B3;J3, on voit le nombre d'infractions. On peut encore optimaliser cela, plus équilibre pour tous les groupes, etc. Bon, mais quelque chose pareil dans Solver, cela serait possible ? Je n'ai pas grand confiance en Solver pour des affaires binaires et integer. Voir PJ, maintenant avec votre modèle solver et seulement 15 lignes qui s'arrête après 5 minute (quelle méthode est la meilleur ??? evolutionary ???") sans solution satisfaisante

@duc441,
votre premier fichier est disparu et ce fichier est complètement différent. Je ne le comprends pas. Vosu n'avez pas un exemple avec une dizaine de noms. Jury1 y est pour les établ. A&B, jury 2 pour C&D ?

 

[crocrocro]

Bonjour à tous les deux
@duc441,
comme @bsalv, j'ai du mal à comprendre votre exemple du dernier fichier qui est effectivement différent du 1er.
Vous écrivez en #6

je recherche plus une solution sous ce format , deux jury pour un groupe , avec les mêmes contraintes , les candidats ne doivent pas connaitre leur jury , du même établissement.

Je ne comprends pas la signification de deux jury pour un groupe, et votre fichier ne donne pas plus d'éclaircissements.

Quelques questions / remarques avant de faire une nouvelle proposition :

1. Un candidat dépend d'1 établissement unique
2. Un jury peut connaitre tous les candidats d'un même établissement
3. Un jury peut connaitre seulement certains candidats d'un même établissement (dans ce cas l'établissement n'est pas déterminant pour élaborer la solution)
4. Un jury peut ne connaitre aucun candidat d'un même établissement
5. Un jury, s'il connait tous les candidats n'aura aucune affectation (dans ce cas, il n'est pas judicieux de l'inclure dans la liste des jurys)
6. Un jury peut ne connaitre aucun candidat
7. Un jury est affecté à 1 et 1 seul groupe
8. Un candidat est affecté à 1 et 1 seul groupe
9. La taille des groupes (d'après votre 1er fichier) est variable.
10. Doit-on essayer, dans la mesure où c'est possible d'équilibrer la taille des groupes ?
11. Combien y a-t-il de jurys ?
12. Combien y a-t-il d'élèves (approximativement) ?
13. Combien connait d'élèves un jury (l'ordre de grandeur) ?

Concernant votre remarque je recherche plus une solution sous ce format, il est très facile après recherche de la solution par le Solveur d'en faire manuellement la présentation que vous souhaitez, on peut aussi l'automatiser.

@bsalv ,
en fonction des réponses qui seront apportées, le Solveur sera à la peine ou non, c'est de la force brute et impossible de savoir à l'avance. Ceci dit, si c'est à faire une fois, et si cela ne prend que quelques minutes, cela ne me parait pas insupportable.

EDIT
j'ai fait un test : avec 200 candidats, 5 jurys et en moyenne 60 incompatibilités (candidat-Jury) par jury, le Solveur trouve une solution en 5 minutes (5 groupes de 50 candidats, 0 incompatibilité)

 

[duc441]

Bonjour, merci pour vos interventions . Le premier fichier n'était pas le bon , puisqu'il n'était pas une bonne base de travail . En effet , la répartition des candidats doivent se faire dans un jury composé de deux personnes , ces personnes (jury) sont déjà présélectionnés. Mais ces personnes (jury) font eux aussi parti des mêmes établissements que les candidats. Deux jury ne peuvent venir du même établissement , et les candidats ne peuvent pas être sélectionné par un jury si l'un des deux membres vient du même établissement que le candidat .

1. Un candidat dépend d'1 établissement unique un ou deux (mais un )
2. Un jury peut connaitre tous les candidats d'un même établissement--->oui
3. Un jury peut connaitre seulement certains candidats d'un même établissement (dans ce cas l'établissement n'est pas déterminant pour élaborer la solution)--> si l'etablissement est le determinant pour la solution
4. Un jury peut ne connaitre aucun candidat d'un même établissement ---> oui si il ne vient pas de l'établissement
5. Un jury, s'il connait tous les candidats n'aura aucune affectation (dans ce cas, il n'est pas judicieux de l'inclure dans la liste des jurys)
6. Un jury peut ne connaitre aucun candidat ---> Il ne connait pas les candidats qui ne sont pas dans son établissement
7. Un jury est affecté à 1 et 1 seul groupe ----> Oui
8. Un candidat est affecté à 1 et 1 seul groupe ---> Oui
9. La taille des groupes (d'après votre 1er fichier) est variable. -->
10. Doit-on essayer, dans la mesure où c'est possible d'équilibrer la taille des groupes ? --> oui
11. Combien y a-t-il de jurys ? --> 2 par groupe --> en totalite il sont entre 10 et 12 groupes de jury
12. Combien y a-t-il d'élèves (approximativement) ? 200
13. Combien connait d'élèves un jury (l'ordre de grandeur) ? aucun
14. 
    Merci pour votre attention sur ma problématique

 

[bsalv]

le fichier est assez simple, pffff. J'ai créé le mien, assez similaire au précédent.
Prêt en moins d'une seconde

 

[crocrocro]

En pj , j'ai adapté ma proposition précédente en tenant compte des nouvelles précisions.
J'ai pris pour exemple :
une liste de 200 candidats répartis en 5 établissements A B C D E
10 groupes de Jury (2 personnes par groupe) en respectant les règles de constitution des jurys.
Une exécution du Solveur a donné le résultat suivant après 6 minutes

Les explications dans la feuille Explications
La constitution du Jury dans la feuille Jurys
Les Candidats dans la feuille Candidats
Le Tableau final dans la feuille Solution (c'est là que l'on doit lancer le Solveur)

Comme je l'ai déjà dit, il est facile de passer ensuite à une présentation telle que vous la souhaitez

 

[duc441]

Bonjour, merci à tous les deux j'ai testé les deux solutions , les deux fonctionnent si je reste sur une configuration d'origine.

Pour le solveur j'ai créé 12 jurys supplémentaires soit 22 jurys , et j'ai mis 184 candidats, et la malheureusement la répartition se fait sur des candidats fantômes (185 à 200) et au niveau de la répartition jury j'ai une répartition inégale , avec des jurys qui on jusqu'à 12 candidats au lieu d'avoir une répartition proportionnelle , comme pour le fichier modèle.

Pour la solution @bsalv , j'ai fait les mêmes modifications , la solution repartie proportionnellement les candidats mais on retrouve des candidats du même établissement que l'un au moins des deux jurys et ça dans plusieurs groupe .

Mais les deux solutions sont super . Merci beaucoup .

 

[bsalv]

je suppose que les formules des colonnes C😀 n'étaient pas mis à jour. Maintenant VBA le fait tout seul.

 

[crocrocro]

Bonjour à tous les 2,
il aurait été (et sera) intéressant d'avoir votre fichier avec vos modifications pour comprendre et faire disparaitre vos fantômes.

EDIT
Dans votre fichier, donnez des noms bidons pour les candidats (mais avec le bon nombre, idem pour les noms des jurys).
Conservez les valeurs des établissements (des codes ?) pour les candidats et les membres du Jury.
Je vous retournerai un fichier (mon fichier mais avec vos données) en espérant avoir compris le pourquoi (je soupçonne des erreurs dans vos adaptations de formules).
Il ne vous restera alors qu'à remplacer les noms bidons par les vrais dans les feuilles Candidats et Jurys dans le fichier que je vous retournerai et lancer la macro de formatage (voir ci-dessous)
FIN EDIT

En pj nouvelle version avec de petits remaniements et comme vous aviez demandé, une mise en forme telle que souhaitez.
Voir la feuille Explications ... pour les explications
Le fichier comporte une macro (donc l'extension est xlsm) qui permet de formater la présentation en lançant la macro CandidatsParJury après exécution du Solveur.
Comme précédemment, avec une liste de 200 candidats répartis en 5 établissements A B C D E et 10 groupes de Jury (2 personnes par groupe) le Solveur donne une solution optimale (20 candidats pour chaque groupe - 0 incompatibilité Jury - Candidat) au bout de 5 minutes.
Le Tableau final généré automatiquement :

EDIT 2
Une question qui m'a échappé : Les binômes de chaque jury doivent-ils être tirés au sort ?
Cela n'a pas été exprimé très clairement.
Dans mon fichier, chaque binôme est constitué manuellement (en respectant la contrainte "pas du même établissement").
Si oui, je ferai une proposition complémentaire.
Je ne sais pas si @bsalv a été plus perspicace que moi.
FIN EDIT 2

EDIT 3
Je viens de tester le dernier fichier de @bsalv.
@bsalv déclaré vainqueur 🏆🏆🏆, sa macro trouve une solution en 2 secondes, avec ma proposition solveur c'est 5 minutes.
Un détail : Comme moi, il n'a pas tiré au sort les binômes de chaque jury.
FIN EDIT 3
EDIT 4
@duc441
en #9, à ma question sur le nombre de groupes :

Combien y a-t-il de jurys ? --> 2 par groupe --> en totalite il sont entre 10 et 12 groupes de jury

en #12 vous écrivez

Pour le solveur j'ai créé 12 jurys supplémentaires soit 22 jurys ,

Une petite ambiguïté sur la notion de jury :
1 jury correspond bien à 1 personne examinatrice.
1 groupe de jurys est composé d'un binôme d'examinateurs.
Et dans ce cas, 24 jurys = 24 personnes examinatrices = 12 groupes
FIN EDIT 4

 

[bsalv]

Un détail : Comme moi, il n'a pas tiré au sort les binômes de chaque jury

dans ma feuille bidon, "blad1", j'avais créé 44 personnes en colonne F. Puis la macro "X" cherche des binômes dans 2 établissements. Puis j'ai copié&transposé ces couples O1😛22 (sorry O1 : P22😇) vers ma feuille "feuil1". Bon, c'était un peu "brouillon" avec de manipulations plutôt manuelles. A améliorer ???
@crocrocro, j'ai adapté votre fichier pour 22 groupes. Comme mon Excel n'est pas françophone, il avait des problèmes avec "VRAI/FAUX", donc ici, c'est 0/1. Avec plus de 200 élèves, le modèle disait que c'était trop.

• J'avais copié les binomes d'ici dessus vers feuille "Jurys" colonne H:I et puis copié&collé/transposé la colonne "concat" vers O2:AJ2
• les établissement, la macro "M_Random_Estab" crée une séquence d'établissements, mais avec un établissement qui est le double des autres (voir plage E12:F17)
• les 2 dernier élèves n'ont pas des "x", donc ils connaissent tous les jurys (pour causer des problèmes volontairement 😎)
• Puis avec le solveur, on sait atteindre une solution en 5-6 minutes.
• Mais j'ai pris votre modèle et avec l'aide de la macro "M_MonSolveur" en 10 secondes, je vous donne aussi une solution (même moins sans le "screenupdating")