XL pour MAC Planification équitable

[Mickey Mouse]

Bonjour à toutes et tous .

Je me permets de vous solliciter à nouveau en vous remerciant par avance de l'intérêt que vous porterez à mon problème :

Je suis chargé de réfléchir à la meilleure façon de planifier quotidiennement du lundi au vendredi des missions de 3 agents pris parmi une équipe de 20 .

J'ai donc calculé le nombre de combinaisons possibles et j'aimerais répartir équitablement idéalement au mois , voire au trimestre les trinômes ( exemple le lundi 1,2 3 - Le mardi 7,8,9 - mercredi 11,,4,6 etc ... Afin que tous les agents participent équitablement aux missions ...

Je ne sais pas si quelqu'un à la solution à mon problème , mais moi je sèche ...

Je vous joins le fichier avec la liste des combinaisons possibles en jetant la bouteille à la mer ...

En vous remerciant

 

[JHA]

Bonjour à tous,

Avant d'aller plus loin, un début de piste.

JHA

 

[job75]

Bonjour Mickey Mouse, JHA

Il faut en effet commencer par créer la liste des combinaisons (il y en a 1140).

Ensuite il faut réaliser un tri aléatoire de cette liste

Enfin on crée des rotations sur cette liste pour avoir le nombre de jours désiré.

Voyez le fichier joint et ces 2 macros :

Sub Combinaisons()
Dim N, lig&, i, j, k, a(1 To 4)
If Val([B1]) < 3 Then [B1] = 3
N = Val([B1])
lig = 1
Application.ScreenUpdating = False
Range("C2:F" & Rows.Count).ClearContents 'RAZ
Randomize
For i = 1 To N - 2
    For j = i + 1 To N - 1
        For k = j + 1 To N
            a(1) = Rnd 'nombre aléatoire
            a(2) = i: a(4) = j: a(3) = k
            lig = lig + 1
            Cells(lig, "C").Resize(, 4) = a
Next k, j, i
N = Application.Combin(N, 3) 'nombre de combinaisons
Cells(2, "C").Resize(N, 4).Sort Columns("C"), Header:=xlNo 'tri aléatoire des combinaisons
Cells(2, "C") = 1
Cells(2, "C").Resize(N).DataSeries 'numérotation
End Sub

Sub Rotations()
Dim N, lig&, i&, a(1 To 4)
Combinaisons
If Val([H1]) < 1 Then [H1] = 1
N = Val([H1])
lig = 1
Range("I2:L" & Rows.Count).ClearContents 'RAZ
Do
    For i = 2 To Application.Max(Columns("C")) + 1
        If lig > N Then Exit Sub
        a(1) = lig: a(2) = Cells(i, 4): a(3) = Cells(i, 5): a(4) = Cells(i, 6)
        lig = lig + 1
        Cells(lig, "I").Resize(, 4) = a
    Next i
Loop While lig < N
End Sub

A+

 

[Mickey Mouse]

Un immense merci job75.
Belle journée ensoleillée

 

[job75]

Pour mieux comprendre comment les combinaisons sont créées il est préférable de faire le tri aléatoire après les rotations, voyez ce fichier (2) :

Sub Combinaisons()
Dim N, lig&, i, j, k, a(1 To 4)
If Val([B1]) < 3 Then [B1] = 3
N = Val([B1])
lig = 1
Application.ScreenUpdating = False
Range("C2:F" & Rows.Count).ClearContents 'RAZ
For i = 1 To N - 2
    For j = i + 1 To N - 1
        For k = j + 1 To N
            a(1) = lig: a(2) = i: a(3) = j: a(4) = k
            lig = lig + 1
            Cells(lig, "C").Resize(, 4) = a
Next k, j, i
End Sub

Sub Rotations()
Dim N, lig&, i&, a(1 To 4)
Combinaisons
If Val([H1]) < 1 Then [H1] = 1
N = Val([H1])
lig = 1
Range("I2:L" & Rows.Count).ClearContents 'RAZ
Randomize
Do
    For i = 2 To Application.Max(Columns("C")) + 1
        If lig > N Then Exit Do
        a(1) = Rnd 'nombre aléatoire
        a(2) = Cells(i, 4): a(3) = Cells(i, 5): a(4) = Cells(i, 6)
        lig = lig + 1
        Cells(lig, "I").Resize(, 4) = a
    Next i
Loop While lig < N
Cells(2, "I").Resize(N, 4).Sort Columns("I"), Header:=xlNo 'tri aléatoire
Cells(2, "I") = 1
Cells(2, "I").Resize(N).DataSeries 'numérotation
End Sub

 

[Mickey Mouse]

Job 75 Sans vouloir abuser de votre gentillesse et compétences , j'aimerais lorsque je planifie n'avoir que les combinaisons avec les agents opérationnels...
Ex : l'agent 1 est en vacances du 12 au 17 Février , l'agent 12 est absent tout le mois d'aout et l'agent 13 n'est pas encore formé , le 15 lui n'est pas là le 23 Mars ...
Si c'est abusé ne vous embêtez pas , je me débrouillerai avec le fichier que vous avez en la gentillesse de m'envoyer .
Bonne journée.

 

[job75]

Il faut construire un 3ème tableau avec les dates et une 3ème macro avec les critères d'exclusions.

Voyez ce fichier (3) et la macro avec les 4 critères que vous avez indiqués :

Sub Tableau_final_2022()
'Ex : l'agent 1 est en vacances du 12 au 17 Février , l'agent 12 est absent tout le mois d'aout
'et l'agent 13 n'est pas encore formé , le 15 lui n'est pas là le 23 Mars ...
Dim R As Range, lig&, i&, dat As Date, x$, exclu1 As Boolean, exclu2 As Boolean, exclu3 As Boolean, exclu4 As Boolean
Rotations
Set R = [N1].CurrentRegion
lig = 1
For i = 2 To R.Rows.Count
    dat = R(i, 1)
    Do
        lig = lig + 1
        x = Chr(1) & Cells(lig, "J") & Chr(1) & Cells(lig, "K") & Chr(1) & Cells(lig, "L") & Chr(1) 'numéros encadrés
        exclu1 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And dat >= CDate("12/02/22") And dat <= CDate("17/02/22")
        exclu2 = InStr(x, Chr(1) & 12 & Chr(1)) > 0 And dat >= CDate("01/08/22") And dat <= CDate("31/08/22")
        exclu3 = InStr(x, Chr(1) & 13 & Chr(1)) > 0
        exclu4 = InStr(x, Chr(1) & 15 & Chr(1)) > 0 And dat = CDate("23/03/22")
    Loop While exclu1 Or exclu2 Or exclu3 Or exclu4
    R(i, 2).Resize(, 3) = Cells(lig, "J").Resize(, 3).Value
Next i
End Sub

Il s'agit donc d'une macro "faite sur mesure" et pas très facile à comprendre.

 

[Mickey Mouse]

Bonjour
Je suis désolé Job75 à eu la gentillesse de me créer un fichier qui répond à beaucoup de mes critères , mais hélas pas à tous :
Je n'ai pas vérifié mais je pense que le nombre d'apparitions sur 365 jours est équitable (Chaque agent travaille le même nombre de fois sur l'année ) par contre si je réduit à un mois par exemple on retrouve plein de fois le 1 et certains pas du tout et d'autres peu .
J'aimerais également rajouter des critères , comme précisé précédemment :
j'aimerais lorsque je planifie n'avoir que les combinaisons avec les agents opérationnels...
Ex : l'agent 1 est en vacances du 12 au 17 Février , l'agent 12 est absent tout le mois d'aout et l'agent 13 n'est pas encore formé , le 15 lui n'est pas là le 23 Mars ...
J'aimerais aussi pouvoir interdire provisoirement certaines associations par exemple , je ne veux pas que le 2 soit associé avec le 16 ou le 1 avec le 13 , le 8 et le 5 . Donc pour résumer j'aimerais n'avoir que des combinaisons reprenant équitablement les différents agents et ce quelque soit la période choisie , pouvoir exclure temporairement les combinaisons comprenant des agents absents à certaines périodes , et ne pas retrouver dans les combinaisons ainsi crées des associations non désirées .
Je suis conscient que mes exigences sont exagérées en tous cas pour moi elles sont impossibles à intégrer (Je ne maitrise pas les macros ) , je vous joins donc le fichier de Job75 qu'il peut bien évidement modifier lui même , mais si quelqu'un d'autres veut m'aider ..

En vous remerciant toutes et tous et particulièrement Job75 .
Bonne soirée

 

[job75]

Apparemment vous n'avez pas vu mon fichier (3)...

Comprenez bien que si vous modifiez ou ajoutez un critère d'exclusion il faut reprogrammer la 3ème macro.

On ne pourra pas vous aider à chaque fois car c'est sans fin.

Le seul moyen pérenne c'est que vous développiez vos connaissances en VBA et que vous puissiez programmer par vous même.

 

[job75]

J'aimerais aussi pouvoir interdire provisoirement certaines associations par exemple , je ne veux pas que le 2 soit associé avec le 16 ou le 1 avec le 13 , le 8 et le 5 .

Si je comprends bien cela fait 4 critères d'exclusions supplémentaires, voyez ce fichier (4) :

Sub Tableau_final_2022()
'Ex : l'agent 1 est en vacances du 12 au 17 Février , l'agent 12 est absent tout le mois d'aout
'et l'agent 13 n'est pas encore formé , le 15 lui n'est pas là le 23 Mars ...
'J'aimerais aussi pouvoir interdire provisoirement certaines associations par exemple , je ne veux pas que le 2 soit associé avec le 16 ou le 1 avec le 13 , le 8 et le 5
Dim R As Range, lig&, i&, dat As Date, x$, exclu1, exclu2, exclu3, exclu4, exclu5, exclu6, exclu7, exclu8
Rotations
Set R = [N1].CurrentRegion
lig = 1
For i = 2 To R.Rows.Count
    dat = R(i, 1)
    Do
        lig = lig + 1
        x = Chr(1) & Cells(lig, "J") & Chr(1) & Cells(lig, "K") & Chr(1) & Cells(lig, "L") & Chr(1) 'numéros encadrés
        exclu1 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And dat >= CDate("12/02/22") And dat <= CDate("17/02/22")
        exclu2 = InStr(x, Chr(1) & 12 & Chr(1)) > 0 And dat >= CDate("01/08/22") And dat <= CDate("31/08/22")
        exclu3 = InStr(x, Chr(1) & 13 & Chr(1)) > 0
        exclu4 = InStr(x, Chr(1) & 15 & Chr(1)) > 0 And dat = CDate("23/03/22")
        exclu5 = InStr(x, Chr(1) & 2 & Chr(1)) > 0 And InStr(x, Chr(1) & 16 & Chr(1)) > 0
        exclu6 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And InStr(x, Chr(1) & 13 & Chr(1)) > 0
        exclu7 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And InStr(x, Chr(1) & 8 & Chr(1)) > 0
        exclu8 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And InStr(x, Chr(1) & 5 & Chr(1)) > 0
    Loop While exclu1 Or exclu2 Or exclu3 Or exclu4 Or exclu5 Or exclu6 Or exclu7 Or exclu8
    R(i, 2).Resize(, 3) = Cells(lig, "J").Resize(, 3).Value
Next i
End Sub

 

[Mickey Mouse]

ok merci désolé de vous avoir fait perdre votre temps , j'aimerais bien programmer mais pour l'instant c'est du chinois ...J'arrive à me débrouiller avec des formules mais pour le fichier en question , je ne vois pas comment m'y prendre .
Encore désolé

 

[job75]

Bonjour Mickey Mouse,

L'inconvénient des méthodes avec tri aléatoire c'est que les écarts entre les nombres de jours des agents sont trop importants.

Dans le fichier joint j'utilise avec cette macro la méthode de rotations utilisée par JHA au post #2 :

Sub Tableau_2022()
'Ex : l'agent 1 est en vacances du 12 au 17 Février , l'agent 12 est absent tout le mois d'aout
'et l'agent 13 n'est pas encore formé , le 15 lui n'est pas là le 23 Mars ...
'J'aimerais aussi pouvoir interdire provisoirement certaines associations par exemple , je ne veux pas que le 2 soit associé avec le 16 ou le 1 avec le 13 , le 8 et le 5
Dim N, agent%, R As Range, i&, dat As Date, j%, x$, exclu1, exclu2, exclu3, exclu4, exclu5, exclu6, exclu7, exclu8
N = 20 'nombre d'agents, à adapter
agent = 0 'de 0 à N - 1, modifiable chaque année
Set R = [A1].CurrentRegion
Application.ScreenUpdating = False
R.Columns(2).Resize(, 3).ClearContents 'RAZ
For i = 2 To R.Rows.Count
    dat = R(i, 1)
    For j = 2 To 4
        Do
            agent = agent + 1
            If agent > N Then agent = 1 'rotation
            R(i, j) = agent
            x = Chr(1) & R(i, 2) & Chr(1) & R(i, 3) & Chr(1) & R(i, 4) & Chr(1) 'numéros encadrés
            exclu1 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And dat >= CDate("12/02/22") And dat <= CDate("17/02/22")
            exclu2 = InStr(x, Chr(1) & 12 & Chr(1)) > 0 And dat >= CDate("01/08/22") And dat <= CDate("31/08/22")
            exclu3 = InStr(x, Chr(1) & 13 & Chr(1)) > 0
            exclu4 = InStr(x, Chr(1) & 15 & Chr(1)) > 0 And dat = CDate("23/03/22")
            exclu5 = InStr(x, Chr(1) & 2 & Chr(1)) > 0 And InStr(x, Chr(1) & 16 & Chr(1)) > 0
            exclu6 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And InStr(x, Chr(1) & 13 & Chr(1)) > 0
            exclu7 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And InStr(x, Chr(1) & 8 & Chr(1)) > 0
            exclu8 = InStr(x, Chr(1) & 1 & Chr(1)) > 0 And InStr(x, Chr(1) & 5 & Chr(1)) > 0
        Loop While exclu1 Or exclu2 Or exclu3 Or exclu4 Or exclu5 Or exclu6 Or exclu7 Or exclu8
Next j, i
End Sub

Bien sûr puisqu'elles ne sont plus aléatoires les séries de 3 agents se reproduisent plus souvent.

A+

 

[job75]

Fichier (2) avec cette formule en A3 pour tenir compte des jours fériés :

=SERIE.JOUR.OUVRE.INTL(A2;1;;Ferie)

 

[Mickey Mouse]

Bonjour

pourquoi n'y a t'il pas de combinaisons avec le 13 ?

Merci

 

[job75]

pourquoi n'y a t'il pas de combinaisons avec le 13 ?

Parce que vous avez dir "l'agent 13 n'est pas encore formé".

Donc je l'ai exclu pour toute l'année avec le critère exclu3.

Bien entendu les critères sont à adapter pour chaque année.