comment retenir 2 voeux parmi 4 en fonction des places disponibles

[excelnubie]

Bonsoir à tous,
je piétine depuis des mois sur un problème qui me dépasse, si quelqu'un peut m'aider je l'en remercie par avance :
Je propose aux élèves d'un lycée de s'inscrire sur des ateliers différents par un formulaire que je récupère au format excel. Les élèves font 4 voeux par ordre de priorité dans les ateliers proposés et je dois en retenir 2. J'ai trois contraintes :
1. les ateliers ne doivent pas dépasser 18 personnes
2. les inscriptions se font sur la base de l'horodatage
3. il me faut établir les listes par atelier (dans des onglets par exemple)
Je joins le fichier qui me sert de base : les colonnes V1 et V2 correspondent aux voeux retenus.
Les formules que j'utilise ne me permettent que de compter les voeux pour chaque atelier mais je dois faire le reste à la main sur près de 1000 enregistrements dès l'année prochaine...
Si Les super forts du forum sont intéressés par mon premier post (de ma vie !) ce serait déjà pas mal...
Merci.

 

[casamartin]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonjour Mr. X,

Pour que l'on puisse t'aider, il manque certains renseignements pour les opérations de filtrage et tri, notamment les critères de choix par ordre priorité, indiquer pour cela le prossessus complet manuel.

Pour indication:
Pour les opérations de filtrage et tri, il faut présenter le tableau différemment.
Voir modèle:
Feuille "Journal" En colonne les inscriptions, en ligne les Cours.
18 autres feuilles avec liaisons, 1 par atelier.
Sauvegarder en fichier avec macro (.xlsm) pour les opérations répétitives.
En attendant d'autre informations.

A+

 

[excelnubie]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonjour casamartin,
ça fait plaisir d'avoir une autre vue sur le problème et surtout aussi vite...
Je retiens l'idée bien que ce n'ait pas été ce que j'avais envisagé au départ...
En fait, les critères de choix des voeux sont les suivants :
on retient tous les voeux 1 jusqu'à compléter chaque atelier jusqu'à 18 inscriptions,
ensuite, pour ceux dont le voeu 1 est celui d'un atelier déjà complet on enregistre son voeu 2 s'il n'est pas complet aussi voire son voeu 3 ou 4.
Au final, on remplit ainsi 2 colonnes V1 et V2 avec les voeux (copiés sous conditions) et la liste peut être donnée aux élèves tel quel.
Cependant, par atelier, il faut une liste des inscrits retenus (dont pourrait par exemple ventiler les résultats en onglets).
J'ai planché sur une formule qui permet de copier automatiquement les voeux lorsqu'ils sont conforme à la condition mais dès que la condition n'est pas remplie, le voeu n'est pas copié... Ca marche pour tous les ateliers demandés moins de 19 fois. Ca ne marche pas pour ceux qui sont demandés plus de 18 fois...
Je t'envoie le tableau avec mes essais de formules si ça peut aider...
Cela dit, ta proposition m'a déjà fait beaucoup avancer...
A+

 

[casamartin]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Votre tableau qui est en fait peut se traiter comme un planning de charge de travail.
Le nombre d'élèves correspond à la charge de travail, 18 maxi.
L'atelier, le poste de travail.
L'enseignant, l'opérateur.
La date le numéro d'ordre.
Pouquoi faire ce qui existe déjà et l'adapter.
Vous pouvez éventuellement faire une recherche d'un modèle de planning excel pour monter votre structure.

Par contre pour éclaicir votre tableau et réduire la hauteur des lignes (il vous faudrait un écran de cinéma).
Passer en commentaire dans la cellule les détails des cours (choses inutiles) et les remplacer dans la cellule par Cou01, Cou02, Cou03,... pour les cours, Ate01, Ate02, Ate03.. pour les ateliers, Ens01, Ens02.. pour les enseignants, Voe01, Voe02.. pour les voeux, Cho01,Cho02 pour les choix. (3 lettres + 2 chiffres,évite la confusion avec les nom de cellule, soit toujours 5 caractères pour éventuellement faire des concaténages et découpages)
Plus facile à gérer par formule que des textes à rallonge.
Si vous pouvez me le simplifier de cette manière je pourrais vous suivre.
A+

 

[casamartin]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Partons sur un tableau simple avec 5 ateliers, 40 éléves,...

A+

 

[Grand Chaman Excel]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonjour excelnubie,

