Discussion :

[joe.levrai]

je lance un regard différent, vu que ça fait quand même quelques jours que j'avais clôturé ce sujet (sans te donner le code )

demeure-t-il vraiment nécessaire de manipuler encore tes temps ... qui du coup se trouvent être des "temps objectifs" ?

ne suffit-il pas de prendre le 80%tième ?

sinon, attend lundi et c'est réglé

[joe.levrai]

Bonjour,

voici pour de nouveaux tests

si temps = 0 : on prend en compte dans le dictionnaire
si quantité = 0 : on ne prend pas en compte

le comportement n'est pas à 100% conforme à ta description au sujet des 80% déjà pris en compte, mais j'ai l'impression que c'est la méthode d'estimation la plus précise.

dis moi ce que ça raconte et si on doit quand même ajuster un peu l'horloge

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Public Function SIGMICNew(Plage As Range)
Dim Pas As Double
Dim Tablo
Dim Verif As Double
Dim MonDico As Object
Dim i As Integer
Dim j As Integer
 
Set MonDico = CreateObject("Scripting.Dictionary")
Tablo = Plage
 
For j = LBound(Tablo, 1) To UBound(Tablo, 1)
    If Tablo(j, 2) = 0 Then
 
    ElseIf Tablo(j, 1) = 0 Then
        On Error Resume Next
            MonDico.Add (Tablo(j, 1) - Verif), (Tablo(j, 1) - Verif)
            i = i + 1
        On Error GoTo 0
    Else
        Pas = Tablo(j, 1) / Tablo(j, 2)
        If Pas > 0 Then
            Verif = 0
            While Verif <= Tablo(j, 1)
                On Error Resume Next
                    MonDico.Add (Tablo(j, 1) - Verif), (Tablo(j, 1) - Verif)
                    i = i + 1
                On Error GoTo 0
                Verif = Verif + Pas
            Wend
        End If
    End If
Next j
 
ReDim Tablo(i - 1)
Tablo = MonDico.items
Call Tri(Tablo, LBound(Tablo), UBound(Tablo))
SIGMICNew = Tablo(UBound(Tablo))
 
End Function

[SIGMIC]

Bonjour 'Joe.levrai' !
Merci pour ces changements. Je vais faire un check et de donne un feedback dès que possible.
Bon weekend à toi et cordiales salutations
Michel

[joe.levrai]

Bonjour,

je reste là, si faut encore modifier le comportement

par contre, pour l'histoire des données que tu dois regrouper en plage afin d'utiliser la fonction .... j'ai pas réussi à modifier ça pour le moment

après, on pourrait procéder à l'envers : on fait une macro qui regroupe automatiquement des chiffres sous forme de plage, si jamais c'était possible

[SIGMIC]

Bonjour/bonsoir 'Joe.levrai',

Merci beaucoup pour les modifications apportées au programme!
J'ai testé le nouveau code et voici mes remarques:

1) Pour le test quantité '0' et temps '0', c'est tout bon. Et s'il n'y a rien qui est retenu dans le dictionnaire, il affiche '#valeur!', ce qui est normal je suppose. Par rapport à mes critères initiaux, il manque juste, si c'est possible, le test du 'temps' = blanc, et si c'est blanc, le programme devrait aussi passer au suivant, comme pour la quantité = 0.

2) J'ai testé la liste suivante: 8 x temps 0 et 4 x temps 25. En prenant 80% (9.6) arrondi (10), on aurait 12.5 et non 18.75, est-ce ce genre d'imprécision dont tu parlais ton ton message précédent? A cause des temps '0' ? J'ai testé d'autres listes beaucoup plus grandes et là c'est précis.

3) Dans ton dernier code, il n'y a plus les lignes "Pas = Round((UBound(Tablo) + 1) / 5)" et "SIGMIC = Tablo(UBound(Tablo) - Pas)", je suppose qu'il faut les rajouter (sinon j'ai un résultat toujours à 0).

4) Calcul du temps à considérer pour chaque commande: La quantité à considérer est ok mais pour le calcul du temps par commande, il y a une petite subtilité que j'avais mentionnée précédemment, car au lieu de diviser "temps/quantité", il faudrait diviser "temps/(80% x quantité)". Exemple: Qté 10, Temps 16 --> division de 16 / (80% de 10) = 16/8=2 --> commandes 1 à 10 = 2, 4, 6, 8, 10, 12, 14, 16, 18 et 20 --> dictionnaire.

5) Finalement l'histoire des plages: Idéalement (c'est déjà tellement super que j'ose bientôt plus le dire...) je devrais pouvoir donner les adresses des cellules dans chaque cellule où doit apparaître l'objectif ( SIGMICNEW(A2;A10;B2;B10;C2;C10, etc.) ) et que la plage soit créée à partir de ces éléments (qu'il y en ait 2 ou 4 ou 10 couples). Est-ce cela à quoi tu pensais ?

Merci mille fois!
Cordialement
Michel

[joe.levrai]

Bonjour,

j 'ai pris compte de tes souhaits, la proposition précédente me semblait simplement plus pertinente
voici qui répond à tes 4 premiers points

1) Ton double problème évoqué n'en était qu'un seul : c'est pris en compte

2) Les 18,25 que tu obtenais proviennent du point 4 que j'ai également intégré
en revanche, le résultat attendu n'est pas 12,5, mais 17,1875 :

- Temps 1 = 0 >> ajout de 0 dans le dictionnaire
- Temps 2 = 25 || Commande 2 = 4 >> ajout dans le dictionnaire de tous les multiples de "Temps2 / 80% de Commande2", en partant de Temps2 et en allant jusque 0, soit {25;17.125;9.35;1.5625}
- le dictionnaire contenant 5 éléments, le 80%tième est l'avant dernier élément, soit 17,125

3) Mea Culpa, tu as très bien fait. Je prenais le dernier élément du tableau car j'estimais qu'en ayant déjà les sous-temps objectifs dans chaque cellule, ça ne servait plus à rien ... j'avais tort

4) C'est pris en compte

5) si tes données sont réparties selon une organisation logique, alors VBA peut très bien aller chercher les données à ta plage, pour les regrouper dans une plage qu'on rendra dynamique Mais s'il y a un défaut de régularité ou trop d'aléatoire, ce n'est pas la peine car ça fait des usines à gaz


Tu peux effacer tout ce que je t'ai donné depuis le début, et tu remplaces par ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Public Function SIGMICNew(Plage As Range)
Dim Pas As Double
Dim Tablo
Dim Verif As Double
Dim MonDico As Object
Dim j As Integer
 
Set MonDico = CreateObject("Scripting.Dictionary")
Tablo = Plage
 
For j = LBound(Tablo, 1) To UBound(Tablo, 1)
    If Tablo(j, 2) > 0 _
    And Tablo(j, 2) <> "" _
    And Tablo(j, 1) <> "" Then
        Pas = Tablo(j, 1) / (Tablo(j, 2) * 0.8)
        Verif = 0
        If Pas > 0 Then
            While Verif <= Tablo(j, 1)
                On Error Resume Next
                    MonDico.Add (Tablo(j, 1) - Verif), (Tablo(j, 1) - Verif)
                On Error GoTo 0
                Verif = Verif + Pas
            Wend
        ElseIf Pas = 0 Then
            On Error Resume Next
            MonDico.Add (0), (0)
            On Error GoTo 0
        End If
    End If
Next j
 
ReDim Tablo(MonDico.Count - 1)
Tablo = MonDico.items
Call Tri(Tablo, LBound(Tablo), UBound(Tablo))
Pas = Round((UBound(Tablo) + 1) / 5)
SIGMICNew = Tablo(UBound(Tablo) - Pas)
 
End Function
 
Sub Tri(a, gauc, droi)
'*** Codé proposé par Jacques BOISGONTIER ***
 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
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call Tri(a, g, droi)
 If gauc < d Then Call Tri(a, gauc, d)
End Sub

EDIT : en faisant quelques tests, je me dis que pour avoir un réel temps objectif, il faudrait inclure une pondération en fonction du nombre de commandes de chaque temps ?
je te laisse tester et me dire

[SIGMIC]

Bonsoir 'Joe.levrai',

Merci infiniment pour ta réponse, c'est géant!
J'ai remplacé le code, j'ai testé et voici encore quelques constatations/affinages:

1) Super.

