XL 2016 Delais moyen de traitement règle de calcul

Yves Decabooter

XLDnaute Nouveau
Bonjour,

J'ai un document access qui m'a été transmis pour calculer le délais de traitement des dossiers entrant (FIFO) en tenant compte d'un nombre de dossier en stock, et de sortie journalière. (voir image)
delais traitement.png

J'aimerais reconstituer la même formule dans Excel (mais la prog vba Access est illisible). Il faudrait un calcul au jour le jour mais aussi moyen sur le total de ma période.

Ayant essayé de tourner le problème un peu dans tous les sens sans succès, j'en fais appel à vous.

Si un as mes math passe par là ;)
 

Yves Decabooter

XLDnaute Nouveau
Merci, en tout cas cela reconstitue mon total... Mais avant d'arriver là il faudrait que j'ai la formule qui me donne le calcul du délais au jour le jour dans la colonne délais.
 

ERIC S

XLDnaute Barbatruc
Re
pour cela il faut avoir les données de base, qui doivent être dans une table access si j'ai bien compris, ou vous voulez mettre en place un outil excel qui ferait la même chose ?
 

Yves Decabooter

XLDnaute Nouveau
Oui je peux vous donner la programmation VBA d'access... Mais je n'ai pas compris le mode de calcul même si en théorie je le comprends.

J'espère que vous pourrez comprendre quelque chose.

Dim db As Database
Dim rst As Recordset
Dim strsql As String
Dim suivi_stock As Long
Dim ntype As Integer
ntype = NtypeSuivi
Set db = CurrentDb
strsql = "SELECT Tdonnées.NTypeSuivi, Tdonnées.Dte, Tdonnées.Arrivée, Tdonnées.Stock_depart, Tdonnées.Stock_fin, Tdonnées.Traité, Tdonnées.Delai"
strsql = strsql & " FROM Tdonnées"
strsql = strsql & " WHERE (((Tdonnées.NtypeSuivi) = " & NtypeSuivi & "))"
strsql = strsql & " ORDER BY Tdonnées.Dte;"
Set rst = db.OpenRecordset(strsql)
If rst.RecordCount = 0 Then
Set db = Nothing
Exit Sub
End If
rst.MoveFirst
suivi_stock = DLookup("stkdépart", "ttypesuivi", "Ntypesuivi=" & NtypeSuivi)
Do Until rst.EOF
If rst!Dte = Date + 1 Then
Exit Do
Else
rst.Edit
rst!Stock_depart = suivi_stock + rst!Arrivée
rst!Stock_fin = suivi_stock + rst!Arrivée - rst!Traité
suivi_stock = rst!Stock_fin
rst.Update
End If

rst.MoveNext
Loop
Set db = Nothing
If Param = 2 Then
Calcul_délai (NtypeSuivi)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE TTypeSuivi SET TTypeSuivi.maj = True WHERE (((TTypeSuivi.NtypeSuivi)=" & NtypeSuivi & "));"
Call visumaj(True)
DoCmd.SetWarnings True
End If
DoCmd.Requery
Dim MonRS As Recordset
Set MonRS = Me.RecordsetClone
MonRS.FindFirst "ntypesuivi =" & ntype
Me.Bookmark = MonRS.Bookmark
MonRS.Close
End Sub
 

ERIC S

XLDnaute Barbatruc
Re

