XL 2016 Répartition élèves ateliers

[andco]

Bonjour à tous
Tout d'abord, merci et bravo pour la mine d'informations, d'astuces, de fichiers.
J'ai fait une recherche sur le forum et ce dont j'ai besoin a déjà été plus ou moins abordé mais comme je n'ai aucune connaissance des macros je n'arrive pas à adapter les contributions à ma propre situation.
Voici ma problématique. Je suis directeur de Segpa et nous organisons chaque année une mise en réseau pour les élèves de 4e avec quatre autres établissements. C'est à dire que nos élèves ont la possibilité d'aller découvrir les ateliers des autres établissements sachant que :
- chaque établissement a 16 élèves maximum sauf un qui en a 32
- chaque élève fait trois vœux qu'il classe par ordre de préférence
- chaque élève doit participer à deux ou trois ateliers selon les années
- un élève ne peut pas faire deux fois le même atelier
- un élève ne peut pas faire un atelier de son propre établissement
- il ne peut y avoir plus de 4 élèves d'un même établissement dans un atelier
- nous nous engageons à ce que tous les élèves aient au moins leur premier vœu et au moins deux de leurs trois veux
Je vous joins le fichier avec la base des élèves et la liste des ateliers.

Merci d'avancer à tous et toutes pour vos contributions.
Belle journée

 

[bsalv]

solution :
* 96 élèves avec leur voeu 1
* 96 élèves avec leur voeu 2 et/ou 3,dont
- 58 avec 2&3
- 30 avec seulement 2
- 8 avec seulement 3
* max 4 élèves du même etablissement dans le même atelier
* max 8 élèves par atelier
Peut-être, il existe encore une meilleure solution, à voir ...

 

[crocrocro]

Un nouvelle version : Nouveau fichier en (V3) en pj du post #15

 

[andco]

Bonjour à tous
Merci pour le temps passé sur ma demande et des nombreuses propositions postées.
Je prends le temps de regarder tout ça et vous fais un retard.
Belle journée

 

[andco]

@crocrocro @bsalv
Encore une fois merci pour vos retours, c'est impressionnant.
En vous lisant et en regardant vos fichiers, je me suis rendu compte que j'ai fait mon boulet.
En effet, dans mon pitch introductif j'ai oublié une contrainte importante mais que bsalv a anticipée. La capacité de chaque atelier est de 8 élèves.
Concernant l'établissement E3, effectivement 32 élèves mais 4 ateliers.
Autre chose, il y a deux erreurs de saisie de ma part :
- pour l'atelier HAS de l'établissement E1, c'est "traiteur" et non "ate"
- pour l'atelier HAS de l'établissement E2 c'est "Pâtissier" et non "Serveur en restauration"

 

[crocrocro]

Bonjour @andco, @bsalv

En vous lisant et en regardant vos fichiers, je me suis rendu compte que j'ai fait mon boulet.
En effet, dans mon pitch introductif j'ai oublié une contrainte importante mais que bsalv a anticipée. La capacité de chaque atelier est de 8 élèves.

Un coup de poignard dans le dos 😩 qui casse complètement mon algorithme.
Je m'étais inscrit pour un duathlon ... c'était un triathlon et j'avais oublié mon maillot de bain, Dommage !
Heureusement @bsalv a anticipé l'oubli 🙂
Bonne continuation !
Je me suis aperçu que j'avais effectué cette mauvaise interprétation : Vœu 1 pour Session 1 - Vœu 2 pour Session 2 - Vœu 3 pour Session 3 avec, Vœu 1 impératif pour la Session 1 et donc comme permutations possibles uniquement Vœu 2 pour Session 3 - Vœu 3 pour Session 2, alors que tous les Vœux sont permutables. Mais Vœu 1 doit être présent dans l'une des Sessions, alors que Session 2 ou 3 peuvent être remplacés si aucune combinaison ne permet de respecter toutes les règles. On remplace prioritairement Vœu 3 par un atelier disponible et si çà ne suffit toujours pas, Vœu 2. Je pense que @bsalv l'a lui correctement interprété. J'avais testé son fichier, il y a quelques jours mais l'exécution de sa macro étant très longue (par rapport à la mienne qui dure à peine 1 seconde), je l'avais stoppé avant la fin.

Edit du 28/05 :
je suis allé jusqu'au bout de la macro du fichier de @bsalv, 👍👍
Juste quelques petites remarques :
- Ma version d'Excel ne connait pas la formule XLOOKUP de la macro (j'ai #NOM? dans les colonnes N O P). @andco n'a pas l'air d'avoir été gêné. A substituer pour être indépendant des versions d'Excel par la combinaison INDEX - EQUIV.
- Le fichier ayant probablement été modifié depuis les explications données dans le post #13, les colonnes ne correspondent plus.

 

