在电子表格中设计模型时，我们可能会想知道，通过改变某个相关变量的取值，结果会如何变化。这个过程可以通过在 Excel 中运行敏感性分析来实现。
如果您是一名从事财务建模或决策流程的数据从业者，掌握如何进行敏感性分析至关重要。
本文将深入探讨敏感性分析。我们将介绍这一“假设分析”家族中实用技术的基础知识，以及如何在 Excel 中执行并解读基础与高级的敏感性分析。最后，我们将讨论 AI 与机器学习在推动更强大敏感性分析中的角色，以及最佳实践与常见误区。
如果您迫不及待想上手实践，欢迎查看我们的 Excel 财务分析课程，其中详细讲解了假设分析。
什么是 Excel 中的敏感性分析？
敏感性分析中最常见的方法是一次只考察一个变量的变化在保持其他因素不变的情况下如何影响最终结果。对所有待考察变量重复此过程。这样有助于识别模型中最具影响力的驱动因素，使企业能够聚焦于影响表现的关键要素。
敏感性分析通过识别对业绩影响最大的因素，帮助企业优先考虑关键风险与机遇。鉴于其特性，敏感性分析常见于金融、商业与项目管理领域，例如用于计算利率可能随时间的变化或理解不同的增长趋势。
此外，敏感性分析常与情景分析结合使用，后者通常会同时分析变量的组合。这两种假设分析都研究因变量对特定输入的反应。然而，情景分析针对特定“情景”，而敏感性分析更为开放，因为它提供了一系列输入和取值。
敏感性分析的目标是了解因变量如何对一系列输入值（即自变量）作出反应。
通过将这种技术纳入决策流程，组织可以主动调整战略，并提高应对不确定性的韧性。
若想更深入了解敏感性分析背后的理论，我们强烈推荐我们的高级概率：数据中的不确定性课程。
敏感性分析通常会以敏感性表的形式展示，并配合条件格式以突出从高到低的数值。
下面是关于供需对价格敏感性的示例。供给与需求是标注在坐标轴上的自变量，价格是因变量，承载在表格的数值中。
接下来章节我们将使用类似的 Excel 敏感性表。
敏感性表。来源： DataCamp
在 Excel 中开始进行敏感性分析
让我们按照流程的每个步骤，看看如何在 Excel 中执行敏感性分析。
Excel 中的工具与功能
Excel 是创建数学模型（包括假设分析）的便捷工具。假设分析本质上是创建允许探索问题的公式，例如敏感性分析。
大多数最新版本的 Excel 都带有“数据”选项卡，提供了大量用于处理数据的工具与功能。其中功能区包含“假设分析”按钮，内含三种工具。其中“数据表”按钮专为创建敏感性表而设计。
要执行情景分析，只需点击“方案管理器（Scenario Manager）”按钮。
我们还将使用“规划求解（Solver）”，这是一款可在“开发工具”选项卡中启用的加载项。
规划求解用于假设分析，以在一个单元格中的公式（称为目标单元格）上求取最优（最大或最小）值，同时受限于工作表中其他公式单元格取值的约束或限制。
在启用规划求解之前，您需要先访问默认不显示的“开发工具”选项卡。要显示该选项卡：
- 转到“文件” > “选项”
- 在左侧面板选择“自定义功能区”
- 在右侧“主选项卡”下，勾选“开发工具”
执行单变量敏感性分析
工具准备就绪后，我们先从在 Excel 中创建一个简单的单变量敏感性分析开始。
设置您的模型
在 Excel 中进行敏感性分析的第一步，是识别模型所依赖的输入与输出。
在本教程中，我们将使用敏感性分析来研究一家卖吉他的公司在改变某些输入变量（如售出吉他数量、吉他价格或生产成本）时，净利润会如何变化。
下面是包含输入与输出变量的表格。
在使用数据表进行敏感性分析时，务必将输出单元格与输入变量建立链接，使 Excel 能理解模型变量之间的关系。这个过程通过公式完成。在我们的示例中：
- 收入（Revenue）通过将每单位价格乘以售出单位数计算（=B2*B4）
- 销售成本（Cost of sales）通过将每单位生产成本乘以售出单位数计算（=B3*B4）
- 利润（Profit）通过用收入减去销售成本计算（=B7-B8）。
创建单向数据表
您可能想了解在保持生产成本与售出单位数不变的情况下，提高或降低每单位价格，利润会如何变化。当我们分析在修改一个输入变量的取值时输出变量如何变化，就需要所谓的单向数据表。
下方 GIF 展示了如何在 Excel 中创建单向数据表。可以看到，表格会自动为每个售出单位数重新计算利润。理所当然地，当售出 250 件时的利润与原始参数下相同（即 50,000 €）。
您也可能想研究在我们修改售出单位数时，不同输出变量如何变化。这也相当简单，如下所示。下方 GIF 中，我们计算了售出单位数对利润与销售成本的影响。
进行双变量敏感性分析
与其分析一个输入对输出的影响，您也可以创建双变量表来分析两个输入变量的影响。比如，您想知道在同时调整吉他销量和每把吉他的价格时，利润会如何变化。
首先，创建一个二维表，将每单位价格的取值范围放在行中，将售出单位数放在列中。然后，在表格左上角选择您要研究的变量。最后，在输入变量表中选择自变量的单元格引用（即每单位价格与售出单位数）。
如您所见，一旦创建好表格，您可以轻松地将左上角的输出变量更换为其他变量，Excel 将重新计算所有数值。
解读 Excel 敏感性分析结果
现在您已经拥有数据表，最后一步就是解读结果，从而洞察输入变化如何影响输出。
分析数据表输出
基于这份敏感性分析，我们可以对利润作出更明智的决策。以下是一些洞察：
- 如果我们将吉他价格提高到 350 €，只需卖出 200 把即可达到当前 50,000 € 的利润。
- 如果我们将吉他价格降至 200 €，则需要卖出 500 台才能达到当前利润。
- 如果我们将售出吉他数量降至 150，把价格不提高到 400 € 以上，就无法匹配当前利润。
如何解读分析结果取决于您公司的具体需求。提出诸如您能生产多少吉他、维持公司稳健运营所需的最低利润水平、以及您可采取哪些措施降低吉他生产成本等问题，对从敏感性分析中提取有意义的洞察至关重要。
在敏感性分析中使用规划求解
当您进行敏感性分析时，您会基于输入变量的变动得到一系列潜在输出。然而，您可能还希望更深入地了解模型的内部机制。
幸运的是，Excel 自带了一款强大的工具——规划求解，可帮助您完成这项任务。如前所述，规划求解用于假设分析，在满足对工作表中其他公式单元格取值的约束或限制的情况下，为某个公式求取最优（最大或最小）值。
这听起来与单变量求解（Goal Seek）的目标类似，后者是一种假设分析，专注于调整单一变量以达到期望结果。然而，规划求解远不止于单变量求解。
首先，规划求解允许您基于多个变量计算最优结果，并能在模型中引入约束条件。
此外，规划求解还带有可选的敏感性功能，可为您提供在更改模型系数时最优解如何变化的洞察。
为展示规划求解的强大功能，让我们回到吉他公司的例子。假设公司生产两款吉他（A 型与 B 型），每款吉他都需要一定比例的桃花心木与雪松。每款的价格不同。
公司希望在给定当前桃花心木与雪松可用量的情况下，确定每款应生产多少单位以使利润最大化。
我们希望规划求解计算三项内容：利润、A 型的产量、B 型的产量。上图展示了模型所基于的输入、公式与约束。
在下方 GIF 中，我们演示了如何使用规划求解在木材可用量的约束下，并确保产量为整数的条件下，找到使利润最大化的最优产量组合。规划求解可用于多种策略来优化模型，既适用于线性问题，也适用于非线性问题。本例中我们使用 Simple LP。
在录入所有参数后，规划求解估计 A 型 48 台、B 型 9 台是使利润（17,591 €）最大化的最佳组合。我们可以在关闭规划求解对话框前点击“敏感性”，Excel 将创建包含敏感性报告的新工作表。
敏感性报告为我们提供了有价值的洞察，以估计当我们改变输入变量时利润会如何变化。影子价格（Shadow Price）表示如果受限资源增加一个单位，利润将增加多少。在本例中，每增加 1 平方米的桃花心木，利润增加 98 €；雪松则增加 103 €，这表明雪松具有更高的潜在投资回报。
敏感性报告中的另一重要元素，是在不改变模型结果的前提下，您可以保持各型号价格的变化范围。
利润通过将每种型号的产量乘以其价格计算，这些数值分别显示在“最终值（Final Value）”与“目标系数（Objective Coefficient）”列中。 相应范围显示在“允许增加值（Allowable Increase）”与“允许减少值（Allowable Decrease）”两列中。
例如，如果将 A 型的价格提高 1,140 € 或降低 180 €，规划求解仍会得到相同的最优生产数量。
使用条件格式可视化数据
如果您创建了单向与双向敏感性表，对结果进行格式化能显著提升快速发现关键信息与优化决策的效率。条件格式是创建重要阈值与吸睛热力图的关键。
让我们回到双向表。与其展示白底单元格的表格，我们可以使用条件格式创建多色热力图或双色刻度，并设置自定义阈值，如 GIF 所示。
整合 AI 与机器学习
下面简要介绍 AI 与机器学习如何帮助改进您的敏感性分析。
AI 增强的敏感性分析
到目前为止，我们已经看到如何手动执行敏感性分析。然而，随着模型复杂性的提升，执行与解读敏感性分析的结果可能会变得耗时。
这正是 AI 发挥作用的地方。借助 AI 与机器学习，您可以在满足特定条件时让机器自动完成计算，并将结果呈现在引人注目的仪表板中。
一旦确定了要执行的计算，您的下一步就是清晰定义一条 AI 流水线，以简化并自动化流程。
想知道如何实现？请查看我们的机器学习、流水线、部署与 MLOps 教程。
蒙特卡罗模拟
蒙特卡罗模拟是一种数学技术，用于对因随机变量介入而难以准确预测的过程，建模其不同结果发生的概率。
首先，将问题中具有内在不确定性的每个变量用一个概率分布来建模。然后从这些概率分布中抽取大量随机样本，并使用这些样本来计算结果。
这个过程会重复多次，从而生成可能结果的分布，之后可进行统计分析，以预测系统的行为方式。与传统敏感性表相比，蒙特卡罗能更全面地分析不确定性。
您可以在我们的Excel 中的蒙特卡罗模拟指南中全面学习这一强大技术。
鉴于蒙特卡罗模拟的特性，您可以用它来增强敏感性分析，找出对输出影响最大的变量。
在我们的吉他示例中，我们可以保持成本价与售出单位数不变，仅改变单价来理解估计值的变化。随后对其余两个变量分别重复这一过程。最终，这一技术有助于了解应将精力聚焦于哪个变量以提升准确性。
最佳实践与常见陷阱
尽管 Excel 让敏感性分析极其简单且易用，但仍有一些常见陷阱需要注意。以下是一些要点。
设计稳健的模型
正如您在本教程中所见，要让敏感性分析发挥作用，Excel 必须了解输入与输出变量之间的关系。这要求您合理使用公式，并提供清晰定义的单元格引用和范围。
如果您想在数据表中新增取值，为避免在重新计算时可能出现的问题，一个良好实践是使用Excel 表格（Tables），因为它们天然具有动态性，新增行或列时会自动扩展。
这将帮助您进行正确的公式重算，并保持信息格式的正确性。
降低错误并确保准确性
即便您的敏感性分析定义清晰，仍需仔细核验结果。手动测试与图表是可视化结果并检查输出中离群值或异常的良好方式。
不过，重要的是记住，敏感性分析可能并不适用于您的用例。该分析基于一组简单假设，例如输入变量的独立性、线性关系的存在，以及输入变量的静态性质。
然而，您的模型可能更为复杂，且可能受敏感性分析本质上忽略的外部因素影响。因此，尽管敏感性分析有其益处，但要确保正确分析，仍需在敏感性之外对模型有全面的理解。
结论
恭喜您读到本教程的最后。敏感性分析是一种强大的假设分析，能在您的决策流程中发挥重要作用。还有其他可增强您财务模型的假设分析工具，如单变量求解（Goal Seek）或方案管理器（Scenario Manager）。幸运的是，DataCamp 可以助您一臂之力。欢迎查看我们精选的 Excel 课程与资料：
%22%3E%3Cpath%20d%3D%22m-369%200h408v79h-408z%22%20fill%3D%22%237933ff%22%2F%3E%3Cpath%20d%3D%22m-148%200h306v45h-306z%22%20fill%3D%22%23974dff%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%2F%3E%3C%2FclipPath%3E%3Cg%20clip-path%3D%22url(%23a)%22%3E%3Cpath%20d%3D%22m76%200h484v36h-484z%22%20fill%3D%22%23974dff%22%2F%3E%3Cpath%20d%3D%22m0%200h384v24h-384z%22%20fill%3D%22%2306bdfc%22%20transform%3D%22matrix(1%200%200%20-1%200%2060)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
在电子表格中设计模型时，我们可能会想知道，通过改变某个相关变量的取值，结果会如何变化。这个过程可以通过在 Excel 中运行敏感性分析来实现。
如果您是一名从事财务建模或决策流程的数据从业者，掌握如何进行敏感性分析至关重要。
本文将深入探讨敏感性分析。我们将介绍这一“假设分析”家族中实用技术的基础知识，以及如何在 Excel 中执行并解读基础与高级的敏感性分析。最后，我们将讨论 AI 与机器学习在推动更强大敏感性分析中的角色，以及最佳实践与常见误区。
如果您迫不及待想上手实践，欢迎查看我们的 Excel 财务分析课程，其中详细讲解了假设分析。
什么是 Excel 中的敏感性分析？
敏感性分析中最常见的方法是一次只考察一个变量的变化在保持其他因素不变的情况下如何影响最终结果。对所有待考察变量重复此过程。这样有助于识别模型中最具影响力的驱动因素，使企业能够聚焦于影响表现的关键要素。
敏感性分析通过识别对业绩影响最大的因素，帮助企业优先考虑关键风险与机遇。鉴于其特性，敏感性分析常见于金融、商业与项目管理领域，例如用于计算利率可能随时间的变化或理解不同的增长趋势。
此外，敏感性分析常与情景分析结合使用，后者通常会同时分析变量的组合。这两种假设分析都研究因变量对特定输入的反应。然而，情景分析针对特定“情景”，而敏感性分析更为开放，因为它提供了一系列输入和取值。
敏感性分析的目标是了解因变量如何对一系列输入值（即自变量）作出反应。
通过将这种技术纳入决策流程，组织可以主动调整战略，并提高应对不确定性的韧性。
若想更深入了解敏感性分析背后的理论，我们强烈推荐我们的高级概率：数据中的不确定性课程。
敏感性分析通常会以敏感性表的形式展示，并配合条件格式以突出从高到低的数值。
下面是关于供需对价格敏感性的示例。供给与需求是标注在坐标轴上的自变量，价格是因变量，承载在表格的数值中。
接下来章节我们将使用类似的 Excel 敏感性表。
敏感性表。来源： DataCamp
在 Excel 中开始进行敏感性分析
让我们按照流程的每个步骤，看看如何在 Excel 中执行敏感性分析。
Excel 中的工具与功能
Excel 是创建数学模型（包括假设分析）的便捷工具。假设分析本质上是创建允许探索问题的公式，例如敏感性分析。
大多数最新版本的 Excel 都带有“数据”选项卡，提供了大量用于处理数据的工具与功能。其中功能区包含“假设分析”按钮，内含三种工具。其中“数据表”按钮专为创建敏感性表而设计。
要执行情景分析，只需点击“方案管理器（Scenario Manager）”按钮。
我们还将使用“规划求解（Solver）”，这是一款可在“开发工具”选项卡中启用的加载项。
规划求解用于假设分析，以在一个单元格中的公式（称为目标单元格）上求取最优（最大或最小）值，同时受限于工作表中其他公式单元格取值的约束或限制。
在启用规划求解之前，您需要先访问默认不显示的“开发工具”选项卡。要显示该选项卡：
执行单变量敏感性分析
工具准备就绪后，我们先从在 Excel 中创建一个简单的单变量敏感性分析开始。
设置您的模型
在 Excel 中进行敏感性分析的第一步，是识别模型所依赖的输入与输出。
在本教程中，我们将使用敏感性分析来研究一家卖吉他的公司在改变某些输入变量（如售出吉他数量、吉他价格或生产成本）时，净利润会如何变化。
下面是包含输入与输出变量的表格。
在使用数据表进行敏感性分析时，务必将输出单元格与输入变量建立链接，使 Excel 能理解模型变量之间的关系。这个过程通过公式完成。在我们的示例中：
创建单向数据表
您可能想了解在保持生产成本与售出单位数不变的情况下，提高或降低每单位价格，利润会如何变化。当我们分析在修改一个输入变量的取值时输出变量如何变化，就需要所谓的单向数据表。
下方 GIF 展示了如何在 Excel 中创建单向数据表。可以看到，表格会自动为每个售出单位数重新计算利润。理所当然地，当售出 250 件时的利润与原始参数下相同（即 50,000 €）。
您也可能想研究在我们修改售出单位数时，不同输出变量如何变化。这也相当简单，如下所示。下方 GIF 中，我们计算了售出单位数对利润与销售成本的影响。
进行双变量敏感性分析
与其分析一个输入对输出的影响，您也可以创建双变量表来分析两个输入变量的影响。比如，您想知道在同时调整吉他销量和每把吉他的价格时，利润会如何变化。
首先，创建一个二维表，将每单位价格的取值范围放在行中，将售出单位数放在列中。然后，在表格左上角选择您要研究的变量。最后，在输入变量表中选择自变量的单元格引用（即每单位价格与售出单位数）。
如您所见，一旦创建好表格，您可以轻松地将左上角的输出变量更换为其他变量，Excel 将重新计算所有数值。
解读 Excel 敏感性分析结果
现在您已经拥有数据表，最后一步就是解读结果，从而洞察输入变化如何影响输出。
分析数据表输出
基于这份敏感性分析，我们可以对利润作出更明智的决策。以下是一些洞察：
如何解读分析结果取决于您公司的具体需求。提出诸如您能生产多少吉他、维持公司稳健运营所需的最低利润水平、以及您可采取哪些措施降低吉他生产成本等问题，对从敏感性分析中提取有意义的洞察至关重要。
在敏感性分析中使用规划求解
当您进行敏感性分析时，您会基于输入变量的变动得到一系列潜在输出。然而，您可能还希望更深入地了解模型的内部机制。
幸运的是，Excel 自带了一款强大的工具——规划求解，可帮助您完成这项任务。如前所述，规划求解用于假设分析，在满足对工作表中其他公式单元格取值的约束或限制的情况下，为某个公式求取最优（最大或最小）值。
这听起来与单变量求解（Goal Seek）的目标类似，后者是一种假设分析，专注于调整单一变量以达到期望结果。然而，规划求解远不止于单变量求解。
首先，规划求解允许您基于多个变量计算最优结果，并能在模型中引入约束条件。
此外，规划求解还带有可选的敏感性功能，可为您提供在更改模型系数时最优解如何变化的洞察。
为展示规划求解的强大功能，让我们回到吉他公司的例子。假设公司生产两款吉他（A 型与 B 型），每款吉他都需要一定比例的桃花心木与雪松。每款的价格不同。
公司希望在给定当前桃花心木与雪松可用量的情况下，确定每款应生产多少单位以使利润最大化。
我们希望规划求解计算三项内容：利润、A 型的产量、B 型的产量。上图展示了模型所基于的输入、公式与约束。
在下方 GIF 中，我们演示了如何使用规划求解在木材可用量的约束下，并确保产量为整数的条件下，找到使利润最大化的最优产量组合。规划求解可用于多种策略来优化模型，既适用于线性问题，也适用于非线性问题。本例中我们使用 Simple LP。
在录入所有参数后，规划求解估计 A 型 48 台、B 型 9 台是使利润（17,591 €）最大化的最佳组合。我们可以在关闭规划求解对话框前点击“敏感性”，Excel 将创建包含敏感性报告的新工作表。
敏感性报告为我们提供了有价值的洞察，以估计当我们改变输入变量时利润会如何变化。影子价格（Shadow Price）表示如果受限资源增加一个单位，利润将增加多少。在本例中，每增加 1 平方米的桃花心木，利润增加 98 €；雪松则增加 103 €，这表明雪松具有更高的潜在投资回报。
敏感性报告中的另一重要元素，是在不改变模型结果的前提下，您可以保持各型号价格的变化范围。
利润通过将每种型号的产量乘以其价格计算，这些数值分别显示在“最终值（Final Value）”与“目标系数（Objective Coefficient）”列中。 相应范围显示在“允许增加值（Allowable Increase）”与“允许减少值（Allowable Decrease）”两列中。
例如，如果将 A 型的价格提高 1,140 € 或降低 180 €，规划求解仍会得到相同的最优生产数量。
使用条件格式可视化数据
如果您创建了单向与双向敏感性表，对结果进行格式化能显著提升快速发现关键信息与优化决策的效率。条件格式是创建重要阈值与吸睛热力图的关键。
让我们回到双向表。与其展示白底单元格的表格，我们可以使用条件格式创建多色热力图或双色刻度，并设置自定义阈值，如 GIF 所示。
整合 AI 与机器学习
下面简要介绍 AI 与机器学习如何帮助改进您的敏感性分析。
AI 增强的敏感性分析
到目前为止，我们已经看到如何手动执行敏感性分析。然而，随着模型复杂性的提升，执行与解读敏感性分析的结果可能会变得耗时。
这正是 AI 发挥作用的地方。借助 AI 与机器学习，您可以在满足特定条件时让机器自动完成计算，并将结果呈现在引人注目的仪表板中。
一旦确定了要执行的计算，您的下一步就是清晰定义一条 AI 流水线，以简化并自动化流程。
想知道如何实现？请查看我们的机器学习、流水线、部署与 MLOps 教程。
蒙特卡罗模拟
蒙特卡罗模拟是一种数学技术，用于对因随机变量介入而难以准确预测的过程，建模其不同结果发生的概率。
首先，将问题中具有内在不确定性的每个变量用一个概率分布来建模。然后从这些概率分布中抽取大量随机样本，并使用这些样本来计算结果。
这个过程会重复多次，从而生成可能结果的分布，之后可进行统计分析，以预测系统的行为方式。与传统敏感性表相比，蒙特卡罗能更全面地分析不确定性。
您可以在我们的Excel 中的蒙特卡罗模拟指南中全面学习这一强大技术。
鉴于蒙特卡罗模拟的特性，您可以用它来增强敏感性分析，找出对输出影响最大的变量。
在我们的吉他示例中，我们可以保持成本价与售出单位数不变，仅改变单价来理解估计值的变化。随后对其余两个变量分别重复这一过程。最终，这一技术有助于了解应将精力聚焦于哪个变量以提升准确性。
最佳实践与常见陷阱
尽管 Excel 让敏感性分析极其简单且易用，但仍有一些常见陷阱需要注意。以下是一些要点。
设计稳健的模型
正如您在本教程中所见，要让敏感性分析发挥作用，Excel 必须了解输入与输出变量之间的关系。这要求您合理使用公式，并提供清晰定义的单元格引用和范围。
如果您想在数据表中新增取值，为避免在重新计算时可能出现的问题，一个良好实践是使用Excel 表格（Tables），因为它们天然具有动态性，新增行或列时会自动扩展。
这将帮助您进行正确的公式重算，并保持信息格式的正确性。
降低错误并确保准确性
即便您的敏感性分析定义清晰，仍需仔细核验结果。手动测试与图表是可视化结果并检查输出中离群值或异常的良好方式。
不过，重要的是记住，敏感性分析可能并不适用于您的用例。该分析基于一组简单假设，例如输入变量的独立性、线性关系的存在，以及输入变量的静态性质。
然而，您的模型可能更为复杂，且可能受敏感性分析本质上忽略的外部因素影响。因此，尽管敏感性分析有其益处，但要确保正确分析，仍需在敏感性之外对模型有全面的理解。
结论
恭喜您读到本教程的最后。敏感性分析是一种强大的假设分析，能在您的决策流程中发挥重要作用。还有其他可增强您财务模型的假设分析工具，如单变量求解（Goal Seek）或方案管理器（Scenario Manager）。幸运的是，DataCamp 可以助您一臂之力。欢迎查看我们精选的 Excel 课程与资料：