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.

 

[pat_leouf]

Bonjour,

si je n'ai pas été clair dans mon message hésitez pas à me le faire savoir.

 

[djidji59430]

Bonjour à tous,

On trouve-t-on la liste de tous tes autocars ?


Crdlmt

 

[Exl-Noob]

Bonjour @pat_leouf,

Tu as de la chance, j'ai piloté un gestionnaire de parc roulant, donc je comprends bien le besoin, c'était même moi le prescripteur en fait

Visiblement tu as besoin de beaucoup de structuration préalable de tes données.

1. Tu as des tableaux sans intitulé de colonne (onglet "Tourisme" par exemple, ou colonne J, B et D de "fast 3")
2. Tu as des colonnes où la remise à zéro est exprimée de diverses manières (vide, ?, ????, x, X, OUI, oui, CAHIER, Facture), il ne devrait y avoir que deux valeurs possibles.
3. Pas de mise en forme conditionnelle (tu fixes la couleur des cellules à la main !? Risque d'erreur/oubli énorme). Rien à faire à ce stade, je ferai cela après.
4. Quel est l'intérêt de la colonne BV (boite de vitesse) ? C'est pas comme si cette information allait changer entre deux pleins...
5. En 2020 la colonne Véhicule de l'onglet "autres scolaires" contient la marque du véhicule et non plus l'immatriculation comme auparavant
6. Tu souhaites avoir le "type de véhicule" dans la synthèse mais cette valeur ne semble définie nulle part.

Je te propose de structurer tes données différemment.

• Un seul onglet avec toutes les données des véhicules, c'est à dire tout ce qui ne change pas (pour l'instant je je dénombre que 34 véhicules en comptant les VL).
• Un onglet avec les consommations, c'est à dire les éléments variables.
• Un onglet avec les conducteurs

Et on utilisera l'onglet synthèse pour faire apparaître toutes les analyses générées.
Peux-tu préciser ce que signifie "moyenne de tous les relevés" ? Ce serait la moyenne de consommation depuis que le véhicule est en circulation (pour pouvoir comparer la dernière utilisation avec la consommation moyenne du véhicule en général) ?

Je te propose déjà de transférer/compléter tes données sous ce nouveau format et on partira de là.

A suivre.

PS : il existe des solutions de relevé automatique des consommations et des kilométrages en utilisant les sondes de remplissages du véhicule. Je vais pas faire de la pub, mais ça évite les erreurs. En plus les véhicules sont trackés pour le même prix.

 

[pat_leouf]

M

Bonjour @pat_leouf,

Tu as de la chance, j'ai piloté un gestionnaire de parc roulant, donc je comprends bien le besoin, c'était même moi le prescripteur en fait

Visiblement tu as besoin de beaucoup de structuration préalable de tes données.

1. Tu as des tableaux sans intitulé de colonne (onglet "Tourisme" par exemple, ou colonne J, B et D de "fast 3")
2. Tu as des colonnes où la remise à zéro est exprimée de diverses manières (vide, ?, ????, x, X, OUI, oui, CAHIER, Facture), il ne devrait y avoir que deux valeurs possibles.
3. Pas de mise en forme conditionnelle (tu fixes la couleur des cellules à la main !? Risque d'erreur/oubli énorme). Rien à faire à ce stade, je ferai cela après.
4. Quel est l'intérêt de la colonne BV (boite de vitesse) ? C'est pas comme si cette information allait changer entre deux pleins...
5. En 2020 la colonne Véhicule de l'onglet "autres scolaires" contient la marque du véhicule et non plus l'immatriculation comme auparavant
6. Tu souhaites avoir le "type de véhicule" dans la synthèse mais cette valeur ne semble définie nulle part.

Je te propose de structurer tes données différemment.

• Un seul onglet avec toutes les données des véhicules, c'est à dire tout ce qui ne change pas (pour l'instant je je dénombre que 34 véhicules en comptant les VL).
• Un onglet avec les consommations, c'est à dire les éléments variables.
• Un onglet avec les conducteurs

Et on utilisera l'onglet synthèse pour faire apparaître toutes les analyses générées.
Peux-tu préciser ce que signifie "moyenne de tous les relevés" ? Ce serait la moyenne de consommation depuis que le véhicule est en circulation (pour pouvoir comparer la dernière utilisation avec la consommation moyenne du véhicule en général) ?

