跳到主要内容

掌握 Excel 中的目标寻求 – 带示例的完整指南

添加一名作者 最后修改时间:2024-01-11

Goal Seek 是 Excel 假设分析工具包的一部分,是用于向后计算的强大功能。本质上,如果您知道公式所需的结果,但不确定什么输入值会产生此结果,则可以使用 Goal Seek 来查找它。无论您是财务分析师、学生还是企业主,了解如何使用 Goal Seek 都可以显着简化您的问题解决流程。在本指南中,我们将探讨 Goal Seek 是什么、如何访问它,并通过各种示例演示其实际应用,从调整贷款支付计划到计算考试最低分数等。


Excel 中的目标搜索是什么?

目标寻求 是 Microsoft Excel 的一个不可或缺的功能,是其假设分析工具套件的一部分。它使用户能够执行反向计算 - 您指定所需的结果,而不是计算给定输入的结果,Excel 会调整输入值以实现该目标。在您需要查找将产生特定结果的输入值的情况下,此功能特别有用。

备注:Excel 365、Excel 2010 及更高版本提供此功能。
在我们深入了解此功能的用法之前,让我们先简要看一下一些注意事项。
  • 单变量调节:
    Goal Seek 一次只能更改一个输入单元格值。
  • 需要一个公式:
    应用目标搜索功能时,目标单元格必须包含公式。
  • 保持公式的完整性:
    Goal Seek 不会改变目标单元格中​​的公式;它会更改公式所依赖的输入单元格中的值。

在 Excel 中访问目标搜索

要访问“目标寻求”功能,请转至 时间 标签,点击 假设分析 > 目标寻求。 看截图:

然后, 目标寻求 弹出对话框。以下是 Excel 的“目标寻求”对话框中三个选项的说明:

  • 设置单元格:包含您要实现的公式的目标单元格。该单元格必须包含公式,Goal Seek 将调整另一个相关单元格中的值,以使该单元格的值达到您设定的目标。
  • 重视价值:您想要的目标值“设置单元格” 来达到。
  • 通过更换电池:您希望 Goal Seek 调整的输入单元格。该单元格中的值将被更改以确保“设置单元格“满足”重视价值“ 目标。

在下一节中,我们将通过详细示例探讨如何利用 Excel 的 Goal Seek 功能。


使用目标寻求示例

在本节中,我们将通过四个常见示例来演示 Goal Seek 的实用性。这些示例涵盖从个人财务调整到战略业务决策,提供有关如何将该工具应用于各种现实场景的见解。每个示例旨在让您更清楚地了解如何有效地使用 Goal Seek 来解决不同类型的问题。


示例 1:调整贷款还款计划

EventXtra XNUMX大解决方案:某人计划申请一笔 20,000 美元的贷款,并计划在 5 年内以 5% 的年利率偿还。每月所需还款额为 377.42 美元。后来,他意识到他可以将每月还款额增加 500 美元。在贷款总额和年利率不变的情况下,现在需要多少年才能还清贷款?

如下表所示:

  • B1 包含贷款金额 $20000。
  • B2包含贷款期限5(年)。
  • B3包含年利率5%。
  • B5包含按公式=PMT(B3/12,B2*12,B1)计算的每月付款。

这里我将向您展示如何使用Goal Seek 功能来完成此任务。

第 1 步:激活目标寻求功能

时间 标签,点击 假设分析 > 目标寻求.

第 2 步:配置目标寻求设置
  1. 设置单元格 框中,选择公式 B5。
  2. 重视 框中输入每月计划还款金额-500(负数代表还款)。
  3. 通过改变细胞 框中,选择要调整的单元格 B2。
  4. 点击 OK.
结果

我们推荐使用 目标寻求状态 然后会出现对话框,显示已找到解决方案。同时,您在“通过更改单元格”框中指定的单元格 B2 中的值将被替换为新值,并且公式单元格将根据变量更改获取目标值。点击 OK 应用更改

现在,他大约需要3.7年才能还清贷款。


示例 2:确定通过考试的最低分数

EventXtra XNUMX大解决方案:一名学生需要参加 5 场考试,每场考试的权重相等。要通过考试,学生所有考试的平均成绩必须达到 70%。完成前 4 次考试并了解分数后,学生现在需要计算第五次考试所需的最低分数,以确保总体平均分达到或超过 70%。

如下截图所示:

  • B1包含第一次考试的分数。
  • B2包含第二次考试的分数。
  • B3包含第三次考试的分数。
  • B4包含第四次考试的分数。
  • B5将包含第五次考试的分数。
  • B7为及格平均分,计算公式=(B1+B2+B3+B4+B5)/5。

为了实现这一目标,您可以按如下方式应用目标搜索功能:

第 1 步:激活目标寻求功能

时间 标签,点击 假设分析 > 目标寻求.

第 2 步:配置目标寻求设置
  1. 设置单元格 框中,选择公式单元格 B7。
  2. 重视 框中,输入及格平均分 70%。
  3. 通过改变细胞 框中,选择要调整的单元格 (B5)。
  4. 点击 OK.
结果

我们推荐使用 目标寻求状态 然后会出现对话框,显示已找到解决方案。同时,您在“通过更改单元格”框中指定的单元格 B7 将自动输入匹配的值,并且公式单元格将根据变量更改获取目标值。点击 OK 接受解决方案。

因此,要达到所需的总平均分并通过所有考试,学生需要在最后一次考试中获得至少 71% 的分数。


