VBA : Calcul Coût gazole mensuel au km

[cibleo]

Bonsoir le forum,

Dans la feuille 2 en colonne E, j'aimerais afficher le coût mensuel du gazole au Km.
Pour cela, je divise donc les valeurs de la colonne B par celles de D.

Problème, les valeurs renvoyées en colonne B résultent d'une mauvaise méthode de calcul.

Exemple de Janvier 2012 :

Le kilométrage effectués est bien égal à 2801 (colonne D)
Le volume consommé est bien égal 151,05 litres (colonne C) mais le montant de 215,55 € n'est pas le bon.
Ce montant correspond au montant réglé en janvier 2012 mais pas au montant consommé puisque le dernier plein de janvier (26,15 Litres) n'est pas encore consommé.

Je ne vois pas du tout comment procéder, en fait le problème doit être similaire à de la gestion de stock et je ny connais rien
La capacité du réservoir est de 60 litres.

Le code dans le module 1 :

Sub Cout_Gazole()
Set mondico = CreateObject("Scripting.Dictionary")
Set mondico1 = CreateObject("Scripting.Dictionary")
Set mondico2 = CreateObject("Scripting.Dictionary")
Set mondico3 = CreateObject("Scripting.Dictionary")
For Each C In Sheets("BT745DG").Range("A4", Sheets("BT745DG").[A65536].End(xlUp))
mondico(Format(C.Value, "mmmm yyyy")) = mondico(Format(C.Value, "mmmm yyyy")) + 1
mondico1(Format(C.Value, "mmmm yyyy")) = mondico1(Format(C.Value, "mmmm yyyy")) + CDec(C.Offset(, 1).Value)
mondico2(Format(C.Value, "mmmm yyyy")) = mondico2(Format(C.Value, "mmmm yyyy")) + C.Offset(, 2).Value
mondico3(Format(C.Value, "mmmm yyyy")) = mondico3(Format(C.Value, "mmmm yyyy")) + C.Offset(, 5).Value
Next C
With Sheets("Feuil2")
.Range("A1:E" & .Range("A" & Rows.Count).End(xlUp).Row).Clear
.[A1] = "Immatriculation du véhicule : " & Mid(Sheets("BT745DG").[A1], InStrRev(Sheets("BT745DG").[A1], " ") + 1)
.[A2] = "Périodes": .[B2] = "Montants TTC": .[C2] = "Volumes"
.[D2] = "Kms effectués": .[E2] = "Coût au Km"
.[A3].Resize(mondico.Count, 1) = Application.Transpose(mondico.keys) 'Clé Périodes
.[B3].Resize(mondico.Count, 1) = Application.Transpose(mondico1.items) 'Montants Mensuels
.[C3].Resize(mondico.Count, 1) = Application.Transpose(mondico2.items) 'Volumes Mensuels
.[D3].Resize(mondico.Count, 1) = Application.Transpose(mondico3.items) 'Kms Mensuels
derli = .Range("A" & Rows.Count).End(xlUp).Row
For Lig = 3 To derli 'colonne E : Calcul du cout au km
.Cells(Lig, 5).Formula = "=" & .Cells(Lig, 2).Address & "/" & .Cells(Lig, 4).Address
' .Cells(Lig, 5).Value = .Cells(Lig, 2).Value / .Cells(Lig, 4).Value
Next
End With
End Sub

La feuille à traiter est la feuille "BT745DG"

Pouvez-vous m'aider à déterminer ce coût ?
Cibleo

 

[frangy]

Re : VBA : Calcul Coût gazole mensuel au km

Bonjour,

Pour simplifier, considères que tu as fait le plein le 31 Déc. 2011. (au lieu du 28 Déc. 2011).
La somme des pleins suivants va bien correspondre à la quantité consommée en janvier.
Soit 151,05 litres pour une distance de 2801 km et un coût de 215.55 €.
Ton calcul semble donc correct.
De plus, avec une moyenne de 5.4l/100km, tu t’en sors plutôt pas mal .

Cordialement.

 

[cibleo]

Re : VBA : Calcul Coût gazole mensuel au km

Re à tous,
Bonjour frangy,

En commençant par la 1ère période Mai 2011 :
En feuille 2, B3 affiche 92,51 € alors que devrait s'afficher 92,03 €

Feuille "BT745DG", je décompose le calcul soit :
- 36.32 litres à 1,308 € ---> 47,51 €
- 23,68 litres à 1,308 € ---> 30,97 €
- 10,36 litres à 1,308 € ---> 13,55 €

Donc :
- 70,36 Litres consommés représentent 92,03 € réellement déboursés pour mai 2011

36.32 + 23,68 = 60 litres soit la capacité du réservoir
23,68 + 10,36 = 34,04 litres ---> dernier plein du mois de mai 2011

Je n'arrive pas à restranscrire cet algorithme en VBA !

Cibleo

 

