Excel Data Analysis 简明教程

What-If Analysis with Scenario Manager

情景管理器对于在敏感性分析中具有两个以上变量的情况下非常有用。情景管理器为所选变量的每组输入值创建情景。情景帮助你探索一系列可能的结果，支持以下内容-

Scenario Manager is useful in the cases where you have more than two variables in sensitivity analysis. Scenario Manager creates scenarios for each set of the input values for the variables under consideration. Scenarios help you to explore a set of possible outcomes, supporting the following −

  1. Varying as many as 32 input sets.

  2. Merging the scenarios from several different worksheets or workbooks.

如果你想分析超过 32 组输入，并且这些值仅代表一到两个变量，你可以使用数据表。尽管它仅限于一到两个变量，但数据表可以包含任意数量不同的输入值。请参阅本教程中的 What-If Analysis with Data Tables 。

If you want to analyze more than 32 input sets, and the values represent only one or two variables, you can use Data Tables. Although it is limited to only one or two variables, a Data Table can include as many different input values as you want. Refer to What-If Analysis with Data Tables in this tutorial.

Scenarios

情景是一组由 Excel 保存的可以在工作表中自动替换的值。你可以在工作表上将不同组的值创建并保存为情景，然后在这些情景之间切换以查看不同的结果。

A scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios on a worksheet and then switch between these scenarios to view the different results.

例如，你可以有几个不同的预算情景，它们比较各种可能的收入水平和支出。你还可以有来自不同来源的不同贷款情景，这些情景比较各种可能的利率和贷款期限。

For example, you can have several different budget scenarios that compare various possible income levels and expenses. You can also have different loan scenarios from different sources that compare various possible interest rates and loan tenures.

如果想要在情景中使用的信息来自不同来源，你可以将此信息收集到单独的工作簿中，然后将不同工作簿中的情景合并到一个工作簿中。

If the information that you want to use in scenarios is from different sources, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one.

拥有所需的所有情景后，你可以创建一个情景汇总报告-

After you have all the scenarios you need, you can create a scenario summary report −

  1. That incorporates information from all the scenarios.

  2. That lets you compare the scenarios side-by-side.

Scenario Manager

情景管理器是 Excel 中的假设分析工具之一。

Scenario Manager is one of the What-if Analysis tools in Excel.

要使用情景管理器创建一个分析报告，您必须遵循以下步骤−

To create an analysis report with Scenario Manager, you have to follow these steps −

Step 1 − 定义初始值的集合，并找出您想要改变的输入单元格，称为可变单元格。

Step 1 − Define the set of initial values and identify the input cells that you want to vary, called the changing cells.

Step 2 − 创建每个情景，命名情景，并为该情景的每个可变输入单元格输入值。

Step 2 − Create each scenario, name the scenario and enter the value for each changing input cell for that scenario.

Step 3 − 选择您想要跟踪的输出单元格，称为结果单元格。这些单元格包含初始值集合中的公式。这些公式使用可变输入单元格。

Step 3 − Select the output cells, called the result cells that you want to track. These cells contain formulas in the initial set of values. The formulas use the changing input cells.

情景管理器创建一份报告，其中包含每个情景的输入和输出值。

The Scenario Manager creates a report containing the input and the output values for each scenario.

Initial Values for Scenarios

在创建若干不同情景之前，您需要定义一组情景将基于其的初始值。

Before you create several different scenarios, you need to define a set of initial values on which the scenarios will be based.

设置情景的初始值的步骤为 −

The steps for setting up the initial values for Scenarios are −

  1. Define the cells that contain the input values.

  2. Name the input cells appropriately.

  3. Identify the input cells with constant values.

  4. Specify the values for the constant inputs.

  5. Identify the input cells with changing values.

  6. Specify the initial values for the changing inputs.

  7. Define the cells that contain the results. The result cells contain formulas.

  8. Name the result cells appropriately.

  9. Place the formulas in the result cells.

考虑前面的贷款示例。现在，按照以下步骤进行 -

