Suivi des comptes bancaires avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Suivi des comptes bancaires

Nous terminons la partie des exercices Excel pour débutant avec une sixième et dernière mise en pratique. Nous enchaînerons ensuite avec des exercices pour utilisateurs intermédiaires. Ici, il s'agit de réaliser le suivi des comptes bancaires. Comme toujours, les calculs doivent être dynamiques. Ainsi le modèle du relevé pour le mois en cours pourra être exploité pour les autres périodes.



Source et présentation de l'objectif
Le tableau modèle est déjà prêt. Il propose la structure pour l'organisation des données et l'essentiel de la mise en forme. Nous devons donc commencer par le récupérer.

• Télécharger le classeur 06-releve-de-banque.xlsx en cliquant sur son lien,
• Double cliquer sur le fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué d'une unique feuille. Elle se nomme Comptes comme le mentionne son onglet, en bas à gauche de l'interface Excel.

Le tableau de cette feuille propose de réaliser un suivi relativement simple des comptes pour particuliers. Les dates des opérations sont inscrites en colonne B, entre les lignes 6 et 16. Et à ce titre, nous pouvons noter un défaut d'alignement. Comme il s'agit d'indications numériques, elles devraient être alignées à droite dans leurs cellules. L'indication sur ces opérations est décrite en colonne C, respectivement pour les mêmes lignes. Il peut s'agir d'un débit, soit d'argent dépensé à soustraire du solde ou d'un crédit, soit d'argent reçu à additionner au solde.



Les montants de ces débits et crédits sont renseignés respectivement en colonnes D et E. Forcément, lorsqu'il s'agit d'un débit, la cellule du crédit pour la ligne correspondante est vierge. Et inversement, lorsqu'il s'agit d'un crédit, la cellule du débit pour la ligne correspondante est vide.

Mais ce n'est pas parce qu'une cellule est vide qu'elle ne doit pas être intégrée dans le calcul. Souvenez-vous, nous souhaitons bâtir un modèle dynamique exploitable pour les autres périodes. Pour le premier calcul, soit le 2 du mois en ligne 6, si nous nous contentons de soustraire le montant du débit au montant du solde, en ignorant la cellule vide du crédit, le tableau produira de graves erreurs pour les mois suivants. Imaginons en effet qu'il s'agisse désormais d'un crédit pour la première opération de ce mois. Comme la cellule n'est pas impliquée dans le calcul, le résultat conduira au solde d'origine.

Cet exercice est donc intéressant à plus d'un titre. Il insiste sur l'aspect dynamique des opérations à bâtir devant intégrer les références des cellules. Il explique que le modèle doit être portable et doit considérer toutes les cellules concernées, même si elles sont vides. Enfin, il va démontrer qu'une cellule vide, bien que ne portant pas explicitement la valeur Zéro (0), n'est pas gênante pour Excel.



Calcul du solde
La formule à bâtir est triviale. Elle consiste à soustraire le débit au montant du solde et à lui additionner le crédit de la même ligne. Il en résultera le nouveau solde calculé dynamiquement. Cette formule devra être répliquée sur la hauteur du tableau. Ainsi, par le jeu des références relatives comme vous le savez, à chaque précédent solde, sera déduit le débit et additionné le crédit de la ligne en cours.

Le premier calcul doit être posé en cellule F6. Il doit considérer le solde précédent placé en F5. Il doit aussi faire intervenir la cellule D6 pour le débit et la cellule E6 pour le crédit.

• Cliquer sur le premier solde à calculer, soit la cellule F6 pour la sélectionner,
• Taper le symbole égal (=) pour initier l'opération,
• Cliquer sur le précédent solde, soit la cellule F5 pour intégrer sa référence dans la syntaxe,
• Taper le symbole moins (-) du pavé numérique pour enclencher la soustraction,
• Sélectionner le débit de la ligne en cours, soit la cellule D6 pour l'intégrer dans la formule,
• Taper le symbole plus (+) du pavé numérique pour déclencher l'addition,
• Sélectionner le crédit de la ligne en cours, soit la cellule E6 pour l'inscrire dans le calcul,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

