Excel 中的“假设分析”

2025年03月17日 | 阅读 9 分钟

在 Excel 中，假设分析（What-if analysis）是指通过更改单元格的值来查看这些更改如何影响工作表结果的过程。您可以使用几组不同的值来探索一个或多个公式中的所有不同结果。

几乎所有数据分析师，特别是中高层管理专业人士，都使用 Excel 的假设分析来做出基于数据的更好、更快、更准确的决策。假设分析在许多情况下都很有用，例如：

您可以根据收入提出不同的预算。
您可以根据给定的历史值预测未来值。
如果您期望某个公式产生特定值，您可以找到产生所需结果的不同输入值集。

要启用假设分析工具，请转到“数据”菜单选项卡，然后在“预测”部分下单击“假设分析”。

现在单击“假设分析”。Excel 具有以下假设分析工具，可以根据数据分析需求使用：

方案管理器
单变量求解
数据表

数据表和方案会采用一组输入值并向前预测以确定可能的结果。单变量求解与数据表和方案不同，因为它会采用一个结果并向后预测以确定产生该结果的可能输入值。

1. 方案管理器

方案是 Excel 保存的一组值，可以自动替换工作表中的单元格。以下是其主要特点，例如：

您可以创建和保存工作表上不同值的组，然后切换到这些新方案中的任何一个来查看不同的结果。
一个方案可以有多个变量，但最多只能包含 32 个值。
您还可以创建方案汇总报告，该报告将所有方案合并到一个工作表中。例如，您可以创建几个不同的预算方案，比较各种可能的收入水平和支出，然后创建一个报告，让您能够并排比较这些方案。
方案管理器是一个对话框，允许您将值保存为方案并命名方案。

2. 单变量求解

如果您想知道某个公式的结果，但不确定公式需要什么输入值才能得到该结果，那么单变量求解就很有用。例如，如果您想借贷款，知道贷款金额、贷款期限和您可以支付的月供，您可以使用单变量求解来找到您可以获得的贷款利率。

单变量求解只能与一个变量输入值一起使用。如果您有多个输入值变量，则需要使用规划求解加载项。

3. 数据表

数据表是一个单元格区域，您可以在其中更改某些单元格中的值，并回答有关某个问题的不同答案。例如，您可能想通过分析不同的贷款金额和利率来了解您可以负担多少房屋贷款。您可以将这些不同的值和 PMT 函数放入数据表中，然后得到所需的结果。

数据表仅适用于一到两个变量，但可以接受这些变量的许多不同值。

假设分析方案管理器

方案管理器是 Excel 中的假设分析工具之一。当敏感性分析中有两个以上的变量时，方案管理器非常有用。方案管理器为所考虑变量的每组输入值创建方案。方案有助于您探索一组可能的结果，支持以下功能：

改变多达 32 组输入。
合并来自几个不同工作表或工作簿的方案。

如果您想分析超过 32 组输入，并且这些值只代表一个或两个变量，您可以使用数据表。

方案的初始值

在创建几个不同的方案之前，您需要定义一组将作为方案基础的初始值。考虑一个公司想购买金属以满足其需求。由于资金短缺，公司希望了解不同购买可能性会产生多少成本。

在这些情况下，我们可以使用方案管理器来应用不同的方案，以了解结果并据此做出决定。现在，以下是设置方案初始值的步骤：

步骤 1：定义包含输入值的单元格。

步骤 2：将单元格命名为Metals_name和Cost。

步骤 3：定义包含结果的单元格。

步骤 4：将结果单元格命名为Total_cost。

步骤 5：在结果单元格中放置公式。

步骤 6：下面是创建的表格。

要使用方案管理器创建分析报告，请按照以下步骤操作：

步骤 1：单击“数据”选项卡。

步骤 2：转到“假设分析”按钮，然后从下拉列表中选择“方案管理器”。

步骤 3：现在会出现方案管理器对话框，单击“添加”按钮创建方案。

步骤 4：创建方案，命名方案，为该方案的每个更改输入单元格输入值，然后单击“确定”按钮。

步骤 5：现在，“B3、B4、B5、B6 和 B7”出现在单元格框中。

步骤 6：现在，将 B3 的值更改为 500，然后单击“添加”按钮。

步骤 7：单击“添加”按钮后，将再次出现“添加方案”对话框。

在方案名称框中，创建方案 2。
选择“防止更改”。
然后单击“确定”。

步骤 8：“方案值”框会再次出现，显示 B3 单元格的更改值。

步骤 9：将 B5 的值更改为 20000，然后单击“确定”按钮。

步骤 10：同样，创建方案 3，然后单击“确定”按钮。

步骤 11：“方案值”框会再次出现，显示 B5 单元格的更改值。

步骤 12：将 B7 的值更改为 10000，然后单击“确定”按钮。

将出现方案管理器对话框。在“方案”下的框中，您将找到您创建的所有方案的名称。

步骤 13：现在，单击“汇总”按钮。将出现“方案汇总”对话框。

Excel 提供两种类型的方案汇总报告：

方案汇总。
方案数据透视表报告。

步骤 14：在“报告类型”下选择“方案汇总”并单击“确定”。方案汇总报告将出现在一个新工作表中。您将获得以下方案汇总报告。

