掌握 Excel 中的目标搜索 – 完整指南及示例

目标搜索是 Excel 假设分析工具包的一部分，是一个用于反向计算的强大功能。简单来说，如果你知道公式的目标结果，但不确定哪个输入值可以产生这个结果，你可以使用目标搜索来找到它。无论你是财务分析师、学生还是企业主，了解如何使用目标搜索都可以显著简化你的问题解决过程。在本指南中，我们将探讨什么是目标搜索、如何访问它，并通过各种示例展示其实际应用，从调整贷款还款计划到计算考试的最低分数等更多内容。

• 示例 1：调整贷款还款计划
• 示例 2：确定考试及格的最低分数
• 示例 3：计算选举胜利所需的票数
• 示例 4：实现企业的目标利润

Excel 中的目标搜索是什么？

目标搜索（Goal Seek）是 Microsoft Excel 的一个核心功能，属于其假设分析工具套件的一部分。它允许用户执行反向计算——不是根据给定的输入计算结果，而是指定所需的结果，Excel 调整某个输入值以实现该目标。此功能在需要找到能够产生特定结果的输入值的情况下特别有用。

• 单变量调整：目标搜索一次只能更改一个输入单元格的值。
• 需要公式：在应用目标搜索功能时，目标单元格必须包含公式。
• 保持公式的完整性：目标搜索不会更改目标单元格中的公式；它会更改公式所依赖的输入单元格的值。

使用 Kutools AI 解锁 Excel 魔法

• 智能执行：执行单元格操作、分析数据和创建图表——所有这些都由简单命令驱动。
• 自定义公式：生成量身定制的公式，优化您的工作流程。
• VBA 编码：轻松编写和实现 VBA 代码。
• 公式解释：轻松理解复杂公式。
• 文本翻译：打破电子表格中的语言障碍。

通过人工智能驱动的工具增强您的 Excel 能力。立即下载，体验前所未有的高效！

在 Excel 中访问目标搜索

要访问目标搜索功能，请转到“数据”选项卡，点击“假设分析” > “目标搜索”。请参见截图：

然后会弹出目标搜索对话框。以下是 Excel 目标搜索对话框中三个选项的解释：

• 设置单元格：包含你希望达成的公式的单元格。此单元格必须包含公式，目标搜索将调整另一个相关单元格的值，使此单元格的值达到你设定的目标。
• 目标值：你希望“设置单元格”达到的目标值。
• 通过更改单元格：你希望目标搜索调整的输入单元格。此单元格的值将被更改，以确保“设置单元格”满足“目标值”的目标。
  
  

在下一节中，我们将通过详细示例探索如何利用 Excel 的目标搜索功能。

通过示例使用目标搜索

在本节中，我们将通过四个常见示例演示目标搜索的实用性。这些示例将涵盖从个人财务调整到战略性商业决策，展示了该工具在各种现实场景中的应用。每个示例都旨在帮助您更清楚地了解如何有效使用目标搜索来解决不同类型的问题。

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

情景：一个人计划贷款 20,000 美元，并打算在 5 年内以 5% 的年利率偿还。每月需还款金额为 377.42 美元。后来，他意识到自己可以将每月还款增加至 500 美元。假设总贷款金额和年利率保持不变，现在需要多少年才能还清贷款？

如下表所示：

• B1 包含贷款金额 20,000 美元。
• B2 包含贷款期限 5 年。
• B3 包含年利率 5%。
• B5 包含通过公式 =PMT(B3/12,B2*12,B1) 计算的月还款额。
  
  

接下来我将向您展示如何使用目标搜索功能完成此任务。

步骤 1：激活目标搜索功能

转到“数据”选项卡，点击“假设分析” > “目标搜索”。

步骤 2：配置目标搜索设置

1. 在“设置单元格”框中，选择公式 B5。
2. 在“目标值”框中，输入每月计划还款金额 -500（负数表示支付）。
3. 在“通过更改单元格”框中，选择要调整的单元格 B2。
4. 点击 确定.
   
   

结果

随后会弹出目标搜索状态对话框，显示已找到解决方案。同时，“通过更改单元格”框中指定的单元格 B2 的值将被替换为新值，公式单元格将根据变量变化获得目标值。点击“确定”以应用更改。

现在，他大约需要 3.7 年即可还清贷款。

示例 2：确定考试及格的最低分数

情景：一名学生需要参加 5 次考试，每次考试权重相同。为了通过考试，学生必须在所有考试中的平均分达到 70%。已经完成了前 4 次考试并知道成绩后，学生现在需要计算第 5 次考试所需的最低分数，以确保总体平均分达到或超过 70%。

如下图所示：

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

要实现这一目标，您可以按照以下方式应用目标搜索功能：

步骤 1：激活目标搜索功能

转到“数据”选项卡，点击“假设分析” > “目标搜索”。

步骤 2：配置目标搜索设置

1. 在“设置单元格”框中，选择公式单元格 B7。
2. 在“目标值”框中，输入及格平均分 70%。
3. 在“通过更改单元格”框中，选择要调整的单元格 (B5)。
4. 点击 确定.
   
   

结果

随后会弹出目标搜索状态对话框，显示已找到解决方案。同时，“通过更改单元格”框中指定的单元格 B7 将自动填入匹配的值，公式单元格将根据变量变化获得目标值。点击“确定”接受解决方案。

