Comment créer un tableau de données pour l’analyse What-If dans Excel
Microsoft Excel offre quelques fonctionnalités pour effectuer des analyses sur des scénarios hypothétiques. L’un de ces outils est un tableau de données. 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 saisir plusieurs taux d’intérêt différents pour un prêt afin de voir quel serait votre paiement avec chaque taux, le tout au même endroit.
Créer une table de données à une variable
Les tableaux de données dans Excel peuvent inclure une ou deux entrées variables. Alors, commençons par un tableau de données à une variable. Nous avons calculé la valeur future de notre investissement avec 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 serait la valeur future de notre investissement avec 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 dessous et à gauche.
Pour 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 vers la colonne D et répertorié nos taux d’intérêt dans la colonne C.
Sélectionnez les cellules contenant la formule, les valeurs et les résultats attendus. Pour nous, c’est C2 à D6.
Accédez à l’onglet Données, cliquez sur la flèche de la liste déroulante What-If Analysis et sélectionnez « Tableau de données ».
Dans la boîte de tableau de données qui s’ouvre, entrez la référence de cellule pour la variable changeante et selon votre configuration. Pour notre exemple, nous saisissons la référence de cellule B3 pour l’évolution du taux d’intérêt dans le champ Column Input Cell. Encore une fois, nous utilisons une table de données basée sur des colonnes.
Cliquez sur « OK » et vous verrez vos résultats. Nous avons maintenant la valeur future de notre investissement avec chaque taux d’intérêt que nous avons entré.
Créer une table de données à deux variables
Vous pouvez ajouter deux variables à votre tableau de données pour une analyse plus détaillée. En utilisant notre même exemple, non seulement nous voulons la valeur future de notre investissement avec des taux d’intérêt différents, mais nous aimerions également voir des valeurs futures différentes en modifiant le nombre de paiements.
Puisque vous utilisez deux variables, vous utiliserez à la fois une table de données orientée colonne et ligne. Placez donc la formule directement au-dessus de la colonne de valeurs et à gauche de la ligne de valeurs, comme indiqué ci-dessous.
Maintenant, sélectionnez toutes les cellules contenant la formule, les valeurs et les sorties attendues. Pour nous, c’est C2 à F6.
Accédez à l’onglet Données et sélectionnez Analyse d’hypothèses > Tableau de données. Lorsque la zone apparaît, entrez les références de cellule 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 à notre nombre de paiements.
Cliquez sur « OK » et vous devriez voir toutes les sorties de vos variables. Ici, nous avons la valeur future de notre investissement en utilisant différents taux d’intérêt et un nombre variable de paiements, le tout affiché dans un tableau agréable et soigné.
La prochaine fois que vous souhaitez analyser des données dans votre feuille avec des nombres ou des montants variables, envisagez d’utiliser un tableau de données. Non seulement les calculs sont effectués pour vous, mais le résultat vous donne un tableau structuré pour une analyse facile.
