XL 2019 Prime Qualité de service sur consommation de carburant

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.

• 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

Exl-Noob à dit:

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.

Cliquez pour agrandir...

Exl-Noob à dit:

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.

Cliquez pour agrandir...

pat_leouf à dit:

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)

Cliquez pour agrandir...

Exl-Noob à dit:

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).

Cliquez pour agrandir...

Exl-Noob à dit:

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".

Cliquez pour agrandir...

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

• 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
  Code:

  =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 :
  Code:

  =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 :
  Code:

  =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 :
  Code:

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

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

Exl-Noob à dit:

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.

Code:

=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
  Code:

  =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 :
  Code:

  =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 :
  Code:

  =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 :
  Code:

  {=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.

Code:

=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.

Cliquez pour agrandir...

Exl-Noob à dit:

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.

Code:

=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
  Code:

  =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 :
  Code:

  =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 :
  Code:

  =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 :
  Code:

  {=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.

Code:

=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.

Cliquez pour agrandir...

Exl-Noob à dit:

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

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

Cliquez pour agrandir...