尽管 Excel 的一长串功能是 Microsoft 电子表格应用程序中最吸引人的功能之一，但仍有一些未充分利用的 gem 可以增强这些功能。一个经常被忽视的工具是假设分析。
Excel 的假设分析(What-If Analysis)工具分为三个主要组件。这里讨论的部分是强大的Goal Seek功能，可让您从函数向后工作并确定从单元格中的公式获得所需输出所需的输入。继续阅读以了解如何使用 Excel 的What-If 分析 Goal Seek(What-If Analysis Goal Seek)工具。
Excel 的目标搜索工具示例
假设您想通过抵押贷款购买房屋，并且您担心贷款利率将如何影响年度付款。抵押贷款金额为 100,000 美元，您将在 30 年内偿还贷款。
使用 Excel 的PMT功能，您可以轻松计算出利率为 0% 时的年付款额。电子表格可能看起来像这样：
A2 的单元格代表年利率，B2 的单元格是贷款年限，C2 的单元格是抵押贷款的金额。D2中的公式为：
=PMT(A2,B2,C2)
并且代表 30 年期、100,000 美元的抵押贷款以 0% 的利息每年支付的金额。请注意(Notice)，D2 中的数字是负数，因为Excel假设付款是来自您的财务状况的负现金流。
不幸的是，没有抵押贷款人会以 0% 的利率借给您 100,000 美元。假设(Suppose)您进行了一些计算，发现您有能力每年偿还 6,000 美元的抵押贷款。您现在想知道您可以为贷款承担的最高利率是多少，以确保您最终每年支付的费用不会超过 6,000 美元。
在这种情况下，许多人会简单地开始在单元格 A2 中输入数字，直到 D2 中的数字达到大约 6,000 美元。但是，您可以使用假设分析目标查找(Analysis Goal Seek)工具让Excel为您完成工作。(Excel)从本质上讲，您将使Excel从 D4 中的结果向后工作，直到达到满足您的最高支出 6,000 美元的利率。
首先单击功能区上的(Ribbon)数据(Data)选项卡，然后在数据工具(Data Tools)部分找到假设分析(What-If Analysis)按钮。单击假设分析(What-If Analysis)按钮并从菜单中选择目标搜索。(Goal Seek)
Excel 打开一个小窗口，要求您只输入三个变量。Set Cell变量必须是包含公式的单元格。在我们的示例中，它是D2。To Value变量是您希望D2处的单元格在分析结束时的数量。
对我们来说，它是-6,000。请记住，Excel将付款视为负现金流。通过更改单元格(By Changing Cell)变量是您希望 Excel 为您找到的利率，因此 100,000 美元的抵押贷款每年只需花费您 6,000 美元。因此，使用单元格A2。
单击“确定(OK)”按钮，您可能会注意到Excel在各个单元格中闪烁了一堆数字，直到迭代最终收敛到最终数字。在我们的例子中，A2 处的单元格现在应该读数约为 4.31%。
该分析告诉我们，为了在 30 年期、100,000 美元的抵押贷款上每年花费不超过 6,000 美元，您需要确保贷款不超过 4.31%。如果您想继续进行假设分析，您可以尝试不同的数字和变量组合，以探索您在尝试获得良好的抵押贷款利率时的选择。
Excel 的What-If Analysis Goal Seek工具是对典型电子表格中的各种函数和公式的强大补充。通过从单元格中的公式结果向后工作，您可以更清楚地探索计算中的不同变量。
Using Excel’s What-If Analysis Goal Seek Tool
Althoυgh Excel’s long list of functions is one of the most enticing fеatures of Microsoft’s spreadsheet application, there a fеw underutilizеd gems that еnhаnсe these fυnctions. One often-overlooked tool is the What-If Analysis.
Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.
Excel’s Goal Seek Tool Example
Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.
Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:
The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:
=PMT(A2,B2,C2)
and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.
Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.
Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.
Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.
Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.
For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.
Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.
This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.
Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.
