Répartition de personnes en groupes prédéterminés

[Alx]

Bonjour à tous!

Mon problème est le suivant. Je susi enseignant et voudrais utiliser Excel pour répartir un nombre déterminé d'étudiants (300) dans plusieurs groupes prédéterminés, où les places sont limitées (10 groupes de 30).

Les 300 étudiants communiquent 3 choix (par ordre de préférence) et doivent se voir attribuer un seul groupe.

Est-il possible de réaliser une telle répartition de manière automatisée, grâce à XL?

 

[pat1545.]

Salut,

un petit coup de tableau croisé non ?


Patrick

 

[Gael]

Bonjour Alx, Bonjour Pat,

Un premier essai ci-joint sur une base de 30 étudiants et 10 groupes de 3.

Attention, la fonction utilisée pour affecter un n° d'ordre alétoire à chaque étudiant ne fait pas partie des fonctions standards d'excel.

Tu dois télécharger une macro complémentaire à l'adresse suivante:

nom de la macro: morefun.xll
Ce lien n'existe plus

Sinon, on peut supprimer ce tri aléatoire, mais j'ai trouvé que c'était plus juste comme système.

Si ce premier essai te convient on pourra l'améliorer par la suite.

@+

Gael [file name=etudiants.zip size=8687]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/etudiants.zip[/file]

 

[Alx]

Ca marche du tonnerre de dieu! Suis super impressionné, tout grand merci !!!

Je me débrouillerai pour ajuster au nombre de groupes et d'étudiants (suffit de modifier les formules de la dernière colonne, je suppose).

Encore merci, vraiment!

Alx

 

[Gael]

Bonjour Alx, Bonjour Pat,

Merci de tes compliments, Alx.

Comme ce n'était qu'un essai, le mieux est que tu me renvoie un fichier présenté comme tu le souhaites que je te renverrai finalisé (les plages ou les formules peuvent être nommées ou de taille variable, certains champs peuvent avoir une saisie validée pour éviter les doublons ou choisir un groupe qui n'existe pas...)

Sinon, il suffit de recopier la formule vers le bas pour l'adapter au nombre de personnes à traiter sans oublier de remplacer les <3 par le nombre de personnes dans un groupe. Attention, les formules sont matricielles donc à saisir en faisant Ctrl-Maj et Entrée simultanément.

Pour le tri, j'ai réfléchi après, il y a des possibilités avec les fonctions standards d'XL (même si Morefun est plus pratique) ou d'autres critères comme des résultats d'examens ou autres).

@+

Gael

 

[JMP55PHYS]

Re : Répartition de personnes en groupes prédéterminés

Bonjour
j'ai tenté de modifier le tableau étudiants afin d'avoir des groupes de tailles différentes.
cela semble marcher à peu près
mais je ne comprends pas pourquoi mes formules d'effectif réel ne donnent pas le bon résultat.
de plus j'aimerais que si un étudiant ne fait aucun voeu, il soit quand même affecté (ce qui n'est pas le cas pour le moment)


je joins le fichier étudiants-2.zip

merci d'avance à ceux qui se pencheront sur le problème.

 

[job75]

Re : Répartition de personnes en groupes prédéterminés

Bonjour JMP55PHYS, bienvenue sur XLD,

Il y avait 2 erreurs dans votre fichier :

1) il manquait des signes $ dans les formules en colonne L, en L4 :

=NB.SI(G[COLOR="Red"]$[/COLOR]2:G[COLOR="red"]$[/COLOR]250;I4)

2) les formules en colonne G sont matricielles et doivent être validées par Ctrl+Maj+Entrée.

Par ailleurs si l'on veut qu'un étudiant soit affecté même s'il ne fait aucun choix (Colonnes C D E vides), il faut utiliser cette formule (matricielle) en G2 :

=SI([COLOR="red"]ET[/COLOR](NB.SI($G$1:$G1;C2)<INDEX(GR;C2;2);[COLOR="red"]C2[/COLOR]);C2;SI([COLOR="red"]ET[/COLOR](NB.SI($G$1:$G1;[COLOR="red"]D2[/COLOR])<INDEX(GR;D2;2);D2);D2;SI([COLOR="red"]ET[/COLOR](NB.SI($G$1:$G1;E2)<INDEX(GR;E2;2);[COLOR="red"]E2[/COLOR]);E2;PETITE.VALEUR(SI(NB.SI($G$1:$G1;LIGNE(INDIRECT("1:15")))<INDEX(GR;INDIRECT("1:15");2);LIGNE(INDIRECT("1:15")));1))))

La modification consiste a tester si C2 D2 E2 existent (en rouge).

A+

 

[JMP55PHYS]

Re : Répartition de personnes en groupes prédéterminés

