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