La recherche d'objectifs vous permet de trouver l'ensemble des entrées qui mènent à un résultat particulier. Parfois, vous souhaitez voir de nombreux résultats différents à partir de diverses combinaisons d'entrées. Si vous n'avez que deux cellules d'entrée à modifier, la fonction Table de données effectuera une analyse de sensibilité.
En utilisant l'exemple de remboursement de prêt, disons que vous souhaitez calculer le prix pour une variété de soldes de principal et pour une variété de conditions.
Assurez-vous que la formule que vous souhaitez modéliser se trouve dans le coin supérieur gauche d'une plage. Mettez différentes valeurs pour une variable dans la colonne de gauche et diverses valeurs pour une autre variable en haut.
Dans l'onglet Données, sélectionnez What-If Analysis, Data Table….
Vous avez des valeurs sur la ligne supérieure de la table d'entrée. Vous voulez qu'Excel branche ces valeurs dans une certaine cellule d'entrée. Spécifiez cette cellule d'entrée pour la cellule d'entrée de ligne.
Vous avez des valeurs dans la colonne de gauche. Vous voulez que ceux-ci soient branchés dans une autre cellule d'entrée. Spécifiez cette cellule pour la cellule d'entrée de colonne.
Lorsque vous cliquez sur OK, Excel répète la formule dans la colonne supérieure gauche pour toutes les combinaisons de la ligne supérieure et de la colonne gauche. Dans l'image ci-dessous, vous voyez 60 paiements de prêt différents, basés sur diverses entrées.
Remarque
J'ai formaté les résultats du tableau pour qu'il n'y ait pas de décimales et utilisé Accueil, Formatage conditionnel, Échelle de couleurs pour ajouter l'ombrage rouge / jaune / vert.
Voici la grande partie: cette table est «en direct». Si vous modifiez les cellules d'entrée le long de la colonne de gauche ou de la ligne supérieure, les valeurs du tableau sont recalculées. Ci-dessous, les valeurs situées à gauche se concentrent sur la plage de 23 000 $ à 24 000 $.
Pointe
Vous pouvez créer des modèles beaucoup plus complexes tout en utilisant une table de données. Dans mon podcast 2141 "Will Asteroid Bennu Strike the Earth" sur YouTube, j'avais un modèle avec 100K NORM.INV et 100K VLOOKUP. Ces 200 000 formules ont été envoyées à une fonction SOMME qui les résumait. J'ai utilisé une table de données pour exécuter ces 200 001 formules 100 fois. Le tout se recalcule en 11 secondes environ.
Merci à Owen W. Green d'avoir suggéré cette technique de tables.