Je te propose déjà de transférer/compléter tes données sous ce nouveau format et on partira de là.

A suivre.

PS : il existe des solutions de relevé automatique des consommations et des kilométrages en utilisant les sondes de remplissages du véhicule. Je vais pas faire de la pub, mais ça évite les erreurs. En plus les véhicules sont trackés pour le même prix.

Merci à toi je regarde ca de près et revient au plus vite

 

[pat_leouf]

Et on utilisera l'onglet synthèse pour faire apparaître toutes les analyses générées.
Peux-tu préciser ce que signifie "moyenne de tous les relevés" ? Ce serait la moyenne de consommation depuis que le véhicule est en circulation (pour pouvoir comparer la dernière utilisation avec la consommation moyenne du véhicule en général) ?

Je te propose déjà de transférer/compléter tes données sous ce nouveau format et on partira de là.

A suivre.

PS : il existe des solutions de relevé automatique des consommations et des kilométrages en utilisant les sondes de remplissages du véhicule. Je vais pas faire de la pub, mais ça évite les erreurs. En plus les véhicules sont trackés pour le même prix.

Pour répondre à tes questions effectivement il s'agit de la moyenne de tous les relevés pour un même véhicule en comparaison du dernier relevé.

Ensuite j'ai aussi besoin de la même chose mais par conducteur (pouvoir connaitre l'évolution de la conso d'un conducteur sur son véhicule)

 

[Exl-Noob]

Ensuite j'ai aussi besoin de la même chose mais par conducteur (pouvoir connaitre l'évolution de la conso d'un conducteur sur son véhicule)

Ok, mais cela implique que le conducteur a un véhicule affecté ? Ou alors tu souhaites avoir plusieurs graphique en fonction du type de véhicule utilisé (VL/TC) ? comme tu as plusieurs catégories de TC il faut pouvoir les différencier dans la liste des véhicules.
Il peut aussi être intéressant de regarder la conso moyenne par véhicule et conducteur, pour identifier ceux qui consomment davantage que d'autres avec le même outil (postulat : les tournées sont faites aléatoirement et ce n'est pas toujours le même qui se retrouve à aller en montagne ou sur autoroute, sinon c'est certain qu'il aura toujours le bilan le plus défavorable).

 

[pat_leouf]

Ok, mais cela implique que le conducteur a un véhicule affecté ? Ou alors tu souhaites avoir plusieurs graphique en fonction du type de véhicule utilisé (VL/TC) ? comme tu as plusieurs catégories de TC il faut pouvoir les différencier dans la liste des véhicules.
Il peut aussi être intéressant de regarder la conso moyenne par véhicule et conducteur, pour identifier ceux qui consomment davantage que d'autres avec le même outil (postulat : les tournées sont faites aléatoirement et ce n'est pas toujours le même qui se retrouve à aller en montagne ou sur autoroute, sinon c'est certain qu'il aura toujours le bilan le plus défavorable).

Tu as tout compris. j'ai regardé ton tableau la seule chose que je note c'est que je connais déjà les moyennes au 100 grâce à mes relevés. je n'ai donc pas besoin d'excel pour les calculer

 

[Exl-Noob]

Tu es en train de me dire que tu fais un relevé du kilomètrage au moment du dernier plein, du nombre de litres ajoutés dans le réservoir, que tu calcules avec ta calculette la conso moyenne et que tu recopies le résultat dans Excel.
Et tu ne vois pas le problème potentiel là ?
Excel est fait pour bouffer des calculs. Nourris la bête avec les données récoltées et laisse la digérer le résultat !

Pour des raisons de compatibilité et pour ne pas perdre ton historique, on peut conserver les moyennes calculées initialement, mais il faut changer les pratiques à compter de 2021, comme indiqué dans l'onglet "Données".

 

[pat_leouf]

Lol, non du tout je ne suis pas si fou, je récupère directement les moyennes sur les ordinateurs de bord que je remet à 0. et les autres sont directement calculé sur les factures de gasoil que je reçois j'ai juste à faire du remplissage sur excel si tu veux.

