Excel 假设分析：目标值求解、方案管理器与数据表

您需要提供三项：

• 设置单元格（Set cell）：包含公式的单元格（输出）
• 目标值（To value）：您希望达到的结果
• 可变单元格（By changing cell）：允许 Excel 调整的输入单元格

来看一个实际示例。 

您经营一家出售笔记本的小企业。目前营收为 $25,000，目标是 $50,000。单价固定为 $25，因此您需要计算要卖出多少件。

操作步骤如下：

1. 转到 数据 > 假设分析 > 目标值求解

2. 在设置单元格中，点击单元格 B4（您的营收公式单元格）

3. 在目标值中，输入 50000

4. 在可变单元格中，选择 B2（销量）

5. 点击 确定

目标值求解会将 B2 从 1,000 调整为 2,000 件——这正是以每件 $25 达到 $50,000 营收所需的销量。

使用目标值求解达到特定结果。图片作者自制。

如何在 Excel 中使用方案管理器

方案管理器是一种假设分析工具，可让您在同一工作表中保存并切换多组输入值。每个保存的版本称为一个“方案”。

您可以按需创建任意数量，并瞬时切换进行对比。

当您希望在不维护三个独立文件的情况下规划最佳、最差与预期三种结果时，它很有用。

您需要提供三项：

• 方案名称：为每组假设设置的标签
• 可变单元格：不同方案间会变化的输入单元格
• 数值：各方案的具体数值

创建方案

假设您在为笔记本业务规划季度销售。

当前利润为 $15,000。您想建立三种方案：最佳、最差和预期。

创建方案的方法如下：

1. 转到 数据 > 假设-分析 > 方案管理器

2. 在方案管理器对话框中，点击添加

3. 在方案名称中，输入 Best Case。

4. 在可变单元格中，选择 B2:B4。

5. 将出现方案值对话框。在相应字段中输入以下数值：

• B2 = 2000，

• B3 = $30

• B4 = $10

6. 点击确定。

为其余两个方案重复上述步骤，并使用下表数值：

创建包含三种情形的方案。图片作者自制。

要在方案间切换，从列表中选择一个并点击显示（Show）。Excel 会立即替换数值，您的利润随之更新。

点击 Show 查看各方案的数值。图片作者自制。

编辑方案

在列表中选择任一方案并点击编辑。您可以重命名、变更所覆盖的单元格或更新数值。只有在点击显示（Show）后，工作表中的内容才会改变。

生成方案报告

方案管理器还可以创建对比所有已保存方案的报告。

生成方案报告的方法：

1. 在方案管理器中，点击摘要

2. 在方案摘要中，选择方案摘要

3. 在结果单元格中，选择 B7（您的利润单元格）

4. 点击 确定

Excel 会在新工作表中生成一个汇总表，将三种方案并排展示

生成方案摘要报告。图片作者自制。

如何在 Excel 中使用数据表

数据表可以在一系列输入值上自动重复计算。与其手动更改输入、记录输出并重复，您只需定义一次范围，Excel 就会一次性填充所有结果。

您需要提供三项：

• 输入范围：您要测试的取值
• 行/列输入单元格：公式中将被这些取值替换的单元格
• 公式单元格：数据表围绕的输出单元格

单变量数据表

单变量数据表用于测试更改一个输入对公式结果的影响。

例如，您有一个贷款还款公式，想查看在 5% 至 10% 的不同利率下月供如何变化。

创建单变量数据表：

1. 设置数据集。需要注意的一点：在我的数据集中，在单元格 E1（正好位于第一条利率之上，而不是在列表标题旁边）中输入 =B5。如果 E1 留空，或公式引用放在其他单元格，数据表会运行，但返回的将是全零而不是付款数值。

创建单变量数据表。图片作者自制。

2. 接着，选择完整范围 D1:E9（包含 E1）

3. 转到 数据 > 假设分析 > 数据表

4. 将行输入单元格留空

5. 在列输入单元格中，选择 B4（利率）

6. 点击 确定。

Excel 会为每个输入值填充计算结果。注意月供并非均匀增加：从 3% 到 4% 增加 $49，但从 9% 到 10% 增加 $61。利率越高，变动的影响越大。

每个输入值的计算结果。图片作者自制。

双变量数据表

双变量数据表用于测试同时更改两个输入对公式结果的影响。

例如，某个定价模型可能同时取决于产品价格和销量。双变量数据表可以在一个视图中展示不同价位与销量组合下的利润。

首先，设置数据集。

看看我使用的数据集：

• 在单元格 D1 中输入 =B5。这是公式引用，必须放在网格左上角，而不是标签旁边

• 在第 1 行，从 E1 开始横向列出要测试的销量：500、1000、1500、2000

• 在 D 列，从 D2 开始纵向列出要测试的价位：$20、$25、$30、$35

为双变量数据表设置数据集。图片作者自制。

创建双变量数据表：

1. 选择完整范围 D1:H5。

