我有一个中等复杂度的 Excel 模型,我想对其进行一些敏感性分析。理想的情况是,对于一组输入中的每一个,运行一系列可能的值并存储结果输出值(因此可能一次更改一个输入值,但在下一个级别,一次更改多个输入值会更好)。
我知道有插件可以解决这个问题 -solver.net 看起来很不错 - 但我不愿意为一个概念上很简单的问题花 1000 美元。现在我遵循手动算法:更改值、查看新值、复制粘贴、重复。这很糟糕。
大家有没有推荐的关于如何自动化这个过程的技巧/技术/宏?
答案1
求解器(和目标搜索)用于优化需要调整变量以产生给定约束的情况(即最大化值、最小化成本、求解特定数字)。
您的问题是一个更直接的敏感性分析。
数据表是一种通过模型灵活输入以实现同时输出的简单方法,可以轻松添加单向(1 个变化变量)和双向表(灵活成对的行和列输入的表)。
- http://www.homeandlearn.co.uk/excel2007/excel2007s7p1.html
- http://support.microsoft.com/kb/282851
- http://office.microsoft.com/en-au/excel-help/calculate-multiple-results-by-using-a-data-table-HP010072656.aspx
这对你来说可能就足够了。
具有 3 个或更多输入的更高级数据表
如果你想改变场景3 个或以上变量,那么您可以通过在数据表中定义案例 1-10 来解决双向表限制,其中选择 1 可能会驱动变量 A、B、C 和 D 的某种组合,选择 2 可能会驱动不同的组合等。
下图就是这么做的。D10 中的单元格从 D14:18 中选择变量,并通过 D3:D8 中的计算器运行。
基本计算器是现金 = 数量 * (收入-成本-O/H)-(1*税率)。
C23:D28 中的数据表同时显示了 5 种场景的输出(即场景 1 为 56,场景 2 为 80 等)。
答案2
我已经编写了一个小型 Excel 插件宏,它允许你对一、二、三和最多 20 个输入单元在电子表格中,同时观察一个或多个输出单元格对输入变化的反应。您可以选择一次改变一个输入单元格(“单一敏感度”)或各种输入的所有组合(“多重敏感度”)。该插件是免费用于商业或私人用途可以在以下网址找到:http://www.life-cycle-costing.de/sensitivity_analysis/
我希望这个小工具能够满足您的要求并且是免费软件。
很高兴听到这是否是您一直在寻找的东西。
答案3
有几种方法可以在 Excel 上进行敏感性分析。您可以使用数据表或场景管理器。这取决于您想要在分析中更改的变量数量以及您想要的输出类型。查看以下帖子以了解有关这两种方法的更多信息。
http://awaisa.wordpress.com/2013/07/04/conducing-sensitivity-analysis-using-scenario-manager/
http://awaisa.wordpress.com/2013/06/20/sensitivity-analysis-with-data-tables/
希望这可以帮助。
答案4
我发现 Oracle 的 Crystal Ball 虽然不便宜,但却能满足我的要求。