Tu es en train de me dire que tu fais un relevé du kilomètrage au moment du dernier plein, du nombre de litres ajoutés dans le réservoir, que tu calcules avec ta calculette la conso moyenne et que tu recopies le résultat dans Excel.
Et tu ne vois pas le problème potentiel là ?
Excel est fait pour bouffer des calculs. Nourris la bête avec les données récoltées et laisse la digérer le résultat !

Pour des raisons de compatibilité et pour ne pas perdre ton historique, on peut conserver les moyennes calculées initialement, mais il faut changer les pratiques à compter de 2021, comme indiqué dans l'onglet "Données".

 

[Exl-Noob]

Alors voilà une première version opérationnelle.
J'ai complété l'onglet "Données" avec de la data randomisée pour voir le résultat.

Onglet Données, une seule formule (pas très utile car on retrouve le résultat dans l'onglet analyse...), mise en forme différente, pour calculer le nombre de kilomètres cumulé avant la date du dernier plein.

=SOMME.SI.ENS([Nb km parcourus];[Immatriculation];[@Immatriculation];[Date];"<"&[@Date])

Onglet "Analyse" : deux niveaux, l'un par véhicule, l'autre par conducteur.

• Nb km parcourus : j'aurais pu faire un INDEX EQUIV pour calculer le nombre de km emmagasinés par chaque véhicule avant le dernier plein, mais c'est beaucoup plus rapide pour Excel de le faire avec

  =SOMME.SI(Data[Immatriculation];[@Immatriculation];Data[Nb km parcourus])

• Moyenne des consommations (depuis que le véhicule tourne) : simple calcul, mais je tiens compte de la taille variable des champs, en retirant les valeurs d'entête de tableau, c'est pour cela que ça donne :

  =MOYENNE.SI(DECALER(Données!$A4;;;NBVAL(Données!A:A)-2);[@Immatriculation];DECALER(Données!$F4;;;NBVAL(Données!F:F)-1))

• Dernière conso moyenne (moyenne de consommation du dernier plein en fait) : comme pour Nb km parcourus, j'évite de rajouter des INDEX EQUIV :

  =SOMME.SI.ENS(Data[Moyenne/km];Data[Immatriculation];[@Immatriculation];Data[Date];[@[Dernier plein]])

• Dernier conducteur : pour pouvoir associer la Dernière conso moyenne au responsable de ladite consommation. Je n'ai pas trouvé comment faire autrement qu'avec une formule matricielle, voilà ma proposition :

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

Bis repetita sur le second tableau d'analyse par conducteur. j'ai juste ajouté un classement pour déterminer celui qui avait le plus mauvais bilan carbone.

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

A noter qu'on compare dans ce classement des choux et des carottes (VL et TC), il faudrait classer par type de véhicule mais cette colonne n'existe pas dans ce que tu as retourné je crois.
Je constate que tu as collé le modèle (FAST Kid) en lieu et place de la marque (ISUZU).

Bref, c'est pas encore opérationnel que la base est déjà polluée

A suivre.

 

[pat_leouf]

Sans vouloir être vulgaire : Putain de bon boulot et un grand merci.

J'essai d'ajouter quelques données et de colonne pour séparer Autocar / Minicar /VL (bon ça sera pas avant ce soir)

Alors voilà une première version opérationnelle.
J'ai complété l'onglet "Données" avec de la data randomisée pour voir le résultat.

Onglet Données, une seule formule (pas très utile car on retrouve le résultat dans l'onglet analyse...), mise en forme différente, pour calculer le nombre de kilomètres cumulé avant la date du dernier plein.

=SOMME.SI.ENS([Nb km parcourus];[Immatriculation];[@Immatriculation];[Date];"<"&[@Date])

Onglet "Analyse" : deux niveaux, l'un par véhicule, l'autre par conducteur.

• Nb km parcourus : j'aurais pu faire un INDEX EQUIV pour calculer le nombre de km emmagasinés par chaque véhicule avant le dernier plein, mais c'est beaucoup plus rapide pour Excel de le faire avec

  =SOMME.SI(Data[Immatriculation];[@Immatriculation];Data[Nb km parcourus])

• Moyenne des consommations (depuis que le véhicule tourne) : simple calcul, mais je tiens compte de la taille variable des champs, en retirant les valeurs d'entête de tableau, c'est pour cela que ça donne :

  =MOYENNE.SI(DECALER(Données!$A4;;;NBVAL(Données!A:A)-2);[@Immatriculation];DECALER(Données!$F4;;;NBVAL(Données!F:F)-1))

• Dernière conso moyenne (moyenne de consommation du dernier plein en fait) : comme pour Nb km parcourus, j'évite de rajouter des INDEX EQUIV :

  =SOMME.SI.ENS(Data[Moyenne/km];Data[Immatriculation];[@Immatriculation];Data[Date];[@[Dernier plein]])

• Dernier conducteur : pour pouvoir associer la Dernière conso moyenne au responsable de ladite consommation. Je n'ai pas trouvé comment faire autrement qu'avec une formule matricielle, voilà ma proposition :

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

Bis repetita sur le second tableau d'analyse par conducteur. j'ai juste ajouté un classement pour déterminer celui qui avait le plus mauvais bilan carbone.

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

A noter qu'on compare dans ce classement des choux et des carottes (VL et TC), il faudrait classer par type de véhicule mais cette colonne n'existe pas dans ce que tu as retourné je crois.
Je constate que tu as collé le modèle (FAST Kid) en lieu et place de la marque (ISUZU).

Bref, c'est pas encore opérationnel que la base est déjà polluée

A suivre.

 

[pat_leouf]

Bonsoir, tout d'abord merci pour le taff que tu as effectué.
à mon niveau ça me parait énorme.

J'ai séparé les véhicules dans l'onglet "véhicule" et dans analyse si y 'a possibilité que les couleurs soient automatique ?

Alors voilà une première version opérationnelle.
J'ai complété l'onglet "Données" avec de la data randomisée pour voir le résultat.

Onglet Données, une seule formule (pas très utile car on retrouve le résultat dans l'onglet analyse...), mise en forme différente, pour calculer le nombre de kilomètres cumulé avant la date du dernier plein.

=SOMME.SI.ENS([Nb km parcourus];[Immatriculation];[@Immatriculation];[Date];"<"&[@Date])

Onglet "Analyse" : deux niveaux, l'un par véhicule, l'autre par conducteur.

• Nb km parcourus : j'aurais pu faire un INDEX EQUIV pour calculer le nombre de km emmagasinés par chaque véhicule avant le dernier plein, mais c'est beaucoup plus rapide pour Excel de le faire avec

  =SOMME.SI(Data[Immatriculation];[@Immatriculation];Data[Nb km parcourus])

• Moyenne des consommations (depuis que le véhicule tourne) : simple calcul, mais je tiens compte de la taille variable des champs, en retirant les valeurs d'entête de tableau, c'est pour cela que ça donne :

  =MOYENNE.SI(DECALER(Données!$A4;;;NBVAL(Données!A:A)-2);[@Immatriculation];DECALER(Données!$F4;;;NBVAL(Données!F:F)-1))

• Dernière conso moyenne (moyenne de consommation du dernier plein en fait) : comme pour Nb km parcourus, j'évite de rajouter des INDEX EQUIV :

  =SOMME.SI.ENS(Data[Moyenne/km];Data[Immatriculation];[@Immatriculation];Data[Date];[@[Dernier plein]])

• Dernier conducteur : pour pouvoir associer la Dernière conso moyenne au responsable de ladite consommation. Je n'ai pas trouvé comment faire autrement qu'avec une formule matricielle, voilà ma proposition :

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

Bis repetita sur le second tableau d'analyse par conducteur. j'ai juste ajouté un classement pour déterminer celui qui avait le plus mauvais bilan carbone.

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

A noter qu'on compare dans ce classement des choux et des carottes (VL et TC), il faudrait classer par type de véhicule mais cette colonne n'existe pas dans ce que tu as retourné je crois.
Je constate que tu as collé le modèle (FAST Kid) en lieu et place de la marque (ISUZU).

Bref, c'est pas encore opérationnel que la base est déjà polluée

A suivre.

 

[Exl-Noob]

Oui, renvoie la dernière version du fichier...

Tous tes véhicules tournent au diesel ou tu as du GNV aussi ?

 

[pat_leouf]

Oui, renvoie la dernière version du fichier...

Tous tes véhicules tournent au diesel ou tu as du GNV aussi ?

Uniquement diesel