Excel Data Analysis 简明教程
What-If Analysis with Goal Seek
目标寻址是一种 What-If 分析工具,它有助于您找到导致您想要的目标值的一个输入值。 Goal Seek 要求使用输入值,以在目标值中给出结果的公式。然后,Goal Seek 通过改变公式中的输入值,尝试得出输入值的解决方案。
目标寻址仅适用于一个变量输入值。如果您有一个以上的输入值要确定,则必须使用求解器加载项。请参阅本教程中的章节– Optimization with Excel Solver 。
Analysis with Goal Seek
假设您想获得 5,000,000 的贷款,您想在 25 年内还清。您可以支付 50000 的 EMI。您想要知道可以借贷什么利率的贷款。
您可以使用 Goal Seek 找到借贷利率,如下所示 −
Step 1 −按如下步骤设置 Goal Seek 的 Excel 单元格。
Step 2 − 在与列 D 对应的列 C 中输入值。单元格 Interest_Rate 保持为空,因为您必须检索该值。此外,虽然您知道您可以支付的 EMI(50000),但您不必使用 Excel PMT 函数就能得出 EMI,因此该值未包含在内。目标寻址需要一个查找结果的公式。PMT 函数被放置在单元格 EMI 中以便 Goal Seek 可以使用它。
Excel 使用 PMT 函数计算 EMI。表格现在如下所示 −
由于 Interest_Rate 单元格为空,因此 Excel 将该值视为 0 并计算 EMI。您可以忽略结果 -13,888.89 。
按照如下步骤执行使用目标寻址的分析 −
Step 1 − 在功能区上转到 DATA > What If Analysis > Goal Seek 。
将出现“目标寻址”对话框。
Step 2 - 在 Set cell 框内输入 EMI。此框为公式所在单元格的引用,在这种情况下为 PMT 函数。它是 C6 单元格,您将其命名为 EMI。
Step 3 - 在 To value 框内输入 -50000。在此处,您将获得公式结果,在这种情况下,为您希望支付的 EMI。该数字为负数,因为它表示一笔付款。
Step 4 - 在 By changing cell 框内输入 Interest_Rate。此框引用包含您希望调整的值的单元格,在这种情况下为利率。它是 C2 单元格,您将其命名为 Interest_Rate。
Step 5 - 目标寻址工具更改的单元格必须由您在“设置单元格”框中指定的单元格内的公式引用。单击“确定”。
目标寻址工具产生如下所示的结果:
正如您所看到的,目标寻址工具使用 C6 单元格(包含公式)找到了利率,显示为 C2 单元格中的 12%。单击“确定”。
Solving Story Problems
您可以使用目标寻址工具轻松地解决情景问题。让我们借助示例了解这一点。
Example
假设有一家书店,库存中有 100 本书。该书的原价为 250,并且已经以该价格售出一定数量的书。稍后,书店宣布对该书提供 10% 的折扣,并清仓了库存。您可能想要知道以原价售出了多少本书,以获得 24,500 的总收入。
您可以使用目标寻址工具来找到解决方案。请按照以下步骤操作:
Step 1 - 按照以下方式设置工作表。
Step 2 - 转到功能区的 DATA > What If Analysis > Goal Seek 。
将出现“目标寻址”对话框。
Step 3 - 在“设置单元格”框、“目标值”框和“更改单元格”框中分别输入 Revenue, 24500 and Books_OriginalPrice 。单击“确定”。
目标寻址工具显示状态和解决方案。
如果以原价出售了 80 本书,收入将为 24500。
Performing a Break-even Analysis
在经济学中,盈亏平衡点是不盈利也不亏损的点。这意味着:
收入 = 支出,或
收入 – 支出 = 0
您可以在 Excel 中执行 break-even analysis with Goal Seek 。
Example
假设有一家商店出售玩具。您可能希望对商店进行盈亏平衡分析。从商店收集以下信息:
-
商店固定成本。
-
玩具单位成本。
-
待售玩具数量。
你需要找出他们应该以什么价格售出玩具才能达到收支平衡。
Step 1 - 按照以下方式设置工作表。
Step 2 − 转到功能区的 DATA > What If Analysis > Goal Seek 。将出现“目标寻求”对话框。
Step 3 − 分别在“设置单元格”框、“目标值”框和“更改单元格”框中键入 Break_even_Point, 0, and Unit_Price 。单击“确定”。
正如你观察到的,目标寻求给出的结果表明,如果单价为 35,商店将收支平衡。