Excel Data Analysis 简明教程
What-If Analysis with Data Tables
使用 Excel 中的数据表，您可以轻松地改变一个或两个输入并执行假设分析。数据表是一组单元格，您可以在其中更改某些单元格中的值，并针对问题想出不同的答案。
有两种类型的数据表 −
-
One-variable Data Tables
-
Two-variable Data Tables
如果您的分析问题中有多个变量，您需要使用 Excel 的情景管理器工具。有关详细信息，请参阅本教程中的章节 - What-If Analysis with Scenario Manager 。
One-variable Data Tables
如果您想了解一个变量的不同值如何在一条或多条公式中改变这些公式的结果，可以使用一个变量数据表。换句话说，使用一个变量数据表，您可以确定更改一个输入如何改变任意数量的输出。您将借助示例了解这一点。
Example
有一笔 5,000,000 美元的贷款，期限为 30 年。您想了解不同利率的每月还款 (EMI)。您可能还想知道第二年支付的利息和本金金额。
Analysis with One-variable Data Table
使用一个变量数据表进行分析需要三个步骤 −
Step 1 - 设置所需的背景。
Step 2 - 创建数据表。
Step 3 - 执行分析。
让我们仔细了解这些步骤：
Step 1: Set the required background
-
假设利率是 12%。
-
列出所有必需的值。
-
为包含值的单元格命名，以便公式具有名称而不是单元格引用。
-
使用 Excel 函数分别设置 EMI、累计利息和累计本金的计算 - PMT、CUMIPMT 和 CUMPRINC。
您的工作表应如下所示：
您可以看到，C 列中的单元格的名称与 D 列相应单元格中给出的名称相同。
Step 2: Create the Data Table
-
按如下步骤在列 E 中键入您要替换在输入单元格中的值列表，即利率：
-
在值列上方一行，向右一个单元格的单元格中键入第一个函数 ( PMT )。在第一个函数右边的单元格中键入其他函数 ( CUMIPMT and CUMPRINC )。现在，利率值之上的两行如下所示：
Step 3: Do the analysis with the What-If Analysis Data Table Tool
-
选择包含您想要替换的公式和值的单元格范围，即选择范围 - E2:H13。
-
单击功能区上的“数据”选项卡。
-
单击“数据工具”组中的“假设分析”。
-
从下拉列表中选择数据表。
出现 Data Table 对话框。
-
单击列输入单元格框中的图标。
-
单击单元格 Interest_Rate ，即 C2。
您可以看到列输入单元格被视为 $C$2。单击确定。
数据表将填充为每个输入值计算的结果，如下所示：
如果您能支付 54,000 的 EMI，您可以观察到 12.6% 的利率适合您。
Two-variable Data Tables
如果您想了解公式中两个变量的不同值将如何改变该公式的结果，则可以使用两个变量的数据表。换句话说，使用两个变量的数据表，您可以确定更改两个输入如何改变单个输出。您将在一个示例的帮助下了解这一点。
Example
有一笔 50,000,000 的贷款。您想知道利率和贷款期限的不同组合将如何影响每月还款额 (EMI)。
Analysis with Two-variable Data Table
需要分三步进行包含两个变量的数据表格分析：
Step 1 - 设置所需的背景。
Step 2 - 创建数据表。
Step 3 - 执行分析。
Step 1: Set the required background
-
假设利率是 12%。
-
列出所有必需的值。
-
对包含值的单元格进行命名，以便公式具有名称而不是单元格引用。
-
使用 Excel 函数 PMT 设置 EMI 的计算。
您的工作表应如下所示：
可以看到，列 C 中的单元格被命名为列 D 中相应单元格中的名称。
Step 2: Create the Data Table
-
在单元格 F2 中输入 =EMI 。
-
键入第一个输入值列表，即沿列 F 向下排列的利率，从公式下方的单元格（即 F3）开始。
-
键入第二个输入值列表，即沿行 2 向右排列的付款数，从公式右边的单元格（即 G2）开始。数据表如下所示：
Do the analysis with the What-If Analysis Tool Data Table
-
选择包含公式以及要替换的两组值的单元格区域，即选择区域 F2:L13。
-
单击功能区上的“数据”选项卡。
-
单击“数据工具”组中的“假设分析”。
-
从下拉列表中选择“数据表格”。
“数据表格”对话框将出现。
-
单击“行输入单元格”框中的图标。
-
单击单元格 NPER （即 C3）。
-
再次单击“行输入单元格”框中的图标。
-
接下来，单击“列输入单元格”框中的图标。
-
单击单元格Interest_Rate（即 C2）。
-
再次单击“列输入单元格”框中的图标。
您将看到，行输入单元格取为 $C$3，而列输入单元格取为 $C$2。单击确定。
数据表将使用两个输入值的每种组合计算出结果并填充数据表：
如果您能支付 54,000 的 EMI，那么利率为 12.2%，288 期 EMI 适合您。这意味着贷款期限将为 24 年。
