在 Excel 中进行敏感性分析的简单方法是什么?

在 Excel 中进行敏感性分析的简单方法是什么?

我有一个中等复杂度的 Excel 模型,我想对其进行一些敏感性分析。理想的情况是,对于一组输入中的每一个,运行一系列可能的值并存储结果输出值(因此可能一次更改一个输入值,但在下一个级别,一次更改多个输入值会更好)。

我知道有插件可以解决这个问题 -solver.net 看起来很不错 - 但我不愿意为一个概念上很简单的问题花 1000 美元。现在我遵循手动算法:更改值、查看新值、复制粘贴、重复。这很糟糕。

大家有没有推荐的关于如何自动化这个过程的技巧/技术/宏?

答案1

求解器(和目标搜索)用于优化需要调整变量以产生给定约束的情况(即最大化值、最小化成本、求解特定数字)。

您的问题是一个更直接的敏感性分析。

数据表是一种通过模型灵活输入以实现同时输出的简单方法,可以轻松添加单向(1 个变化变量)和双向表(灵活成对的行和列输入的表)。

  1. http://www.homeandlearn.co.uk/excel2007/excel2007s7p1.html
  2. http://support.microsoft.com/kb/282851
  3. 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 虽然不便宜,但却能满足我的要求。

相关内容