Excel What-If Analysis propose un tableau de données. C'est une mauvaise réputation. Il devrait être appelé analyse de sensibilité. C'est cool. Lisez à ce sujet ici.
La recherche d'objectifs vous permet de trouver l'ensemble des entrées qui mènent à un résultat particulier. Parfois, vous voulez voir de nombreux résultats différents de diverses combinaisons d'entrées. À condition que vous n'ayez que deux cellules d'entrée à modifier, le tableau de données offre un moyen rapide de comparer les alternatives.
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, table de données.
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 comme 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 comme 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 en fonction de divers résultats.
Notez que 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 seront recalculées. Ci-dessous, les valeurs situées à gauche se concentrent sur la plage de 23 000 $ à 24 000 $.
Merci à Owen W. Green pour avoir suggéré des tableaux.
Regarder la vidéo
- Trois outils de simulation dans Excel
- Hier - Recherche d'objectifs
- Aujourd'hui - un tableau de données
- Idéal pour les problèmes à deux variables
- Trivia: la fonction de tableau TABLE ne peut pas être entrée manuellement - cela ne fonctionnera pas
- Utilisez une échelle de couleurs pour colorer les réponses
- Et si vous avez 3 variables à changer? Scénarios? Non! Copier la feuille de calcul
- Les tableaux sont lents à calculer: mode de calcul pour tous sauf les tableaux
- Merci à Owen W. Green d'avoir suggéré cette astuce
Transcription vidéo
Apprenez Excel à partir d'un podcast, épisode 2034 - What-Ifs with a Data Table!
Je fais un podcast sur tout ce livre, cliquez sur le «i» dans le coin supérieur droit pour accéder à la playlist!
Aujourd'hui, nous allons parler du deuxième outil sous l'analyse hypothétique, hier nous avons parlé de la recherche d'objectifs, aujourd'hui nous allons couvrir un tableau de données. Nous avons donc ce joli petit modèle ici, c'est un petit modèle, 3 cellules d'entrée, une formule. Mais ce modèle pourrait être des centaines de cellules d'entrée, des milliers de lignes, tant qu'il se résume à une réponse finale, et nous voulons modéliser cette réponse pour plusieurs valeurs différentes de 2-3 (?) Cellules d'entrée. Par exemple, peut-être que nous sommes intéressés à regarder différentes voitures, donc n'importe où à partir de 20000, donc je vais mettre 20 et 21000 là-bas, saisir la poignée de remplissage et faire glisser, ramener cela à 28000. En haut, nous ' re regardant des termes différents, donc un prêt de 36 mois, un prêt de 42 mois, un prêt de 48 mois, 54, 60, 66 et même 72.
Très bien maintenant, cette étape suivante est complètement facultative, mais cela m'aide vraiment à y réfléchir, je change toujours les couleurs des valeurs en haut et les valeurs en haut à gauche. Et la chose vraiment importante ici est que cette cellule d'angle, cette cellule d'angle très importante, doit être la réponse que nous essayons de modéliser, d'accord. Vous devez donc commencer à sélectionner dans cette cellule d'angle avec la réponse, puis sélectionner toutes les lignes et toutes les colonnes. Donc, nous allons dans les données, l'analyse hypothétique et un tableau de données, et il demande deux choses ici, et voici comment vous en pensez. Il dit qu'il y a tout un tas d'éléments différents le long de la rangée supérieure du tableau, je veux prendre ces éléments, un à la fois, et les brancher dans le modèle, où devrions-nous entrer? Donc ces éléments, ce sont des termes, ils devraient entrer dans la cellule B2. Puis,il y a tout un tas d'éléments le long de la colonne de gauche, nous voulons les prendre, un à la fois, et les brancher sur B1, comme ça, d'accord et nous cliquons sur OK, BAM, il exécute ce modèle encore et encore et encore .
Maintenant, juste un peu de nettoyage ici, je vais toujours à la maison et probablement 0 décimale, comme ça. Et peut-être un peu de mise en forme conditionnelle, des échelles de couleur, et allons-y avec des nombres rouges pour les grands et verts pour les petits, juste pour me donner un moyen, vous savez, de suivre cela visuellement. Maintenant, il semble que si nous tournons pour 425 $, nous sommes en quelque sorte, vous savez, à cet endroit ou à cet endroit, ou vous savez, peut-être qu'ici, nous nous rapprocherons tous de 425 $. Je peux donc voir quelles sont les différentes probabilités, nos diverses combinaisons, pour nous amener à ces valeurs.
Maintenant, quelques choses, cette partie à l'intérieur d'ici, est en fait une formule de grand tableau, donc = TABLE (B2, B1), l'entrée de ligne et de colonne. C'est curieux, vous n'êtes pas autorisé à taper ceci, vous ne pouvez le créer qu'en utilisant Data, What-If Analysis, vous devez utiliser cette boîte de dialogue. Si vous essayez de taper cette formule, appuyez sur Ctrl + Maj + Entrée, cela ne fonctionnera pas, non? Donc, c'est une fonction dans Excel, mais si vous êtes assez intelligent pour le taper, dommage, cela ne fonctionnera pas, mais il recalcule constamment. Donc, si nous déterminons que nous ne regardons que les termes de 48, et que nous voulons regarder par groupes de 3 ou quelque chose comme ça, alors que je change ces chiffres, tout cela est un calcul. Dans ce cas, il ne fait qu'une formule pour chacun, mais imaginez que si nous faisions 100 formules, cela ralentirait considérablement. Alors ici sous Formules, là 's en fait une option Options de calcul, Automatique ou Manuel, il y en a une troisième qui dit "Ouais, recalculez tout sauf pour les tableaux de données, ne recalculez pas le tableau de données." Parce que cela peut être un énorme frein sur les temps de calcul.
Très bien maintenant, les tableaux de données sont géniaux lorsque vous avez deux variables à modifier, mais nous avons trois variables à modifier. Et s'il y avait des taux d'intérêt différents, est-ce que je recommande d'aller au gestionnaire de scénarios? NON, je ne recommande JAMAIS d'aller au gestionnaire de scénarios! Dans ce cas, nous avons 9x7, c'est 63 scénarios différents que nous avons calculés ici, pour créer 63 scénarios différents de Scenario Manager prendrait 2 heures, c'est horrible. Je ne parle pas de cela dans le livre «MrExcel XL», car ce sont les 40 meilleurs conseils. C'est probablement dans mon livre "Power Excel" avec 567 mystères Excel résolus, mais je suis sûr que je me suis plaint de la misère à utiliser, vous ne me verrez pas faire le gestionnaire de scénarios ici. Si nous devions vraiment faire cela pour plusieurs taux différents, la meilleure chose à faire est simplement Ctrl-glisser, prendre cette feuille, Ctrl-glisser, Ctrl-glisser,Ctrl-faites glisser, puis modifiez les taux sur chaque feuille. Donc, si nous pouvions obtenir un 5% ou 4,75% ou quelque chose comme ça et ainsi de suite, c'est vrai, il n'y a pas de moyen facile de configurer cela pour 3 variables dans le gestionnaire de scénarios. Très bien, "40 meilleurs conseils Excel de tous les temps", tout dans ce livre, vous pouvez acheter le livre, cliquez sur ce "i" dans le coin supérieur droit.
Récapitulatif des épisodes d'aujourd'hui: Il existe trois outils de simulation dans Excel, hier, nous avons parlé de Goal Seek, aujourd'hui le tableau de données. C'est génial pour les problèmes à 2 variables, demain vous en verrez un avec un problème à 1 variable. La fonction de tableau de tableau ne peut pas être saisie manuellement, elle ne fonctionnera pas, vous devez utiliser Data, What-If Analysis, Data Table. J'ai utilisé une échelle de couleurs, Accueil, Mise en forme conditionnelle, Échelles de couleurs, pour colorer les réponses. Si vous avez 3 variables à changer, vous faites des scénarios? Non, faites simplement des copies de la feuille de calcul ou des copies du tableau, elles sont lentes à calculer, surtout avec un modèle complexe. Il existe un mode de calcul automatique pour tous sauf les tables, et Owen W. Green a suggéré d'inclure cette fonction dans les livres.
Alors merci à lui, et merci à vous pour votre visite, on se revoit la prochaine fois pour un autre webcast de!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2034.xlsx