因此，为了达到所需的总体平均分并通过所有考试，学生在最后一次考试中至少需要得 71 分。

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

情景：在一场选举中，候选人需要计算确保多数票所需的最低票数。已知总投票数，候选人必须获得多少票才能超过 50% 并赢得选举？

如下图所示：

• 单元格 B2 包含选举中的总投票数。
• 单元格 B3 显示候选人目前收到的票数。
• 单元格 B4 包含候选人希望实现的多数票百分比 (%)，通过公式 =B2/B1 计算得出。
  
  

要实现这一目标，您可以按照以下方式应用目标搜索功能：

步骤 1：激活目标搜索功能

转到“数据”选项卡，点击“假设分析” > “目标搜索”。

步骤 2：配置目标搜索设置

1. 在“设置单元格”框中，选择公式单元格 B4。
2. 在“目标值”框中，输入所需的多数票百分比。在这种情况下，为了赢得选举，候选人必须获得超过一半（即超过 50%）的有效票数，因此我输入 51%。
3. 在“通过更改单元格”框中，选择要调整的单元格 (B2)。
4. 点击 确定.
   
   

结果

随后会弹出目标搜索状态对话框，显示已找到解决方案。同时，“通过更改单元格”框中指定的单元格 B2 将自动替换为新值，公式单元格将根据变量变化获得目标值。点击“确定”以应用更改。

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

示例 4：实现企业的目标利润

情景：一家公司希望在当前年度实现 150,000 美元的具体利润目标。考虑到固定成本和可变成本，他们需要确定必要的销售收入。需要销售多少单位才能达到这一利润目标？

如下图所示：

• B1 包含固定成本。
• B2 包含每单位的可变成本。
• B3 包含每单位的价格。
• B4 包含售出的单位数量。
• B6 是总收入，通过公式 =B3*B4 计算得出。
• B7 是总成本，通过公式 =B1+(B2*B4) 计算得出。
• B9 是当前销售利润，通过公式 =B6-B7 计算得出。
  
  
  </li

要基于目标利润获取总销售单位数，您可以按照以下方式应用目标搜索功能。

步骤 1：激活目标搜索功能

转到“数据”选项卡，点击“假设分析” > “目标搜索”。

步骤 2：配置目标搜索设置

1. 在“设置单元格”框中，选择返回利润的公式单元格。这里是单元格 B9。
2. 在“目标值”框中，输入目标利润 150000。
3. 在“通过更改单元格”框中，选择要调整的单元格 (B4)。
4. 点击 确定.
   
   

结果

随后会弹出目标搜索状态对话框，显示已找到解决方案。同时，“通过更改单元格”框中指定的单元格 B4 将自动替换为新值，公式单元格将根据变量变化获得目标值。点击“确定”接受更改。

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

目标搜索的常见问题及解决方案

本节列出了一些目标搜索的常见问题及其相应的解决方案。

1. 目标搜索找不到解决方案

• 原因：这通常发生在目标值无法通过任何可能的输入值实现，或者初始猜测与潜在解决方案相差太远时。
• 解决方案：调整目标值以确保其在可行范围内。此外，尝试不同的初始猜测，使其更接近预期解决方案。确保“设置单元格”中的公式正确且逻辑上能够生成所需的“目标值”。

2. 计算速度慢

• 原因：当处理大型数据集、复杂公式或初始猜测与解决方案相差较远时，目标搜索可能会很慢，需要更多迭代次数。
• 解决方案：尽可能简化公式并减少数据量。确保初始猜测尽可能接近预期解决方案，以减少所需的迭代次数。

3. 精度问题

• 原因：在使用目标搜索时，您可能会注意到有时 Excel 会说找到了解决方案，但它并不是您想要的——接近但不够精确。由于 Excel 的计算精度和四舍五入，目标搜索可能无法始终提供高度准确的结果。
  
  例如，要计算某个数字的特定乘方，并使用目标搜索找到实现所需结果的基数。这里我将更改单元格 A1（基数）中的输入值，以便单元格 A3（乘方结果）与目标结果 25 匹配。
  
  正如您在下图中看到的，目标搜索提供了近似值而不是确切的根。
  
• 解决方案：在 Excel 的计算选项中增加小数位数以提高精度，然后重新运行目标搜索功能。请按以下步骤操作：
  
  1. 点击“文件” > “选项”以打开 Excel 选项窗口。
  2. 选择 公式 在左侧窗格中，在 计算选项 部分中，增加 最大变化 框中的小数位数。这里我将 0.001 改为 0.000000001 并点击 确定.
     
  3. 重新运行目标搜索，您将得到如下面截图所示的确切根。
     

4. 局限性及替代方法

• 原因：目标搜索只能调整一个输入值，可能不适合需要同时调整多个变量的方程。
• 解决方案：对于需要调整多个变量的场景，使用 Excel 的规划求解（Solver），它功能更强大并允许设置约束条件。

目标搜索是一种强大的工具，可用于执行反向计算，省去了手动测试不同值以逼近目标的繁琐任务。借助本文提供的见解，您现在可以自信地应用目标搜索，推动您的数据分析和效率迈向新的高度。对于那些渴望深入了解 Excel 功能的人，我们的网站提供了丰富的教程。在这里发现更多 Excel 技巧和窍门。