Microsoft 365 Solveur Production Lineaire

Kezel

XLDnaute Nouveau
Bonjour,

Je suis a la recherche d'aide pour la réalisation d'un solveur d'équilibrage de production sous Excel.

Sachant que :

J'ai actuellement 167 affaires.
Ces affaires n'ont pas forcement la mêmes durée (cela va de 10 minutes a 540 minutes).
La prod tourne la nuit entre 00h00 et 09h00.
Les affaires ont des objectifs de fin de prod différents (entre 06h00 et 08h50).

Actuellement j'ai réussi a quantifier le temps de production pour chaque affaires et maintenant j'aimerai mettre en place un solveur qui m'indiquerai l'heure de démarrage de chaque affaire afin d'équilibrer au maximum la charge serveur et éviter les pic ou les trous a certaines heures tout en respectant la contrainte heure de fin de prod.

J'imagine qu'il va falloir passer par du VBA et c'est la que je manque de compétence ...

Merci
 

Pièces jointes

  • Solveur.xlsx
    126 KB · Affichages: 12

job75

XLDnaute Barbatruc
Bonjour Kezel, bienvenue sur XLD,
maintenant j'aimerai mettre en place un solveur qui m'indiquerai l'heure de démarrage de chaque affaire
Cette macro permet de déterminer pour chaque affaire l'heure de début et l'heure de fin :
VB:
Sub Calcul()
Dim d As Object, dd As Object, c As Range, n&
Set d = CreateObject("Scripting.Dictionary")
Set dd = CreateObject("Scripting.Dictionary")
With Sheets("Source")
    For Each c In .Columns(3).Resize(, .Columns.Count - 2).SpecialCells(xlCellTypeConstants)
        If Not d.exists(c.Value) Then d(c.Value) = .Cells(c.Row, 1)
        dd(c.Value) = .Cells(c.Row, 1)
    Next
End With
'---restitution---
n = d.Count
With Sheets("Période").[A2]
    If n Then
        .Resize(n) = Application.Transpose(d.keys)
        .Cells(1, 2).Resize(n) = Application.Transpose(d.items)
        .Cells(1, 3).Resize(n) = Application.Transpose(dd.items)
        .Cells(1, 4).Resize(n) = "=RC[-1]-RC[-2]"
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 4).ClearContents 'RAZ en dessous
End With
End Sub
Je ne vois pas ce que vous voulez faire avec le Solveur.

A+
 

Pièces jointes

  • Solveur(1).xlsm
    137.6 KB · Affichages: 9
Dernière édition:

Kezel

XLDnaute Nouveau
Bonjour,

Merci pour ton aide ca me donne quelques pistes :)

L'utilité pour moi de cet outil est assez compliqué a expliquer par écrit mais je vais tout de même essayer :)

L'entreprise dans laquelle je travail développe des applications et chaque nuit nos serveurs de production traitent les données clients qui ont été synchronisées via les outils que nous vendons.

C'est données sont regroupées par affaires (chaque affaire = 1 client).
Le souci que nous avons c'est que durant la nuit, le nombre d'affaires traitées simultanément n'est pas optimisé, par exemple dans l'onglet source, on peut voir qu'a 06h00 il y a 74 affaires active en simultanée alors que seulement 16 affaires sont active a 08h50.

Cela a pour impact de surchargé les serveurs lorsque nous somme a 74 affaires ou alors de sous-exploiter notre pool de serveurs quand il y a seulement 16 affaires.
Tout ceci retarde donc l'heure à laquelle la production de nuit se termine.

L'équilibrage de la production permet donc de maintenir un nombre +/- égal d'affaires simultanées afin d'utiliser le plein potentiel de nos serveur tout au long de la production nocturne et pouvoir livrer plus rapidement nos clients.

Grace a cet outil je vais donc pouvoir modifier de façon optimal l'heure de départ de chaque affaire dans notre planificateur.
 

job75