Consider the previous example of loan. Now, proceed as follows −

  1. Define a cell for Loan Amount. This input value is constant for all the scenarios. Name the cell Loan_Amount. Specify the value as 5,000,000.

  2. Define the cells for Interest Rate, No. of payments and Type (Payment at the beginning or end of the month). These input values will be changing across the scenarios. Name the cells Interest_Rate, NPER and Type. Specify the initial values for the analysis in these cells as 12%, 360 and 0 respectively.

  3. Define the cell for the EMI. This is the result value. Name the cell EMI. Place the formula in this cell as − =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

您的工作表如下所示 -

Your worksheet looks as shown below −

define cells

您会看到输入单元格和结果单元格在 C 列中，名称如 D 列中给出的那样。

As you can see that the input cells and the result cells are in column C with the names as given in column D.

Creating Scenarios

在为情景设置初始值后，您可以使用情景管理器创建情景，如下所示 -

After setting up the initial values for the Scenarios, you can create the scenarios using Scenario Manager as follows −

  1. Click the DATA tab on the Ribbon.

  2. Click What-if Analysis in the Data Tools group.

  3. Select Scenario Manager from the dropdown list.

creating scenarios

将出现“情景管理器”对话框。您会看到其中包含一条消息 -

The Scenario Manager Dialog box appears. You can observe that it contains a message −

“No Scenarios defined. Choose Add to.”

“No Scenarios defined. Choose Add to.”

add scenario

您需要为情景管理器中每组变化值创建情景。最好使用初始值定义第一个情景，因为它使您能够在显示不同情景时随时切换回初始值。

You need to create scenarios for each set of changing values in the Scenario Manager. It is good to have the first scenario defined with initial values, as it enables you to switch back to initial values whenever you want while displaying different scenarios.

按照以下步骤使用初始值创建第一个情景 -

Create the first scenario with the initial values as follows −

  1. Click the Add button in the Scenario Manager Dialog box.

将出现 Add Scenario 对话框。

The Add Scenario dialog box appears.

  1. Under Scenario Name, type Scenario 1.

  2. Under Changing Cells, enter the references for the cells i.e. C3, C4 and C5 with the Ctrl key pressed.

对话框名称更改为“编辑情景”。

The name of the dialog box changes to Edit Scenario.

  1. Edit the text in the Comment as – Initial Values box.

  2. Select the option Prevent changes under Protection and then click OK.

prevent changes

将出现 Scenario Values 对话框。您定义的初始值将出现在每个改变单元格框中。

The Scenario Values dialog box appears. The initial values that you have defined appear in each of the changing cells boxes.

scenario values

用初始值创建 Scenario 1 。

Scenario 1 with the initial values is created.

创建三个不同场景，如下所示，场景中的值各不相同：

Create three more scenarios with varying values in the changing cells as follows −

  1. Click the Add button in the Scenario Values dialog box.

将显示“添加方案”对话框。请注意，“C3、C4、C5”将显示在“变化单元格”框中。

Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box.

  1. In the Scenario Name box, type Scenario 2.

  2. Edit the text in the Comment as – Different Interest Rate.

  3. Select Prevent changes under Protection and click OK.

select prevent changes

将显示 Scenario Values 对话框。初始值将显示在改变单元格中。将 Interest_Rate 的值更改为 0.13 ，然后单击 Add 。

The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of Interest_Rate to 0.13 and click Add.

scenario values dialog box

将显示 Add Scenario 对话框。请注意，“C3、C4、C5”将显示在“变化单元格”下的框中。

The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the box under changing cells.

  1. In the Scenario Name box, type Scenario 3.

  2. Edit the text in the Comment box as – Different no. of Payments.

  3. Select Prevent changes under Protection and click OK.

scenario name box

将显示“方案值”对话框。初始值将显示在改变单元格中。将 NPER 的值更改为 300，然后单击 Add 。

The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of NPER to 300 and click Add.

change value

将显示 Add Scenario 对话框。请注意，“C3、C4、C5”将显示在“变化单元格”框中。

