Problème d'optimisation de personnel + Solver

[Etienne2323]

Bonjour à tous,
je bloque actuellement sur un problème d'optimisation de personnel. Je crois que la manière de réaliser le projet est avec le solveur Excel mais je n'arrive pas à le créer.

Voici un résumé de ma problématique.
Je dois gérer un groupe de marqueurs de parties de hockey. Je dois attribuer à chacune des parties 2 marqueurs, en fonction de leurs grades, de manière à minimiser les salaires versés par l'organisation à chaque semaine.

Les marqueurs qui ont un grade 3 sont des marqueurs principaux et les marqueurs avec des grades 1 ou 2 sont des assistants marqueurs.

Toutes les contraintes sont présentées dans l'onglet Excel.

En espérant que le problème soit clair lorsque vous ouvrirez le fichier Excel.

En vous remerciant beaucoup,

Étienne

 

[david84]

Re : Problème d'optimisation de personnel + Solver

Bonjour Etienne,
ne connaissant rien au Hockey (qu'est-ce qu'un marqueur ? Un arbitre ou une personne officiant à la table de marque ?), je pense que le plus simple serait que tu remplisses manuellement ton fichier avec les résultats attendus dans ton exemple. Il serait ensuite plus facile de comprendre ton attente et de voir ensuite comment automatiser la procédure.
J'ai vu que ton fichier contenait des formules : préfères-tu une solution formules ou VBA ?
A+

 

[Etienne2323]

Re : Problème d'optimisation de personnel + Solver

Salut David,
merci pour la réponse.

Un marqueur est simplement une personne qui s'occupe du chronomètre, qui marque les points, entre les punitions, etc...

Je vais remplir le tableau rapidement et je le post tout de suite après.

Merci,

Étienne

 

[Etienne2323]

Re : Problème d'optimisation de personnel + Solver

Salut David, le forum,
je viens de remplir la section pour les joutes du samedi. Il resterait encore la section du dimanche à faire mais sans le solver, c'est un vrai casse-tête pour remplir toutes les contraintes.

Ce qu'il faudrait, c'est que le solver inscrive les numéros d'employés de manière à optimiser toutes les contraintes, soit les cases que j'ai mis en vert.

Je me demande simplement comment bâtir mon solver pour les cases en vertes se remplissent avec les numéros d'employés plus bas. Pour ce qui est de faire la liste de contraintes dans le solver, je crois que j'y arriverai.

Pour ce qui est de la solution en formules ou en VBA, peu m'importe ! Le plus efficace sera le mieux !
Encore une fois, un gros merci !

Étienne

 

[david84]

Re : Problème d'optimisation de personnel + Solver