La touche Entrée aurait parfaitement validé l'opération. Mais souvenez-vous, cette combinaison de touches permet de conserver active la cellule du résultat. De fait et dans la foulée, nous pourrons l'exploiter sans devoir la resélectionner, pour répercuter sa logique de calcul sur les autres cellules.



Quoiqu'il en soit, nous obtenons le premier résultat du solde en cours. Il est parfaitement cohérent. Son montant est inférieur au précédent du fait de la présence d'un débit. La cellule vierge du crédit est bien impliquée dans le calcul. En l'état, elle est ignorée par Excel puisque vide. Mais si nous tapions une valeur dans sa cellule, nous constaterions que le solde serait automatiquement et dynamiquement crédité. Nos calculs sont donc parfaitement dynamiques pour un modèle portable.



En bas à droite de la cellule du résultat (F6) qui doit toujours être sélectionnée, vous notez la présence d'un petit carré. Il s'agit de la poignée de la cellule. Dans le cas d'un calcul, elle permet de reproduire sa logique. Nous avons insisté sur ce point à maintes reprises dans les précédents exercices. On ne refait jamais deux fois le même calcul dans Excel. Si vous pointez sur ce petit carré à l'aide de la souris, vous notez que son curseur se transforme en une petite croix noire. Excel indique que la poignée est prête à être exploitée.

• Pointer sur la poignée de la cellule F6 sélectionnée,
• Lorsque le curseur se transforme en une croix noire, double cliquer,

Excel détecte automatiquement les bornes du tableau. De fait, cette méthode très efficace permet de reproduire le calcul confortablement, sur toutes les lignes du dessous. Nous aurions aussi pu accompagner le mouvement en cliquant et glissant la poignée de la cellule jusqu'à la dernière ligne, soit la ligne 16. Nous avons appris cette méthode dans les précédents exercices pour débutants. Sachez d'ailleurs que le double clic sur la poignée ne fonctionne pas à l'horizontale. Donc, lorsqu'il s'agira de reproduire une formule sur d'autres colonnes, nous cliquerons et glisserons sa poignée.

Enfin et comme vous le savez, il existe une autre méthode particulièrement efficace. Comme nous l'avons démontrée à de nombreuses occasions, nous ne la réaliserons pas ici. Mais elle consiste à présélectionner toutes les cellules des résultats à trouver, soit la plage de cellules F6:F16. Ensuite, il convient de bâtir la formule comme s'il s'agissait seulement de la première ligne, soit strictement comme nous l'avons fait précédemment. Enfin, il s'agit de valider la formule nécessairement par la combinaison CTRL + Entrée. De la sorte, le calcul est répliqué sur toutes les cellules instantanément, sans même devoir agir sur la poignée.

A la lecture des résultats, la surprise est agréable. Nous remarquons que le solde en fin de mois est supérieur et nettement à celui du début. La troisième opération est en effet une remise de chèque dont le montant est important. Ceci tend à prouver que le calcul répliqué a bien suivi la chronologie des opérations. Nous allons le voir, les références des cellules impliquées ont en effet suivi le sens de déplacement imposé par la poignée.

• Sélectionner le tout dernier solde calculé, soit la cellule F16,

Comme vous le savez, la barre de formule située au-dessus de la feuille Excel rappelle le contenu exact de la cellule sélectionnée. En l'occurrence ici, il s'agit de la syntaxe de la formule ayant conduit au résultat affiché. Et comme vous le remarquez, par rapport à la toute première formule bâtie en F6 : =F5-D6+E6, les indices de ligne des cellules impliquées ont augmenté autant de fois que de lignes ont été franchies : =F15-D16+E16.