The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box.

  1. In the Scenario Name box, type Scenario 4.

  2. Edit the text in the Comment box as – Different Type of Payment.

  3. Select Prevent changes under Protection and click OK.

add scenario dialog box

将显示 Scenario Values 对话框。初始值将显示在改变单元格中。将“类型”的值更改为 1。单击“确定”，因为您已添加所有要添加的方案。

The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of Type to 1. Click OK as you have added all the scenarios that you wanted to add.

changed value

Scenario Manager 对话框出现。在情景下的框中，你将找到你已创建的所有情景的名称。

The Scenario Manager dialog box appears. In the box under Scenarios, You will find the names of all the scenarios that you have created.

  1. Click Scenario 1. As you are aware, Scenario 1 contains the initial values.

Scenario Summary Reports

Excel 提供两种类型的“情景摘要”报表 -

Excel provides two types of Scenario Summary reports −

  1. Scenario summary.

  2. Scenario PivotTable report.

在“情景摘要”对话框中，你可以找到这两个“报表”类型。

In the Scenario Summary dialog box, you can find these two Report types.

选择“报表类型”下的“情景摘要”。

Select Scenario summary under Report type.

select scenario summary

Scenario Summary

在 Result cells 框中，选择单元格 C6 （在此，我们已放置 PMT 函数）。单击“确定”。

In the Result cells box, select the cell C6 (Here, we had put the PMT function). Click OK.

“情景摘要”报表会出现在一个新的工作表中。工作表命名为“情景摘要”。

Scenario Summary report appears in a new worksheet. The worksheet is named as Scenario Summary.

scenario summary

在“情景摘要”报表中，你可以观察到以下内容 -

You can observe the following in the Scenario Summary report −

  1. Changing Cells − Enlists all the cells used as changing cells. As you have named the cells, Interest_Rate, NPER and Type, these appear to make the report meaningful. Otherwise, only cell references will be listed.

  2. Result Cells − Displays the result cell specified, i.e. EMI.

  3. Current Values − It is the first column and enlists the values of that scenario which is selected in the Scenario Manager Dialog box before creating the summary report.

  4. For all the scenarios you have created, the changing cells will be highlighted in gray.

  5. In the EMI row, the result values for each scenario will be displayed.

可以通过显示在创建情景时添加的注释，使报表更有意义。

You can make the report more meaningful by displaying the comments that you added while creating the scenarios.

  1. Click the + button to the left of the row containing the scenario names. The comments for the scenarios appear in the row under the scenario names.

click plus button

Scenarios from Different Sources

假设你从三个不同来源获取情景，且你需要在主工作簿中准备“情景摘要”报表。你可以通过将不同工作簿中的情景合并到主工作簿中来完成此操作。按照以下步骤操作 -

Suppose you get the scenarios from three different sources and you need to prepare the Scenario summary report in a Master workbook. You can do this by merging the scenarios from different workbooks into the Master workbook. Follow the steps given below −

  1. Assume that the scenarios are in the workbooks, Bank1_Scenarios, Bank2_Scenarios and Bank3_Scenarios. Open the three workbooks.

  2. Open the Master workbook, in which you have the initial values.

  3. Click DATA > What-if Analysis > Scenario Manager in the Master workbook.

出现 Scenario Manager 对话框。

The Scenario Manager Dialog box appears.

scenarios from different sources

正如你所见，没有场景，因为你尚未添加任何场景。单击 Merge 。

As you can observe, there are no scenarios as you have not yet added any. Click Merge.

出现“合并场景”对话框。

The Merge Scenarios dialog box appears.

merge scenarios

正如你所见，在“从以下位置合并场景”下，有两个框 −

As you can see, under Merge scenarios from, you have two boxes −

  1. Book

  2. Sheet

你可以从特定工作簿中选择包含场景的特定工作表，这些场景想要添加到你的结果中。单击 Book 的下拉箭头以查看工作簿。

