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。 桌子现在看起来像 −
由于 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，商店将收支平衡。
