Comment créer un tableau de données pour l’analyse « et si » dans Excel

Microsoft Excel offre plusieurs fonctionnalités pour analyser des scénarios « et si ». L’un de ces outils est la fiche technique. Avec lui, vous pouvez expérimenter différentes valeurs pour voir différents résultats.

C’est un moyen utile de voir des éléments tels que les prêts, les investissements, les marges sur les produits, etc., avec différentes variables. Par exemple, vous pouvez entrer plusieurs taux d’intérêt différents pour un prêt pour voir quel sera votre paiement à chaque taux, le tout en un seul endroit.

Créer une table de données avec une variable

Les tableaux de données dans Excel peuvent inclure une ou deux entrées différentes. Commençons donc avec une table de données avec une variable. Nous avons calculé la valeur future de notre investissement en utilisant la fonction FV et des constantes pour le taux d’intérêt annuel, le nombre de paiements et le montant du paiement.

Ici, nous verrons quelle sera la valeur future de nos investissements à différents taux d’intérêt.

Vous pouvez entrer vos valeurs dans une colonne ou une ligne, mais le placement affecte l’endroit où vous devez placer votre formule. Si vos valeurs sont dans une colonne, placez la formule une cellule au-dessus et à droite. Si vos valeurs sont alignées, placez la formule une cellule en bas et à gauche.

Dans notre exemple, nous utilisons une table de données orientée colonne. Comme vous pouvez le voir dans la capture d’écran ci-dessous, nous avons déplacé notre formule dans la colonne D et répertorié nos taux d’intérêt dans la colonne C.

Sélectionnez les cellules qui contiennent la formule, les valeurs et les résultats attendus. Pour nous c’est du C2 au D6.

Cliquez sur l’onglet Données, cliquez sur la flèche de la liste déroulante Analyse d’hypothèses et sélectionnez Table de données.

Dans la fenêtre Tableau de données qui s’ouvre, saisissez la référence de cellule de la variable à modifier et selon vos paramètres. Dans notre exemple, nous saisissons une référence à la cellule B3 pour le taux d’intérêt changeant dans le champ Column Entry Cell. Encore une fois, nous utilisons un tableau de données basé sur des colonnes.

Cliquez sur OK et vous verrez les résultats. Nous avons maintenant la valeur future de notre investissement avec chaque taux d’intérêt saisi.

Créer une table de données avec deux variables

Vous pouvez ajouter deux variables au tableau de données pour une analyse plus détaillée. En utilisant le même exemple, non seulement nous voulons obtenir la valeur future de nos investissements avec différents taux d’intérêt, mais nous voulons également voir différentes valeurs futures en modifiant le nombre de paiements.

Étant donné que vous utilisez deux variables, vous utiliserez un tableau de données orienté à la fois en colonnes et en lignes. Placez donc la formule directement au-dessus de la colonne de valeur et à gauche de la ligne de valeur, comme indiqué ci-dessous.

Sélectionnez maintenant toutes les cellules contenant la formule, les valeurs et les résultats attendus. Pour nous, c’est de C2 à F6.

Cliquez sur l’onglet Données et sélectionnez Analyse d’hypothèses > Table de données. Lorsque la fenêtre apparaît, entrez les références des cellules d’entrée de ligne et de colonne.

En utilisant notre exemple, nous entrons B3 pour la cellule d’entrée de colonne qui correspond à notre taux d’intérêt et B4 pour la cellule d’entrée de ligne qui correspond au montant de notre paiement.

Cliquez sur OK et vous devriez voir toute la sortie de vos variables. Ici, nous avons la valeur future de notre investissement en utilisant différents taux d’intérêt et différents nombres de paiements, le tout affiché dans un tableau agréable et soigné.

La prochaine fois que vous souhaitez analyser des données sur une feuille de calcul avec des nombres ou des montants différents, envisagez d’utiliser une feuille de données. Non seulement les calculs sont faits pour vous, mais le résultat vous donne un tableau structuré pour une analyse facile.