Excel的一项经常被忽视的功能是假设分析(Analysis)工具，它允许您在不更改电子表格中的值的情况下探索不同的场景。此功能对于经济学和管理专业以及企业主特别有用，因为您可以看到不同的值如何影响模型、公式和预计利润。
Excel 的假设分析工具
假设(Suppose)您拥有一家销售小部件的小企业，并且您已确定每销售一个小部件将获得 10 美元的利润。要计算销售这些小部件的利润，您可以在Excel中创建一个如下所示的工作表：
请注意(Notice)，单元格 A3 中的数字代表售出的小部件数量，而 B3 中的数字代表每个售出的小部件的利润。C3中的数字使用以下公式计算：
=A3*B3
并表示以 10 美元的利润出售 1 个小部件的利润。
但是，您可能对售出不同数量的小部件时的总利润感兴趣。例如，假设(Suppose)您想知道如果您卖出 100、200 或 300 个小部件，您的利润是多少。
一种方法是针对我们上面确定的每个场景更改 A3 中的数字。换句话说，您可以将 A3 更改为 100，并注意 C3 中利润的变化。然后，您可以对 200 和 300 执行相同的操作。但是，Excel 的假设分析(What-If Analysis)功能就是为这种情况而设计的。
要使用 Excel 的假设分析(What-If Analysis)工具，首先单击功能区上的(Ribbon)数据(Data)选项卡，然后找到数据工具(Data Tools)部分下的假设分析(What-If Analysis)工具按钮。单击(Click)假设分析(What-If Analysis)按钮并从选项列表中选择方案管理器。(Scenario Manager)
您现在应该正在查看Scenario Manager窗口。单击(Click)“添加(Add)”按钮，Excel会要求您命名第一个场景。您可以选择任何您想要的名称，但谨慎的做法是选择一个描述您正在创建的场景的名称。对于我们这里的示例，将场景命名为100 Widgets。
在“更改单元格”框中，键入A3，然后单击“确定(OK)”按钮。
在打开的场景值(Scenarios Values)窗口中，输入100并单击确定(OK)按钮。本质上，您所做的是创建了一个新的假设情景，如果在 A3 中销售的小部件数量为 100，则该情景将计算总利润。
完成(Complete)上述步骤，再创建两个场景，一个用于200 个小部件(200 widgets)，一个用于300 个小部件(300 widgets)。完成后，场景管理器(Scenario Manager)窗口应如下所示：
单击标有100 个小部件(100 Widgets)的场景，然后单击“显示”(Show)按钮。请注意(Notice)，单元格 A3 更改为 100，单元格 C3 更改为 $1,000。现在，单击标有200 个小部件(200 Widgets)的场景，然后单击“显示”(Show)按钮。请注意(Notice)，单元格 A3 更改为 200，单元格 C3 更改为 $2,000。对标记为300 Widgets的场景执行相同的操作。
What-If Scenario还允许您一次查看您在前面的步骤中创建的所有场景的摘要。单击(Click)摘要(Summary)按钮并在结果单元格框中键入C3 。
单击确定(OK)，Excel将创建一个新工作表，在一个方便的摘要中显示所有方案。
这个例子虽然简单，但展示了Excel的假设分析(What-If Analysis)功能有多么强大。使用填充了数据和公式的工作表，您可以使用Excel轻松创建有用(Excel)的假设分析(What-If Analysis)场景，从而省去编辑单个单元格以查看这些更改将如何影响其他单元格中的值的麻烦。享受！
How to Use Excel’s What-If Analysis
One often overloоked feature of Excel is the What-If Analysis tool that allows you to explore different scenarios without changing the values in yoυr spreadsheet. Τhis function is partiсularly useful for econоmiсs and management maјors as well as buѕiness owners because you can ѕee how different valueѕ affect models, formulas, and projected profits.
Excel’s What-If Analysis Tool
Suppose you own a small business selling widgets and you have determined that you will make a $10 profit for each widget you sell. To figure your profit from selling these widgets, you create a worksheet in Excel that looks like this:
Notice that the figure in cell A3 represents the number of widgets sold and the figure in B3 represents the profit per widget sold. The figure in C3 was calculated with the following formula:
=A3*B3
and represents the profit from selling 1 widget at a $10 profit.
However, you may be interested in your total profit at different numbers of widgets sold. Suppose, for example, you want to know what your profit would be if you sold 100, 200, or 300 widgets.
One way to do this is to change the figure in A3 for each of the scenarios we identified above. In other words, you could change A3 to 100 and note the change in profit in C3. You could then do the same for 200 and 300. However, Excel’s What-If Analysis function was designed for just such an occasion.
To use Excel’s What-If Analysis tool, begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis tool button under the Data Tools section. Click on the What-If Analysis button and choose Scenario Manager from the list of options.
You should now be looking at the Scenario Manager window. Click on the Add button and Excel asks you to name the first scenario. You can choose any name you want but it would be prudent to choose one that is descriptive of the scenario you are creating. For our example here, name the scenario 100 Widgets.
In the Changing Cells box, type in A3 and then click the OK button.
In the Scenarios Values window that opens, type in 100 and click the OK button. Essentially what you have done is created a new what-if scenario that will calculate the total profit if the number of widgets sold in A3 were 100.
Complete the steps above to create two more scenarios, one for 200 widgets and one for 300 widgets. When done, the Scenario Manager window should look like this:
Click on the scenario labeled 100 Widgets and then click the Show button. Notice that cell A3 changes to 100 and cell C3 changes to $1,000. Now, click on the scenario labeled 200 Widgets and then click the Show button. Notice that cell A3 changes to 200 and cell C3 changes to $2,000. Do the same for the scenario labeled 300 Widgets.
The What-If Scenario also lets you see at one time a summary of all of the scenarios you created in the previous steps. Click on the Summary button and type C3 into the Results Cell box.
Click OK and Excel creates a new worksheet displaying all of your scenarios in one convenient summary.
Although simple, this example shows how powerful Excel’s What-If Analysis function can be. Using a worksheet filled with data and formulas, you can easily create useful What-If Analysis scenarios with Excel to save you the trouble of editing individual cells to see how these changes will affect the values in other cells. Enjoy!
