Une fois téléchargé, le fichier ressemble à ceci. Notez la mise en forme des cellules déjà faite.

Petit exercice simple pour commencer, le calcul du prix unitaire. On divise donc le montant par le nombre de litres d'où la formule =D2/C2

Consommation aux 100 km. C'est le nombre de litres divisé par la distance parcouru multipliée par 100. Donc la formule est =(C3/(B3-B2)*100) On commence en ligne 3 puisqu'on ne connait pas le kilométrage précédent.

Il ne reste plus qu'à faire la recopie de nos formules et faire la mise en forme des cellules pour améliorer la présentation.

Comme rien n'est plus causant qu'un graphique, nous allons insérer un diagramme.

Pour avoir une date de repère, sélectionnons la colonne des dates (sauf la 1ère valeur, puisqu'il n'y a pas de consommation en équivalence). Sélectionnons simultanément la colonne des consommations.

Pour sélectionner simultanément 2 zones différentes, il faut maintenir la touche Ctrl enfoncée tout en maintenant le clic gauche et en se déplaçant sur les cellules concernées).

Notez la forme du curseur. Taille et position pourront être modifiées par la suite.

Dans la boite de dialogue AutoFormat de diagramme, les zones sélectionnées sont inscrites dans la Plage. N'oubliez pas de cochez Première colonne comme étiquette (ce sont les dates).

Vous pouvez choisir plusieurs formats. Un aperçu partiel est possible en cochant Afficher l'aperçu des objets de texte.

Donnons un titre au diagramme.

Voila le résultat. Décevant ! Bien sûr il faut commencer par agrandir le diagramme.

On y voit plus clair, mais aussi les défauts.

A droite la légende Colonne F ne sert à rien. Il suffit de la sélectionner et d'appuyer sur la touche suppression pour la faire disparaitre.

Le diagramme se réajuste automatiquement.

Autre défaut, en bas les dates, vraiment pas lisibles.

En survolant avec la souris lorsque vous voyez Axe X, cliquez à droite pour le menu flottant et cliquez sur axe X pour orienter les dates verticalement (90°), dans l'onglet Etiquette. N'oubliez pas de décocher Saut.

Pour pouvoir modifier la ligne, cliquez à droite lorsque cette info-bulle est visible. Un menu flottant apparaît.

Cliquez sur Propriétés de l'objet.

Vous pourrez modifier l'épaisseur et la couleur de ligne dans l'onglet Ligne.

Après avoir sélectionné Paroi du diagramme, onglet Remplissage, vous pouvez choisir un fond personnalisé.

Voila un résultat causant. Dans cet exemple, on voit une période de surconsommation.

Maintenant nous allons faire une synthèse mensuelle des consommations et des sommes dépensées. Pour ceci il nous faut une colonne Mois. Nous allons la créer entre la colonne Date et km, en insérant une colonne. Dans cette nouvelle colonne, nous allons extraire le mois de la date à l'aide de la fonction MOIS, avec la formule =MOIS(A2)

Résultat curieux, l'affichage est anormal. On ajuste la largeur de colonne (voir hauteur lignes, largeur colonnes) et mauvaise surprise, le résultat ne ressemble pas à celui attendu. Le format de la cellule est un format date. Intéressant, mais ne correspond pas au numéro de 1 à 12 souhaité. Il faut donc modifier le format de cellules en format Standard pour que l'affichage soit correct (1 dans cet exemple).

Ensuite nous allons nommer les zones qui vont servir aux calculs. Pourquoi ? Pour simplifier la lecture des formules. Nous allons donc sélectionner la zone des données de Mois, puis via le menu Insertion, Noms ou Alt+I, M ou Ctrl+F3

Dans la boite Définir des noms, entrons le nom choisi. Notez l'appellation Mmois pour éviter de confondre avec le mot-clé MOIS.

A partir de maintenant, lorsque vous appelez Mmois, ça veut dire $Relevés.$B$2:$B$21, ou plus simplement la zone des cellules des lignes 2 à 21 de la colonne B, de la feuille Relevés. Avouez que Mmois est plus simple !

Avec la même méthode, nommons les zones Litres, KmTotal, Montant.

Ensuite passons à la feuille nommée Synthèse. La feuille est déjà nommée, c'est du pré-maché !

Inscrivons les titres dans la première ligne : Mois, Km Total, Km Mensuel, Litres et Montant. La mise en forme des cellules n'est pas indispensable mais souhaitable.

Nous allons aborder maintenant les formules matricielles. Ces formules agissent sur des tableaux et sont reconnaissables au fait qu'elles sont encadrées par les symboles {}. Il est possible de se faire assister par l'Autopilote de fonctions (si vous cochez Matrice) appelé par Ctrl+F2. Dans notre cas nous écrirons les formules sans assistance. Commençons par le plus simple, les montants.

Notre formule doit calculer le total des montants pour un mois donné, à l'aide de la fonction SOMME. Attention au nombre et à la position des parenthèses. Dans l'exemple a formule va être : =SOMME((A2=Mmois)*(Montant)) . Cette formule est partiellement écrite.

Dans cette formule on teste si le n° du mois correspond au mois lu (A2=Mmois), et si c'est exact, on ajoutera au montant du mois.

Cependant, ce n'est pas tout. Pour faire de cette formule une formule matricielle, il faut utiliser une combinaison de touches bien particulière. Au lieu de faire Entrée, il faut faire la combinaison Ctrl+Shift+Entrée. Ceci aura pour résultat d'encadrer la formule par les caractères { et }, ce qui donnera : {=SOMME((A2=Mmois)*(Montant))}

Nota: N'essayez pas d'entrez ces caractères manuellement, le résultat sera très différent à vos attentes.

Il ne reste plus qu'à faire la même chose avec la consommation.

Pour faire la recopie des formules matricielles, il faut appliquer la méthode normale de recopie en maintenant la touche Ctrl enfoncée.

Pour les kilométrages totaux, on ne fait plus la somme, mais on cherche le kilométrage maximum du mois avec la fonction MAX. Ensuite on applique la formule matricielle et on recopie vers le bas.

Cependant un problème survient. Pour le mois de juin, zéro kilomètres. Bien sûr, le véhicule n'a pas roulé, mais le kilométrage du compteur n'est pas pour autant revenu à zéro.

Il faut donc tenir compte de ce cas avec la formule suivante où on rajoute la fonction SI:

{=SI(MAX((A2=Mmois)*(KmTotal))=0;B1;MAX((A2=Mmois)*(KmTotal)))}

Simple, nous disons que si le résultat égal zéro, on reprend le résultat du kilométrage précédent, sinon, on utilise bien le calcul matriciel.

Il va donc falloir reprendre toutes les formules pour le kilométrage.

Voila le tableau final.

Les totaux ? Oubliés ! Mais oui, vous savez faire, comme la consommation moyenne absente de ce tableau. Vous pouvez aussi faire de jolis graphiques...

Petite indication, vous pouvez comme ici ne pas afficher les valeurs égales à zéro, via le menu Outils, Options, Classeur, Affichage et décochez Valeurs zéro.

Le tableur Calc par l'exemple : christianwtd@free.fr

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.0 France License.