Bonsoir Job75 et merci beaucoup de votre aide:

Les corrections que vous avez apportées marchent bien ...

...sauf si tous les groupes et en particulier le dernier ont des élèves.

Je m'explique:
j'ai d'abord mis aucun premier voeu pour quelques élèves
j'ai ensuite réparti les 60 élèves dans les 15 (petits) groupes
et quand on renseigne l'effectif du dernier groupe on voit une erreur apparaître
le nombre d'élèves affectés n'est plus égal à 60!

je joins un fichier qui montre le problème

merci par avance

 

[job75]

Re : Répartition de personnes en groupes prédéterminés

Bonsoir JMP55PHYS,

C'est bien fait pour moi, il ne faut jamais utiliser les fonctions ET ou OU dans les formules matricielles.

Et je l'ai pourtant toujours su

Alors en G2 la formule matricielle :

=SI([COLOR="Red"]C2*[/COLOR](NB.SI($G$1:$G1;C2)<INDEX(GR;C2;2));C2;SI([COLOR="red"]D2*[/COLOR](NB.SI($G$1:$G1;D2)<INDEX(GR;D2;2));D2;SI([COLOR="red"]E2*[/COLOR](NB.SI($G$1:$G1;E2)<INDEX(GR;E2;2));E2;PETITE.VALEUR(SI(NB.SI($G$1:$G1;LIGNE(INDIRECT("1:15")))<INDEX(GR;INDIRECT("1:15");2);LIGNE(INDIRECT("1:15")));1))))

Bonne nuit.

 

[JMP55PHYS]

Re : Répartition de personnes en groupes prédéterminés

Bravo ! c'est impeccable!
Je n'en reviens pas d'avoir une réponse aussi rapide !

C'est vraiment très gentil de votre part.

merci beaucoup !

 

[JMP55PHYS]

Re : Répartition de personnes en groupes prédéterminés

Bonsoir job75

j'ai progressé encore dans mon projet:
En fait il fallait dispatcher environ 230 élèves dans une quinzaine de groupes (de tailles variables) selon 3 voeux et selon 2 heures
Les élèves doivent faire soit 2 activités différentes durant chacune 1 h
soit une seule activité de 2 h
A partir de mon 1er tableau
j'ai précisé les groupes durant 1 h (0) ou 2h (1)
puis j'ai fabriqué des voeux C'1 et C'2 qui tiennent compte de l'affectation de la 1ère heure.
Les élèves qui sont tombés sur un groupe de 2 h ne participent pas au tirage de la 2ème heure.
J'ai alors écrit une petite macro qui copie sur une nouvelle feuille les noms des seuls élèves qui ont droit à une 2ème heure, ainsi que leur 1ère affectation et leurs 2 voeux restants.
J'ai bien sûr défini de nouvelles variable NE2 (nb d'élèves pour la 2ème heure) et G_H2 tableau de groupes pour la 2ème heure.
pour l'instant tout marche bien
Où cela coince c'est quand je refais le 2ème tirage pour la 2ème heure: le nb de personnes affectées dans un groupe peut être supérieur à l'effectif maxi du groupe.
C'est pourquoi je sollicite de nouveau votre aide.
En effet je ne vois pas d'erreurs dans ma feuille etudiants H2 colonne G
et comme je ne comprends pas comment intervient la fonction aleas, je me dis que mon erreur n'est peut-être pas évidente.
j'ai un souci pour vous envoyer mon tableau, car compressé en Zip il fait environ 100 ko.
aussi j'ai rusé un peu en fabriquant deux fichiers etudiants-4-part1.rar et eudiants-4-part2.rar que j'ai renommés en .zip pour qu'ils soient acceptés par le serveur. Pour reconstituer le tableau, il faut donc renommer les zip en rar avant de les décompresser.
Voilà j'espère que j'ai été assez clair.
et je vous remercie par avance de votre aide.

dernière minute: je viens de trouver mon erreur et tout marche bien.
encore merci.

 

[JeremieSaintCharles]

Re : Répartition de personnes en groupes prédéterminés

Bonsoir à vous,

Je suis tout à fait novice, pour ne pas dire ignorant avec l'utilisation d'Excel. A la lecture du forum et plus précisément de cette discussion, j'ose cependant vous exposer mon problème en espérant sincèrement que vous pourrez m'aider à trouver une solution.

Si je poste à la suite de cette discussion c'est que tout ce qui a été cité ci-dessus me parait pertinent.

Voici donc l'exposé de mon souci:

Je travail dans un établissement scolaire, et on vient de me confier la tâche d'organiser une journée (mardi 26 février) consacrée à la découverte d'atelier pour mes élèves de 4ème.

