如何在Microsoft Excel中使用目标搜索

本 Excel 教程将指导您逐步使用 Excel 目标求解。

在数据分析中，您经常需要调整公式中变量的值以达到特定结果。这就是目标搜索发挥作用的地方。

它使您能够通过公式向后推移，设置目标结果，并让 Excel 确定达到该目标所需的输入。

无论您是经验丰富的分析师还是电子表格新手，学习如何使用目标搜索都可以显著增强您的决策过程。

Excel 中的目标搜索是什么？

Excel 的目标求解是一种动态工具，可让您轻松解决复杂方程式。它对于反向计算特别有用，可让您设置数学方程式的结果。

然后 Excel 确定实现结果所需的输入。由于结果始终包含 Excel 公式，因此目标求解会自动考虑数学练习中涉及的所有变量。

目标求解通过为所选变量输入不同的输入值来自动执行方程的迭代计算。由于 Excel 在目标求解工具背后使用了一种算法，因此该过程通常比求解方程的试错法更快。

无论您要调整贷款支付、确定最佳产品定价还是微调财务模型，Goal Seek 都会通过反复优化输入直至达到所需结果来简化流程。Goal Seek 可自动执行假设情景，从而通过分析原始数据来增强您的决策能力。

探索目标搜索 Excel 工具

如何在Microsoft Excel中使用目标搜索 — Excel 输入中的目标搜索

您可以在“数据”选项卡 > “预测” > “假设分析”菜单中找到“目标求解”工具。启动“目标求解”Excel 插件的快捷方式或键盘输入是Alt> A> W> G。

设置单元格

这是指包含公式或计算的单元格，您要将其结果设置为特定值。这是您要调整以实现所需结果的输出单元格。

例如，如果您正在计算贷款的每月还款额，并且想要找到实现特定还款额所需的利率，则设置单元格将是包含还款计算的单元格。

设置单元格字段应始终引用工作表中包含公式的单元格。但是，设置单元格字段中不应存在任何循环引用，否则目标搜索工具将产生错误。

价值

这是使用目标搜索后您希望设置单元格达到的目标值。您可以在输出单元格中提供您想要实现的期望值。

再次使用贷款示例，这可能是您想要达到的具体付款金额。

通过更换单元格

这是指输入单元格，也称为变量单元格，您可以调整它以达到设置单元格中的目标值。

在贷款场景中，这将是包含利率的单元格，您可以修改它以找到与所需付款相匹配的利率。

使用 Excel 目标搜索的原因

Excel 目标求解可以有效地确定特定公式结果所需的输入值，从而简化复杂的向后计算。
它简化了场景分析，通过自动迭代帮助做出最佳决策，比手动方法节省时间。
通过修改一个变量，目标寻求可以实现精确的目标并增强财务建模、数据分析和解决问题的过程。
该工具能够迭代地求解方程以获得期望的结果，从而为寻找手动方法可能难以找到的解决方案提供了一种实用的方法。
无论是优化贷款支付、微调定价策略还是其他操作，Goal Seek 都能让用户根据精确计算做出明智的选择。

现在您已经对 Excel 目标寻求工具有了基本的了解，下面是可以应用此 Excel 数据分析技能的几个实际场景。

使用 Excel 目标搜索计算抵押贷款

查找抵押贷款的还款年限、利率或每月分期付款是 Excel 中这个强大的迭代计算工具最常见的用途之一。

假设您正在申请贷款为您的企业购买新设备。以下是抵押贷款的具体内容：

年利率：4%
还款年限：20
贷款金额：X
每月分期付款：-$1,500

Excel 目标求解可以这样帮助您确定您能负担的贷款金额（每月还款额为1,500 美元）。

将行标签“年利息”、“还款年限”、“贷款金额”和“每月还款额”添加到行A1至A4。
在B 列下，填充值，例如B1=4%，，B2=20和B3应为空。目标搜索将为B3您计算。

在单元格中B4，输入以下PMT或贷款偿还公式：

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

点击后 Excel中Enter应该显示$0.00B4。

现在，您必须调用目标搜索工具来计算贷款金额。操作方法如下：

单击“数据”选项卡并转到“预测”命令块。
单击假设分析下拉菜单。
在显示的上下文菜单中，单击“目标搜索”。

目标搜索 Excel 插件用户界面将会弹出。
在那里，单击“设置单元格”框并选择B4工作表上的单元格。
在“到期金额”字段中，输入您每月想要支付的金额，即-1500。
单击“通过更改单元格”字段内部并突出显示B3电子表格上的单元格。
单击“确定”即可计算您应该考虑的借款金额。

上图为目标搜索状态对话框，显示了根据上述抵押约束条件计算出的您可以选择的贷款金额。

如果您对结果满意，请单击Excel 对话框中目标搜索上的“确定”按钮。

