Attribution de prestations de cours en fonction des indisponibilités

Bonjour,

C'est la galère au boulot avec les attributions de prestations aléatoires qui ne tiennent pas compte des indisponibilités des formateurs. Pourriez-vous m'aideeer ....

Est-il possible d'attribuer des prestations automatiquement à chaque formateur en fonction de leurs indisponibilités et en fonctions des différentes plages horaire (8h30-12h30 / 13h30-17h30 / 18h00-22h00) ?

Pour l'instant, nous fonctionnons par semestre.

Je vous joins un tout petit exemple de notre tableau actuel mais que nous remplissons à la mains prestation par prestation.

J'aimerais pouvoir encoder les indisponibilités des formateurs (peu importe la raison, vacances, personnel, formation, ...) et que le tableau attribue automatiquement à chaque formateur un nombre minimum de prestations ou d'heures dans les différents cours. Est-ce que cela est possible?

Je suis toute ouïe à vos propositions !

Merci,

Zabou

Bonjour et bienvenue sur le forum

Pourrais-tu donner un exemple de ce que tu attends car j'avoue ne pas comprendre...

Bye !

Bonjour, salut gmb !

C'est un sujet complexe, voilà un essai :

Dans les grandes lignes :
- Transformation de tous les tableaux en tableaux structurés
- Ajout d'un onglet avec une table pour recenser les indisponibilités/absences (données bidons pour le test)
- Ajout d'une table bilan qui totalise les heures affectées à chaque formateur + attribue un ID unique à chacun
- Ajout de colonnes pour déterminer la date + heure de début et fin de chaque prestation, dans un format exploitable par un tableur (texte)
- Ajout une colonne qui recense les éventuelles indisponibilités du formateur sur le créneau (1 si formateur absent ou déjà affecté sur un autre créneau qui empiète sur celui-ci)
- Enfin : création d'un scénario d'optimisation dans le Solveur qui test des milliers de combinaisons d'affectations

