Excel Data Analysis 简明教程
What-If Analysis with Data Tables
使用 Excel 中的数据表，您可以轻松地改变一个或两个输入并执行假设分析。数据表是一组单元格，您可以在其中更改某些单元格中的值，并针对问题想出不同的答案。
With a Data Table in Excel, you can easily vary one or two inputs and perform What-if analysis. A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem.
有两种类型的数据表 −
There are two types of Data Tables −
-
One-variable Data Tables
-
Two-variable Data Tables
如果您的分析问题中有多个变量，您需要使用 Excel 的情景管理器工具。有关详细信息，请参阅本教程中的章节 - What-If Analysis with Scenario Manager 。
If you have more than two variables in your analysis problem, you need to use Scenario Manager Tool of Excel. For details, refer to the chapter – What-If Analysis with Scenario Manager in this tutorial.
One-variable Data Tables
如果您想了解一个变量的不同值如何在一条或多条公式中改变这些公式的结果，可以使用一个变量数据表。换句话说，使用一个变量数据表，您可以确定更改一个输入如何改变任意数量的输出。您将借助示例了解这一点。
A one-variable Data Table can be used if you want to see how different values of one variable in one or more formulas will change the results of those formulas. In other words, with a one-variable Data Table, you can determine how changing one input changes any number of outputs. You will understand this with the help of an example.
Example
有一笔 5,000,000 美元的贷款，期限为 30 年。您想了解不同利率的每月还款 (EMI)。您可能还想知道第二年支付的利息和本金金额。
There is a loan of 5,000,000 for a tenure of 30 years. You want to know the monthly payments (EMI) for varied interest rates. You also might be interested in knowing the amount of interest and Principal that is paid in the second year.
Analysis with One-variable Data Table
使用一个变量数据表进行分析需要三个步骤 −
Analysis with one-variable Data Table needs to be done in three steps −
Step 1 - 设置所需的背景。
Step 1 − Set the required background.
Step 2 - 创建数据表。
Step 2 − Create the Data Table.
Step 3 - 执行分析。
Step 3 − Perform the Analysis.
让我们仔细了解这些步骤：
Let us understand these steps in detail −
Step 1: Set the required background
-
Assume that the interest rate is 12%.
-
List all the required values.
-
Name the cells containing the values, so that the formulas will have names instead of cell references.
-
Set the calculations for EMI, Cumulative Interest and Cumulative Principal with the Excel functions – PMT, CUMIPMT and CUMPRINC respectively.
您的工作表应如下所示：
Your worksheet should look as follows −
您可以看到，C 列中的单元格的名称与 D 列相应单元格中给出的名称相同。
You can see that the cells in column C are named as given in the corresponding cells in column D.
Step 2: Create the Data Table
-
Type the list of values i.e. interest rates that you want to substitute in the input cell down the column E as follows −
-
Type the first function (PMT) in the cell one row above and one cell to the right of the column of values. Type the other functions (CUMIPMT and CUMPRINC) in the cells to the right of the first function. Now, the two rows above the Interest Rate values look as follows −
Step 3: Do the analysis with the What-If Analysis Data Table Tool
-
Select the range of cells that contains the formulas and values that you want to substitute, i.e. select the range – E2:H13.
-
Click the DATA tab on the Ribbon.
-
Click What-if Analysis in the Data Tools group.
-
Select Data Table in the dropdown list.
出现 Data Table 对话框。
Data Table dialog box appears.
-
Click the icon in the Column input cell box.
-
Click the cell Interest_Rate, which is C2.
您可以看到列输入单元格被视为 $C$2。单击确定。
You can see that the Column input cell is taken as $C$2. Click OK.
数据表将填充为每个输入值计算的结果，如下所示：
The Data Table is filled with the calculated results for each of the input values as shown below −
如果您能支付 54,000 的 EMI，您可以观察到 12.6% 的利率适合您。
If you can pay an EMI of 54,000, you can observe that the interest rate of 12.6% is suitable for you.
Two-variable Data Tables
如果您想了解公式中两个变量的不同值将如何改变该公式的结果，则可以使用两个变量的数据表。换句话说，使用两个变量的数据表，您可以确定更改两个输入如何改变单个输出。您将在一个示例的帮助下了解这一点。
A two-variable Data Table can be used if you want to see how different values of two variables in a formula will change the results of that formula. In other words, with a twovariable Data Table, you can determine how changing two inputs changes a single output. You will understand this with the help of an example.
Example
有一笔 50,000,000 的贷款。您想知道利率和贷款期限的不同组合将如何影响每月还款额 (EMI)。
There is a loan of 50,000,000. You want to know how different combinations of interest rates and loan tenures will affect the monthly payment (EMI).
Analysis with Two-variable Data Table
需要分三步进行包含两个变量的数据表格分析：
Analysis with two-variable Data Table needs to be done in three steps −
Step 1 - 设置所需的背景。
Step 1 − Set the required background.
Step 2 - 创建数据表。
Step 2 − Create the Data Table.
Step 3 - 执行分析。
Step 3 − Perform the Analysis.
Step 1: Set the required background
-
Assume that the interest rate is 12%.
-
List all the required values.
-
Name the cells containing the values, so that the formula will have names instead of cell references.
-
Set the calculation for EMI with the Excel function – PMT.
您的工作表应如下所示：
Your worksheet should look as follows −
可以看到，列 C 中的单元格被命名为列 D 中相应单元格中的名称。
You can see that the cells in the column C are named as given in the corresponding cells in the column D.
Step 2: Create the Data Table
-
Type =EMI in cell F2.
-
Type the first list of input values, i.e. interest rates down the column F, starting with the cell below the formula, i.e. F3.
-
Type the second list of input values, i.e. number of payments across row 2, starting with the cell to the right of the formula, i.e. G2. The Data Table looks as follows −
Do the analysis with the What-If Analysis Tool Data Table
-
Select the range of cells that contains the formula and the two sets of values that you want to substitute, i.e. select the range – F2:L13.
-
Click the DATA tab on the Ribbon.
-
Click What-if Analysis in the Data Tools group.
-
Select Data Table from the dropdown list.
“数据表格”对话框将出现。
Data Table dialog box appears.
-
Click the icon in the Row input cell box.
-
Click the cell NPER, which is C3.
-
Again, click the icon in the Row input cell box.
-
Next, click the icon in the Column input cell box.
-
Click the cell Interest_Rate, which is C2.
-
Again, click the icon in the Column input cell box.
您将看到，行输入单元格取为 $C$3，而列输入单元格取为 $C$2。单击确定。
You will see that the Row input cell is taken as $C$3 and the Column input cell is taken as $C$2. Click OK.
数据表将使用两个输入值的每种组合计算出结果并填充数据表：
The Data Table gets filled with the calculated results for each combination of the two input values −
如果您能支付 54,000 的 EMI，那么利率为 12.2%，288 期 EMI 适合您。这意味着贷款期限将为 24 年。
If you can pay an EMI of 54,000, the interest rate of 12.2% and 288 EMIs are suitable for you. This means the tenure of the loan would be 24 years.
Data Table Calculations
每次包含数据表的电子表格重新计算时，即使数据表没有更改，也会对数据表重新计算。若要加快包含数据表的工作表的计算速度，则需要更改计算选项，在下一部分中将为此提供说明，以 Automatically Recalculate 工作表，但是不更改数据表。
Data Tables are recalculated each time the worksheet containing them is recalculated, even if they have not changed. To speed up the calculations in a worksheet that contains a Data Table, you need to change the calculation options to Automatically Recalculate the worksheet but not the Data Tables, as given in the next section.
Speeding up the Calculations in a Worksheet
可以通过两种方式加快包含数据表的工作表的计算速度 −
You can speed up the calculations in a worksheet containing Data Tables in two ways −
-
From Excel Options.
-
From the Ribbon.
From Excel Options
-
Click the FILE tab on the Ribbon.
-
Select Options from the list in the left pane.
“Excel 选项”对话框出现。
Excel Options dialog box appears.
-
From the left pane, select Formulas.
-
Select the option Automatic except for data tables under Workbook Calculation in the Calculation options section. Click OK.