Une proposition avec macro. J'ai ajouté quelques colonnes dans le fichier pour travailler avec des numéros (1, 2, 3, ...) au lieu des noms des ateliers et des élèves.

La macro pourrait s'adapter facilement avec une mise en forme du fichier. Il pourrait même y avoir une automatisation selon le nombre d'élèves, d'ateliers, ... Bref, ce n'est pas encore complet à 100% mais la base est là et ça donne une idée de ce que la macro peut faire.

Le bouton pour la macro et les résultats apparaissent dans l'onglet "Feuil 1".

A+

 

[excelnubie]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonsoir,
tout à fait d'accord avec l'idée que les noms à rallonge ne simplifient rien et rendent illisible le tableau...
Je propose une version simplifiée avec 5 ateliers et 50 élèves qui ont tous fait 4 voeux pour ces 5 ateliers (n'importe comment puisque c'est une formule aléatoire qui a nommé les ateliers...)
Le but du jeu c'est de n'avoir aucun atelier au dessus de 18 personnes en exploitant la souplesse de 4 voeux faits pour 2 retenus.
Les élèves sont dans l'ordre d'inscription (horodatage). La priorité est donnée aux premiers inscrits...
On essaie de donner les deux premiers voeux demandés mais si ce n'est pas possible (au-delà de 18 personnes dans l'atelier) alors on essaie avec le voeu 3 ou le voeu 4...
Je ne sais pas si c'est plus clair, mais pour moi, c'est un peu la forme que prendra le tableau issu du formulaire sur lequel je travaille car la complexité actuelle (trop d'infos sur une seule case) gêne trop la lecture.
Par contre je veux bien réutiliser un planning que tu proposes mais je ne sais pas où en trouver un à adapter...
Si tu as un exemple...
Merci en tout cas pour cette vue simplifiée qui m'aide moi aussi à y voir plus clair...
A+

 

[excelnubie]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonsoir Grand Chaman Excel,
C'est une macro magique qui fait tout en un clin d'oeil... Je suis rudement impressionné !
J'ai besoin de temps pour explorer la piste (du grand Chaman...) parce que je n'ai pas tout compris mais ça marche c'est sûr...
Bonne analyse en tout cas parce que le voeu redemandé plusieurs fois ne compte qu'une fois en effet, il se peut que des ateliers ne soient nullement demandés (ils n'ont pas lieu).
J'ai cependant quelques questions devant cette machine automatisée fascinante :
Comment extraire les valeurs uniques des 4 colonnes des voeux ?
Comment passe-t-on d'un tableau comme le mien à un tableau qui indique les ateliers en colonnes ?
Comment exécuter la même macro sur un tableau différent : je veux dire sur le prochain tableau d'inscription qui aura la même structure mais avec des voeux différents...
Comment avoir la liste des élèves de chaque atelier sous la forme simple d'une fiche "atelier1" : avec la liste des inscrits ?
Question subsidiaire : Comment on fait pour devenir aussi bon sur excel ? (Faut faire de la magie sinon c'est pas correct avec les autres !)
Bonne soirée et merci encore.

 

[Grand Chaman Excel]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonsoir excelnubie,

Bien content de voir que la macro donne le résultat escompté...
Je peux continuer à t'aider si tu veux. Comme j'avais indiqué, la base est là, maintenant on peut construire dessus et compléter le travail.

Pour répondre à tes questions :

Comment extraire les valeurs uniques des 4 colonnes des voeux ?
Comment passe-t-on d'un tableau comme le mien à un tableau qui indique les ateliers en colonnes ?

Là je l'ai fait manuellement mais une macro pourrait le faire.

Comment exécuter la même macro sur un tableau différent : je veux dire sur le prochain tableau d'inscription qui aura la même structure mais avec des voeux différents...

Encore une fois, avec une macro. Si la structure reste là même (ordre des colonnes, nom de l'onglet, etc...), il serait "facile" d'adapter la macro.

Comment avoir la liste des élèves de chaque atelier sous la forme simple d'une fiche "atelier1" : avec la liste des inscrits ?

Encore une fois, avec une macro, il serait possible de soit :
1) créer un onglet par atelier ou
2) une colonne par atelier ou
3) un onglet avec une liste déroulante où tu choisis l'atelier et la liste des élèves se met à jour

