XL 2019 Prime Qualité de service sur consommation de carburant

[pat_leouf]

Bonjour à tous,

Etant pas vraiment pote avec excel j'aurai besoin de vos sciences.

J'ai besoin de créer un fichier automatisé, je m'explique je relève toutes les semaines la moyenne de consommation carburant de mes véhicules (autocars au nombre de 40)
j'ai donc besoin que sur ma 1ere page excel d'avoir un recap propre de ces relevés (une colonne : dernier relevé (date) / type de vehicule / immat / dernier conso moyenne / moyenne de tous les releves) et un autre cadre avec dernier relevé / nom du conducteur / type de vehicule / dernier conso moyenne / moyennes de tous les relevés)

Je ne sais pas très bien si je suis clair.

Je vous un fichier de mon fichier (mes remplissages perso ne concerne que 2020)

Merci d'avance à vous.

 

[Exl-Noob]

Si tu ne retournes pas le fichier avec tes modifs dans l'onglet véhicule on ne pourra pas t'aider davantage.

 

[pat_leouf]

Oops je pensais l'avoir mis ce matin

 

[Exl-Noob]

@pat_leouf,

Voilà la fichier rectifié.
J'ai modifié l'onglet Véhicule, car tu avais encore pris le reflexe de faire plusieurs tableaux... non un seul avec une colonne qui distingue les véhicules
J'ai modifié l'onglet Données pour mettre à droite les colonnes calculées, seules celles au fond clair sont à compléter.
J'ai ajouté le type de véhicule dans l'onglet Analyse.
Pour faire apparaitre le type de véhicule dans le tableau par conducteur, j'ai utilisé une recherche avec deux conditions :

{=INDEX(Data;EQUIV(1;(Data[Conducteur]=[@Conducteur])*(Data[Date]=[@[Dernier plein]]);0);7)}

Pour la mise en forme conditionnelle, il fallait tenir compte de deux critères minimum pour affecter une couleur, ce qui s'est réglé par les règles suivantes :

orange =OU(ET($A2="Autocar";$H2>23;$H2<26);ET($A2="Minicar";$H2>11,5;$H2<13))
rouge =OU(ET($A2="Autocar";$H2>=26);ET($A2="Minicar";$H2>=13))
vert =OU(ET($A2="Autocar";$H2<=23);ET($A2="Minicar";$H2<=11,5))

Enfin, pour faire le classement du conducteur le plus économe, en fonction du type de véhicule utilisé, je n'ai pas su ajouter une condition à :

=EQUATION.RANG([@[Moyenne conso]];[Moyenne conso];1)

que j'ai donc remplacé par :

=NB.SI.ENS(Analyse_Conducteur[Type];Analyse_Conducteur[@Type];Analyse_Conducteur[Dernière conso moyenne];"<="&Analyse_Conducteur[@[Dernière conso moyenne]])

Au passage, je calcule désormais le classement sur la moyenne de tous ses pleins, plutôt qu'uniquement sur le dernier.

En bonus, j'ai ajouté un tableau croisé dynamique dans un nouvel onglet appelé TCD, avec son graphique qui va bien et les moyens de contrôle pour faire des analyses par date, conducteur, type de véhicule.

A suivre.

 

[pat_leouf]

@Exl-Noob
Franchement un très grand merci, je vais chipoter désolé, penses tu que sur la page analyses il soit faisable d'avoir un 3eme tableau avec la conso annuel par groupe d'autocar (un groupe "fast kid" un groupe "croswway" ...) ? je te joins le fichier avec mes données insérées (enfin j'ai pas terminé), dans le cas ou tu verrais une erreur.
L'onglet TCD est très intéressant mais je doute de la véracité du graphe ? (ou alors je suis miro)

 

[Exl-Noob]

@pat_leouf,

Dans la liste des Véhicules, j'ai ajouté un Minicar de prêt et remis en place la validation de données dans l'onglet Données pour éviter plus tard d'avoir des problèmes d'analyse sur les graphiques et tableaux (apparition de #N/A et zéros).
Mais la bonne pratique serait d'ajouter toutes les informations du véhicule de prêt dans l'onglet Véhicules : Type + numéro de parc (prêt) + Marque +modèle +Immatriculation + BV.
Il y a des chances de toute façon que tu les réutilises à l'avenir, tu n'auras pas à le faire à chaque nouvelle location.
Comme ça tu pourrais aussi renégocier parfois le tarif de location en fonction de la surconsommation que tu peux constater avec un véhicule prêté (ou mesurer si c'est un véhicule intéressant pour l'intégrer à ton parc ultérieurement). Bref, il faut capitaliser sur toutes les expériences acquises et éviter les fourre-tout. C'est mon côté hyper analyste qui prend le dessus

J'ai retrié le tableau par conducteur de l'onglet Analyse pour faire apparaitre plus clairement le rang par type de véhicule (Données > trier) avec la règle suivante :

Ca ne se met pas à jour tout seul, il faudra que tu le fasses à la main quand tu voudras faire un point ponctuel.

Tu disais que le graphique du TCD te paraissait erroné. Pas tout à fait. Il était exact avec les données bidon que j'avais incluses initialement. Pour la mise à jour du TCD il faut faire une actualisation (Clic dans le tableau > onglet Analyse > Actualiser), ou plus rapide, clic droit sur l'un des outils sous le graphique (Date/Conducteur/Type) puis Actualiser.
Je viens de le faire.

Pour répondre à ta demande, j'ai ajouté un graphique qui fait ressortir la consommation moyenne par Type/Marque/Modèle dans l'onglet TCD. On voit d'ailleurs que tu as Mercedes "Spinter"... il manque un "r", je te laisse faire un peu de boulot dans ta liste de véhicules

Tu as supprimé la fonction dans le colonne Nb km total de l'onglet Données. Il aurait sans doute fallu avant qu'on rajoute une colonne dans le véhicule, pour déterminer le kilométrage initial à partir duquel on effectue le suivi, et conserver la formule pour effectuer le calcul et éviter les erreurs humaines de calcul ou de recopie. Dit-moi si ça t'intéresse.

Et puisqu'on parle de prime de conduite après tout, le classement actuel ne corrige pas une injustice de fond : si comme conducteur on te remet un véhicule qui consomme davantage, tu seras pénalisé dans ta prime si elle est indexée sur la consommation moyenne que tu vas faire avec ce véhicule.
Du coup, j'ai aussi ajouté la BV dans le graphique, et il devient apparent que la BV automatique est plus économe : -0.62 litres/km pour l'instant (à voir avec un échantillon plus grand), à intégrer dans ton coût total d'acquisition la prochaine fois que tu vas hésiter entre acheter le véhicule avec une BV et une BM....

Je ne sais pas quels sont les indicateurs que tu utilises pour le calcul de la prime, mais en grand pourfendeur des injustices, je me dis que ça mériterait presque un onglet distinct à cette fin pour intégrer la consommation moyenne du chauffeur comparée à la consommation moyenne des autres chauffeurs avec le même véhicule. Bref, mais ça c'était si on construisait un tableau pour calculer une prime de conduite et que j'avais toutes les données en main. Là on vient juste de finaliser un outil de suivi de tes consommations je crois

A suivre.