示例 3:计算选举胜利所需的票数

EventXtra XNUMX大解决方案:在选举中,候选人需要计算获得多数票所需的最低票数。在已知总票数的情况下,候选人最少必须获得多少票才能获得 50% 以上的票数并获胜?

如下截图所示:

  • 单元格 B2 包含选举中投票的总数。
  • 单元格 B3 显示候选人当前收到的票数。
  • 单元格 B4 包含候选人希望获得的所需多数票 (%),其计算公式为 =B2/B1。

为了实现这一目标,您可以按如下方式应用目标搜索功能:

第 1 步:激活目标寻求功能

时间 标签,点击 假设分析 > 目标寻求.

第 2 步:配置目标寻求设置
  1. 设置单元格 框中,选择公式单元格 B4。
  2. 重视 框中,输入所需的多数票百分比。在这种情况下,候选人要想赢得选举,必须获得有效票总数的一半以上(即50%以上),所以我输入51%。
  3. 通过改变细胞 框中,选择要调整的单元格 (B2)。
  4. 点击 OK.
结果

我们推荐使用 目标寻求状态 然后会出现对话框,显示已找到解决方案。同时,您在“通过更改单元格”框中指定的单元格 B2 将自动替换为新值,并且公式单元格将根据变量更改获取目标值。点击 OK 应用更改。

因此,候选人至少需要 5100 票才能赢得选举。


示例 4:实现企业目标利润

EventXtra XNUMX大解决方案:某公司本年度的具体利润目标为 150,000 美元。考虑到固定成本和可变成本,他们需要确定必要的销售收入。需要多少销量才能达到这个利润目标?

如下截图所示:

  • B1包含固定成本。
  • B2 包含每单位的可变成本。
  • B3 包含每单位的价格。
  • B4 包含已售出的单位。
  • B6为总收入,计算公式=B3*B4。
  • B7为总成本,计算公式=B1+(B2*B4)。
  • B9为当期销售利润,计算公式=B6-B7。

要根据目标利润获取总销售单位,您可以应用“目标搜索”功能,如下所示。

第 1 步:激活目标寻求功能

时间 标签,点击 假设分析 > 目标寻求.

第 2 步:配置目标寻求设置
  1. 设置单元格 框中,选择返回利润的公式单元格。这是单元格 B9。
  2. 重视 框中,输入目标利润 150000。
  3. 通过改变细胞 框中,选择要调整的单元格 (B4)。
  4. 点击 OK.
结果

然后将出现“目标寻求状态”对话框,显示已找到解决方案。同时,您在“通过更改单元格”框中指定的单元格 B4 将自动替换为新值,并且公式单元格将根据变量更改获取目标值。点击 OK 接受更改。

因此,该公司需要至少销售 6666 台才能实现 150,000 万美元的目标利润。


Goal Seek 的常见问题和解决方案

本节列出了 Goal Seek 的一些常见问题和相应的解决方案。

1. 目标寻求找不到解决方案
  • 原因:当使用任何可能的输入值都无法实现目标值,或者初始猜测与任何潜在解决方案相差太远时,通常会发生这种情况。
  • 解决方案:调整目标值,确保其在可行范围内。另外,尝试更接近预期解决方案的不同初始猜测。确保“设置单元格”中的公式正确并且可以逻辑地生成所需的“目标值”。
2.计算速度慢
  • 原因:对于大型数据集、复杂的公式,或者当初始猜测与解决方案相差甚远时,目标搜索可能会很慢,需要更多迭代。
  • 解决方案:尽可能简化公式并减少数据量。确保初始猜测尽可能接近预期解,以减少所需的迭代次数。
3. 准确性问题
  • 原因:使用目标查找时,您可能会注意到有时 Excel 会说它找到了解决方案,但这并不完全是您想要的 - 接近但不够接近。由于 Excel 的计算精度和舍入,Goal Seek 可能并不总是提供高度准确的结果。
    例如,计算某个数字的特定幂并使用 Goal Seek 查找实现所需结果的底数。在这里,我将更改单元格 A1(基数)中的输入值,以便单元格 A3(求幂结果)与目标结果 25 匹配。
    正如您在下面的屏幕截图中看到的,Goal Seek 提供了一个近似值而不是精确的根。
  • 解决方案:增加 Excel 计算选项中显示的小数位数以获得更高的准确性,然后重新运行“目标搜索”功能。请按以下步骤操作。
    1. 点击 文件 > 附加选项 打开 Excel选项 窗口。
    2. 选择 公式 在左窗格中,然后在 计算选项 部分,增加小数位数 最大变化 盒子。这里我改变一下 0.0010.000000001 并点击 OK.
    3. 重新运行 Goal Seek,您将获得确切的根,如下面的屏幕截图所示。
4. 局限性和替代方法
  • 原因:Goal Seek 只能调整一个输入值,可能不适用于需要同时调整多个变量的方程。
  • 解决方案:对于需要调整多个变量的场景,可以使用Excel的Solver,功能更强大,并且可以设置约束。

Goal Seek 是执行反向计算的强大工具,使您免于手动测试不同值以近似目标的繁琐任务。有了本文的见解,您现在可以自信地应用 Goal Seek,将您的数据分析和效率推向新的高度。对于那些渴望深入研究 Excel 功能的人,我们的网站拥有丰富的教程。 在这里了解更多 Excel 提示和技巧.


最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations