Calcul de consommation carburant d'un parc de véhicules

[ivan27]

Bonsoir à tous,

Ce sujet a déjà été traité de nombreuses fois mais je n'ai pas trouvé mon bonheur parmi les propositions existantes sur le forum.

Je vous explique sur la pièce jointe ce que je souhaite obtenir.

Le fichier final représentera très probablement plus de 20000 lignes et plusieurs dizaines de colonnes.

Par ailleurs, je souhaite utiliser des colonnes sans calcul de consommation pour faire apparaître des écarts, des ratios ou des sparklines.

Aussi, une proposition par VBA me paraît plus adapté à ma demande...

N'hésitez pas à m'interroger si je n'ai pas été suffisamment précis dans mes explications.

Bien cordialement et merci d'avance pour votre aide.

Ivan

 

[ROGER2327]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonjour ivan27.

Un essai en pièce jointe.
Voyez si les résultats attendus sont au rendez-vous.​

Bonne soirée.


ℝOGER2327
#7981

Mercredi 18 Tatane 142 (Saint Trou, chauffeur - fête Suprême Quarte)
13 Thermidor An CCXXIII, 6,7208h - abricot
2015-W31-5T16:07:48Z

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonjour ROGER2327, le forum,

Merci beaucoup pour le travail réalisé.
Après mes premiers tests, je te livre mes premières remarques et suggestions...
- Les modifications dans la feuille BD, ne sont pas prises en compte par les formules.
- Lorsque un calcul est impossible, est-il possible de retourner une cellule vide plutôt que 0,00 ?
Je vais faire d'autres tests dans les semaines à venir et je t'informerai des problèmes éventuels.
Je réitère mes remerciements,
Bien cordialement et bonne journée,
Ivan

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonsoir ROGER2327, le forum,

Je reviens sur ce sujet.
Je n'ai constaté aucune erreur sur les périodes testées.
Par contre, j'ai toujours un problème avec les calculs qui ne se font pas après mise à jour de la feuille BD et il y a le risque d'oublier de copier les formules sur des longues listes sur la feuille STAT.
Est-il possible de convertir la fonction en procédure (calcul des litres / 100 km) ?
Merci d'avance
Ivan

 

[ROGER2327]

Re : Calcul de consommation carburant d'un parc de véhicules

Re...

(...)
Je n'ai constaté aucune erreur sur les périodes testées.
(...)

Parfait.
Je n'étais pas certain d'avoir exactement compris le problème. C'est pourquoi j'ai proposé une solution brute de fonderie. Ce n'est certainement pas sous cette forme qu'il faut l'exploiter.
Nous allons pouvoir aller plus loin.​

(...)
Par contre, j'ai toujours un problème avec les calculs qui ne se font pas après mise à jour de la feuille BD (...)

Pour que les calculs s'exécutent à la volée, on peut ajouter

Application.Volatile

au début du code de la fonction. Mais l'exécution sera assez lente s'il y a beaucoup de lignes.​

(...) et il y a le risque d'oublier de copier les formules sur des longues listes sur la feuille STAT.
Est-il possible de convertir la fonction en procédure (calcul des litres / 100 km) ?

C'est tout à fait possible.
Je dois m'absenter jusqu'à dimanche : je vous ferai des propositions la semaine prochaine.
En attendant, une question : les données sont-elles toujours ordonnées par dates (croissantes ou décroissantes) ?
​

Bonne fin de semaine.


ℝOGER2327
#7984

Mardi 24 Tatane 142 (Saint Pissedoux, caporal des hommes libres - fête Suprême Quarte)
19 Thermidor An CCXXIII, 2,2491h - gentiane
2015-W32-4T05:23:53Z

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonsoir ROGER2327, le forum,

Je vous réitère mes remerciements pour votre implication dans le traitement de ma demande.
Les données sont toujours ordonnées par dates croissantes.
En attendant la semaine prochaine et des nouvelles propositions, je poursuis les tests avec cette version.
Bien cordialement,

Ivan

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonsoir ROGER2327, le forum,

J'ai identifié un comportement non souhaité.
Voir pièce jointe.
Bien cordialement et bonne fin de semaine à tous.

Ivan

 

[ROGER2327]

Re : Calcul de consommation carburant d'un parc de véhicules

Re...

(...)
- Lorsque un calcul est impossible, est-il possible de retourner une cellule vide plutôt que 0,00 ?
(...)

(...)
Est-il possible de convertir la fonction en procédure (calcul des litres / 100 km) ?
(...)

C'est fait dans le classeur joint.
Il n'y a plus de formule =conso(), les feuilles de résultats sont recalculées lors de leur activation. Pour cela, et dans l'ignorance de la structure réelle de votre classeur, j'ai dû définir quelques plages nommées pour que la fonction sache où prendre les données.​

(...)
J'ai identifié un comportement non souhaité.
(...)