pas spécialiste access et ne connait pas ton appli :(

A priori tes données sont dans une table qui s'appelle Tdonnées.
Comment procèdes-tu aujourd'hui ? Tu lances une macro sous access ?

en première partie elle récupère des champs (tous?) de la table en fonction du critère Ntypesuivi et les trie par date d'arrivée. il faudrait regarder les champs de Tdonnées et ce qu'ils contiennent pour en savoir plus
il devrait être possible de lier un fichier excel à la table access et ensuite faire les calculs (sans doute en jours ouvrés + fériés
 

Yves Decabooter

XLDnaute Nouveau
En fait je fait je clique sur un bouton calculer qui appelle la fonction VBA ci-avant et les données de Tdonnées sont uniquement celles que tu vois (le type de suivi c'est parce que j'ai un calcul de délais pour plusieurs suivi d'activités différentes ) :
capture de ma table Tdonnées
upload_2018-11-28_16-14-23.png
 

ERIC S

XLDnaute Barbatruc
Re
dans ta dernière copie d'écran les délais jour sont déjà calculés. C'est ce que tu récupères après clic sur bouton ou c'est ?
ta base initiale doit plutôt être une ligne par donnée avec date début et date fin non ?

Donc, de quelles données veux-tu ou peux-tu partir ? Après un calcul formule ou vba est toujours possible, il faut par contre savoir si on gère avec jours ouvrés …
dans ton code le délai doit être calculé par "Calcul_délai (NtypeSuivi)", une fonction définie ailleurs je pense
 

Yves Decabooter

XLDnaute Nouveau
Les délais sont calculés par le bouton et la fonction VBA.

Ensuite il ne faut pas tenir compte des jours ouvrés, un calcule simplement de date a date. La fonction de calcul délais est celle mise ci avant...

Public Sub Calcul_délai(NtypeSuivi)
On Error GoTo err_delai
Dim db As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim cumul_arrivée As Long
Dim indicjour As Byte
Dim cumul_jour As Long
Dim qté_à_traiter As Long
Dim qté_traitée As Long
Dim strsql As String
Dim date_début As Date
Dim stk_initial As Integer
Dim indic_fin As Byte
' == ctrl
strsql = "SELECT Tdonnées.NTypeSuivi, Tdonnées.Stock_depart, Tdonnées.Stock_fin"
strsql = strsql & " FROM Tdonnées"
strsql = strsql & " WHERE (((Tdonnées.NTypeSuivi)=" & NtypeSuivi & ") AND ((Tdonnées.Stock_depart)<0)) OR (((Tdonnées.NTypeSuivi)=" & NtypeSuivi & ") AND ((Tdonnées.Stock_fin)<0));"
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)
If rst.RecordCount > 0 Then
MsgBox "Stocks négatifs impossibles." & vbCrLf & "Veuillez corriger les arrivées et/ou les quantités traitées." & vbCrLf & vbCrLf & "Les délais n'ont pas été recalculés.", 48, "Erreur"
Set db = Nothing
Exit Sub
End If
date_début = DLookup("dtfige", "TTypeSuivi", "NTypeSuivi=" & NtypeSuivi)
stk_initial = DLookup("StkDépart", "TTypeSuivi", "NTypeSuivi=" & NtypeSuivi)
strsql = "SELECT TDonnées.Dte, TDonnées.Arrivée, TDonnées.Stock_depart, TDonnées.Stock_fin, TDonnées.Traité, TDonnées.Delai"
strsql = strsql & " FROM TDonnées"
strsql = strsql & " WHERE (((TDonnées.Dte) >= #" & Format(date_début, "MM/DD/YYYY") & "# And (TDonnées.Dte) < Date()) and ((tdonnées.ntypesuivi) = " & NtypeSuivi & "))"
strsql = strsql & " ORDER BY TDonnées.Dte DESC;"
Set rst = db.OpenRecordset(strsql)
rst.MoveFirst
Do Until rst.EOF
Début:
If rst!Traité = 0 Then

rst.Edit
rst!Delai = 0
rst.Update
rst.MoveNext
If rst.EOF Then
Set db = Nothing
Exit Sub
End If
GoTo Début
End If

strsql = "SELECT TDonnées.Dte, TDonnées.Arrivée, TDonnées.Stock_depart, TDonnées.Stock_fin, TDonnées.Traité"
strsql = strsql & " FROM TDonnées"
strsql = strsql & " WHERE (((TDonnées.Dte) <= #" & Format(rst!Dte, "MM/DD/YYYY") & "#) and tdonnées.ntypesuivi = " & NtypeSuivi & ")"
strsql = strsql & " ORDER BY TDonnées.Dte DESC;"

Set rst2 = db.OpenRecordset(strsql)
rst2.MoveFirst
cumul_arrivée = 0
indicjour = 0

Do Until cumul_arrivée >= rst!Stock_depart
If rst2.EOF Then
cumul_arrivée = cumul_arrivée + stk_initial
indic_fin = 1
Else
cumul_arrivée = cumul_arrivée + rst2!Arrivée
indicjour = indicjour + 1
rst2.MoveNext
End If
Loop

rst2.MovePrevious
qté_traitée = 0
cumul_jour = 0


If indic_fin = 0 Then
qté_à_traiter = (rst2!Arrivée - (cumul_arrivée - rst!Stock_depart))
Else
qté_à_traiter = (rst2!Arrivée + stk_initial - (cumul_arrivée - rst!Stock_depart))
End If

If qté_à_traiter >= rst!Traité Then
qté_à_traiter = rst!Traité
cumul_jour = cumul_jour + (indicjour * qté_à_traiter)
If cumul_jour <= 0 Then
MsgBox "hhh"
End If
qté_traitée = rst!Traité
End If

Do Until qté_traitée >= rst!Traité

cumul_jour = cumul_jour + (indicjour * qté_à_traiter)


indicjour = indicjour - 1
qté_traitée = qté_traitée + qté_à_traiter

If indicjour > 0 Then
rst2.MovePrevious
End If

If rst!Traité - qté_traitée >= rst2!Arrivée Then
qté_à_traiter = rst2!Arrivée
Else
qté_à_traiter = rst!Traité - qté_traitée
End If

Loop
rst.Edit
rst!Delai = cumul_jour / rst!Traité

rst.Update
rst.MoveNext
Loop
Set db = Nothing
Exit Sub
err_delai:
If Err = 3021 Then
MsgBox "Impossible de calculer le délai du " & rst!Dte & vbCrLf & "Vous devez saisir au moins " & rst!Stock_depart & " en arrivée sur ce jour et/ou les jours précédents.", 48
Set db = Nothing
Exit Sub
Else
MsgBox Err.Description, , Err
End If
Exit Sub
End Sub
 

ERIC S

XLDnaute Barbatruc
Re

sous Excel (formules ou vba …) il est possible de faire quelque chose mais je ne comprends toujours pas ce que tu veux car dans ton post 7 les délais sont déjà calculés
je peux regarder pour te faire un calcul de délais en considérant que les données de départ sont les colonnes Dte … traité
Mais est-ce cela que tu veux ? car ce sont probablement déjà des données travaillées, les données de départ pouvant être 1 ligne par dossier.
 

Yves Decabooter

XLDnaute Nouveau
Alors en théorie le calcul devrait être le suivant :
si pas de qté traitées alors 0,
Ensuite pour la journée du 3/10 (70 qté traitées) => (42 venait de la vielle (soit 42*2jours) et 28 du jour même (soit 28*1).
Donc mon calcul est (42*2 + 28) = 112 que je divise par mon nombre de qté traitées 70.
Soit 112/70 = 1,6
Cqfd ☺

Par contre s'il reste du stock de l'avant veille celles-ci doivent être comptabilisé pour 3 jours de délais (*3) et ainsi de suite plus on prend du retard.
C'est cette formule là que je ne parvient pas à monter !
 

ERIC S

XLDnaute Barbatruc
re

sauf si tu es sûr d'avoir très souvent des stocks à 0, les formules vont se compliquer très vite
un essai avec une fonction personnalisée, à bousculer
 

Fichiers joints

Yves Decabooter

XLDnaute Nouveau
Je vais regarder à ta fonction... Mais en effet potentiellement, mes tableaux sont mensualisé donc le risque est d'aller théoriquement jusque 30j de retard, ce que je n'espère pas sinon je suis virés lol ☺.
 

Yves Decabooter

XLDnaute Nouveau
Bonjour, j'ai enfin eu le temps de regarder ta fonction qui semble bien fonctionner, sauf que mon fichier excel n'as pas les colonnes dans le même ordre que toi et que du coups j'ai du mal à la modifié pour mon cas. Je me perds dans les offsets ☺.

Voilà comment est fait mon fichier Excel.
upload_2018-12-6_10-3-31.png

Attention, il ne faut pas figer les lettres de colonnes car j'ai la même chose à calculer sur d'autres colonnes. Petite info, pas besoin de sierreur car mon solde du mois précédent est toujours noté avant.
 

Discussions similaires


Haut Bas