Monsieur Excel
Pour tout savoir faire sur Excel !

07 août 2015

Suivi de conso carburant

Le modèle que je vous présente aujourd’hui est lié à une demande qui m’a été faite de la part de la FAO à Madagascar. Vous avez en colonne A l’identification d’un camion, en colonne B le kilométrage qu’il affichait quand il a fait le plein, et en colonne C le nombre de litres pris à cette occasion.

La particularité de ce modèle est que le nombre de litres n’est pas toujours retranscrit… L’indolence des îles ?

Dans le tableau en E2:H10, on souhaite récupérer, pour chaque camion, la dernière quantité retranscrite pour son plein, l’avant-dernière (s’il y en a une), et enfin le nombre de kms parcourus entre les deux derniers pleins avec le litrage renseigné.


Pour trouver la formule à utiliser en F3, vous devez déjà être un expert Excel !
Pour trouver les formule à utiliser en G3 et H3, vous devez déjà être un grand expert Excel !

Je vous laisse chercher un peu. Ne lisez la suite que si vous ne trouvez pas la solution…
Formule de F3, reproduite ensuite vers le bas :
=index(C:C;max(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0)))

Formule de G3, reproduite ensuite vers le bas :
=index(C:C;grande.valeur(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0);2))

Formule de H3, reproduite ensuite vers le bas :
=si(estnum(G3);index(B:B;max(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0)))
-index(B:B;grande.valeur(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0);2));"")

Les trois formules ci-dessus sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].

En fait, en colonne G, le résultat est un texte quand on ne trouve pas au moins deux litrages dans la colonne C. Nous avons donc utilisé le format personnalisé « 0;;0; » pour masquer ces textes. Et c’est ce qui explique le estnum(G3) dans la formule de H3.

Remarque 1 – Cela faisait quelque temps que je vous avais pas prouvé que, avec des formules matricielles bien conçues, on pouvait faire pratiquement n’importe quoi dans Excel !

Remarque 2 – Notons dans la formule de G3 la fonction grande.valeur() qui est très utile et pourtant assez peu connue.

En E12, nous avons mis une validation de cellule par liste avec la référence =$E$3:$E$10.

Enfin, dans le bloc de gauche, nous avons mis un format conditionnel dont la définition est reproduite dans le cadre bleu. Cela met en relief les lignes relatives au camion sélectionné.

9 Commentaire(s):

  • Bonjour,

    Je préfère éviter de faire un Index sur 1 000 000 de lignes par habitude.

    Pourquoi pas :
    Colonne F : =INDIRECT("C"&MAX(($E3=$A$2:$A$25)*LIGNE($A$2:$A$25)*($C$2:$C$25>0)))
    et
    Colonne G : =SIERREUR(INDIRECT("C"&GRANDE.VALEUR(($E3=$A$2:$A$25)*LIGNE($A$2:$A$25)*($C$2:$C$25>0);2));"")

    By Blogger Arnaud Cottin, sur 11:51 AM  

  • Merci pour la suggestion, il est toujours intéressant de constater qu'avec Excel on a toujours plusieurs solutions possibles.

    Sur le plan pratique, même avec 100 camions et un plein tous les 4 jours, le nombre des lignes de l'année à Madagascar n'atteindra jamais 10.000. On est donc très loin du million de lignes...

    By Blogger Hervé Thiriez, sur 4:59 PM  

  • Que veux dire "indolence des îles " ? Auriez-vous des préjugés racistes ? Quand on lit ce genre de choses, on comprend mieux que la justice immanente vous frappe à bord du Thalys. Vous, qui êtes un consultant dans des entreprises en métropole depuis des années, devriez savoir que l'indolence n'est pas une particularité des gens des îles. Ce genre de facilité ne vous honore pas "Monsieur" Excel.

    By Anonymous Anonyme, sur 5:46 PM  

  • Cher lecteur anonyme,

    Je ne vois pas en quoi l'utilisation de l'expression "indolence des îles" serait raciste. Je ne parle pas de la couleur des iliens.

    De nombreux auteurs en littérature ont utilisé cette expression sans avoir été suspectés de racisme par qui que ce soit.

    Votre réaction taubiresque me désole. Je comprends pourquoi vous ne la signez pas.

    By Blogger Hervé Thiriez, sur 7:25 PM  

  • Je suis prêt à mettre de l'eau dans mon vin et à reconnaître que je ne suis très certainement allé de mains mortes dans ma critique. C'est juste qu'il m'est difficile de laisser passer certaines expressions si souvent usitées et qui peuvent avoir, un relent de racisme. Cependant sur internet comme ailleurs, la modération doit être de rigueur et le bénéfice du doute accordé, surtout lorsque l'on parle d'Excel : une chose qui nous passionne tous (du moins ceux qui trouvent un plaisir non dissimulé à visiter fréquemment votre site et qui apprécie votre travail).
    Je vous présente donc mes plus plates excuses. Cependant, l'adjectif "taubiresque" me laisse quelque peu pantois... Mais bon !!! Vive Excel et surtout VBA !!! Merci encore pour votre contribution et votre volonté de partager vos connaissances.

    By Blogger Stefyu Thechosenone, sur 1:28 PM  

  • Vous aurez noté que j'ai signé cette fois-ci mon message.
    Cordialement,
    STEPHANE

    By Blogger Stefyu Thechosenone, sur 1:30 PM  

  • Merci Stefyu !

    Je dois avouer que, moi aussi, je n'y ai pas été de main morte avec l'adjectif taubiresque... J'étais dans la réaction :)



    By Blogger Hervé Thiriez, sur 7:52 PM  

  • La formule d'Arnaud Cotton est très intéressante mais présente le désavantage d'utiliser la fonction INDIRECT qui est volatile et qui, sur des tables importantes de données, pourra allonger le temps de calcul. Mais très ingénieux et plus compacte que celle proposée par notre illustre hôte.
    J'aurais une préférence pour l'option suivante : retenir la formule de M.Excel en mettant en faisant référence à des plages nommées à partir d'INDEX(qui renvoie la dernière référence) et non de DECALER(autre fonction volatile) au lieu de plages de plus de 1 000 000.
    Merci encore pour cette article très intéressant.

    Cordialement,

    STEPHANE

    By Blogger Stefyu Thechosenone, sur 1:55 PM  

  • Désolé, je voulais dire Arnaud Cottin.

    By Blogger Stefyu Thechosenone, sur 1:56 PM  

Enregistrer un commentaire

<< Accueil