Plus embêtant, ça...
Un essai dans le classeur joint.
Dans les onglets Conso_1 et Vol_1, j'ai conservé la fonction initiale (rebaptisée Conso_1()).
Dans les onglets Conso_2 et Vol_2, j'ai utilisé une variante nommée Conso_2(). Il me semble qu'elle règle le problème signalé, mais j'ai bien peur qu'elle en crée d'autres. À vous de voir.​

Bonne journée.


ℝOGER2327
#7987

Samedi 28 Tatane 142 (Nativité de Saint Bruggle - fête Suprême Quarte)
23 Thermidor An CCXXIII, 5,6069h - lentille
2015-W33-1T13:27:23Z

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonsoir ROGER2327, le forum,

Merci pour ces nouvelles propositions pour lesquelles je vous livre mes premières analyses :

Améliorations :
- Je préfère ce «*système*» de calcul automatique lors de l’activation d’une feuille, à celui de la fonction.
- En cas de calcul impossible, les procédure renvoie une cellule vide au lieu de 0.

Dysfonctionnements :
- Sur les feuilles Conso et Vol, les calculs ne se font pas au-delà de la ligne 8 et de la colonne AM

- Pas de changement pour Conso_1; l’effet non souhaité décrit hier est toujours présent.

- Dysfonctionnement pour Conso_2

Exemple pour le véhicule AA-111-AA sur la période du 27/02/13 au 25/04/13
La procédure devrait trouver 11530 kilomètres et 4076,1 litres, soit une consommation de 35,35 litres au 100

Or,
- Conso_1 / Vol_1 trouve un volume de 4578,10 litres pour une conso de 35,39 litres au 100
- Conso_2 / Vol_2 trouve un volume de 4478,10 litres pour une const de 34,83 litres au 100

Conso_1 se base sur les données du jour suivant comme décrit dans mon précédent post,
pour conso_2 :
- volume du jour suivant -100 litres
- kilométrage = 12857 km (la procédure prend comme kilomètre de départ 449383 du 22/02/13 au lieu de 450710 du 27/02/13

Bien cordialement,

Ivan

 

[ROGER2327]

Re : Calcul de consommation carburant d'un parc de véhicules

Re...

(...)

Dysfonctionnements :
- Sur les feuilles Conso et Vol, les calculs ne se font pas au-delà de la ligne 8 et de la colonne AM
(...)

Comme je vous l'ai indiqué dans mon précédent message, j'utilise des plages nommées pour fournir les données à la procédure ; ces plages sont nommées PlgDates et PlgId. Adaptez-les à votre convenance.​

(...)

- Pas de changement pour Conso_1; l’effet non souhaité décrit hier est toujours présent.

(...)

Normal : comme je vous le disais plus haut,​

Dans les onglets Conso_1 et Vol_1, j'ai conservé la fonction initiale (rebaptisée Conso_1()).

(...)
- Dysfonctionnement pour Conso_2

Exemple pour le véhicule AA-111-AA sur la période du 27/02/13 au 25/04/13
La procédure devrait trouver 11530 kilomètres et 4076,1 litres, soit une consommation de 35,35 litres au 100
(...)

Je ne trouve pas de solution satisfaisante pour tous les cas.
Dans le classeur joint, les onglets Conso_3 ou Vol 3 donne le résultat que vous voulez sur la période du 27/02/13 au 25/04/13. Mais je doute qu'elle soit correcte en général !

J'imagine que je n'ai pas trouvé le bon principe. Peut-être faut-il repartir sur une autre base ? Mais je n'ai pas d'idée pour le moment.​

Bonne journée.


ℝOGER2327
#7989

Mercredi 4 Phalle 142 (Saint Mnester, confesseur - fête Suprême Quarte)
27 Thermidor An CCXXIII, 5,2445h - colsa
2015-W33-5T12:35:12Z

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonjour ROGER2327, le forum,

Merci pour cette nouvelle proposition. Je reviens vers vous après le test de cette version3.
Bien cordialement,

Ivan

 

[ivan27]

Re : Calcul de consommation carburant d'un parc de véhicules

Bonjour ROGER2327, le Forum,

Comme convenu, je reviens vers vous après des tests de cette version 3.

Pour ne pas avoir à effectuer des nombreux calculs manuels, j'ai créé rapidement une formule dans un onglet "Conso_4".

Il ressort que la dernière proposition "Conso_3" est presque parfaite.

Voici mes observations :

- 5 écarts sont constatés par rapport à "Conso_4". Les erreurs se produisent quand pour un même véhicule il y a plusieurs pleins le même jour. Un conducteur peut faire le plein de son véhicule le matin avant le départ et refaire le plein au retour les soir. En ças de prises multiples le même jour, c'est la dernière qui doit être prise en compte.

- Concernant la vitesse d'exécution des calculs lors de la sélection de la feuille : Plus les plages PlgDates et PlgId sont importantes, plus le temps de calcul est long, même si un seul véhicule est présent en colonne A. Avec une centaine de ligne seulement, les résultats apparaissent après 25 secondes environ.

Bien cordialement,

Ivan