Débuter avec un tableau d’amortissement

Bienvenue dans cet article destiné aux débutants qui souhaitent apprendre à utiliser Excel. Microsoft Excel est un outil incontournable pour la manipulation de données et le calcul, que ce soit en gestion financière, en suivi de projets ou en analyse de données. En maîtrisant ces compétences de base et les fonctionnalités Excel, vous serez en mesure de tirer le meilleur parti de l’outil pour simplifier et automatiser vos tâches quotidiennes, améliorant ainsi votre efficacité et votre productivité. 

Tout au long de cet article, nous nous servirons du calcul d’un tableau d’amortissement comme trame d’apprentissage.

1.Commencer sur Excel : les cellules 

La solution Microsoft Excel est un tableur. Il permet de représenter des données en cellules dans un tableau en deux dimensions, dans lesquelles il est possible de réaliser des opérations. Ainsi, chaque cellule peut contenir des données :

• Fixe : un texte, un nombre, une date ;
• Dynamique : une donnée dépendante d’une autre cellule.

1.1 La donnée dynamique

Pour avoir une donnée dynamique, il suffit de préfixer le contenu de la cellule par un « = ». Par exemple : on saisit « =A1 » dans la cellule B1 et lorsque l’on valide (en tapant sur « entrée » sur le clavier), la donnée de A1 apparaît dans B1. Lorsque l’on sélectionne la cellule B1, la valeur affichée est bien « coucou », mais la donnée est le « =A1 » que nous avons écrit.

1.2 Entendre une donnée dynamique

Pour éviter des copier-coller fastidieux, un petit carré est disponible en bas à droite de la cellule sélectionnée, permettant d’étendre facilement le contenu de la cellule. Il suffit de cliquer sur le carré, et d’étendre en maintenant le clic actif, puis de relâcher à la cellule de fin souhaitée.

L’extension fonctionne même avec les données dynamiques, tout en conservant une cohérence avec la cellule copiée. Dans notre exemple, le contenu de la cellule B1 que nous avions remplie avec « A1 » a été étendu jusqu’à B9, en gardant la bonne ligne pour chaque cellule. B6 est ainsi rempli avec A6.

1.3 Comment bloquer une cellule

Cependant, on peut avoir besoin d’une liaison avec une cellule fixe, même lors de son extension. Il suffit de rajouter un « $ » avant la colonne (A) et la rangée (1) que l’on veut fixer.

Ici, on étend en gardant toujours la référence « A1 » grâce au « $A$1 » (qui vaut « 1 ») et en étendant A1 sur A2, A3…A10. B10 vaut donc A1+A10 = 1+10 = 11.

2. Comment utiliser Excel : les mises en forme

La mise en forme d’une cellule se fait par un simple clic sur le bouton droit de la cellule, à partir duquel on peut choisir le format d’affichage. Certains formatages se font par défaut, comme dans le cas des pourcentages. Pour afficher des montants, il faut alors indiquer le format souhaité.

2.1 Mise en forme conditionnelle

… que nous allons l’étendre jusqu’en bas. Nous constatons que nos cellules ont été écrasées par la valeur de A1. C’est là qu’intervient le collage spécial qui consiste à recopier que la mise en forme.

On peut évidemment lier le critère à une cellule, avec $A$5 au lieu de « 5 » par exemple :

3. Comment calculer sur Excel : les formules

Nous avons vu une opération simple, mais il est également possible de créer des opérations plus complexes, comme le calcul d’une mensualité d’un crédit immobilier.

La formule étant :

capital emprunté x taux mensuel / (1 – [(1 + taux mensuel) puissance (-mensualités)])

Nous pouvons ajouter nos mensualités, mais cela reste difficilement lisible. Il est possible de masquer des lignes pour avoir une vision d’ensemble, avec un clic bouton droit sur les lignes que l’on veut masquer.

3.1 La fonction « SI » dans Excel

Mais en étendant notre « +1 », on dépasse allégrement notre nombre de mensualités. C’est pourquoi nous allons faire une petite formule pour vérifier notre nombre et son affichage, grâce à la fonction « SI ». Pour ajouter une fonction à une cellule, il suffit de cliquer sur le bouton « fx ».

Ici, le test logique de la cellule B9 est SI la valeur de B8+1 (1) est inférieur ou égal (<=) au nombre de mensualités, c’est à dire la cellule bloquée $B$3.

• Si c’est vrai : on affiche B8+1 (2) ;
• Si c’est faux : on affiche « » (laisse la cellule vide)

Les fonctions pour faire un calcul sur Excel

On peut également composer les fonctions. La fonction MOD permet de sortir le reste d’une division : MOD(B31;12) donne le reste de l’opération B31 (24) divisé par 12 (= 0). Pour rappel, nous avions masqué quelques rangées, donc une double ligne indique une rangée (ou colonne le cas échéant) masquée.

Comment réaliser un tableau d’amortissement

Avec tout ce que l’on a appris, on peut désormais avancer sur notre tableau d’amortissement.

On peut notamment rajouter les intérêts du mois, qui sont l’application du taux mensuel sur le Capital Restant Dû (qu’on appellera CRD) …

… puis l’amortissement, qui est la différence entre la mensualité et les intérêts…

… le CRD en fin de période, qui est le CRD de début de période moins l’amortissement…

… et enfin, on peut recalculer le nouveau CRD de début de prochaine période :

Il reste alors à étendre les cellules…

… afin de remplir le tableau ci-dessous :

Pour le peaufiner et le rendre plus esthétique et lisible, il est aussi possible de rajouter des petits effets visuels. Ainsi, pour savoir quand l’amortissement est supérieur à l’intérêt, deux mises en forme conditionnelles peuvent s’appliquer aux cellules comprises entre E8 et E307, en comparant la valeur à la cellule de la même rangée de la colonne D (le D8 indiqué est comparé au E8 qui est la cellule de saisie avant extension).

Résultat, on peut jouer maintenant facilement sur le capital en B1, le taux en B2 ou la durée en B3 dans Excel.

Conclusion

Cet article est une première approche d’Excel, permettant d’entrapercevoir des utilisations concrètes, à des fins aussi bien professionnelles que personnelles. Nous avons appris ce qu’était un tableur, comment naviguer dans les cellules, et les utiliser et les faire interagir entre elles, le tout avec un peu de rendu visuel.

Il existe bien d’autres formules, facilitant par exemple la génération de traitement de masse avec des formules de modifications de textes, ou facilitant le dédoublonnage de données avec des recherches de texte.

Il est également possible de « coder » son Excel, permettant de se connecter et d’échanger avec des applications tierces, entre autres pour mettre à jour la valeur d’une devise. Bref, beaucoup d’autres utilisations concrètes que nous présenterons dans un autre article.