Sélection aléatoire avec quotas
Bonjour à tous,
J'espère être dans la bonne section et si tel n'est pas le cas je vous prie de bien vouloir m'en excuser. Idem, si le sujet existe déjà je suis désolé de ne pas l'avoir trouvé et d'en avoir créé un nouveau.
Je rencontre des difficultés pour créer une/des sélection(s) aléatoire(s) avec quotas et +/- une pondération.
Je m'explique (je travaille sur un fichier partagé google sheet) : pour des épreuves d'examens facultatives, j'ai un certain nombre d'élèves dont l'identité apparait en colonne A. En colonne B j'ai noté "0" si l'élève n'a pas prévu de participé à l'épreuve et "1" s'il à répondu favorablement à la convocation. Je dois répartir les élèves présents (soit ceux ayant un "1" dans a colonne B) aléatoirement entre 4 établissements afin qu'ils passent leurs épreuves. Pour cela j'aurai pu m'en sortir avec des fonctions "ALEA" et "SI" en colonne C en ne tenant compte pour la fonction ALEA que des élèves ayant un "1" en colonne B et en attribuant à chaque établissement un nombre entre 1 et 4.
Là où cela se gâte et où mes rudimentaires connaissances en Excel ne suffisent plus, c'est que je dois ajouter quelques conditions à cette répartition aléatoire, à savoir :
- Je dois appliquer des quotas pour chaque établissement : par exemple, l'établissement 1 doit recevoir 40 élèves, l'établissement 2, 30 élèves, l'établissement 3, 20 élèves, et l'établissement 4, le reste. Je pensais procéder par étapes avec les fonctions SI et ALEA : (1) sélection aléatoire de 40 élèves pour l'établissement 1, (2) sélection aléatoire parmi les élèves non sélectionnés à l'étape 1 de 30 élèves pour l'établissement 2, (3) sélection aléatoire parmi les élèves non sélectionnés aux étapes 1 et 2 de 20 élèves pour l'établissement 3, et (4) affectation des tous les élèves non sélectionnés aux étapes 1, 2 et 3, à l'établissement 4. Problème, je ne sais pas comment affecter un quotas à la fonction ALEA.
- Pour des raisons de conflits d'intérêt potentiels avec certains enseignants présents dans l'un des établissements, je souhaite que certains élèves soient affectés prioritairement à l'un des 3 autres établissements (exemple, l'élève A est parent avec un enseignant de l'établissement 2, je souhaite donc qu'il ne lui soit pas possible d'être affecté à l'établissement 2). Pour cette étape, je ne sais pas du tout comment faire.
Je ne sais pas si tout est faisable ni si mes explications/informations sont très claires, auquel cas n'hésitez pas à me le signaler, je pourrai préciser ma pensée.
En vous remerciant par avance pour vos retours éclairés (et éclairant je l'espère !).
Bonjour,
Pour les sélections aléatoires, tu peux utiliser :
=ALEA.ENTRE.BORNES(2;4)Pour les quotas par établissement :
=query($C$2:$E;"select C where not D = 1 and E = 2 limit 40")Une MFC pour signaler les conflits de l'établissement 4 non traités :
=query($C$2:$D;"select C where D = 4 ";0)=$J2Une ébauche, mais attention dès que tes listes te conviennent, il faut faire un copier/coller des valeurs dans une autre feuille, sinon à chaque ouverture du fichier les tirages vont se relancer (F5 pour refaire un tirage aléatoire) !
https://docs.google.com/spreadsheets/d/1LxSM1LMqurLXcComnG4ApDnIXRKAtafzzmNzqdv26wQ/edit?usp=sharing
Cordialement,
Fil.
Bonsoir Fil,
Merci beaucoup pour cette réponse rapide et complète. Je vais tester ça, merci.
Si j'ose abuser, as-tu une solution pour, plutôt que d'afficher la liste des élèves dans chaque établissement en colonnes comme dans ton document, ce soit plutôt l'établissement d'affectation qui s'affiche sur la même ligne que l'élève ? Je ne sais pas si je suis clair...
Il suffit de rajouter 2 colonnes :
=SORT({{query($G$2:$G;"select * where G is not null")\transpose(split(rept($G$1&"+";NBVAL($G$2:$G));"+"))};{query($H$2:$H;"select * where H is not null")\transpose(split(rept($H$1&"+";NBVAL($H$2:$H));"+"))};{query($I$2:$I;"select * where I is not null")\transpose(split(rept($I$1&"+";NBVAL($I$2:$I));"+"))};{query($J$2:$J;"select * where J is not null")\transpose(split(rept($J$1&"+";NBVAL($J$2:$J));"+"))}})Bonjour Fil,
Encore une fois merci pour ton aide précieuse.
Tout fonctionne quasiment à la perfection à deux exceptions prêts, mais c'est parce que j'essaye de perfectionner au mieux le truc.
1. Dans la formule :
=query($C$2:$E;"select C where not D = 1 and E = 2 limit 40"
Est-il possible, plutôt que d'indiquer une valeur limite fixe, de renvoyer la fonction "limit" à la valeur d'une case ? Cela permettrait de faire varier simplement cette valeur (d'un examen à l'autre, les quotas par établissement peuvent changer) sans avoir à toucher à la formule.
2. Suite à ma dernière question (celle où je demandais comment mettre en ligne l'identité de l'élève et son établissement d'affectation), j'ai un peu réfléchi, et si je te remercie de ta réponse, ta solution "supprime" les élèves ne participant pas, or je souhaite les garder dans le listing, mais sans faire apparaitre d'affectation. J'ai donc procédé de la manière suivante : création dans une seconde feuille (intitulée 'Listes') du tableau (colonnes AK à AN) avec avec les établissements en têtes de colonnes et les étudiants répartis dans ces colonnes (ce qui correspond aux colonnes G et J dans ton exemple). Puis, dans ma feuille principale, celle avec tout le listing des étudiants, j'ai créé une colonne avec la formule suivante :
=SI(Listes!AK2:AK=$A2,"Établissement 1",SI(Listes!AL2:AL=$A2,"Établissement 2",SI(Listes!AM2:AM=$A2,"Établissement 3",SI(Listes!AN2:AN=$A2,"Établissement 4",""))))<br>
Si cela fonctionne généralement pour la première cellule, l'extension sur toute la colonne ne fonctionne pas (cela ne m'affiche rien). J'en déduis donc que pour une ligne données, disons la ligne 4, ma formule ne trouve pas la valeur de A4 dans la colonne de l'établissement 1, ni dans celle de l'établissement 2, ni dans celle de l'établissement 3 ni dans celle de l'établissement 4, alors qu'elle si trouve.
Je n'arrive pas à trouver où est mon erreur...
Avec mes remerciements.
Bonjour,
Pour avoir la possibilité d'affecter le nombre d'élèves/établissements :
=query($C$2:$E;"select C where not D = 1 and E = 2 limit "&P2&"")Pour avoir la liste intégrale avec les affectations ou non :
Liste des élèves :
=query(Calculs!$A$2:$A;"select * Where A is not null")Affectations :
=SIERREUR(byrow(query(Calculs!$A$2:$A;"select A where A is not null ");lambda(n;query(Calculs!$A$2:$M;"select M Where A is not null and L = '"&n&"' "))))Bonjour Fil,
Merci beaucoup pour cette solution qui marche très bien.
J'ai encore un soucis : la répartition dans les établissements ne se fait pas bien. Je m'explique :
- Je renseigne une valeur d'étudiants de 80 pour l'établissement 1, 25 pour l'établissement 2 et l'établissement 3, et le reste dans l'établissement 4.
- Je remplis toutes les formules parfaitement, comme les tiennes.
- Résultat, le logiciel ne me fournit que rarement les 80 étudiants pour l'établissement 1 (parfois 70, parfois 65, parfois 75...), mais quasiment jamais 80. Pour les établissements 2 et 3, pas de soucis ils ont toujours leurs 25 étudiants.
Sais-tu comment régler ça ?
Ben, ca me parait normal. La proba que tu aies un tirage de 80 sur 130 est assez faible.
La solution est malheureusement de répéter les tirages jusqu'à l'obtention d'un tirage satisfaisant et de faire un copier/coller de cette sélection...
J'ai planché sur une version totalement différente :
https://docs.google.com/spreadsheets/d/1-5Llhqxxb4nUJRMAa1KY5yoxe2LVqYiwhIpGM3rgSPo/edit?usp=sharing
En G2, crée une liste élèves/établissements :
={query($A$2:$B;"select A where B = 1 ")\{flatten(split(rept($J$2&"+";$K$2);"+"));flatten(split(rept($J$3&"+";$K$3);"+"));flatten(split(rept($J$4&"+";$K$4);"+"));flatten(split(rept($J$5&"+";$K$5);"+"))}}En K2, K3, K4 entrée des quotas (K5 se calcule automatiquement par différence).
En M2:N faire un copier/coller de G2:H après le traitement de H2:H avec Plage aléatoire (menu Données).
En P2 sort la liste des exclusions non traitées :
=SORT(UNIQUE(ARRAYFORMULA(SI(NB.SI(arrayformula(query($C$2:$D;"select C where D is not null ";0) & " " & "Établissement " & query($C$2:$D;"select D where D is not null ";0));arrayformula({(query($G$2:$G;"select * where G is not null ")) & " " & query($H$2:$H;"select * where H is not null ")}))>0;arrayformula({(query($G$2:$G;"select * where G is not null ")) & " " & query($H$2:$H;"select * where H is not null ")});))))Tout à fait d'accord, cela me parait tout à fait normal puisque le quota n'est appliqué qu'après que la fonction ALEA n'ai été réalisée. D'où ma question, est-il possible de forcer ALEA à me sortir des nombres aléatoires respectant des règles (via une autre formule qu'ALEA j'imagine) ?
Une première solution à laquelle j'ai pensé, tu me diras ce que tu en penses, serait d'utiliser une fonction SI, "N" le nombre de "1" (correspondant à l'établissement 1 auquel je dois envoyer 80 élèves) tirés au hasard par la fonction ALEA, et inférieur à 80, alors, nouvelle fonction ALEA appliquée aux élèves restants, et ainsi de suite, jusqu'à obtenir le nombre désiré.
Une autre solution : peut-on procéder par étape ? Étape 1, dans la liste d'élèves, on tire aléatoirement 80 étudiants pour l'établissement 1, puis, étape 2, dans la liste d'élèves restants, on tire aléatoirement 25 étudiants pour l'établissement 2, etc... Bien sur tout en appliquant les exclusions lorsque l'on ne souhaite pas envoyer un étudiant dans un établissement précis...
La solution des aléas en cascades présente pas mal de contraintes; c'est pourquoi j'ai préféré m'orienter vers une autre solution...
Dis-moi si tu as bien saisi la méthode avec la plage aléatoire...
Il faut que j'essaye. J'ai compris le principe ça devrait le faire.
Une question : avec cette méthode, l'étape "copier/coller" est à faire à chaque fois (l'idée étant de créer un fichier automatisé, réutilisable pour d'autres occasions, donc sur lequel je ne pourrai modifier que l'identité et le nombre total d'élèves, ainsi que les nombres d'élèves attendus dans chaque établissement) ?
Tu fais le copier/coller une seule fois après avoir saisi tes quotas et traité la plage aléatoire!
Si tu as des exclusions non traitées dans la liste, soit tu les traites manuellement soit tu refais la manip plage aléatoire/copier-coller...
Alors, la formule suivante m'indique une "erreur d'analyse de formule" :
={query($A$2:$B;"select A where B = 1 ")\{flatten(split(rept($J$2&"+";$K$2);"+"));flatten(split(rept($J$3&"+";$K$3);"+"));flatten(split(rept($J$4&"+";$K$4);"+"));flatten(split(rept($J$5&"+";$K$5);"+"))}}J'ai bien vérifié, remplacé les références des cellules comme il faut pour que ça colle à mon document, mais cela ne fonctionne pas...
Si je veux réutiliser ce fichier l'an prochain pour une nouvelle salve d'examens, cela implique que je refasse la manip' "plage aléatoire/copier-coller" ?
Il faut faire copier avec collage spécial uniquement les valeurs. J'ai oublié de le préciser...
Je crois que je n'en suis pas encore là. Je coince à l'étape à laquelle je suis censé constituer les colonnes G et H. La formule QUERY revient en erreur.
C'est peut-être parce qu'il y a des colonnes masquées dans mon tableau ?
Bonjour,
As-tu pu constituer les colonnes G et H ?
Sinon partage ta formule en erreur, afin de trouver une solution...