Par cette reproduction naturelle, Excel a bien considéré les éléments contextuels nécessaires. Il s'agit en l'occurrence du précédent solde situé en F15, soit la ligne du dessus. Mais il s'agit aussi du débit et du crédit pour la date en cours, soit respectivement les cellules D16 et E16.

De fait et nous ne le répèterons jamais assez, ce modèle de suivi des comptes bancaires est parfaitement dynamique. Si la valeur d'une opération change, le solde pour la date en cours s'actualise. Comme il est lui-même impliqué directement ou indirectement dans toutes les opérations qui suivent, les soldes des lignes du dessous se mettent à jour eux aussi automatiquement.

Une dernière opération triviale reste à matérialiser. Elle consiste à restituer le dernier solde calculé, dans une cellule indépendante, en F18, pour des raisons de clarté. Pour conserver l'aspect dynamique, il suffit de réaliser l'égalité entre les deux cellules.

• Sélectionner la cellule F18,
• Taper le symbole égal (=),
• Cliquer sur le dernier solde en cellule F16 pour réaliser l'égalité,
• Valider ensuite à l'aide de la touche Entrée par exemple,

Grâce à une mise en forme prédéfinie, le résultat est d'autant plus parlant. Il méritera, lui comme les autres, d'être affiché au format monétaire. Nous n'en avons pas fini avec les simulations. Nous ajouterons des fonctionnalités dynamiques fort intéressantes, à cet outil de suivi des comptes bancaires.

Présentation et formatage des cellules
Tout d'abord, nous souhaitons travailler sur la présentation du tableau. Les travaux de mise en forme ne doivent jamais être négligés. Ce sont eux qui rendent limpide la lecture d'un tableau au premier coup d'oeil, notamment pour les personnes non initiées. Ils facilitent l'interprétation des résultats. Ils renforcent donc la pertinence d'un outil conçu avec Excel.

Les valeurs numériques des opérations et des soldes doivent être affichées en Euros. Nous le répétons à chaque occasion, la devise ne doit jamais être inscrite manuellement dans la cellule. Nous devons appliquer un format. Et pour optimiser les opérations, nous souhaitons regrouper toutes les cellules concernées dans une même sélection. Rappelez-vous, c'est la touche CTRL du clavier qui permet d'additionner des sélections, lorsque les plages ne sont pas adjacentes.

• Sélectionner tout d'abord la plage de cellules D6:F16 en glissant avec la souris,
• Tout en maintenant la touche CTRL enfoncée, sélectionner le premier solde en F5,
• Toujours avec la touche CTRL enfoncée, ajouter le dernier solde en F18,
• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Format Nombre Comptabilité,

Toutes les cellules ainsi regroupées dans la sélection apparaissent formatées en Euro.

• Cliquer sur la cellule du premier débit en D6 pour la sélectionner,

La barre de formule restitue le contenu exact d'une cellule sélectionnée. En la consultant, au-dessus de la feuille, vous remarquez qu'Excel continue de considérer la donnée brute d'origine. Un format est destiné à l'affichage et à l'impression pour améliorer la présentation. La devise ou l'unité de mesure ne doit jamais apparaître dans cette barre de formule.

Nous devons désormais corriger le petit défaut d'alignement des dates des opérations en colonne B. Par esprit de cohérence, le titre en B4 doit subir la même mise en forme. Comme toutes les autres cellules de la colonne sont vides, la méthode la plus efficace consiste à la désigner intégralement par son étiquette.

• Cliquer sur l'étiquette B de colonne pour la désigner intégralement,
• Puis, dans la section Alignement du ruban Accueil, cliquer sur le bouton Aligner à droite,

La présentation est désormais plus cohérente. Ces ajustements ont l'air de rien mais sans eux, des défauts imperceptibles et gênants subsisteraient sur le rendu papier.

Alertes visuelles dynamiques
Pour parfaire ce petit outil Excel de suivi des comptes bancaires, nous souhaitons déclencher automatiquement des jeux de couleurs, en fonction de la valeur du solde. Un indicateur rouge doit alerter lorsque le solde devient bas, par exemple inférieur à 100 Euros. Un indicateur jaune doit se déclencher lorsque le solde est considéré comme faible, inférieur à 300 Euros par exemple.