您可以在方案汇总报告中观察到以下内容：

更改的单元格：列出所有用作更改的单元格。
结果单元格：显示指定的 the result cell。
当前值：这是第一列，列出了在创建汇总报告之前，在方案管理器对话框中选定的方案的值。
对于您创建的所有方案，更改的单元格将用灰色突出显示。
在 C9 行中，将显示每个方案的结果值。

假设分析单变量求解

单变量求解是一个假设分析工具，可帮助您找到产生所需目标值的输入值。单变量求解需要一个使用输入值来产生目标值的公式。然后，通过更改公式的输入值，单变量求解会尝试解决输入值。

单变量求解只能与一个变量输入值一起使用。如果您有多个输入值需要确定，则必须使用规划求解加载项。以下是使用 Excel 中单变量求解功能的步骤：

步骤 1：在“数据”选项卡上，转到“假设分析”，然后单击“单变量求解”选项。

步骤 2：将出现单变量求解对话框。

步骤 3：在“设为单元格”框中键入 C9。此框是包含您要解析的公式的单元格的引用。

步骤 4：在“目标值”框中键入 57000。在这里，您将获得公式结果。

步骤 5：在“通过更改单元格”框中键入 B9。此框包含您要调整的值的单元格的引用。

步骤 6：公式必须引用的单元格将通过对您在“设为单元格”框中指定的单元格进行更改来目标。单击确定。

步骤 7：单变量求解框将产生以下结果。

正如您所观察到的，单变量求解通过 B9 找到了解决方案，并且由于目标值和当前值相同，因此它在 B9 单元格中返回 0。

假设分析数据表

使用 Excel 中的数据表，您可以轻松地改变一到两个输入并执行假设分析。数据表是一个单元格区域，您可以在其中更改某些单元格中的值，并回答有关某个问题的不同答案。数据表有两种类型：

单变量数据表
双变量数据表

如果您的分析问题中有两个以上的变量，则需要使用 Excel 方案管理器工具。

单变量数据表

单变量数据表可用于查看一个或多个公式中一个变量的不同值如何改变这些公式的结果。换句话说，使用单变量数据表，您可以确定一个输入的更改如何改变任意数量的输出。下面是创建单变量数据表的示例。

数据表的一个好例子是使用 PMT 函数，并结合不同的贷款金额和利率来计算贷款。

有一笔 100,000 的贷款，期限为 5 年。您想知道不同利率下的每月还款额（EMI）。您还想知道第二年支付的利息和本金金额。

步骤 1：创建所需的表格。

假设利率为 10%。
列出所有必需的值。
为包含值的单元格命名。
分别使用 Excel 函数 PMT、CUMIPMT 和 CUMPRINC 设置 EMI、累计利息和累计本金的计算。
下面是创建的表格。

步骤 2：键入您想在输入单元格中替换的利率值列表。

正如您所观察到的，利率值上方有一个空行。这一行是用于公式的。

步骤 3：在值列上方一个单元格、右侧一个单元格中键入第一个函数（PMT）。在第一个函数右侧的单元格中键入其他函数（CUMIPMT 和 CUMPRINC）。

步骤 4：数据表的外观如下。

步骤 5：选择包含公式和您想替换的值的单元格范围，E2:H13。

步骤 6：转到“数据”选项卡，选择“假设分析”，然后在下拉列表中单击“数据表”工具。

步骤 7：将出现数据表对话框。

在“列输入单元格”框中单击。
然后单击“Interest_Rate”单元格，即 C2。

您可以看到“列输入单元格”被设为 $C$2。

步骤 8：单击“确定”按钮。

数据表将填充每个输入值的计算结果。

双变量数据表

双变量数据表可用于查看公式中两个变量的不同值如何改变该公式的结果。换句话说，使用双变量数据表，您可以确定两个输入的更改如何改变单个输出。

例如，一笔 100000 的贷款，您想知道不同利率组合将如何影响每月还款额。

步骤 1：创建以下表格。

步骤 2：现在创建数据表。

在 F2 单元格中写入 =EMI。
键入第一组输入值，即利率，沿列 F 向下输入，从公式下方的单元格开始，即 F3。
键入第二组输入值，即付款次数，沿第 2 行输入，从公式右侧的单元格开始，即 G2。
数据表的外观如下。

步骤 3：选择包含公式和您想替换的两组值的单元格范围，即 F2:L13。

步骤 4：转到“数据”选项卡，单击“假设分析”，然后从下拉列表中选择“数据表”。

步骤 5：将出现数据表对话框。

步骤 6：在“行输入单元格”框中单击。

单击“NPER”单元格，即 C3。
再次，在“列输入单元格”框中单击。
单击“Interest_Rate”单元格，即 C2。

您会看到“行输入单元格”被设为 $C$3，而“列输入单元格”被设为 $C$2。

步骤 7：单击“确定”按钮。

数据表将填充每个输入值组合的计算结果。

数据表计算

数据表在包含它们的工作表每次重新计算时都会重新计算，即使它们没有更改。

要加速包含数据表的工作表的计算，您需要将计算选项更改为“自动重新计算”工作表，但不要重新计算数据表。

下一个主题如何在 MS Excel 中插入复选框