XLDnaute Barbatruc
Bonjour Kezel, le forum,

Voyez le fichier joint et ce code dans la feuille "Equilibrage" :
VB:
Private Sub Worksheet_Activate()
Dim F As Worksheet, d As Object, nlig&, c As Range, n&, a, b, ncol%, i&, j%
Set F = Sheets("Source")
'---nombre de points par affaire---
Set d = CreateObject("Scripting.Dictionary")
With F.[A1].CurrentRegion
    nlig = .Rows.Count - 1
    If .Columns.Count > 2 Then
        For Each c In .Offset(, 2).Resize(, .Columns.Count - 2)
            If c <> "" Then n = n + 1: d(c.Value) = d(c.Value) + 1 'comptage
        Next c
    End If
End With
'---équilibrage---
Application.ScreenUpdating = False
Cells.Delete 'RAZ
F.Columns("A:B").Copy [A1]
If n = 0 Then Exit Sub 'sécurité
a = d.items: b = d.keys
Tri a, b, 0, UBound(a) 'tri décroissant
ncol = Application.RoundUp(n / nlig, 0)
For i = 0 To UBound(a)
    For Each c In Range("C2").Resize(nlig, ncol)
        If c = "" And a(i) <= nlig + 2 - c.Row Then
            c.Resize(a(i)) = b(i)
            GoTo 2
        End If
    Next c
1   For Each c In Cells(2, ncol + 3 + j).Resize(nlig) 'ajoute une colonne supplémentaire
        If c = "" And a(i) <= nlig + 2 - c.Row Then
            c.Resize(a(i)) = b(i)
            GoTo 2
        End If
    Next c
    j = j + 1
    GoTo 1
2 Next i
Columns.AutoFit 'ajustement largeurs
End Sub

Sub Tri(a, b, gauc, droi)  ' Quick sort
Dim ref, g, d, temp
    ref = a((gauc + droi) \ 2)
    g = gauc: d = droi
    Do
        Do While a(g) > ref: g = g + 1: Loop
        Do While ref > a(d): d = d - 1: Loop
        If g <= d Then
            temp = a(g): a(g) = a(d): a(d) = temp
            temp = b(g): b(g) = b(d): b(d) = temp
            g = g + 1: d = d - 1
        End If
    Loop While g <= d
    If g < droi Then Call Tri(a, b, g, droi)
    If gauc < d Then Call Tri(a, b, gauc, d)
End Sub
Il s'exécute automatiquement quand on active la feuille.

A+
 

Pièces jointes

  • Solveur(2).xlsm
    38.9 KB · Affichages: 10
Dernière édition:

Kezel

XLDnaute Nouveau
Bonsoir,

Merci pour ton aide ca marche super bien par contre je vois pas comment intégrer les contraintes horaires des affaires au code vba ...

Si tu as une idée j'aimerai que tu m'expliques pour 1 affaire et je ferai les autres car ca prend bcp de temps et tu m'as déjà bcp aidé.

En tout cas c'est déjà pour moi une belle avancée :)

Merci !!!
 

job75

XLDnaute Barbatruc
Bonjour Kezel, le forum,

par contre je vois pas comment intégrer les contraintes horaires des affaires au code vba ...
Pour chaque affaire la seule contrainte horaire prise en compte par le code est sa durée (nombre de points).

Pour l'équilibrage les durées sont classées en ordre décroissant.

Il n'y a pas d'autres contraintes si l'on veut pouvoir optimiserr.

A+
 

Kezel

XLDnaute Nouveau
Bonjour,

En fait sur le premier Excel que j'ai fournis il y a dans la feuille "données utiles" les contraintes pour chaque affaires qui sont les heures a laquelle l'affaire doit être terminée.

Ces horaires correspondent a la connexion serveur de chaque client, il faut donc que je les respectes :)

Merci a toi ;)
 

Statistiques des forums

Discussions
311 720
Messages
2 081 913
Membres
101 837
dernier inscrit
Ugo