如何在 Excel 中使用单变量求解：含真实示例的指南

单变量求解的工作原理

当您知道某个公式想要的结果，但尚不确定达到该结果所需的输入值时，就会使用单变量求解。 您通过设定期望的输出，反向推导，由 Excel 自动调整输入变量来找到解。

单变量求解的常见应用

尽管单变量求解在不同领域中均有应用，最常见的场景是财务建模和学术场景。包括：

• 预算预测：单变量求解可帮助确定为达到某个财务目标所需的储蓄或投资金额。例如，若您希望在年底存下某个数额，它可以计算达到该目标所需的每月储蓄额。
• 贷款还款：它可以基于固定的每月还款额，计算您可承担的贷款金额。如果您每月能支付的金额固定，单变量求解可调整贷款本金，求得可借款的最大额度。
• 保本分析：企业可用单变量求解找出覆盖成本并达到收支平衡所需的销量。通过将利润设为 0，单变量求解可调整销售数量或价格以确定保本点。
• 成绩计算：学生和教师可用单变量求解确定为达到期望课程总评所需的期末考试分数。例如，若学生想知道为确保总评 85% 需要在期末考取得多少分，单变量求解可基于当前成绩与期末权重进行计算。
• GPA 目标：单变量求解还能确定为达到或维持特定 GPA，在未来课程中需要取得的成绩。例如，若学生希望将 GPA 提升到某一水平，单变量求解可给出后续课程所需的分数。

现在，让我们在 Microsoft Excel 中探索一些真实示例。

如何在 Excel 中使用单变量求解

在您的数据模型中应用单变量求解，主要包含三个可重复的步骤。

步骤 1：设置数据

首先以便于 Excel 解析的方式设置数据与公式。确保您要求解的公式已正确链接到将被调整的输入单元格。

单变量求解计算的示例数据。图片由作者提供。

根据上图中的公式，由于结果 C取决于输入 A和输入 B，我们可以更改输入 A或输入 B中的任意一个。

步骤 2：运行单变量求解

先选择其结果需要调整的公式单元格（如C2）。转到数据选项卡，单击模拟分析，选择单变量求解。

在 Excel 中选择单变量求解。图片由作者提供。

在单变量求解对话框中，指定包含公式的单元格（设置单元格）、期望的结果（目标值），以及要更改的输入单元格（可变单元格）。

单变量求解对话框。图片由作者提供。

单击确定，让 Excel 查找合适的输入值。

单变量求解最后一步。图片由作者提供。

或者，若您想保留原值，可单击取消以终止单变量求解操作。当我们对更改不满意并希望接着运行另一项单变量求解时，这很常见。

步骤 3：分析结果 

运行单变量求解后，Excel 会自动调整所选输入单元格以达到目标输出。现在可以分析该输入单元格中的更新值，以理解模型的结果。

单变量求解结果。图片由作者提供。

如上图所示，将输入 B从 100 更改为 200，我们实现了目标结果 C为 1000。这三个步骤构成了在不同数据模型中使用单变量求解的标准流程。 

接下来看看这些步骤如何用于解决现实问题。以下示例展示了单变量求解如何有效解决常见的财务问题，例如确定贷款还款或实现销售目标。

Excel 单变量求解示例

让我们看两个您在工作中可能会遇到的 Excel 单变量求解示例。 

示例 1：贷款还款计算

假设您计划买房，想确定在每月还款 1,000 美元的前提下，您最多能承担的贷款金额。基于您对多家银行的调研，您已有利率和贷款期限的大致范围，但需要确定要申请的贷款本金。您可以用单变量求解来解决。

步骤 1：设置数据 

打开一个新的 Excel 工作表，并按下图所示进行设置。 

贷款还款数据集。图片由作者提供。

在A2单元格输入您调研得到的利率（如 5%），在B2输入贷款期限（如 30 年），在C2输入贷款金额的初始估算（如 200,000 美元）。在D2单元格中输入使用 PMT 函数计算每月还款的公式：

=PMT(B1/12, B2*12, -B3)

该公式将年利率除以 12（得到月利率），将贷款年限乘以 12（得到总期数），并使用负的贷款金额来计算每月还款额。

步骤 2：运行单变量求解 

我们的目标是找出能使每月还款为 1000 美元的贷款金额。换言之，我们将修改C2，使D2等于 1000 美元。如前所述，在数据选项卡的模拟分析中调用单变量求解，打开如下对话框。

贷款还款示例的单变量求解选项。图片由作者提供。

在单变量求解对话框中，我们输入如下内容：

• 设置单元格：D2（每月还款单元格）。

• 目标值：输入1000（期望的每月还款）。

• 可变单元格：输入C2（贷款金额）。

