Calcul taux de service avec userform
Bonjour, je suis un débutant sur vba et j'espère que mon explication sera assez claire.
Je dois réaliser un tableau permettant de déterminer les taux de service des fournisseurs de l'entreprise. Les données à traiter sont issues d'une extraction SAP.
Je souhaiterais traiter ces données de telle sorte qu'à partir d'un userform on puisse avec des menus déroulants obtenir le taux de service par fournisseur, par article, par mois. Je ne sais pas vraiment par où commencer, le calcul du taux de service me parait simple à coder mais je ne sais comment traiter dans un premier temps les données brutes obtenues depuis SAP.
Je joins à ce message un fichier excel avec sur la 1ere page un exemple de données qui peuvent être issues de SAP pour un fournisseur. J'ai également réalisé la mise en forme du userform qui permettra sans doute plus clairement de comprendre ma démarche.
Merci de votre compréhension.
bonjour
mon avis personnel : pas de userform, mais un tableau croisé
simple, élégant, pas de code, hyper fiable, facile à modifier si besoin
Bonsoir le fil, bonsoir le forum,
Mon avis, personnel aussi : une UserForm ! simple, élégant, code, hyper fiable, facile à modifier et ludique...
Seul problème, et de taille, l'ignare que je suis ne sait pas ce qu'est le taux de service des fournisseurs de l'entreprise.
Si tu te donnais la peine d'expliquer clairement ce que tu attends comme résultat dans les textboxes, je pourrais essayer de te coder ça...
@ Jmd. Je plaisante bien sûr. Je suis plus que convaincu de l'efficacité des formules et des TCD mais... Ça ne passe pas, je n'y peux rien. Le VBA m'éclate, le reste m'emm...de.
re salut à tous
ThauThème,
bon, mais je comprendrai jamais le plaisir du codage (et pourtant j'en fais 4 h certains jours, c'est sans doute pour ça
le taux de service en % c'est
= 1 - [ (nombre de livraisons en retard / nombre total des livraisons) % ]
(formule éditée 2 fois, j'ai du mal à ne pas écrire = D34 -F567 + DE56 * 22.457 / PI() * l'âge du capitaine
rem : il est aussi intéressant de sortir le retard maxi par mois (ou par an ou par...) car cela représente le client le plus mécontent
Merci pour vos réponses. Je vais tester le TCD. Pour ce qui est du taux de service je souhaite distinguer deux aspects: le respect de la quantité ((qté reçue/qté attendue)*100) et comme l'a dit jmd, le respect du délai de livraison).
Ci-joint un fichier exemple où en lançant la macro analyse vous récupérez le taux de service avec une gestion assez simple de l'alerte en cas de retard.
C'est plus limite "une idée" ou une "base de départ" (à prendre ou à laisser) pour votre besoin de suivi d'indicateur en fonction de ce que vous voulez suivre...(Le plus compétant pour livrer tel produit, le plus compétent sur tous les produits...etc etc!)
Je ne sais pas comment vous comptez faire mais c'est d'usage, où du moins très recommandé, de s'engager sur se terrain là avec une politique PDCA partagée par tous sinon le risque majeur, c'est que les relevés ne reste que des relevés qui dormiront dans un tiroir/dossier et qu'un jour, quelqu'un découvrira par hasard...
Au delà de l'aspect industriel, pensez à bien ranger vos données en ligne pour qu'ensuite le travail avec le TCD soit moins fastidieux
Merci pour votre réponse Addict. Je rencontre cependant un problème au niveau du traitement des dates avec votre code:
dans la 1ere feuille on a une date du type: "08.10.2013" et celle-ci se transforme en "10/08/2013" après avoir simplement remplacé les "." par des "/".
Je ne parviens pas à obtenir la bonne date qui devrait être 08/10/2013, j'ai tenté d'utiliser .NumberFormat ou de convertir les données de la feuille 1 grâce à Cdate mais je n'obtiens pas le résultat recherché.
Merci pour votre aide.
Bonjour le fil, bonjour le forum,
Il vaut envoyer les dates au format américain (YYYY/MM/DD) pour qu'elles soient ensuite écrites au format indiqué dans les cellules. Essaie avec le code modifié :
Sub analyse()
Dim dl As Long
Dim i As Integer
Dim CEL As Range
Dim DT As String
dl = Sheets("import").Range("a" & Rows.Count).End(xlUp).Row
Sheets("traitement").Activate
Sheets("traitement").Cells.Clear
Sheets("import").Cells.Copy Sheets("traitement").Cells(1, 1)
With Sheets("traitement")
.Range("K1") = 100
.Range("a2").EntireRow.Delete
.Range("h1") = "Taux de Service"
.Range("i1") = "Jours de retard"
.Columns("D:D").Replace What:=".", Replacement:=""
.Columns("E:E").Replace What:=".", Replacement:=""
.Columns("E:E").Replace What:=",", Replacement:=""
.Range("K1").Copy
.Range("D2:d" & dl - 1).PasteSpecial operation:=xlPasteSpecialOperationDivide
.Range("e2:e" & dl - 1).PasteSpecial operation:=xlPasteSpecialOperationDivide
.Range("K1") = ""
For Each CEL In Range("F2:G" & dl - 1)
DT = Split(CEL.Value, ".")(2) & "/" & Split(CEL.Value, ".")(1) & "/" & Split(CEL.Value, ".")(0)
CEL.Value = DT
Next CEL
For i = 2 To dl - 1
.Range("h" & i) = Application.WorksheetFunction.Round((.Range("e" & i) / .Range("d" & i) * 100), 0)
If .Range("g" & i) > .Range("f" & i) Then
.Range("i" & i) = DateDiff("d", Range("f" & i), .Range("g" & i))
.Range("h" & i) = "0"
End If
Next
.Range("A2").Select
End With
Application.CutCopyMode = False
End SubMince désolé! je n'avais pas connaissance de se...phenomène! ceci dit, tauthème rectifie mon incompétence
Merci, ce code m'a bien servi.
Je rencontre de nouvelles difficultés du fait de l'import dans le fichier d'un nouveau fournisseur:
certains chiffres sont de types "11.000,00" et d'autres sont directement au bon format, c'est à dire "11000".
Je souhaiterais que ne soient traités uniquement ceux qui ne sont pas au bon format.
Je joint un classeur avec ces nouvelles données.
J'espère que ma demande n'est pas trop excessive mais on me demande absolument de réaliser ce travail sous forme de userform.
Dans le fichier Excel je joint le userform que j'attends, son contenu est le suivant:
- un menu déroulant avec la possibilité de sélectionner les différents fournisseurs présents dans la feuille où sont réalisés les traitements
- un menu déroulant permettant de sélectionner le mois et l'année de la date attendue
- un bouton qui réalise la moyenne des taux de service du fournisseurs sélectionné pour le mois et l'année sélectionnés et pour la totalité des articles de ce fournisseur si aucun article n'est sélectionné dans le menu déroulant article
-un menu déroulant qui permet de sélectionner un article en particulier de ce fournisseur (la référence d'un article d'un fournisseur n'est pas présente chez un autre fournisseur)
La possibilité d'obtenir une moyenne par trimestre par article et par fournisseur m'est également demandée.
La tâche me parait ardue mais j'espère que vous pourrez m'accorder quelques conseils à minima.
Merci par avance.
Bonjour tout le monde!
Ce type de calcul m'intéresserait également dans une approche client. Mais je n'ai pas les compétences techniques pour répondre à vos attentes.
Si quelqu'un peut aiguiller cette demande en ce qui concerne l'approche avec le userform je suis intéressé.
Cdlt.
Bonjour, j'ai réussi à créer deux menus déroulants:
- une combobox comprenant les fournisseurs
- une combobox liée à la précédente qui comprend les articles
J'ai une difficulté sur mes 3ème et 4eme combobox où je souhaite inclure une délimitation de période comprenant mois+année où il existe une réception prévue pour un article (colonne g de la feuille excel).
Ces combobox sont donc liées à celle comprenant les articles. Mon problème est que je dispose dans ma feuille excel de date comprenant jours/mois/année, à partir de ces dates je dois obtenir dans les 3eme et 4eme combobox uniquement mois et année.
La 4eme combobox ne serait accessible qu'une fois que la 3eme à été complétée, et les dates de la 4eme doivent être plus avancées dans le temps que celles de la 3eme.
Je vous remercie. Je reviendrais vers vous dans un second temps si je ne suis pas parvenu à réaliser les calculs par la suite.
Merci.
Je copie ici le code que j'ai modifié dans mon userform:
Ma combobox3 s'intitule MoisDepart mais elle ne fonctionne pas, les mois ne s'affichent pas.
'Cette procédure s'exécute lorsque s'initialise l'UserForm
Private Sub UserForm_Initialize()
'Remplir la ComboBox1 sans doublons
Dim i As Integer
For i = 2 To Sheets("Traitement").Range("A65536").End(xlUp).Row
ListeFourn = Sheets("Traitement").Range("A" & i)
If ListeFourn.ListIndex = -1 Then ListeFourn.AddItem Sheets("Traitement").Range("A" & i)
Next i
'mise à zéro des contrôles
ListeFourn = ""
ListeArticle.Clear
MoisDepart.Clear
End Sub
Private Sub ListeFourn_Change()
Dim i As Integer
'Cette procédure s'exécute lorsque la valeur de Combobox1 change
'remplir la ComboBox2
'Ici le test dit que si la donnée en colonne A = valeur combobox1
'Alors on insère la valeur contenue colonne C dans combobox2
'Et comme la Combobox1, on enlève les doublons...
ListeArticle.Clear
For i = 2 To Sheets("Traitement").Range("C65536").End(xlUp).Row
If Sheets("Traitement").Range("A" & i) = ListeFourn.Value Then
ListeArticle = Sheets("Traitement").Range("C" & i)
If ListeArticle.ListIndex = -1 Then ListeArticle.AddItem Sheets("Traitement").Range("C" & i)
End If
Next i
ListeArticle = ""
End Sub
Private Sub MoisDepart_Change()
Dim i As Integer
MoisDepart.Clear
MoisDepart.Value = Format(MoisDepart.Value, "mm/yyyy")
For i = 2 To Sheets("Traitement").Range("G65536").End(xlUp).Row
If Sheets("Traitement").Range("C" & i) = ListeArticle.Value Then
MoisDepart = Sheets("Traitement").Range("G" & i)
If MoisDepart.ListIndex = -1 Then MoisDepart.AddItem Sheets("Traitement").Range("G" & i)
End If
Next i
MoisDepart = ""
End SubBonsoir le fil, bonsoir le forum,
En pièce jointe ton fichier modifié et commenté. J'ai remplacé les plages de type Range par des tableaux de cellules de type Variant. Cela accélère considérablement l'exécution du code. Pour la même raison, j'ai utilisé un objet Dictionary pour éviter les doublons. J'ai réussi à récupérer les dates (mois/année) dans les Comboboxes 4 et 6 mais je ne savais pas ce qu'il fallait faire pour les Comboboxes 5 et 7. J'avoue que je n'ai toujours rien compris au Taux de Service alors je te laisse le soin de voir ça...
Bonjour à tous,
Encore merci pour cette réponse avec en plus une description bien précise pour chaque étape!
En ce qui concerne les combobox 5 et 7, elles sont basées sur les mêmes données que les combobox 4 et 6 (colonne G de la feuille en l'occurrence) mais je souhaite introduire une contrainte qui implique que les dates sélectionnables dans les combobox 5 et 7 soient supérieures ou égales à celles sélectionnées dans les combobox 4 et 6.
Je ne parviens pas à introduire cette contrainte, comment dois-je procéder?
Je copie ici le code au cas où.
Merci.
Private Sub UserForm_Initialize() 'Cette procédure s'exécute lorsque s'initialise l'UserForm
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Long 'déclare la variable I (Incrément)
Set T = Sheets("Traitement") 'définit l'onglet T
TC = T.Range("A1").CurrentRegion 'définit le tableau de cellules TC
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To UBound(TC, 1) 'boucle sur toutes les lignes I du tableau de cellules TC (en partant de la seconde)
If TC(I, 1) <> "" Then D(TC(I, 1)) = "" 'alimente le dictionnaire D avec les valeurs de la colonne 1 (=A) [Fournisseur]
Next I 'prochaine ligne de la boucle
Me.ListeFourn.List = D.keys 'alimente la combobox [ListeFourn] avec la liste des éléments du dictionnaire D sans doublons
End Sub
Private Sub ListeFourn_Change() 'Cette procédure s'exécute lorsque la valeur de Combobox1 change
Dim I As Long 'déclare la variable I (Incrément)
Dim TL() As Variant 'déclare la variable T (onglet Traitement)
Dim J As Long 'déclare la variable T (onglet Traitement)
ListeArticle.Clear 'vide la combobox [ListeArticle]
For I = 2 To UBound(TC, 1) 'boucle sur toutes les lignes I du tableau de cellules TC (en partant de la seconde)
'condition : si la valeur ligne I colonne 1 de TC est égale à la valeur de la combobox [ListeFourn]
If TC(I, 1) = Me.ListeFourn.Value Then
ReDim Preserve TL(J) 'redimensionne le tableau de lignes TL
TL(J) = TC(I, 3) 'récupère dans la ligne J du tableau TL la valeur de la colonne 3 de TC (transposition)
J = J + 1 'incrément J (ajoute une ligne à TL)
End If 'fin de la condition
Next I 'prochaine ligne de la boucle
If J = 0 Then Exit Sub 'juste pour éviter un plantage si valeur saisie (au lieu de choisie) dans la combobox [ListeFourn]
Me.ListeArticle.List = Application.Transpose(TL) 'alimente la combobox [ListeArticle] avec le tableau TL tranposé
End Sub
Private Sub ListeArticle_Change() 'Cette procédure s'exécute lorsque la valeur de Combobox2 change
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Long 'déclare la variable I (Incrément)
Dim E As Object 'déclare la variable E (dictionnaire)
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To UBound(TC, 1) 'boucle sur toutes les lignes I du tableau de cellules TC (en partant de la seconde)
'condition 1 : si la valeur ligne I colonne 1 de TC est égale à la valeur de la combobox [ListeFourn] et
'si la valeur ligne I colonne 3 (convertie en textte) de TC est égale à la valeur de la combobox [ListeArticle]
If TC(I, 1) = Me.ListeFourn And CStr(TC(I, 3)) = Me.ListeArticle Then
On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
'alimente le dictionnaire avec les valeurs de la colonne 7 (=G) [date prévue], sans le jour
'peux générer une erreur si la valeur n'est pas une date
D(Split(TC(I, 7), "/")(1) & "/" & Split(TC(I, 7), "/")(2)) = ""
If Err <> 0 Then 'condition 2 : si une erreur a été générée
Err.Clear 'supprime l'erreur
GoTo suite 'va à l'étiquette suite
End If 'fin de la condition2
On Error GoTo 0 'annule la gestion des erreurs
End If 'fin de la condition 1
suite: 'étiquette
Next I 'prochaine ligne de la boucle
Me.ComboBox4.List = D.keys 'alimente la ComboBox4 avec la liste des éléments du dictionnaire D sans doublons
Me.ComboBox6.List = D.keys 'alimente la ComboBox6 avec la liste des éléments du dictionnaire D sans doublonsBonjour le Fil, bonjour le forum,
En pièce jointe le fichier modifié avec ce code :
Private Sub ComboBox4_Change() 'au changement dans la ComboBox4
Dim I As Byte 'déclare la variable I
Me.ComboBox5.Clear 'vide la ComboBox5
For I = Me.ComboBox4.ListIndex + 1 To Me.ComboBox4.ListCount - 1 'boucle sur les éléments de la ComboBox4 après l'élément choisi
Me.ComboBox5.AddItem Me.ComboBox4.List(I) 'ajoute l'élément à la ComboBox5
Next I 'prochain élément de la boucle
End Sub
Private Sub ComboBox6_Change() 'au changement dans la ComboBox6
Dim I As Byte 'déclare la variable I
Me.ComboBox7.Clear 'vide la ComboBox7
For I = Me.ComboBox6.ListIndex + 1 To Me.ComboBox6.ListCount - 1 'boucle sur les éléments de la ComboBox6 après l'élément choisi
Me.ComboBox7.AddItem Me.ComboBox6.List(I) 'ajoute l'élément à la ComboBox7
Next I 'prochain élément de la boucle
End SubMerci, cela fonctionne parfaitement.
Je vais tenter de mettre en place les calculs que je souhaite réaliser. Je reviendrais vers vous dans la soirée si je n'y suis pas parvenu sinon je validerais votre réponse.
Encore merci ThauThème.
Bonsoir, je ne suis pas parvenu à atteindre le résultat visé, je précise donc mon besoin en ce qui concerne les deux commandbutton de mon userform et les textbox correspondantes.
Lors d'un clic sur le bouton "analyse fournisseur" (commandbutton6), je souhaite réaliser la moyenne des valeurs présentes dans la colonne J ("taux de service") pour le fournisseur sélectionné dans la combobox en tenant compte également de la période sélectionné sur les combobox 4 et 5. Il ne faut donc pas prendre en compte la combobox contenant les articles.
Le résultat s'affiche alors dans la textbox6.
Pour le bouton "analyse article", la moyenne est toujours réalisée sur les valeurs présentes dans la colonne J mais en tenant cette fois-ci compte du fournisseur sélectionné, de l'article sélectionné et de la période sélectionnée dans les combobox 6 et 7. Il est donc nécessaire d'avoir effectué un choix dans chaque liste déroulante.
Le résultat s'affiche dans la textbox7.
De manière générale, pour ce qui est de la sélection des périodes, si on s'intéresse à obtenir un résultat pour le mois de mars 2014 uniquement par exemple, il faut sélectionner dans la combobox de départ 03/14 et dans celle de fin 03/14 également.
J'espère avoir été assez clair avec ces éléments.
Je joins le fichier où j'ai ajouté une toute petite modification.
Merci !
Bonsoir le fil, bonsoir le forum,
En pièce jointe la version 3 avec les calculs. Le seul doute que j'ai est au niveau de la date. Je ne savais pas si il fallait prendre la colonne 7 (date prévue) ou la 8 (date réelle). J'ai pris la 7, tu adapteras...
C'est parfait, cela fonctionne.
Merci beaucoup!