Note : l'optimisation est guidée par la cellule P15, avec pour objectif de minimiser sa valeur. Celle-ci totalise les indisponibilités de la solution actuelle (colonne J) ainsi que 10% de l'écart-type entre durées totales de prestation de chaque formateur (dans l'optique d'équilibrer au maximum les heures affectées, mais sous contrainte de disponibilité). Dans l'exemple actuel, le formateur A est absent une majeure partie de la semaine, donc on ne peut l'affecter que sur un nombre limité de créneaux, ce qui explique qu'il ait moins d'heures de prestation que les autres (et dégrade donc l'écart type).

Bonjour Pedro22,

Par curiosité, comment fonctionne la création du scénario par le solveur sur le fichier, que tu as joins?

J'ai testé le solveur et il me donne un résultat en P15 de 0.31 différent du 0.18 que vous avez obtenu ? Seriez vous pourquoi?,

Merci d'avance,

Cordialement,

Bonjour Massari !

Peux-tu préciser ta question ? Comment fonctionne le Solveur en général ? Comment j'ai paramétré les contraintes ou la fonction objectif pour le cas présent ?

OK je viens de voir que tu as édité ton message. Dans le cas présent, on utilise le moteur de calcul Evolutionnaire, car on optimise pas une fonction continue. En clair, à il y a des "sauts" dans le résultats de la fonction entre 2 combinaisons proches, lié par exemple à l'apparition d'une indisponibilité dans la combinaison testée (+1 à la fonction objectif) qui peut disparaitre à la suivante. Tu n'aurais pas ce genre de problème en déterminant par exemple les meilleurs paramètres d'une équation de régression (dans ce cas la fonction objectif évolue de manière continue à l'approche de la solution optimale).

L'autre conséquence, c'est qu'il existe plusieurs combinaisons possibles qui donne un résultat équivalent. D'autre part, du fait du nombre très important de combinaisons possibles, Excel ne les teste pas toutes. Il tâtonne sur des combinaisons proches lorsque le résultat de la fonction objectif s'améliore. C'est pourquoi tu peux obtenir des résultats différents à chaque exécution du Solveur. Parfois il converge vers des solutions optimales qui ne ne correspondent pas à la meilleure solution possible, faute d'avoir pu tout tester. Ce risque augmente à mesure que le nombre d'éléments à optimiser augmente (par exemple 20 formateurs au lieu de 15 ou 50 créneaux à affecter au lieu de 20). Sur des cas trop complexes et/ou avec trop de combinaisons, le Solveur devient même complètement inefficace.

Merci Pedro 22 pour ton retour,

Je ne connaissais pas l’utilisation du solveur n'ayant jamais eu besoin à ce jour, mais on ne sait jamais,

Je prends bien note de tes remarques,

Cordialement,

Bonjour tout le monde. Déjà, un grand merci pour vous être penché sur mon problème si rapidement. Vous êtes top!

J'avoue que je n'ai pas tout compris (). Je vais me pencher sur le tableau. Je vous tiens au courant.

Bonjour tout le monde. Déjà, un grand merci pour vous être penché sur mon problème si rapidement. Vous êtes top!

J'avoue que je n'ai pas tout compris (). Je vais me pencher sur le tableau. Je vous tiens au courant.

Le principal élément à retenir, c'est l'utilisation du Solveur qui vise à donner la meilleure solution (ou du moins une solution acceptable) sous contraintes (ici de disponibilité et d'équilibre entre formateurs). Tu trouveras des explications complémentaires en suivant le lien de mon 1er post vers un cours sur le Solveur (mais il existe beaucoup d'autre sujets qui traite du Solveur sur internet, si besoin).

Si besoin, je peux détailler les formules utilisées et le paramétrage du Solveur.

bonjour,

je commence avec de félicitations.

La colonne J, disponibilités, n'est pas 100%. Supposons une indisp; de 14:00 à 15:00 pendant un cours de 13:00 à 18:00, la formule ne detecte que les 2 premiers des (4?) situations,

schermafbeelding 2022 06 17 140300

Re bonjour, je ne suis pas très douée ... j'ai inséré le nom des formateurs (Nom prénom) dans le tableau des indisponibilités mais dans le tableau des affectations, rien ne se passe, il y a toujours "formateur A" formateur B ...).

Et une autre question, est-il possible d'encoder dans le tableau des indisponibilités des indispos récurrentes comme ( Pierre jamais dispo le lundi, Cécile, jamais dispo les soirées des semaines impaires, Robert jamais dispo le vendredi matin ...). J'en demande beaucoup je crois

Bonjour Zabou22, je viens de lire ton dernier poste. Euh, comment dire, je suis une vraie clinche en formules excel (je maitrise quand-même les sommes automatiques , tu vois le niveau! Je veux bien toute l'aide que je peux avoir

Salut @BsAlv, tu as tout a fait raison ! La formule initiale ne tient pas compte de tous les cas de figure. Voilà une nouvelle version qui devrait corriger ce défaut :

=NB.SI.ENS(TabIndispo[Noms formateurs];[@[Noms formateurs]];TabIndispo[Début indisponibilité];"<="&[@Fin];TabIndispo[Fin indisponibilité];">="&[@Debut])+(NB.SI.ENS([Debut];"<="&[@Fin];[Fin];">="&[@Debut];[Noms formateurs];[@[Noms formateurs]])-1

@Zabou22 : le vrai nom des formateur est à remplacer dans TabIndispo et dans TabRécap. Les 2 tableaux ne sont pas liés, donc la mise à jour d'un seul de 2 ne permettra pas de changer l'autre.

Il n'y a pas de formule miracle pour les absences récurrentes, tu devras les traiter au cas par cas. Du moins, on peut certainement les déduire par formule, mais il faudra probablement autant de formules que de cas de figure (1 formule dédiée aux jours impaires, 1 formule dédiée aux lundis, 1 pour les vendredi après midi, etc). Pour les paires et les récurrences, creuser du côté de la fonction MOD, pour les jours de la semaine, JOURSEM, pour les numéros de semaine, NO.SEMAINE ou NO.SEMAINE.ISO...

Un exemple de récurrence (absence du formateur A les vendredi matin, une semaine sur 2) dans le fichier en PJ (lignes en rouge de la feuille N°2). Attention, il faudrait copier-coller les valeurs en dur après pour éviter les erreurs si le tableau est trié différemment par la suite.

Bonjour Zabou22, je viens de lire ton dernier poste. Euh, comment dire, je suis une vraie clinche en formules excel (je maitrise quand-même les sommes automatiques , tu vois le niveau! Je veux bien toute l'aide que je peux avoir

Il y a quand même pas mal de choses à intégrer, je suggère vivement de te former (par toi même et/ou via une/des session(s) de formation) afin d'être un minimum autonome sur l'utilisation et la maintenance de ton fichier.

Dans mon 1er post, je t'ai déjà mis un lien vers un cours sur les tableaux structurés. Leur utilisation est facultative, mais ils permettent néanmoins une adaptation automatique des formules en cas d'évolution de la dimension des tableaux (+ de lignes de cours ou de formateurs par exemple). Ils permettent également une syntaxe spécifique des formules qui facilitent grandement leur compréhension (très utile dans le cas de formule à rallonge, comme c'est le cas ici).

Idem pour le Solveur, je t'ai mis un lien. On peut faire le parallèle entre le Solveur et un GPS, qui détermine parmi des centaines ou des milliers d'itinéraires possibles, le meilleur (sous contrainte qu'il soit praticable en voiture par exemple, ou ouvert à la circulation). Ici c'est le même principe, mais on cherche une affectation idéale des formateurs, avec des contraintes spécifiques (disponibilité, 1 seul cours en même temps, et une répartition la plus homogène possible entre formateurs).

Dans le détail :
- La formule en colonne J identifie si le formateur choisi est indisponible sur le créneau de cours, auquel cas elle renvoie 1
- Toujours en colonne J, la seconde partie de la formule vise à identifier si le formateur choisi est déjà affecté à un autre cours qui se déroule tout ou partie en même temps. Si oui, elle renvoie 1 également
- En P15, la formule synthétise toute l'information en une valeur unique, qui sert à guider le Solveur (comme le temps de trajet que l'on cherche à minimiser avec un GPS). Cette formule est découpée en 2 parties : la première totalise les indisponibilités et cours en doublon de la colonne J (plus il y en a, plus la valeur augmente). La deuxième moitié de la formule calcul l'écart-type de la durée totale de formation entre formateurs. Plus la durée est hétérogène, plus la valeur augmente. J'ai arbitrairement appliqué un coefficient de 10% à cette valeur pour qu'elle ne prennent pas trop de poids dans la solution optimale. En effet il est plus important de n'affecter que des formateurs disponibles, et sans doublon de cours sur un même créneau, plutôt que de privilégier une stricte équité des durées de formation.

Pédro22. Merci pour tous ces conseils. Je me penche sur le problème la semaine prochaine. Je ne manquerai pas de vous faire un retour.

Bon we à tous!

Bonjour Zabou22,

As-tu eu l'occasion de te plonger dans le fichier ?

Rechercher des sujets similaires à "attribution prestations cours fonction indisponibilites"