2) J'avais un autre résultat car c'était encore sans tenir compte du point 4). Serait-il toutefois possible d'arrondir dans l'exemple "80% de commande2" (4*0.8=3.2 >> 3), ainsi nous aurions: 25/3=8.33 pour le 1er, 16.67 pour le 2e, 25 pour le 3e (=80%=donnée) et 33.33 pour le 4e (dernière qté, au-delà du 80%). Pour la commande1 (quantité 8, temps 0) nous aurions: 0 pour le 1er, 0 pour le 2e, ... 0 pour le 8e (car chaque qté à sa place! peut-être en partie la pondération dont tu parles sous EDTI). Final: 12 éléments [0, 0, 0, 0, 0, 0, 0, 0, 8.33, 16.67, 25, 33.33], 80% = 9.6 = 10e = 16.67.

3) Parfait.

4) Voir 2) pour affinage.

5) Mmmm, je pense que je vais mettre ces données sources dans des plages. Par contre, vu que les lignes se suivent et que les plages auront toutes sortes de hauteur et que je ne les désire pas trop éloignées (la structure du fichier est déjà assez lourde), serait-ce possible d'avoir la plage sur une ligne, par exemple: au lieu de A1 à B3 (3 couples, 6 éléments), d'avoir A1 à F1 ?

EDIT: Tu as raison et c'est d'ailleurs une approche par moyenne pondérée que j'avais souhaité dans l'entreprise. Toutefois nous avons opté pour cette approche "80%" répondant mieux à la courbe mathématique réelle qui n'est que rarement syméthrique. Toutefois en considérant tous les éléments (même chaque quantité à "0" sous le point 2), avec chacun ayant une part de temps augmentant linéairement (car on ne connait pas la vraie répartition - enfin le système la connaitrait mais je ne veux pas intégrer celà dans ce programme), je m'approche beaucoup plus d'une certaine réalité qu'en prenant mes anciennes formules de moyennes pondérées, car le système me donne tous les chiffres réels en approche "80%". Je ne sais pas si c'est ce à quoi tu pensais.

Mille mercis!
Bien cordialement
Michel

[joe.levrai]

Bonjour,

si on reprend l'ensemble de tes paramètres, qui ont changés en cours de route (les 80% calculés en amont par exemple), et le fait qu'on soit amené à pondérer les Temps par le nombre de commandes, je pense qu'on va pouvoir simplifier grandement la chose.

en effet, SOMMEPROD semble être la nouvelle solution.

je regarde ça et je reviens vers toi ... on est sur de la contrainte et de la logique statistique, ce qui parfois nécessite une solution qui n'est pas celle qu'on pense être la plus évidente surtout qu'on ne manipule plus des données brutes, mais déjà travaillées

[SIGMIC]

Bonjour/bonsoir ´Joe.levrai',
Merci pour ton retour. Peut-être qu'il existe un autre chemin, mais j'ai pourtant l'impression que la solution actuelle est tellement proche. Il arrive bien sûr qu'un nombre important de commandes soient réglées dans la journée (temps 0), chacune à par contre son poids dans la bibliothèque. Quant au temps moyen à incrémenter depuis le bas pour toutes les commandes, c'est ´Temps' divisé par 80% de 'Quantité'. Je ne suis par contre pas sûr qu'il faille travailler sur les produits, car chaque élément prend une place dans la bibliothèque afin de pouvoir pointer sur la position ´target' finale après le super et rapide tri qui est fait sur chacun des éléments.
Merci pour ta patience.
Bien cordialement,
Michel

[SIGMIC]

Bonjour 'Joe.levrai',

Afin de pouvoir préparer mes gros fichiers avec le code provisoire dans toutes les cellules concernées, pourrais-tu me dire si des plage alignées sont possibles, par exemple: au lieu de A1 à B3 (3 couples, 6 éléments), d'avoir A1 à F1 ?

Si c'est compliqué, je structurerai en plages (A1 à B3, etc.).

Merci pour ton feedback!
Cordiales salutations
Michel

[joe.levrai]

Bonjour,

Les grands esprits se rencontrent, j'allais revenir sur ton sujet ce soir

j'avais pas encore converti la plage sous forme de ligne
pour des raisons de simplicité, il faudra que les données soient ainsi :

