Tables de données à 1 et 2 variables

Introduction
Avant de commencer
Table de données à 1 variable
Table de données à deux variables

Introduction

La commande Table de données vous permet de créer rapidement un tableau qui affiche plusieurs résultats qui vous prendraient beaucoup plus de temps à réaliser manuellement.  Les deux prochains exemples vont vous montrer à quel point cette commande peut être pratique.

Avant de commencer

Pour suivre cet exercice, vous pouvez aller chercher le classeur demos-excel-3.xlsx qui se trouve sur la page des fichiers de démonstrations.

Il s'agit de trouver le montant de chaque paiement pour un emprunt de 200 000 $ à un taux de 5,5% sur une hypothèque de 25 ans.

Le modèle utilise la fonction VPM(Intérêt par période; nombre de paiements; montant à rembourser) pour déterminer le montant de chaque remboursement. Pour cette analyse, les paiement sont fait à chaque deux semaines ou 26 fois par année. La cellule B9 indique le total des paiements annuels. La cellule B10 indique le total de tous les paiements. Dans ce cas, une personne qui prends une hypothèque de 200 000 $, sous ces conditions, devra débourser 368 241,29 $ pour rembourser cette dette.

Table de données à une variable

Le premier exemple consiste à déterminer quel sera le montant de chaque paiement ainsi que le total pour l’année si on change le taux d’intérêt. Il est possible de créer ce tableau manuellement en essayant toutes les possibilités. Mais la commande table de données le fait beaucoup plus rapidement.

Voici le modèle qui calcule le montant de chaque paiement (B8), du total par année (B9) et du total de tous les paiements (B10).

Il faut préparer un tableau avant de pouvoir utiliser la table de données.

Les données ci-dessus vont être utilisées pour créer un tableau. Ce tableau sert à chercher les données qui seront utilisées dans le modèle et d’aller aussi chercher les résultats des cellules demandées. La première ligne contient les valeurs qui vont être remplacées dans le modèle. Pour cet exemple, il s’agit du taux d’intérêt. Le contenu de la première colonne contient les formules dont on veut voir le résultat.
La commande de la table de données va prendre chacune des valeurs de la première ligne et les placer dans le modèle ci-dessus. Elle va ensuite chercher les valeurs des cellules recherchées dans la première colonne (B8, B9) et les retranscrire dans le tableau. Il est possible d’inverser le contenu de la première ligne et de la première colonne si vous le désirez. Il est maintenant temps de générer le tableau.

Sélectionnez les cellules A13 à G15.
Allez à l’onglet Données.
Sous le bouton Analyse de scénarios, sélectionnez l’option Table de données.

Dans la case Cellule d’entrée en ligne, sélectionnez la cellule B4.
Appuyez sur le bouton OK.

Cette cellule contient le taux d’intérêt du modèle. Excel va prendre chacune des valeurs de la première ligne du tableau, où se trouve les taux de 5% à 7,5%, et les placer dans la cellule B4 pour ensuite voir l'impact aux cellule de la première colonne du tableau (=B8, =B9). Cela va prendre que quelques instants pour générer le tableau et vous le présenter.

Voici donc le résultat de l’analyse généré par le tableau de données (avec le format de présentation monétaire en plus). Cela veut dire que si la cellule B4 est égale à 5%, la cellule B8 sera égale à 539,32 $. La même chose se répète pour les autres cellules du tableau. Il est donc facile et rapide de créer un tableau de données et de comparer plusieurs possibilités dans un tableau simple à lire.

Table de données à 2 variables

Vous pouvez aussi utiliser la même commande pour générer un tableau ayant deux variables. Le prochain exercice consiste à connaître quel sera le montant total des paiements selon le taux d’intérêt et le nombre d’années que vous prenez pour payer la totalité de l’hypothèque (B10). Pour utiliser une table de données à deux variables, il faut placer les valeurs sur la première ligne et la première colonne. La première ligne aura les taux d’intérêt qu’on veut vérifier. La première colonne aura le nombre d’années qui sera pris pour payer au complet l’hypothèque.

À l’intersection de la première ligne et de la première colonne du tableau, entrez l’adresse de la cellule que vous désirez voir le résultat. Pour cet exercice, entrez la formule =B10.
Sélectionnez les cellules A19 à G23.
Allez à l’onglet Données.
Sous le bouton Analyse de scénarios, sélectionnez l’option Table de données.

Dans la case Cellule d’entrée en ligne, sélectionnez la cellule B4.
Dans la case Cellule d’entrée en colonne, sélectionnez la cellule B5.
Appuyez sur le bouton OK.

Les tables de données à deux variables peuvent seulement afficher le résultat d’une variable à la fois. Vous pour créer d’autres tables pour chaque variable que vous désirez voir le résultat ou changer l’adresse de la cellule dans le coin supérieur gauche de la table. Une autre façon plus simple est de changer l’adresse de la cellule à suivre dans la première cellule du tableau. Par exemple, changez le contenu de cette cellule de =B10 à =B8 et constatez le résultat.