Ai-je déjà payé la facture de téléphone ? Quand expire celle de l’électricité ? Des questions que nous nous sommes tous posées au moins une fois. Nous pouvons tout organiser grâce à Excel.
Ai-je déjà payé la facture de téléphone ? Quand expire celle de l’électricité ? Des questions que nous nous sommes tous posées au moins une fois. Nous pouvons tout garder en ordre grâce à Excel.
Tout d’abord, préparons la feuille de calcul qui contiendra le tableau de nos factures. Supprimons Feuille3 (qui est créée automatiquement pour chaque nouveau classeur) et renommons Feuille1 en Factures et Feuille2 en Statistiques. Dans la première feuille, nous insérerons les factures reçues, dans la seconde un tableau croisé dynamique qui nous permettra d’observer l’évolution des consommations dans les différentes périodes de l’année.
Commençons par la feuille que nous avons nommée Factures. Nous devons préparer au moins cinq colonnes : Facture, Période, Montant, Échéance, Paiement. Dans la première colonne, nous enregistrerons le type de facture (ex. « ENEL »); dans la deuxième la période de référence (« jan/fév 2003 »); dans la troisième le montant ; dans la quatrième la date d’échéance ; dans la cinquième celle à laquelle le paiement a effectivement été effectué. Simple, non ?
Insérons quelques données d’exemple. Nous pouvons les inventer, en veillant à choisir des dates et montants plausibles, ou bien fouiller dans un tiroir poussiéreux pour être sûrs d’avoir un tableau réaliste de la situation. Même si cette étape est ennuyeuse et demande un minimum d’attention, elle nous permet dès le début d’expérimenter les fonctions les plus avancées de notre feuille de calcul et de vérifier que les formules sont correctes.
Commençons à compliquer légèrement les choses : maintenant, nous voudrions qu’Excel nous indique combien de jours restent avant l’échéance. Comment le calculer ? Insérons une colonne appelée JAE (Jours Avant Échéance) et mettons dans la première cellule la formule : =$D2-AUJOURDHUI() qui fournit exactement le nombre de jours restant avant l’échéance de la facture (ou un nombre négatif si elle est déjà échue, ou zéro si elle expire aujourd’hui).
Copions la formule sur toute la colonne. Cette configuration est peu lisible car elle ne prend pas en compte les factures déjà payées. Insérons une nouvelle colonne, État, avec la formule =SI($E2<>« »; « Payée »; $E2). La recherche des factures est déjà nettement simplifiée, mais raisonner en termes de nombres relatifs est aussi gênant pour celles et ceux qui sont assez familiers avec l’algèbre. Voyons comment améliorer davantage.
Modifions la troisième partie de l’instruction SI pour qu’elle se comporte différemment selon que les nombres sont positifs ou négatifs : =SI($E2<>« »; « Payée »; SI($F2<0; "Échue depuis " & (-$F2) & " jours"; "Expire dans " & $F2 & " jours")). Cela semble compliqué, mais cela nous permet de distinguer d’abord entre factures payées et non, puis entre celles échues et celles à venir. La complexité de la formule est récompensée par la facilité de lecture.
Nous nous demandons si nous pouvons rendre notre schéma encore plus clair. Essayons de mettre en évidence les textes avec la mise en forme conditionnelle. Sélectionnons la première cellule de la colonne État puis allons dans le menu Format/Mise en forme conditionnelle. La fenêtre de dialogue Mise en forme conditionnelle permet d’insérer au maximum trois conditions logiques et de leur associer différents styles. Cette technique permet de mettre en valeur des situations particulières.
Commençons par la condition la plus simple : les factures payées. Sélectionnons dans la première liste déroulante « La valeur de la cellule est », dans la seconde « Égale à » et dans la troisième texte la parole « Payée ». Ensuite, en cliquant sur le bouton Format, choisissons le style qui convient le mieux. Le choix du style s’effectue (presque) exactement comme depuis le menu Format. Certaines options sont absentes (Nombre, Alignement, Protection), mais nous n’en aurons pas besoin.
Dans la fenêtre Format de cellule, on peut configurer les bordures, le fond et le style de caractère des cellules. Dans ce cas, un vert clair, rassurant et lisible devrait convenir. Pour voir l’effet de notre modification, cliquons sur OK : si dans la cellule se trouve le mot « Payée », la couleur du texte devrait changer. Un pas supplémentaire vers une meilleure clarté visuelle. Occupons-nous maintenant des deux autres conditions.
Ouvrons de nouveau la fenêtre de Mise en forme conditionnelle comme au point 8. En cliquant sur Ajouter >>, insérons une autre condition pour gérer le cas où la facture n’est pas encore échue. Cette fois, préférons utiliser une formule pour identifier plus clairement la situation et sélectionnons « La formule est » et entrons à côté =$F2>=0 puis formatons avec un orange, peut-être en gras, qui inspire la vigilance.
Avant de fermer la fenêtre, ajoutons une autre condition basée sur la formule =$F2
Désormais dans la colonne, nous aurons notre texte coloré de manière appropriée selon les situations et il sera facile de repérer d’un coup d’œil les anomalies. À ce stade, il n’est plus nécessaire que la colonne JAE soit visible et nous pouvons la masquer en cliquant avec le bouton droit sur l’en-tête puis en choisissant Masquer. Elle continuera à être utilisée dans les formules mais n’occupera plus d’espace utile. Pour la réafficher, il suffira d’utiliser le command Afficher.
Pour améliorer encore la fonctionnalité du document, nous pouvons utiliser les commandes de tri du menu Données. Tout d’abord, sélectionnons notre tableau (y compris les en-têtes). Puis utilisons la commande Données/Trier et choisissons JAE avec ordre décroissant. En cliquant sur OK, nous voudrions que nos factures avec échéance la plus proche soient placées en haut du tableau.
Malheureusement, elles seront mélangées avec celles déjà payées. Pour résoudre ce problème, utilisons à nouveau la commande Trier du menu Données et modifions le critère de tri : en premier, insérons Paiement/Croissant, en second JAE/Croissant. De cette manière, les factures déjà payées se retrouveront en tête de liste et dessous, dans l’ordre de retard, les autres. La patience nécessaire pour collecter les données est récompensée.
Pour l’analyse des données, rien ne vaut un tableau croisé dynamique. Sélectionnons Données/Rapport de tableau croisé dynamique et indiquons comme source la table dans la feuille Factures et comme destination la feuille Statistiques. Insérons ensuite en colonne le type de facture, en ligne la période et au centre le montant. L’usage de cet outil demande un minimum d’expérience, mais offre une série de perspectives très intéressantes et interchangeables entre elles.
Soyez le premier à commenter