[bsalv]

je ne comprends pas votre contrainte que les "voeux 1" doivent se trouver dans la colonne "Affectation 1", cela cause des difficultés pour trouver une solution.

 

[crocrocro]

je ne comprends pas votre contrainte que les "voeux 1" doivent se trouver dans la colonne "Affectation 1", cela cause des difficultés pour trouver une solution.

Je ne sais pas si cette remarque m'est adressée mais si c'est le cas, c'est effectivement l'erreur d'interprétation que j'ai signalé dan le post #20 :

e me suis aperçu que j'avais effectué cette mauvaise interprétation : Vœu 1 pour Session 1 - Vœu 2 pour Session 2 - Vœu 3 pour Session 3 avec, Vœu 1 impératif pour la Session 1 et donc comme permutations possibles uniquement Vœu 2 pour Session 3 - Vœu 3 pour Session 2, alors que tous les Vœux sont permutables.

 

[crocrocro]

Et comme je disais dans mon post précédent :

- Ma version d'Excel ne connait pas la formule XLOOKUP de la macro (j'ai #NOM? dans les colonnes N O P). @andco n'a pas l'air d'avoir été gêné. A substituer pour être indépendant des versions d'Excel par la combinaison INDEX - EQUIV.

Ce qui donnerait (je n'ai pas réussi à faire l'adressage relatif équivalent du RC[-5], d'où les 3 formules (1 par colonne)

     With Range("tabel2[jour1]").Resize(, 3)
          .Value2 = aBest
          '.FormulaR1C1 = "=XLOOKUP(RC[-5],Tabel1[N°],Tabel1[Métiers découverts],""?"",0)"
          With .Offset(, 5)
                .FormulaLocal = "=INDEX(Tabel1;EQUIV(Tabel2[jour1];Tabel1[N°];0);1)"
                .Value = .Value
                .EntireColumn.AutoFit
          End With
          With .Offset(, 6)
                .FormulaLocal = "=INDEX(Tabel1;EQUIV(Tabel2[jour2];Tabel1[N°];0);1)"
               .Value = .Value
               .EntireColumn.AutoFit
          End With
          With .Offset(, 7)
                .FormulaLocal = "=INDEX(Tabel1;EQUIV(Tabel2[jour3];Tabel1[N°];0);1)"
               .Value = .Value
               .EntireColumn.AutoFit
          End With
        End With

 

[bsalv]

re,
RC[-5] au lieu de Tabel2[jourx]

.FormulaR1C1 = "=IFERROR(INDEX(Tabel1[Métiers découverts],MATCH(RC[-5],Tabel1[N°],0)),""?"")"

Dans la macro, la macro "MaVersion" me dit que mon Excel365 a version "16.0", je ne sais pas le résultat pour le vôtre. Je pense que ce sera aussi "16.0", bizarre ... . Sinon un petit teste "Quick and dirty" pour savoir si votre version connait "Xlookup"

 

[crocrocro]

re,
RC[-5] au lieu de Tabel2[jourx]

.FormulaR1C1 = "=IFERROR(INDEX(Tabel1[Métiers découverts],MATCH(RC[-5],Tabel1[N°],0)),""?"")"

Dans la macro, la macro "MaVersion" me dit que mon Excel365 a version "16.0", je ne sais pas le résultat pour le vôtre. Je pense que ce sera aussi "16.0", bizarre ... . Sinon un petit teste "Quick and dirty" pour savoir si votre version connait "Xlookup"

Je suis, comme d'ailleurs @andco (voir l'en-tête de la discussion) en version Excel 2016 donc privé de Xlookup .
Votre formule est ok 👍
Une dernière suggestion, mais c'est du détail, pour savoir où on en est dans la boucle, dans la barre d'état, ajouter sur 100 :

 Application.StatusBar = iLoop & " sur 100 :  " & dBest

 

[bsalv]

okay. Je vois maintenant aussi 😵 que Andco n'avait que excel2016.
Votre version avec "MaVersion" était aussi "16.0" ?

 

[crocrocro]

okay. Je vois maintenant aussi 😵 que Andco n'avait que excel2016.
Votre version avec "MaVersion" était aussi "16.0" ?

Ma version Excel : 2016 que l'on ne peut apparemment pas connaitre par Application.Version (qui renvoie 16.0)
Avec cette macro que j'ai retouché -> 2016