Question subsidiaire : Comment on fait pour devenir aussi bon sur excel ? (Faut faire de la magie sinon c'est pas correct avec les autres !)

Question la plus difficile... Avec un peu (beaucoup) de pratique, quelques (peut-être trop) d'heures passées devant Excel on arrive à transposer en langage VBA notre raisonnement.

A+

 

[excelnubie]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonjour GC,
merci pour ces réponses.
Moi je n'en suis qu'aux formules et pas encore au VBA mais je vais m'y mettre vu la puissance du langage...
Une ou deux remarques :
1. les onglets par ateliers sont une bonne solution pour moi car ils permettent de donner une ou plusieurs liste(s) pour chaque prof et fixer clairement qui fait quoi où et quand.
2.Il est inutile d'adapter la macro à un autre tableau de même structure car je n'ai qu'à copier et coller les nouvelles inscriptions dans ce tableau-ci... je viens de m'en rendre compte...
3. Le prochain tableau d'inscriptions contiendra la conversion atelier et n° de manière directe car c'est à partir du formulaire que je construis que le codage aura lieu : Choix 1 parmi les ateliers de 1 à 34, choix 2 pareil, etc.
Du coup mon tableau sera déjà constitué de N° d'ateliers ce qui simplifiera la conversion.
4. Tous les renseignements liés à chaque atelier sont sur un autre tableau excel du type
LU,S1,Salle 10,Mme Machin,ANG,Intitulé
je pourrais donc faire correspondre les N° d'ateliers et les détails qui leur sont associés aux deux colonnes de choix retenus. Ca me permettra d'affiner recherchev que j'ai compris grâce à ton travail...
5. Est-il possible de régler le seuil d'admission dans les ateliers (16 pour tous les ateliers au lieu de 18 par exemple) par une case qui contiendrait ce seuil ?
6. Enfin, pour rationaliser au mieux les ressources on pourrait introduire un seuil inférieur de 8 élèves/atelier ce qui complexifie encore fortement l'ensemble (moi je suis déjà un peu hors de mon niveau...) Du coup les critères de passage à un autre choix dépendent aussi de la répartition d'ensemble... Mais bon là c'est pour fignoler...
7. Là je pense également à une autre méthode possible : est-ce que le filtrage en amont (dans le formulaire par exemple) ne résoudrait pas le problème plus aisément (sauf que je ne sais pas faire le formulaire avancé dont j'ai besoin...) ? Je pense à un formulaire en ligne (pour le moment j'utilise Google docs... qui n'a pas cette fonctionnalité) qui ne proposerait plus les choix dès que la limite serait atteinte de 18 personnes inscrites... Mais bon là on dépasse peut-être ce que peut faire excel...
En tout cas je suis preneur d'une aide pour affiner ce qui a déjà été construit sachant que ce n'est pas seulement pour moi que nous avons travaillé (bon, surtout toi, et j'y gagne du temps..., beaucoup même) mais parce que favoriser le choix des élèves est plus sympa (et efficace) que de les inscrire d'office dans n'importe quel atelier par ordre alphabétique...
Merci et A+

 

[Grand Chaman Excel]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bon matin excelnubie,

1. les onglets par ateliers sont une bonne solution pour moi car ils permettent de donner une ou plusieurs liste(s) pour chaque prof et fixer clairement qui fait quoi où et quand

OK

3. Le prochain tableau d'inscriptions contiendra la conversion atelier et n° de manière directe car c'est à partir du formulaire que je construis que le codage aura lieu : Choix 1 parmi les ateliers de 1 à 34, choix 2 pareil, etc.
Du coup mon tableau sera déjà constitué de N° d'ateliers ce qui simplifiera la conversion.
4. Tous les renseignements liés à chaque atelier sont sur un autre tableau excel du type
LU,S1,Salle 10,Mme Machin,ANG,Intitulé

Super. Fais moi parvenir ton fichier avec ce tableau lorsqu'il sera prêt.

5. Est-il possible de régler le seuil d'admission dans les ateliers (16 pour tous les ateliers au lieu de 18 par exemple) par une case qui contiendrait ce seuil ?

Oui, facile.

6. Enfin, pour rationaliser au mieux les ressources on pourrait introduire un seuil inférieur de 8 élèves/atelier ce qui complexifie encore fortement l'ensemble (moi je suis déjà un peu hors de mon niveau...) Du coup les critères de passage à un autre choix dépendent aussi de la répartition d'ensemble... Mais bon là c'est pour fignoler...

Sûrement possible, mais va falloir que j'y réfléchisse...

7. Là je pense également à une autre méthode possible : est-ce que le filtrage en amont (dans le formulaire par exemple) ne résoudrait pas le problème plus aisément (sauf que je ne sais pas faire le formulaire avancé dont j'ai besoin...) ? Je pense à un formulaire en ligne (pour le moment j'utilise Google docs... qui n'a pas cette fonctionnalité) qui ne proposerait plus les choix dès que la limite serait atteinte de 18 personnes inscrites... Mais bon là on dépasse peut-être ce que peut faire excel..

. ... rendu là, ça dépasse mes compétences... pas mal certain que Excel peut le faire.

A+

 

[excelnubie]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Salut à toi Grand Chaman, et à tous ceux du forum également,
Voici mon modeste travail à partir d'une formule que je ne savais pas maîtriser (Recherchev)
voir en PJ aux colonnes AU et AV feuil1 pour redonner le vrai nom de l'atelier à partir du
N°... Bon c'est modeste mais ça prouve que je progresse...
Voici plus sérieusement le fichier de préparation qui est renseigné par les profs en amont de l'inscription pour éviter les erreurs : les ateliers sont numérotés de 1 à 31 par ordre chronologique de LUNDI M1 à VENDREDI S4 avec une colonne par élément descriptif de l'atelier.
La macro actuelle fonctionne-t-elle pour un nombre plus important d'enregistrements (disons 350 par exemple...) et pour un nombre plus important d'ateliers (disons 60...) ? Ou bien faut-il fixer cela à l'avance ?
Merci.
A+

 

[Grand Chaman Excel]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonjour excelnubie,

Ayant en main la liste de correspondance des ateliers, il sera maintenant facile d'adapter la macro.
La macro pourra fonctionner peut importe le nombre d'enregistrements et d'ateliers.
Je continue de regarder ça dès que je peux...

A+

 

[Grand Chaman Excel]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Bonjour excelnubie,

Voici une version améliorée qui devrait fonctionner peut importe le nombre d'ateliers ou d'élèves. Il y a aussi moyen d'optimiser si le nombre minimum d'inscription n'est pas atteint.

Je te conseille de lire les instructions dans l'onglet "Paramètres". Le format de l'onglet Reponses est important.
Il reste encore quelques manipulations "manuelles" à faire et à générer la liste d'inscription par atelier, mais pour le reste, ça semble bien fonctionner.

A+

 

[excelnubie]

Re : comment retenir 2 voeux parmi 4 en fonction des places disponibles

Impressionnant...
J'en oublie de saluer avant d'écrire... Salut au grand Chaman !
Il faut que je vérifie tout ceci à tête reposée... je te confirmerai demain soir ou dimanche si aucune erreur n'est venue se glisser dans la machine superbe que tu as construite... A priori, c'est sans problème (mais je n'ai pas essayé tous les paramètres, ni vérifié tous les cas de figure...)
Quelques remarques :
Les paramètres non modifiables sont pourtant variables... Les élevés seront en tout cas plus nombreux à partir de septembre... au moins 350... davantage encore l'année suivante...
les ateliers sont toujours proportionnés pour qu'une marge de choix soit possible en respectant les critères de la demi classe maximum par atelier...
En revanche multiplier le nombre de voeux est inutile même si passer de 4 à 5 augmente la souplesse le sentiment est toujours le même lorsqu'on n'obtient pas les premiers voeux...
A priori le nombre de voeux retenus doit rester 2...
Je n'ai pas bien compris comment fonctionnait l'optimisation (la procédure appliquée, pas le code, j'en suis pas là ) mais elle produit un effet...
Par contre comment sait-on quels élèves sont à replacer éventuellement ?
Dans l'onglet "réponses", les colonnes K à N sont elles utiles si les colonnes E à H sont renseignées par des N° identiques dès le début ?
On n'est peut-être pas obligé de concaténer le tableau des ateliers car il est plus lisible en tableau que concaténé...
L'idée est d'afficher (puis dans une étape ultérieure d'envoyer par mail) la liste des élèves par ordre alpha avec les deux choix retenus et les détails essentiels (jour heure salle prof intitulé) et d'envoyer parallèlement les listes des élèves aux profs des ateliers (sachant que parfois un profs fait trois ateliers dans la semaine avec des élèves différents mais un même intitulé).
En tout cas chapeau bas pour une telle analyse et un tel résultat...
Merci.
A bientôt.
trop cool... Trop cool...