如何在假设情况下在Microsoft Excel中使用目标搜索

Excel的假设分析使您可以查看更改单元格如何影响公式的输出。为此,可以使用Excel的工具来计算更改公式中单元格的值的效果。

Excel具有三种类型的假设分析工具:方案管理器,目标搜索和数据表。使用“目标搜索”,您可以确定将公式反向转换为特定输出所需的输入。

Excel中的“目标搜索”功能是反复试验的,因此,如果它不能产生您想要的功能,则Excel会努力提高该值,直到达到目标为止。

什么是Excel目标搜索公式?

Excel中的“目标搜索”基本上分为三个主要部分:

  1. 设置单元格:您要用作目标的单元格。
  2. 重视:您想要作为目标的价值。
  3. 通过更改单元格:您要更改以达到目标价值的单元格。

设置了这三个设置后,Excel将尝试通过更改单元格来改善您在单元格中设置的值,直到在“设置单元格”设置的单元格达到在“至值”中确定的值为止。

如果这一切听起来令人困惑,那么下面的示例将使您对目标搜索的工作原理有一个很好的了解。

目标搜寻范例1

例如,假设您有两个单元格(A和B),第三个单元格计算这两个单元的平均值。

现在假设您有一个静态的A值,并且希望通过更改B的值来提高平均值。通过使用“目标搜索”,您可以计算B的哪个值将产生您想要的平均值。

相关:在Microsoft Excel中节省时间的提示

如何在Excel中使用目标搜索

  1. 在Excel中,单击单元格C1
  2. 在编辑栏中,输入以下代码:
     =AVERAGE(A1:B1)

    该公式将使Excel计算单元格A1B1中的平均值,并在单元格C1中输出平均值。您可以通过在A1和B1中输入数字来确保公式有效。 C1应该显示其平均值。

  3. 对于此示例,将A1单元格的值更改为16
  4. 选择单元格C1 ,然后从功能区中转到“数据”选项卡。
  5. 在“数据”标签下,选择“假设分析” ,然后选择目标搜索” 。这将打开“目标搜索”窗口。
  6. 在“目标搜索”窗口中,将“设置”单元格设置C1 。这将是您的目标单元。在打开“目标搜索”窗口之前突出显示C1将自动在“设置”单元格中进行设置
  7. “值”中,插入所需的目标值。对于此示例,我们希望平均值为26,因此目标值也将为26。
  8. 最后,在“通过更改单元格”中,选择要更改以达到目标的单元格。在此示例中,这将是单元格B2
  9. 单击“确定”使“目标搜索”发挥其魔力。

单击确定后,将出现一个对话框,通知您目标寻求已找到解决方案。

单元格A1的值将更改为解决方案,这将覆盖先前的数据。最好在数据表的副本上运行“目标搜索”,以避免丢失原始数据。

相关:出色的疯狂Excel公式

目标搜寻范例2

在现实生活中使用目标搜寻时,目标搜寻可能会很有用。但是,要充分发挥“目标搜寻”的潜力,首先需要制定适当的公式。

对于此示例,让我们以一个小型银行案例为例。假设您有一个银行帐户,该帐户的年收入为您帐户中资金的4%。

使用假设分析和目标寻求,您可以计算您的帐户中需要多少钱才能获得一定数量的每月利息。

对于此示例,假设我们希望每月从利息支付中获得350美元。计算方法如下:

  1. 在单元格A1中,输入Balance
  2. 在单元格A2中,键入年费率
  3. 在单元格A4中,键入“每月利润”
  4. 在单元格B2中,输入4%
  5. 选择单元格B4,然后在公式栏中输入以下公式:
     =B1*B2/12

    每月利润等于帐户余额乘以年利率,然后除以一年中的月数(12个月)。

  6. 转到数据标签,点击假设分析,然后选择目标寻求
  7. 在“目标搜索”窗口中,在“设置单元格”中键入B4
  8. 在“到”值单元格中键入350。 (这是您想要获得的每月利润)
  9. 更改单元格中键入B1 。 (这会将余额更改为每月可产生$ 350的值)
  10. 单击确定。将弹出“目标搜索状态”对话框。

在“寻找目标状态”对话框中,您将看到“寻找目标”已在单元格B4中找到了解决方案。在单元格B1中,您可以看到解决方案,它应该是105,000。

目标搜寻要求

使用“目标搜索”,您可以看到它只能更改一个单元格才能达到目标值。这意味着,如果目标搜寻具有多个变量,则无法解决并达成解决方案。

在Excel中,您仍然可以求解多个变量,但是您需要使用另一种称为规划求解的工具。您可以阅读我们有关如何使用Excel的求解器的文章,了解有关Excel的求解器的更多信息。

目标寻求限制

目标寻求公式使用试验和改进过程来达到最佳值,当公式产生不确定值时,这可能会带来一些问题。您可以自己测试以下方法:

  1. 在单元格A1中,键入3
  2. 选择单元格C1 ,然后在编辑栏中输入以下公式:
     =1/(A1-5)

    该公式将被A1-5除以1,如果A1-5恰好为0,则结果将是不确定的值。

  3. 转到数据标签,点击假设分析,然后选择目标寻求
  4. 在“设置单元格”中,键入C1
  5. 在“要值”中,键入1
  6. 最后,在通过更改单元格类型A1中。 (这会将A1值更改为C1中的1)
  7. 单击确定

“目标寻求状态”对话框将告诉您可能没有找到解决方案,您可以再次检查A1值是否偏离。

现在,此“目标寻求”的解决方案是在A1中仅包含6。这将得出1/1,等于1。但是在试验和改进过程中的某一时刻,Excel在A1中尝试5,这给出了1/0(未定义),从而终止了该过程。

解决“目标寻求”问题的一种方法是具有不同的初始值,可以避免尝试和改进过程中未定义的初始值。

例如,如果将A1更改为大于5的任何数字,然后对“目标搜索”重复相同的步骤以获取1,则应该得到正确的结果。

通过目标搜索提高计算效率

假设分析和目标寻求可以极大地加快您的公式的计算速度。学习正确的公式是成为excel大师的又一重要步骤。