[frangy]

Re : VBA : Calcul Coût gazole mensuel au km

Re,

Je regarde la Feuille "BT745DG" sur la 1ère période Mai 2011 et je ne vois pas ce que tu décris.
Je lis :
- 36.32 litres à 1,308 € ---> 47,51 €
- 34.04 litres à 1,322 € ---> 45,00 €
Ce qui justifie l’affichage de 92,51 € en B3 de Feuil2.

Si je modifie les données avec :
- 36.32 litres à 1,308 € ---> 47,51 €
- 23,68 litres à 1,308 € ---> 30,97 €
- 10,36 litres à 1,308 € ---> 13,55 €
J’obtiens bien 92,03 € en B3 de Feuil2.

Cordialement.

 

[Misange]

Re : VBA : Calcul Coût gazole mensuel au km

Bonjour
une solution sans macro
je ne suis pas d'accord sur les consos trouvées par la macro ni pour le calcul pour mai 2011.
Avec la macro tu considères que tu as roulé seulement 615 km alors que tu en as fait 1315.

MAis pour tes calculs tu es obligé de faire comme les loueurs de voiture : tu prends un véhicule avec le plein et tu le rends avec le plein. Donc même si fin janvier tu paies un plein que tu n'utiliseras qu'en février, il faut considérer que quand tu as commencé tes calculs en mai 2011 tu es parti avec un plein que tu n'avais pas payé. Ca crée forcément un petit décalage du à l'augmentation du prix du gasoil mais tu ne peux pas faire autrement je mense.

 

[frangy]

Re : VBA : Calcul Coût gazole mensuel au km

Bonjour Misange, cibleo, le forum,

L’écart que tu constates au mois de mai est lié au fait que la ligne 1 de la feuille BT745DG a été déplacée (CONSOMMATION GAZOLE BT-745-DG).
Avec le fichier d’origine, le calcul est correct, la plage des données prises en compte débutant en ligne 4.

Cordialement.

 

[cibleo]

Re : VBA : Calcul Coût gazole mensuel au km

Bonjour le forum,
Bonjour Misange, frangy

On s'égare, je reprend tout à zéro pour se focaliser sur feuille "BT745DG".
Dans cette feuille au regard de chaque dates, je cherche à déterminer le prix moyen du litre de gazole à chaque plein effectué.
Le réservoir contiendra alors 60 litres.

Voir résultat souhaité en colonne H, I et J si je ne me suis pas trompé dans mes calculs manuels.
Pour l'instant, on laisse tomber feuille 2 et sa macro.

cibleo

 

[frangy]

Re : VBA : Calcul Coût gazole mensuel au km

Désolé d’insister mais si tu souhaites obtenir le coût moyen du gazole au Km, ton calcul est correct.
C’est le coût à la pompe divisé par le nombre de km effectués.
Que le réservoir fasse 60 litres ou 400 litres ne change rien. Quand tu fais le plein, tu rajoutes à ta réserve ce que tu as consommé et tu payes ce que tu as consommé au prix du jour. Les litres dans le réservoir, c’est du patrimoine. Tu récupèreras ta mise à la vente du véhicule (avec les plus-values).
La seule contrainte pour obtenir le coût moyen mensuel, c’est d’effectuer un plein à la fin du mois.
A mon avis, la question à se poser porte plutôt sur la finalité de ta demande. Si tu es persuadé que la méthode de calcul est mauvaise, c’est que vraisemblablement tu attends autre chose de ce calcul.

Cordialement.

 

[cibleo]

Re : VBA : Calcul Coût gazole mensuel au km

Bonsoir à tous,

Je pense toucher au but :
Feuille "BT745DG", j'ai rajouté 2 colonnes (H et I) pour calculer le coût de chaque plein consommé : voir les formules pas très jolies jolies.

Dans le code du module 1, j'ai remplacé cette ligne

mondico1(Format(C.Value, "mmmm yyyy")) = mondico1(Format(C.Value, "mmmm yyyy")) + CDec(C.Offset(, 1).Value)

par celle-ci pour tenir compte des valeurs de la colonne I et non plus de la colonne B

mondico1(Format(C.Value, "mmmm yyyy")) = mondico1(Format(C.Value, "mmmm yyyy")) + CDec(C.Offset(, 8).Value)

Cliquez en feuil 2 pour voir le résultat s'afficher.
En colonne E, j'obtiens donc le coût moyen du km pour chaque mois (gazole)
Pour info, ce sont de vrais données concernant un véhicule CITROEN C4 1,6 HDI 90 Ch FAP

En espérant ne m'être planté dans ma méthode de calcul.

Cibleo

Edit : comme j'aime bien les comparatifs, je m'aperçois que lorsque je parcours 11 kms en janvier 2012, j'ai consommé l'équivalent d'une baguette de pain soit 0,85 €, ça commence à faire chère la voiture.