单击确定后，Excel 会调整 C2 中的贷款金额，以确定在每月还款 1,000 美元的前提下，您最多能借到多少。

步骤 3：解读结果 

单变量求解运行并停在下图所示界面：

贷款还款示例的单变量求解结果。图片由作者提供。

单击确定后，Excel 会在C2中显示调整后的贷款金额。这是在给定利率、贷款期限和每月还款的条件下，您可承担的最高贷款本金。

调整后的贷款金额为 186,282 美元。以每月 1,000 美元的还款额，我们能承担的最高贷款金额为此。您可以随意尝试不同数值，多次运行单变量求解。

示例 2：实现营收目标

假设您是一名销售经理，目标是在某款产品上实现 500,000 美元的营收。 您已知产品的单价，但尚不确定需要销售多少件才能达到该营收目标。单变量求解可以帮助您确定所需的确切销量。

步骤 1：设置数据  

打开一个新工作表，并按下图所示输入数据。 

销售目标数据集。图片由作者提供。

在A2单元格输入实际单价，在B2中输入销量的估计值。在C2单元格输入以下用于计算总营收的公式：

=A2 * B2

该公式将单价乘以销量来计算总营收。

步骤 2：运行单变量求解  

在数据选项卡的模拟分析中调用单变量求解，打开如下对话框。

销售目标示例的单变量求解选项。图片由作者提供。

在单变量求解对话框中，我们输入如下内容：

• 设置单元格：输入C2（总营收单元格）。

• 目标值：输入500000（期望的营收目标）。

• 可变单元格：输入B2（销售数量）。

Excel 会调整B2中的销量，确定为达到 500,000 美元营收需要销售的数量。

步骤 3：解读结果  

单变量求解运行并停在下图所示步骤：

销售目标示例的单变量求解结果。图片由作者提供。

单击确定后，Excel 会在B2中显示调整后的销量。结果为 2,000 件——您需要以每件 250 美元的价格销售 2,000 件，才能达到 500,000 美元的营收目标。若单价或营收目标日后发生变化，您可以用更新后的数值重新运行单变量求解。

使用单变量求解时的常见错误及处理方法

回顾上面的两个示例，我们以固定步骤获得了结果。然而，在 Excel 中使用单变量求解时，一些常见错误可能会影响计算。我们来看看这些问题。

1. Excel 单变量求解可能未找到解

单变量求解最常见的问题之一是无法找到满足目标的解。这可能出现在两种情况下：

• 解不存在。
• 在设定的步数内未能收敛到解。

以我们先前的销售目标示例来理解。假设我们误把单价填成了 0 美元而非 250 美元。数据如下所示：

包含错误的销售目标数据集。图片由作者提供。

我们按常规步骤运行单变量求解：

对包含错误的销售目标数据集运行单变量求解。图片由作者提供。

单击确定后，会发生什么？

单变量求解可能未找到解的错误。图片由作者提供。

我们看到结果为“单变量求解可能未找到解”。出现该错误是因为方程不可解。在运行单变量求解之前，必须检查公式和输入值，确保问题可解。

如果您确认解是存在的，则可能是单变量求解未能在默认的 100 次迭代内收敛。为帮助其收敛到解，可采取以下措施：

1. 增加最大迭代次数：更高的迭代次数允许单变量求解运行更久。您可以通过文件->选项->公式->最大迭代次数进行更改。
2. 从更好的估计开始：如果您能大致猜到正确数值的范围，请在该范围内输入一个初始值。

确认方程可解，并允许其在设定步数内收敛，将避免出现未找到解的错误。

2. “单元格必须包含一个值”的错误

单变量求解要求被更改的单元格为数值，否则会报“单元格必须包含一个值”的错误。当单元格的值由其他单元格通过公式计算得出时，这种情况很常见。

我们仍以销售目标示例来说明该错误。假设单价是用总成本除以总销量计算得到的。数据设置如下：

更新后的销售目标数据集。图片由作者提供。

当我们尝试运行单变量求解，并在可变单元格选项中选择A2时，会出现错误。

“单元格必须包含一个值”的错误。图片由作者提供。

出现该错误是因为当待修改的单元格不包含数值时，无法运行单变量求解。确保待修改的单元格包含一个值即可避免此错误。

结论

本文向您介绍了常用的情景分析工具——单变量求解。通过多个示例情景，我们学习了如何在 Excel 中用简明步骤完成操作；同时也了解了实施过程中常见的错误及其避免方法。

建议您在自己的数据模型中多加尝试单变量求解，以熟悉该工具及其适用性。若您希望进一步学习在 Excel 中使用与单变量求解类似的工具进行财务建模，不妨查看我们的入门课程 Financial Modelling in Excel。