您可以通过更改变量来自定义目标搜索。例如，如果您想要贷款250,000 美元，还款期限为20 年，您可以计算每年必须支付的利息。

或者，您可以固定贷款金额和年利息，然后使用目标搜索来计算还款年限。

使用目标搜索找出所需分数

假设您必须获得75 分的平均分才能通过某项选拔考试。您已经参加了三场考试，以下是您获得的分数：

生物学 70
物理 67
数学 68

您还没有参加最后一场英语考试。下面介绍如何使用 Excel 中的目标搜索来计算通过选拔过程必须获得的分数：

如上所示，在 Excel 工作表中输入可用数据。

为平均值创建一个新行，并在单元格中输入以下公式B7：

=AVERAGE(B2:B5)

点击Enter获取初步平均值。

现在，此数据集已准备好在 Goal Seek Excel 插件中进一步分析。操作方法如下：

调用目标搜索工具。
设置单元格字段应为B7。
在“目标值”字段中输入目标，即75。
按改变单元格应为B5。
单击“确定”即可了解最后一份试卷必须达到的分数。

使用 Excel 中的目标搜索来查找盈亏平衡点

假设您是一家初创企业，需要计算盈亏平衡点，以便提高所生产产品的销量。

在这种情况下，您可以使用 Excel 目标搜索工具快速计算出您必须带来的收入，至少在没有盈利的情况下支付业务费用。

您收集了上个月的销售和支出数据，并在 Excel 工作表中创建了上述数据集。它显示每月的销售额不等于业务支出。您每月损失4,750 美元。

现在，您必须增加每月的产品销售额来填补这一缺口。下面介绍如何在此场景中使用 Goal Seek Excel 插件来解决目标，即每月收入26,000 美元。

按Alt> A> W>G键调出目标求解工具。
选择B5作为设置单元格值。
由于您只想平衡收入和支出，因此“目标值”字段应为0。
通过改变单元格应该是B2，因为您只能增加产品数量。

单击“确定”，Excel 应计算新的收支平衡销售数量，即305.88。

假设您不只是想要收支平衡，还想赚取利润。在这种情况下，将“目标值”字段更改为您想要赚取的利润金额。目标搜索将自动计算获利所需的销售量。

上图显示，目标搜索预测，如果您可以将销售量提高到每月317.64单位，那么您可以从您的业务中赚取1,000 美元的利润。

使用 VBA 在多个单元格中使用 Excel 目标搜索

到目前为止，您已经看到每次只能将目标求解应用于一个问题。Excel 没有任何用户界面按钮允许进行多个目标求解计算。

但是，您可以使用Excel VBA脚本在一个工作表中同时运行任意数量的单变量求解分析。此方法还使您能够自动执行涉及 Excel 单变量求解分析的计算。

查找下面的 VBA 脚本和在工作表中实现代码的步骤：

按Alt+F11调用Excel VBA 编辑器工具。
在VBA 工具栏上，单击插入按钮。
在弹出的上下文菜单上选择“模块” 。
将以下脚本复制并粘贴到空白模块中：

Sub goalseekvba()
For row_no = 2 To 6
Worksheets("Sheet4").Cells(row_no, "D").GoalSeek Goal:=0.35, _
ChangingCell:=Worksheets("Sheet4").Cells(row_no, "C")
Next row_no
End Sub

单击保存按钮。
单击关闭按钮关闭该工具。

现在，您必须按照以下步骤运行 VBA 宏：

按Alt+F8启动宏对话框。
选择goalseekvba宏。
点击“运行”按钮来执行代码。

Excel 将自动计算列表中所有产品达到 35% 利润百分比所需的销售量。

如何解决 Excel 目标搜索插件问题

如果您正确设置了目标搜索输入字段，则不会遇到任何错误。但是，如果您向目标搜索提供了复杂的模型，则可能会产生以下两个错误：

目标求解目前已执行最大次数迭代，但仍无可用解决方案。
您给出的目标值为 100，但目标求解生成的解决方案的目标值为 101。

要解决上述问题，您必须更改 Excel 目标求解的某些设置。操作方法如下。

单击“文件”选项卡，然后从左侧导航窗格中选择“选项” 。
单击左侧面板上的公式选项。
在计算选项下，勾选启用迭代计算复选框。
将最大迭代次数值更改为1,000或更多。
另外，将最大变化值减小到1以提高准确度。
单击“确定”应用更改。

更多的迭代将有助于获得更准确的目标解决方案。

结论

这就是当您知道结果或目标时使用 Excel 目标求解来获取数学方程中未知变量的值的方法。

使用您自己的数据集尝试一下，并在下面评论您的体验。如果您知道 Goal Seek Excel 插件的任何秘密技巧和窍门，请在您的评论中提及它们。