2. 转到数据 > 假设分析 > 数据表

3. 在行输入单元格中，选择 B3（销量）

4. 在列输入单元格中，选择 B2（单价）

5. 点击 确定。

Excel 会填充每个价格与销量组合下的利润。

各输入组合的计算结果。图片作者自制。

当单一假设无法说明全部问题时，双变量数据表尤其有用。价格单看可能不错，但利润还取决于实际销量。第二个变量的加入让模型更具参考价值。

目标值求解 vs. 方案管理器 vs. 数据表

目标值求解、方案管理器和数据表都属于 Excel 的假设分析工具包，但各自解决的问题不同。

以下是它们之间的简要对比

假设分析 vs. 规划求解器（Solver）

假设分析和规划求解器都能帮助我们在 Excel 中探索不同结果，但它们解决的问题类型不同。

• 假设分析用于测试假设。您更改一个或多个输入，查看这些变化如何影响结果。目标是理解可能的结果，而非寻找最优解。
• 规划求解器（Solver）的工作方式不同。您给定一个目标，如最大化利润或最小化成本，它会搜索最满足该目标的输入组合。它不是测试某个假设，而是求解最优方案。

例如，贷款申请人可以用目标值求解来确定导致每月还款 $500 的贷款金额；而制造企业可能使用规划求解器，在劳动力与材料受限的前提下，确定带来最高利润的生产组合。

注意：规划求解器并非标准假设分析工具的一部分。它是一个独立加载项。在大多数 Excel 版本中，您需要在“Excel 选项”中手动启用，随后它才会出现在“数据”选项卡。

假设分析最佳实践

以下是在进行假设分析时应遵循的一些最佳实践：

1. 将假设与公式分离

将输入值存放在独立单元格中，并在公式中引用这些单元格。避免将数字硬编码到计算中。

例如，下面这个公式不易更新：

=(50*1000)-10000

而这个版本更易维护：

=(B2*B3)-B4

当假设发生变化时，您只需更新输入单元格，而无需在整个工作簿中编辑公式。

2. 清晰标注方案

方案名称应能让读者一目了然地理解其含义。“方案 1”“方案 2”“方案 3”在六个月后毫无提示意义。用实际含义命名，例如“最佳：15% 增长”。

3. 校验输入

假设分析的可靠性取决于被测试的输入。在运行分析前，请检查：

• 百分比是否录入正确
• 日期格式是否一致
• 计量单位是否一致
• 输入值是否在现实范围内

基于 500% 销售增长的收入预测，技术上或许没错，但对决策毫无帮助。

4. 记录假设

今天对您有意义的模型，三个月后未必对您或其他人依然清晰。

在每个假设旁加一句简短说明，即便只是“基于第三季度实际数据”，也能在日后省去大量猜测。记录有助于他人理解数字来源，并让后续更新更轻松。

假设分析的常见问题

大多数假设分析问题源于工作表设置而非工具本身。如果目标值求解、方案管理器或数据表给出意外结果，请检查几个常见区域；通常能解决问题。

1. 公式未更新

您更改了输入，但没有任何变化。通常意味着 Excel 的计算模式被切换到了手动。

解决方法：转到“公式” > “计算选项” 并确保设置为“自动”。若已是自动，按 F9 强制重新计算。

2. 单元格引用不正确

假设分析工具依赖准确的单元格引用。如果选择了错误的单元格，即使 Excel 成功完成计算，结果也会不正确。

例如，目标值求解可能更改了错误的输入单元格；数据表可能引用了错误的行或列输入单元格；方案管理器可能为错误的单元格存储了数值。

解决方法：在运行分析前检查输入单元格，在各工具的对话框中仔细核对所选内容，并使用具描述性的标签，以便清楚每个单元格代表什么。

3. 误解目标值求解的局限

许多用户期望目标值求解能处理涉及多个可变变量的问题。它不行。目标值求解一次只会更改一个输入单元格来达到目标值。

例如，目标值求解可以确定实现 $15,000 利润所需的销量；但无法同时调整销量、价格和成本来达到该目标。

解决方法：当仅需更改一个变量时使用目标值求解；当涉及多个变量时使用方案管理器或规划求解器。

4. 过于复杂的方案模型

方案管理器技术上最多允许每个方案设置 32 个可变单元格，但能做到并不意味着应该这样做。

当变量超过四五个时，方案会难以比较，更难向他人解释。如果您的模型需要这么多活动部件，与其将它们都塞进一个方案，不如考虑重构模型。

结语

每个电子表格里或多或少都有假设。可能是增长率、价格或利率。

假设分析并不会消除这些假设。它会展示：如果假设错了会发生什么——而这恰恰是良好决策的核心所在。

如果您是新手，不要一上来就做包含五个变量、三层嵌套方案的模型。先从小处着手：一个输入、一个输出、一次目标值求解，先理解输入与结果如何联动。等基础打牢，再处理复杂情况。