Ces couleurs doivent donc surgir dynamiquement en respectant ces règles. Il ne s'agit pas d'une mise en forme classique, qui une fois qu'elle est posée est statique. Dans l'exercice précédent, nous avons mis en oeuvre la mise en forme conditionnelle. Mais cette fois, nous ne souhaitons pas faire ressortir les deux plus grandes valeurs. Nous devons construire ces règles régissant ces alertes. La chronologie est importante dans la construction. Les critères de ces deux règles se chevauchent lorsque le solde est inférieur à 100 Euros. Mais la dernière règle posée est prioritaire. Donc, nous devons débuter par celle déclenchant l'alerte jaune.

• Sélectionner tout d'abord la plage de cellules F5:F16,
• Avec la touche CTRL Enfoncée, cliquer sur F18 pour l'inclure dans la sélection,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste qui apparaît, pointer sur Règles de mise en surbrillance des cellules,
• Dans le sous menu qui suit, cliquer sur la règle : Inférieur à,

Une petite boîte de dialogue apparaît. Elle consiste à définir la règle selon laquelle la couleur à indiquer doit se déclencher sur la plage de cellules sélectionnée.

• Dans la zone de saisie, taper la valeur 300,
• Dans la liste déroulante sur sa droite, choisir le remplissage jaune,
• Puis, valider ces réglages en cliquant sur le bouton Ok,

De retour sur la feuille, aucune alerte n'est fort logiquement déclenchée. Pour l'instant, aucun solde ne répond à ce critère. Nous aurons l'occasion de faire des simulations. Mais tant que la plage de cellules est toujours sélectionnée, nous proposons de bâtir la seconde règle de mise en forme conditionnelle.

• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, pointer sur Règles de mise en surbrillance des cellules,
• Puis, choisir l'option : Inférieur à,
• Dans la boîte de dialogue, saisir la valeur 100,
• Conserver le remplissage rouge associé et proposé par défaut,
• Valider cette règle par le bouton Ok,

Nous proposons désormais de réaliser une petite simulation en modifiant les valeurs des débits et crédits. Tant pis si les désignations des opérations ne sont plus en cohérence. L'objectif est de constater que non seulement les calculs sont dynamiques, mais aussi que des alertes de couleurs se déclenchent automatiquement.

• Sélectionner le premier crédit en cellule E8,
• Enfoncer la touche Suppr du clavier pour l'effacer,

Comme vous le remarquez, instantanément, le solde en cours et tous les suivants s'actualisent dynamiquement. La première vérification est donc conforme à nos attentes.

• Dans la cellule D8 de débit sur sa gauche, saisir 2900 et valider par Entrée,

Non seulement le solde et les suivants se mettent à jour, mais des alertes dynamiques de couleurs se déclenchent. Elles sensibilisent l'utilisateur sur l'aspect préoccupant de la situation.

• Sélectionner la cellule E13,
• Remplacer la valeur 90 par 480 et valider,

Là encore, les calculs et les couleurs s'adaptent à la nouvelle situation.

• Sélectionner le dernier débit soit la cellule D16,
• Enfoncer la touche Suppr du clavier pour effacer la valeur,
• Dans la cellule E16 du crédit sur sa droite, saisir le montant 500,

Au passage, vous remarquez qu'il se formate tout seul en Euros puisque la mise en forme est prédéfinie. Et ensuite, nous remarquons que nous sauvons les meubles in extremis. L'alerte de couleur a disparu. Elle révèle deux indications importantes. Nous sommes créditeurs et possédons une certaine marge.



Avec seulement des notions d'initiation, nous avons réussi à bâtir un petit outil dynamique pour le suivi des comptes bancaires.

Rendez-vous donc dans les prochains exercices où il s'agira désormais de découvrir des techniques et calculs du niveau intermédiaire.