Tps1 / NbCommandes1 / Tps2 / NbCommandes2 /etc...

je pars sur cette base et je te propose une nouvelle solution tout à l'heure

dis moi si c'est ok sur cette structure

[SIGMIC]

Bonsoir 'Joe.levrai',

Merci pour tes nouvelles - Bien sûr, c'est parfait ainsi pour les plages!

Concernant l'affinage du traitement des couples (mon avant-dernier courriel), vois-tu une possibilité sans nouvelle approche, juste en modifiant le code existant ?
Rappel: Diviser le temps par 80% des quantités et incrémenter ce résultat pour toutes les commandes.

Exemple:
Couple 1: Q1 15 T1 24 >>> 24/arrondi(15*0.8)=2 >>> 1ère commande = 2, 2e = 4, ... 12e = 24 ... 15e = 30. 15 commandes dans la bibliothèque.
Couple 2: Q2 0 T2 55 >>> Passer au couple suivant car quantité nulle. Rien dans la bibliothèque.
Couple 3: Q3 20 T3 0 >>> 0/(20*0.8)=0 >>> 1ère commande = 0, ... 20e commande = 0. 20 commandes à temps 0 dans la bibliothèque.
Final: Trier la bibliothèque dans l'ordre ascendant des 35 commandes (15+20) et édition de l'objectif général de la commande 28 (35*0.8) = 16.

MERCI !!!
Michel

[joe.levrai]

Je tente surtout d'instaurer une pondération pour ton nombre de commandes, histoire d'être au plus près de la réalité

mais il ne faut pas que ça entraine un allongement du traitement, voir un plantage par saturation de mémoire, puisque tu m'as parlé de lapossibilité d'avoir des quantités de commandes de plusieurs dizaines de milliers

par exemple, si tu as deux couples :

Tps 1 : X | NbCmd 1 : 1
Tps2 : Y | NbCmd 2 : 100 000

il faut doser subtilement le coefficient de pondération, afin de ne pas se retrouver avec une pondération d'un facteur 100 000 pour ton Tps2

ton Excel et ton processeur ne vont pas apprécier

mais je suis pas loin d'un juste équilibre... qui ne nécessite pas de changer radicalement le paradigme qu'on a pris depuis le début

on va laisser tomber le dico par contre, pour revenir sur un tableau qui inclus les doublons de diviseurs s'il y en a
(ce fut une bêtise de ma part et pas vraiment dans ton souhait de départ, je m'en suis rendu compte tardivement )

[SIGMIC]

Merci pour ces précisions 'Joe.levrai' !
J'espère surtout que mon exemple est suffisamment clair, sinon n'hésite pas à revenir vers moi.
Sauf erreur l'algorithme aurait permis d'éviter certains plantages, mais je ne comprends pas pourquoi il faut abandonner la bibliothèque.
Un grand merci par avance pour ton aide précieuse!
Cordialement, Michel

[SIGMIC]

Bonjour/bonsoir 'Joe.levrai',

J'aurais terminé mes préparations de tous mes fichiers acceptant les plages de couples en ligne (T1/Q1, T2/Q2, T3/Q3, etc.).
Je suis par conséquent impatient d'avoir ta dernière variante pour la mettre en place !!

Merci par avance pour tes nouvelles...
Cordiales salutations
Michel

[SIGMIC]

Bonjour 'Joe.levrai',

Comment vois-tu la suite ?

Si le ciel est trop nuageux, je me contenterais d'une modification de programme tenant compte d'un l'alignement des couples, car j'ai modifié mes fichiers dans ce sens, et on laisse tomber le reste.

Merci par avance pour ton petit feedback.
Bien cordialement,
Michel

[joe.levrai]

Je tente encore quelques pistes avant d'abandonner et de rester sur des anciennes versions

j'ai des résultats conformes qui deviennent aberrants quand tu as des données triviales (1 seul commande ou temps à 0 sur des grosses commandes par exemple)

déjà, je te confirme que la plage en forme de ligne c'est ok
c'est la juste pondération qui me pose problème.

[SIGMIC]

Merci pour ton feedback 'Joe.levrai' !
Si je modifiais mes données de base (T1/Q1, T2/Q2, etc.) afin que le programme ne doivent pas les travailler, serait-il possible qu'il fasse les opérations suivantes:

1) A chaque couple ayant une quantité de 0, on passe au couple suivant.
2) Chaque couple avec qté supérieure à 0, on divise le temps par la quantité pour obtenir X.
3) On passe en revue les quantités jusqu'à la dernière en incrémentant chaque fois de X et en mettant chaque fois ces petits couples dans la bibliothèque.
4) On trie et sort comme dans les anciennes version le temps correspondant au dernier des premiers %.