Les données sont donc les suivantes:
360 élèves de 4ème devront participer à 3 clubs durant la journée. il y a 15 proposition de clubs avec pour chacun d'eux un nombre d'élève participant maximum différent.
Nos élèves ont remplis une feuille en hiérarchisant de 1 à 15 leur souhait de participer à tel ou tel club.
Les clubs auront lieu à 9h30 puis 10h30 et enfin à 14h30.
Pour chacun de ces horaires nous aurons donc 15 clubs différents.

Je me suis permis de regarder les fichier joint précédemment, mais ma connaissance d'Excel avoisinant le néant, je suis dans l'incapacité de faire évoluer de quelconque manière que ce soit ce qui a déjà été réalisé.

Je vous remercie de l'attention que vous porterez à mon souci.

Je me tiens bien évidemment à disposition si vous avez besoin d'information complémentaires.

Jérémie.

 

[job75]

Re : Répartition de personnes en groupes prédéterminés

Annulé, je revois le problème.

 

[job75]

Re : Répartition de personnes en groupes prédéterminés

Bonjour JeremieSaintCharles , bienvenue sur XLD,

Voici une solution VBA, je pense que par formule ce serait très difficile.

Le fichier ci-joint est organisé pour 360 élèves et 15 clubs.

Donc allez dans VBA (Alt+F11) et voyez les 2 macros.

1) Macro lancée par le bouton "Choix aléatoires" en feuille Choix :

Sub ChoixAleatoires()
'bien entendu cette macro ne sert que pour tester
Dim P As Range, cc As Byte, r As Range, c As Range, n As Byte
Application.ScreenUpdating = False
Set P = [C3:Q362]
cc = P.Columns.Count 'nombre de clubs
P.ClearContents 'RAZ
Randomize
For Each r In P.Rows
  For Each c In r.Cells
    Do
      n = Int(1 + cc * Rnd) 'nombre entier aléatoire de 1 à cc
      If Application.CountIf(r, n) = 0 Then c = n: Exit Do
    Loop
  Next
Next
End Sub

En réalité les choix seront faits par les élèves eux-mêmes.

2) Macro lancée par le bouton "Organiser" de la feuille Clubs :

Sub Organiser()
Dim Pchoix As Range, P As Range, rc As Byte, cc As Byte
Dim n As Byte, col As Byte, lig As Byte, c As Range
Dim test As Boolean, n1 As Integer, c1 As Range
Set Pchoix = Sheets("Choix").[A3:Q362]
If Application.Count(Pchoix) < Pchoix.Rows.Count * (Pchoix.Columns.Count - 1) _
  Then MsgBox "La plage des choix n'est pas correctement remplie...": Exit Sub
Application.ScreenUpdating = False
Set P = [A4:O27] '1ère session
rc = P.Rows.Count
cc = P.Columns.Count
P.EntireRow.ClearContents 'RAZ
For n = 1 To 3 'les 3 sessions
  If n > 1 Then Set P = P.Offset(, cc) 'plage décalée
  For col = 1 To cc
    Pchoix.Sort Pchoix.Columns(col + 2), xlAscending, Header:=xlNo 'tri
    lig = 1
    For Each c In Pchoix.Columns(2).Cells
      test = Application.CountIf(P, c) = 0
      If test Then
        For n1 = 1 To n - 1 'pour ne pas participer plus d'une fois au même club
          If Application.CountIf(P.Columns(col).Offset(, cc * (n1 - n)), c) _
            Then test = False: Exit For
        Next
        If test Then
          P(lig, col) = c
          lig = lig + 1
          If lig > rc Then Exit For
        End If
      End If
    Next c
  Next col
  'si la plage P n'est pas entièrement remplie on la complète :
  If Application.CountA(P) < P.Count Then
    For Each c1 In P.SpecialCells(xlCellTypeBlanks)
      For Each c In Pchoix.Columns(2).Cells
        If Application.CountIf(P, c) = 0 Then c1 = c: Exit For
      Next c
    Next c1
  End If
Next n
Pchoix.Sort Pchoix.Columns(1), xlAscending, Header:=xlNo 'tri sur colonne A
End Sub

Un élève est inscrit dans un club d'une session selon 2 critères :

- ne pas déjà être inscrit dans cette session, c'est évident

- ne pas avoir été inscrit dans ce club dans les sessions précédentes.

Ce dernier critère est contraignant : il peut se faire que quelques élèves ne soient pas placés à la fin.

Alors tant pis, on les place même s'ils ont déjà été dans ce club.

Je ne vois pas comment faire autrement, sauf à faire des placements aléatoires (très compliqués).

A+

 

[JeremieSaintCharles]

Re : Répartition de personnes en groupes prédéterminés

Merci beaucoup, je m'attache à comprendre le fonctionnement maintenant.