Public Function AppVersion() As Long
Dim registryObject As Object
Dim rootDirectory As String
Dim keyPath As String
Dim arrEntryNames As Variant
Dim arrValueTypes As Variant
Dim x As Long

    Select Case Val(Application.Version)
    
        Case Is = 16
            'Check for existence of Licensing key
            keyPath = "Software\Microsoft\Office\" & CStr(Application.Version) & "\Common\Licensing\OlsToken"
            rootDirectory = "."
            Set registryObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & rootDirectory & "\root\default:StdRegProv")
            registryObject.EnumValues &H80000001, keyPath, arrEntryNames, arrValueTypes

            On Error GoTo ErrorExit
            For x = 0 To UBound(arrEntryNames)
                If InStr(arrEntryNames(x), "365") > 0 Then
                    AppVersion = 365
                    Exit Function
                End If
                If InStr(arrEntryNames(x), "2019") > 0 Then
                    AppVersion = 2019
                    Exit Function
                End If
                'ajout crocrocro
                If InStr(arrEntryNames(x), "homestudentretail") > 0 Then
                ' probablement Version 2016 Famille - étudiant
                    AppVersion = 2016
                    Exit Function
                End If
            Next x

        Case Is = 15
            AppVersion = 2013
        Case Is = 14
            AppVersion = 2010
        Case Is = 12
            AppVersion = 2007
        Case Else
            'Too old to bother with
            AppVersion = 0
    End Select

    Exit Function

ErrorExit:
    'Version 16, but no licensing key.  Must be Office 2016
    AppVersion = 2016

End Function

 

[bsalv]

ma vérification "Quick&Dirty" était plus facile 😉

 

[crocrocro]

solution :
* 96 élèves avec leur voeu 1
* 96 élèves avec leur voeu 2 et/ou 3,dont
- 58 avec 2&3
- 30 avec seulement 2
- 8 avec seulement 3
* max 4 élèves du même etablissement dans le même atelier
* max 8 élèves par atelier
Peut-être, il existe encore une meilleure solution, à voir ...

Bonjour,
@bsalv, je viens d'examiner votre code : Chapeau 😍.
Je suppose qu'il s'agit là d'adaptations d'un code que vous aviez mis au point pour d'autres cas.
Je donnerais bien pour nom à cet algorithme "Le Solveur Magique de Bsalv" 😉
Il ne reste plus qu'à mettre des commentaires dans le code et déposer le brevet !

 

[bsalv]

oui, c'est une adaptation, mais il faut voir l'ensemble comme par exemple 100 fois jouer le jeu "Tetris" mais au lieu des pièces de formes différentes, on a ces 3 listes de voeux et on reçoit des points pour chaque voeu, càd 1, 2 et 3 pour chaque voeu 1, 2 et 3, mais on ajoute 10 points pour chaque voeu pas accompli. L'objet c'est donc de minimaliser cette somme. Pendant le jeu, si une pièce/voeu dépasse certaines limites (nombre d'élèves par atelier ou par établissement), la pièce/voeu n'est pas placée (et reçoit donc 10 points de pénalité).
En plus on ajoute 1.000.000 et 10.000 points (valeurs plutôt arbitrair) de pénalité supplémentaire pour chaque voeu 1 ou 2 pas réalisé.
J'ai ajouté le tableau "tabel7" avec le résultat de 100 fois jouer ce jeu "Tetris" et vous verrez les points réalisés dans la colonne AE (points) et avec l'ajout pénalités en colonne AC. J'ai mis les 3 meilleurs résultats en rouge et la macro choisit la meilleur résultat pour remplir les colonnes M:O.

Donc, ma solution n'est pas un jeu avec un algorithme "malin", mais plutôt jouer le jeu x fois en "brute force" avec un minimum de règles mais chaque infraction entraîne des points de pénalité et au bout, le jeu avec le moins de points est choisi.
Vous voyez aussi que, avec les voeux exprimés, seulement 10-20 des 100 jeux finissent avec tous les 96 voeu1 et voeux2&3 réalisés. Si les voeux sont encore plus compliqué, peut-être aucun jeu sera capable à réaliser cela, mais la macro vous donnera encore toujours le meilleur résultat.
Je n'ai pas dit qu'il n'existe pas un résultat qui est encore mieux que celui-ci, mais je pense qu'on aurait besoin d'un ordinateur et un algorithme assez performant !!! Et un résultat après une calculation d'une minute est mieux qu'un résultat légèrement mieux (remplacer un voeu3 par un voeu2 ou un voeu réalisé supplémentair) après 1 heure de calculation. (Oubien vous choississez jouer ce jeu 1.000 fois par exemple)
Chaque fois que vous lancez la macro, vous recevez une autre solution.

Maintenant avec commentaire dans la macro