Excel Data Analysis 简明教程
What-If Analysis with Goal Seek
目标寻址是一种 What-If 分析工具，它有助于您找到导致您想要的目标值的一个输入值。 Goal Seek 要求使用输入值，以在目标值中给出结果的公式。然后，Goal Seek 通过改变公式中的输入值，尝试得出输入值的解决方案。
Goal Seek is a What-If Analysis tool that helps you to find the input value that results in a target value that you want. Goal Seek requires a formula that uses the input value to give result in the target value. Then, by varying the input value in the formula, Goal Seek tries to arrive at a solution for the input value.
目标寻址仅适用于一个变量输入值。如果您有一个以上的输入值要确定，则必须使用求解器加载项。请参阅本教程中的章节– Optimization with Excel Solver 。
Goal Seek works only with one variable input value. If you have more than one input value to be determined, you have to use the Solver add-in. Refer to the chapter – Optimization with Excel Solver in this tutorial.
Analysis with Goal Seek
假设您想获得 5,000,000 的贷款，您想在 25 年内还清。您可以支付 50000 的 EMI。您想要知道可以借贷什么利率的贷款。
Suppose you want to take a loan of 5,000,000 and you want to repay in 25 years. You can pay an EMI of 50000. You want to know at what interest rate you can borrow the loan.
您可以使用 Goal Seek 找到借贷利率，如下所示 −
You can use Goal Seek to find the interest rate at which you can borrow the loan as follows −
Step 1 −按如下步骤设置 Goal Seek 的 Excel 单元格。
Step 1 − Set up the Excel cells for Goal Seek as given below.
Step 2 − 在与列 D 对应的列 C 中输入值。单元格 Interest_Rate 保持为空，因为您必须检索该值。此外，虽然您知道您可以支付的 EMI（50000），但您不必使用 Excel PMT 函数就能得出 EMI，因此该值未包含在内。目标寻址需要一个查找结果的公式。PMT 函数被放置在单元格 EMI 中以便 Goal Seek 可以使用它。
Step 2 − Enter the values in column C corresponding to column D. The cell Interest_Rate is kept empty, as you have to retrieve that value. Further, though you know the EMI that you can pay (50000), that value is not included as you have to use the Excel PMT function to arrive at it. Goal Seek requires a formula to find the result. The PMT function is placed in the cell EMI so that it can be used by Goal Seek.
Excel 使用 PMT 函数计算 EMI。表格现在如下所示 −
Excel computes the EMI with the PMT function. The table now looks like −
由于 Interest_Rate 单元格为空，因此 Excel 将该值视为 0 并计算 EMI。您可以忽略结果 -13,888.89 。
As the Interest_Rate cell is empty, Excel takes that value as 0 and calculates the EMI. You can ignore the result -13,888.89.
按照如下步骤执行使用目标寻址的分析 −
Perform the Analysis with Goal Seek as follows −
Step 1 − 在功能区上转到 DATA > What If Analysis > Goal Seek 。
Step 1 − Go to DATA > What If Analysis > Goal Seek on the Ribbon.
将出现“目标寻址”对话框。
The Goal Seek dialog box appears.
Step 2 - 在 Set cell 框内输入 EMI。此框为公式所在单元格的引用，在这种情况下为 PMT 函数。它是 C6 单元格，您将其命名为 EMI。
Step 2 − Type EMI in the Set cell box. This box is the reference for the cell that contains the formula that you want to resolve, in this case the PMT function. It is the cell C6, which you named as EMI.
Step 3 - 在 To value 框内输入 -50000。在此处，您将获得公式结果，在这种情况下，为您希望支付的 EMI。该数字为负数，因为它表示一笔付款。
Step 3 − Type -50000 in the To value box. Here, you get the formula result, in this case, the EMI that you want to pay. The number is negative because it represents a payment.
Step 4 - 在 By changing cell 框内输入 Interest_Rate。此框引用包含您希望调整的值的单元格，在这种情况下为利率。它是 C2 单元格，您将其命名为 Interest_Rate。
Step 4 − Type Interest_Rate in the By changing cell box. This box has the reference of the cell that contains the value you want to adjust, in this case the interest rate. It is cell C2, which you named as Interest_Rate.
Step 5 - 目标寻址工具更改的单元格必须由您在“设置单元格”框中指定的单元格内的公式引用。单击“确定”。
Step 5 − This cell that Goal Seek changes, must be referenced by the formula in the cell that you specified in the Set cell box. Click OK.
目标寻址工具产生如下所示的结果：
Goal Seek produces a result, as shown below −
正如您所看到的，目标寻址工具使用 C6 单元格（包含公式）找到了利率，显示为 C2 单元格中的 12%。单击“确定”。
As you can observe, Goal Seek found the solution using cell C6 (containing the formula) as 12% that is displayed in the cell C2, which is the interest rate. Click OK.
Solving Story Problems
您可以使用目标寻址工具轻松地解决情景问题。让我们借助示例了解这一点。
You can solve story problems easily with Goal Seek. Let us understand this with the help of an example.
Example
假设有一家书店，库存中有 100 本书。该书的原价为 250，并且已经以该价格售出一定数量的书。稍后，书店宣布对该书提供 10% 的折扣，并清仓了库存。您可能想要知道以原价售出了多少本书，以获得 24,500 的总收入。
Suppose there is a bookstore that has 100 books in storage. The original price of the book is 250 and certain number of books was sold at that price. Later, the bookstore announced a 10% discount on that book and cleared off the stock. You might want to know how many books are sold at the original price to obtain a total revenue of 24,500.
您可以使用目标寻址工具来找到解决方案。请按照以下步骤操作：
You can use Goal Seek to find the solution. Follow the steps given below −
Step 1 - 按照以下方式设置工作表。
Step 1 − Set the worksheet as given below.
Step 2 - 转到功能区的 DATA > What If Analysis > Goal Seek 。
Step 2 − Go to DATA > What If Analysis > Goal Seek on the Ribbon.
将出现“目标寻址”对话框。
The Goal Seek dialog box appears.
Step 3 - 在“设置单元格”框、“目标值”框和“更改单元格”框中分别输入 Revenue, 24500 and Books_OriginalPrice 。单击“确定”。
Step 3 − Type Revenue, 24500 and Books_OriginalPrice in the Set cell box, To Value box and By changing cell box respectively. Click OK.
目标寻址工具显示状态和解决方案。
Goal Seek displays the status and solution.
如果以原价出售了 80 本书，收入将为 24500。
If 80 books were sold at the original price, the revenue would be 24500.
Performing a Break-even Analysis
在经济学中，盈亏平衡点是不盈利也不亏损的点。这意味着：
In economy, break-even point is the point at which there is neither profit nor loss. This would mean −
收入 = 支出，或
Revenue = Expenses, or
收入 – 支出 = 0
Revenue – Expenses = 0
您可以在 Excel 中执行 break-even analysis with Goal Seek 。
You can do break-even analysis with Goal Seek in Excel.
Example
假设有一家商店出售玩具。您可能希望对商店进行盈亏平衡分析。从商店收集以下信息：
Suppose there is a store that sells toys. You might want to make a break-even analysis of the store. Collect the following information from the store −
-
Fixed cost of the store.
-
Unit cost of the toy.
-
Number of toys to be sold.
你需要找出他们应该以什么价格售出玩具才能达到收支平衡。
You need to find at which price they should sell the toys to break even.
Step 1 - 按照以下方式设置工作表。
Step 1 − Set the worksheet as given below.
Step 2 − 转到功能区的 DATA > What If Analysis > Goal Seek 。将出现“目标寻求”对话框。
Step 2 − Go to DATA > What If Analysis > Goal Seek on the Ribbon. The Goal Seek dialog box appears.
Step 3 − 分别在“设置单元格”框、“目标值”框和“更改单元格”框中键入 Break_even_Point, 0, and Unit_Price 。单击“确定”。
Step 3 − Type Break_even_Point, 0, and Unit_Price in the Set cell box, To value box and By changing cell box respectively. Click OK.
正如你观察到的，目标寻求给出的结果表明，如果单价为 35，商店将收支平衡。
As you can observe, Goal Seek gave the result that if the Unit Price is 35, the store will break even.