Re
pas évident à comprendre et encore moins à mettre en œuvre ton fichier...
Bon, si j'ai bien compris, tu as 5 paramètres à gérer : le grade, le nombre de parties, auto ou pas auto, le lieu de résidence du marqueur par rapport au lieu de la rencontre et les horaires des matchs. Le problème, c'est que lorsque je regarde ton exemple pour les grades 3, je ne sais pas dégager la priorité parmi tous les paramètres. Or, je pense que c'est précisément cela que tu dois déterminer : quel est le 1er paramètre prioritaire ? Quel est le paramètre que l'on doit considérer ensuite comme le 2ème prioritaire, etc. Si par exemple, je ne prends que 3 paramètres (grade=3, partie=2,auto="OUI") et que je l'applique à une formule, il me sort comme 3 premiers résultats les n° 5,9 et 2 (or il est noté dans ton exemple 5, 9, 5 puis 2)...
Il te faut donc à mon avis définir des scénarii par ordre de priorité (par ex scénario priorité 1 : grade=3, partie=2,auto="OUI", scénario priorité 2 :grade=3, partie=1,auto="OUI", scénario priorité 3 :grade=3, partie=2,auto="NON",... Quand les n° d'employés répondant au scénario 1 sont tous placé, passer au scénario 2, puis au 3,...
Je ne sais pas si je suis bien clair.
Mais avant d'avancer sur ton projet il t'appartient de définir l'ordre des priorités car toi seul le connaît.
A+

 

[Etienne2323]

Re : Problème d'optimisation de personnel + Solver

Salut David, le forum.
effectivement, tu as raison.

En fait, l'idéal serait que je change ma question. Cela me permettrait d'essayer de construire le solver et ensuite pouvoir intégrer toutes les contraintes, pouvant ainsi intégrer l'ordre de priorités des critères. Je sais que plusieurs contraintes ne pourront pas être remplies. Il faudra donc que j'établisse les critères qui pourront être transgresser si jamais on ne peut remplir toutes les contraintes. Mais ça, je m'en charge.

La vraie question serait celle-ci : Comment puis-je dire au solver ceci : Le numéro d'employé doit être un numéro d'employé provenant de la liste se trouvant en C55:C73 ?

Merci énormément,

Étienne

 

[david84]

Re : Problème d'optimisation de personnel + Solver

Re

La vraie question serait celle-ci : Comment puis-je dire au solver ceci : Le numéro d'employé doit être un numéro d'employé provenant de la liste se trouvant en C55:C73 ?

Non, à mon avis le plus compliqué n'est pas de lui dire cela mais lui donner l'ordre de priorité. Si tu établies clairement l'ordre de priorité le reste devrait suivre...en formule comme en VBA.
Fais tes choix en établissant les différents scénarii par ordre de priorité comme expliqué dans mon dernier post et la presque totalité des données seront automatisées. Il restera ensuite à remplir les dernières case vides en gérant les cas qui restent (enfin c'est comme cela que je vois les choses personnellement).
Vois le fichier colonne U,V et W. J'ai fait un début de formule simplement pour voir (1ère colonne : une condition, 2ème colonne : 2 conditions, 3ème colonne : 3 conditions, simplement pour tester (certaines colonnes sont nommées))...
A+

 

[Etienne2323]

Re : Problème d'optimisation de personnel + Solver

Salut David,
merci encore une fois de cette réponse.

Voici donc comment je procéderais pour l'ordre de priorités. Il faut partir avec l'idée qu'on manque de grade 3, donc c'est certain que plusieurs parties ne pourront pas avoir deux marqueurs, et qu'ils auront donc seulement un assistant marqueur.

1) Les déplacements : Les marqueurs qui n'ont pas de voiture et qui doivent rester soit dans l'est de la ville ou dans l'ouest de la ville sont la priorité #1.

2) Les grades : Il faut essayer s'assurer de mettre un marqueur grade 3 dans toutes les parties possibles. On a 6 marqueur grade 3 donc on peut couvrir 12 parties avec 2 marqueurs. Les autres parties devront nécessairement se déroulement avec seulement un assistant marqueur.

3) Le nombre de partie maximal par fin de semaine : Les marqueurs ne peuvent être employés plus de deux fois par fin de semaine.

4) La gestion du salaire : Il est plus important de gérer l'allocation des marqueurs que le coût qui leur est relié. Si tout est OK, alors on doit appliquer toutes les contraintes relatives aux salaires.

Encore une fois, un gros merci David,

Cordialement,

Étienne

 

[david84]

Re : Problème d'optimisation de personnel + Solver

Re
ci-joint 1er essai histoire de me familiariser avec ta problématique (pas facile !). Pour l'instant, je n'ai pas testé, j'ai juste essayé de comprendre et de voir si les résultats ramenés via les formules concordaient à peu près avec les résultats manuels.
Compare, vérifie les résultats teste au besoin en modifiant les données et dis-moi ce qu'il en est.
A+

 

[Etienne2323]

Re : Problème d'optimisation de personnel + Solver

Salut David,
c'est absolument génial ce que tu as créé là. L'ordre de priorités par formules, c'est exactement ce que j'avais besoin. Un immense merci pour ça ! Je crois que je devrais être capable, en suivant ton modèle, d'intégrer la journée de dimanche. Si jamais il y a quoi que ce soit et que je bloque de nouveau, je te fais signe !

Encore une fois, un gros merci pour tout !

Cordialement,

Étienne