Si le temps = 0 et la quantité 5, on divise 0/5 = 0 et on met dans la bibliothèque: 1/0, 2/0, 3/0, 4/0 et 5/0.

L'approche ci-dessus pourrait-elle simplifier ton soucis ?

Cordialement,
Michel

[SIGMIC]

Bonjour/bonsoir 'Joe.levrai',

As-tu pu jeter un oeil sur ma dernière proposition ?
Sinon serait-ce possible d'avoir une version déjà avec les couples en lignes ?

Merci par avance pour ton retour et reçois mes cordiales salutations
Michel

[joe.levrai]

Bonjour,

c'est précisément ce que tu décris dans ton dernier message, mais est-ce bien le résultat attendu ?

ceci annule et remplace tout, test et dis moi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Public Function SIGMICNew(Plage As Range)
 
Dim Tablo
Dim TabVal
Dim i As Long
Dim j As Long
Dim Pas As Double
 
Tablo = Application.Transpose(Plage)
 
For j = LBound(Tablo, 1) To UBound(Tablo, 1) - 1 Step 2
    If Tablo(j + 1, 1) > 0 And Tablo(j + 1, 1) <> "" And Tablo(j, 1) <> "" Then
        Pas = CDbl(Tablo(j, 1)) / Round(CDbl(Tablo(j + 1, 1)))
        If Pas > 0 Then
            While Pas <= CDbl(Tablo(j, 1))
                If Not IsArray(TabVal) Then ReDim TabVal(1) Else ReDim Preserve TabVal(UBound(TabVal) + 1)
                TabVal(UBound(TabVal)) = Pas
                Pas = Pas + Pas
            Wend
        ElseIf Pas = 0 Then
            For i = 1 To Tablo(j + 1, 1)
                If Not IsArray(TabVal) Then ReDim TabVal(1) Else ReDim Preserve TabVal(UBound(TabVal) + 1)
                TabVal(UBound(TabVal)) = 0
            Next i
        End If
    End If
Next j
 
Call Tri(TabVal, LBound(TabVal), UBound(TabVal))
Pas = Round((UBound(TabVal) + 1) / 5)
SIGMICNew = CDbl(TabVal(UBound(TabVal) - Pas))
 
End Function
 
Sub Tri(a, gauc, droi)
'*** Codé proposé par Jacques BOISGONTIER ***
 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
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call Tri(a, g, droi)
 If gauc < d Then Call Tri(a, gauc, d)
End Sub

EDIT : apparemment ta proposition ajoute trop de 0, il faut modérer la quantité, ce qui revient à notre problème précédent
je reviens sur une suggestion précédente, pourquoi ne pas utiliser SOMMEPROD() qui gère bien la pondération ?
c'est ce que j'utilise pour calculer des délais sur des indicateurs complexes et de dimensions différentes, j'ai jamais eu trop de surprises

essaye cette nouvelle approche, je suis certain que c'est ce qu'il te faut (quitte à affiner en prenant 80% du résultat)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Option Base 1
Public Function SIGMICProd(Plage As Range)
Dim TabTps
Dim TabQt
 
With Plage
    For i = 1 To .Cells.Count - 1 Step 2
        If Not IsArray(TabTps) Then
            ReDim TabTps(1): ReDim TabQt(1)
        Else
            ReDim Preserve TabTps(UBound(TabTps) + 1): ReDim Preserve TabQt(UBound(TabQt) + 1)
        End If
        TabTps(UBound(TabTps)) = .Cells(1, i): TabQt(UBound(TabQt)) = .Cells(1, i + 1)
    Next i
End With
 
With Application.WorksheetFunction
    SIGMICProd = .SumProduct(TabTps, TabQt) / .Sum(TabQt)
End With
 
End Function