You can select specific worksheet from a specific workbook that contains the scenarios, which you want to add to your results. Click the drop-down arrow of Book to see the workbooks.

Note − 对应的文件应在列表中打开以显示。

Note − The corresponding workbooks should be open to appear in this list.

merge scenarios dialog box

选择文件 – Bank1_Scenarios 。

Select the book – Bank1_Scenarios.

显示 Bank1 工作表。在对话框底部，显示源工作表中找到的场景数。单击确定。

Bank1 sheet is displayed. At the bottom of the dialog box, the number of scenarios found on source sheet is displayed. Click OK.

select book

将出现“场景管理器”对话框。合并到“主”工作簿中的两个场景将在“场景”下按列表展示。

The Scenario Manager dialog box appears. The two scenarios that were merged into the Master workbook will be listed under Scenarios.

master workbook

单击 Merge 按钮。 Merge Scenarios 对话框将显示。现在，从“文件”框的下拉列表中选择 Bank2_Scenarios 。

Click the Merge button. The Merge Scenarios dialog box appears. Now, select Bank2_Scenarios from the drop-down list in the Book box.

显示 Bank2 工作表。在对话框底部，显示源工作表中找到的场景数。单击确定。

Bank2 sheet is dislayed. At the bottom of the dialog box, the number of scenarios found on source sheet are displayed. Click OK.

source sheet displayed

出现 Scenario Manager 对话框。合并到“主”工作簿中的四个场景在“场景”下按列表展示。

The Scenario Manager Dialog box appears. The four scenarios that were merged into the Master workbook are listed under Scenarios.

scenarios

单击 Merge 按钮。 Merge Scenarios 对话框显示。现在，从“文件”框的下拉列表中选择 Bank3_Scenarios 。

Click the Merge button. The Merge Scenarios dialog box appears. Now, select Bank3_Scenarios from the drop-down list in the Book box.

显示 Bank3 工作表。在对话框底部，将显示源工作表中找到的场景数。单击确定。

Bank3 sheet is displayed. At the bottom of the dialog box, the number of scenarios found on source sheet will be displayed. Click OK.

scenario found

将出现“场景管理器”对话框。合并到“主”工作簿中的五个场景将在“场景”下按列表展示。

The Scenario Manager Dialog box appears. The five scenarios that were merged into the Master workbook will be listed under Scenarios.

scenarios merged

现在，你有生成场景摘要报表所需的所有场景。

Now, you have all the required scenarios to produce the Scenario summary report.

单击“摘要”按钮。出现 Scenario Summary 对话框。

Click the Summary button. The Scenario Summary dialog box appears.

  1. Select Scenario summary.

  2. In the Result cells box, type C6 and click OK.

result cells box

场景摘要报表将显示在“主”工作簿中的新工作表上。

The Scenario summary report appears on a new worksheet in the Master workbook.

new worksheet summary

Displaying Scenarios

假设你演示你的方案，并且希望动态地从一个方案切换到另一个方案，并显示相应方案的输入值和结果值集合。

Suppose you are presenting your scenarios and you would like to dynamically switch from one scenario to another and display the set of input values and result values of the corresponding scenario.

  1. Click DATA > What-if Analysis > Scenario Manager from the Data Tools group. The Scenario Manager Dialog box appears. The list of scenarios appear.

  2. Select the scenario you want to display. Click Show.

displaying scenarios

工作表上的值将更新为所选方案。结果值将被重新计算。

The values on the worksheet are updated to that of the selected scenario. The result values are recalculated.

selected scenario

Scenario PivotTable Report

你也可以在数据透视表的格式下看到方案报告。

You can see the Scenario report in the form of a PivotTable also.

  1. Click the Summary button in the Scenario Manager Dialog box. The Scenario Summary dialog box appears.

  2. Select the Scenario PivotTable report under Report type.

  3. Type C6 in the Result cells box.

scenario pivottable report

方案数据透视表报告将显示在一个新工作表上。

Scenario PivotTable report appears on a new worksheet.

scenario pivottable report on worksheet