Excel 高级数据分析 - 带有目标搜索的假设分析

Goal Seek 是一种假设分析工具,可帮助您找到产生所需目标值的输入值。 Goal Seek 需要一个使用输入值给出目标值结果的公式。 然后,通过改变公式中的输入值,Goal Seek 尝试得出输入值的解。

Goal Seek 仅适用于一个可变输入值。 如果要确定多个输入值,则必须使用 Solver 插件。 请参阅章节 – 本教程中的使用 Excel 规划求解进行优化


目标寻求分析

假设你想贷款 5,000,000 并且你想在 25 年内偿还。 你可以支付 50000 的 EMI。你想知道你可以以什么利率借到贷款。

您可以使用 Goal Seek 找到您可以借入贷款的利率,如下所示 −

步骤 1 −如下所示为 Goal Seek 设置 Excel 单元格。

目标搜索

步骤 2 − 在 C 列中输入与 D 列相对应的值。单元格 Interest_Rate 保持为空,因为您必须检索该值。 此外,虽然您知道可以支付的 EMI (50000),但该值未包括在内,因为您必须使用 Excel PMT 函数才能得出该值。 Goal Seek 需要一个公式来找到结果。 PMT 函数被放置在单元 EMI 中,以便 Goal Seek 可以使用它。

Excel 使用 PMT 函数计算 EMI。 桌子现在看起来像 −

计算 EMI

由于 Interest_Rate 单元格为空,Excel 将该值设为 0 并计算 EMI。 您可以忽略结果-13,888.89

使用目标搜索执行分析如下 −

步骤 1 − 转到功能区上的 DATA > What If Analysis > Goal Seek

在功能区上寻找目标

目标搜索对话框出现。

步骤 2 − 在 Set cell 框中键入 EMI。 此框是包含要解析的公式的单元格的引用,在本例中为 PMT 函数。 它是单元格 C6,您将其命名为 EMI。

步骤 3 − 在目标值 框中键入 -50000。 在这里,您将获得公式结果,在本例中为您要支付的 EMI。 这个数字是负数,因为它代表了一笔付款。

步骤 4 − 在通过更改单元格 框中键入 Interest_Rate。 此框包含包含您要调整的值的单元格的引用,在本例中为利率。 它是您命名为 Interest_Rate 的单元格 C2。

步骤 5 − 目标搜索更改的此单元格必须由您在"设置单元格"框中指定的单元格中的公式引用。 单击"确定"。

设置单元格框

Goal Seek 产生一个结果,如下图 −

目标求结果

如您所见,Goal Seek 使用单元格 C6(包含公式)找到了解决方案,该解决方案显示在单元格 C2 中,即利率为 12%。 单击确定。


求解问题

您可以使用 Goal Seek 轻松解决求解问题。 让我们借助一个例子来理解这一点。

示例

假设一家书店有 100 本书。 这本书的原价是 250,并以该价格出售了一定数量的书。 后来,书店宣布该书打折10%,并清仓。 您可能想知道以原价卖出了多少本书,总收入为 24,500。

您可以使用 Goal Seek 来找到解决方案。 按照下面给出的步骤 −

步骤 1 − 如下所示设置工作表。

求解问题

步骤 2 − 转到功能区上的 DATA > What If Analysis > Goal Seek

目标搜索对话框出现。

步骤 3 − 在设置单元格框、至值框和通过更改单元格框分别键入 Revenue、24500 和 Books_OriginalPrice。 单击"确定"。

收入类型

Goal Seek 显示状态和解决方案。

现状及解决方案

如果按原价销售 80 本书,则收入为 24500。


进行收支平衡分析

在经济中,盈亏平衡点是指既不盈利也不亏损的点。 这意味着 −

收入=费用,或者

收入 - 费用 = 0

您可以在 Excel 中使用 Goal Seek 进行收支平衡分析

示例

假设有一家卖玩具的商店。 您可能想要对商店进行盈亏平衡分析。 从店铺收集以下信息 −

  • 商店的固定成本。
  • 玩具的单位成本。
  • 待售玩具数量。

您需要找到他们应该以什么价格出售玩具才能实现收支平衡。

步骤 1 − 如下所示设置工作表。

设置工作表

步骤 2 − 转到功能区上的 DATA > What If Analysis > Goal Seek。 出现"目标搜索"对话框。

步骤 3 − 在设置单元格框、至值框和通过更改单元格框分别键入 Break_even_Point、0 和 Unit_Price。 单击确定。

值和单元格框

如您所见,Goal Seek 给出的结果是,如果单价为 35,商店将收支平衡。

中断存储