使用数据表进行假设分析
使用 Excel 中的数据表，你可以轻松更改一两个输入并执行假设分析。数据表是一系列单元格，你可以在其中更改某些单元格中的值并为问题提供不同的答案。
数据表有两种类型：
- 一变量数据表
- 二变量数据表
如果你的分析问题中有两个以上的变量，则需要使用 Excel 的 Scenario Manager Tool。详情请参阅章节—— 使用场景管理器进行假设分析 在本教程中。
一变量数据表
如果你想了解一个或多个公式中一个变量的不同值将如何改变这些公式的结果，可以使用单变量数据表。换句话说，使用单变量数据表，你可以确定更改一个输入如何更改任意数量的输出。借助示例，你将了解这一点。
例子
有一笔 5,000,000 的贷款，期限为 30 年。你想知道不同利率的月供 (EMI)。你可能还想知道第二年支付的利息和本金金额。
单变量数据表分析
单变量数据表分析需要分三步进行：
步骤 1 ： 设置需要的背景。
步骤 2 ： 创建数据表。
步骤 3 ：进行分析。
让我们详细了解这些步骤：
第一步：设置所需的背景
-
假设利率为 12%。
-
列出所有必需的值。
-
为包含值的单元格命名，以便公式具有名称而不是单元格引用。
-
分别使用 Excel 函数 PMT、CUMIPMT 和 CUMPRINC 设置 EMI、累积利息和累积本金的计算。
你的工作表应如下所示：
你可以看到 C 列中的单元格的名称与 D 列中相应单元格的名称相同。
第 2 步：创建数据表
-
在 E 列下方的输入单元格中键入要替换的值列表，即利率，如下所示：
-
键入第一个函数 ( PMT ) 在值列上方一排和右侧一单元格中。键入其他函数 ( CUMIPMT 和 CUMPRINC ) 在第一个函数右侧的单元格中。
现在，利率值上方的两行如下所示：
如你所见，利率值上方有一个空行。此行用于你要使用的公式。
数据表如下：
第 3 步：使用假设分析数据表工具进行分析
-
选择包含要替换的公式和值的单元格范围，即选择范围 - E2:H13。
-
单击功能区上的数据选项卡。
-
单击数据工具组中的假设分析。
-
在下拉列表中选择数据表。
数据表 对话框出现。
- 单击列输入单元格框中的图标。
- 单击单元格 利率 ，即 C2。
你可以看到 Column 输入单元格被视为 $C$2。单击确定。
数据表填充了每个输入值的计算结果，如下所示：
如果你可以支付 54,000 的 EMI，你可以观察到 12.6% 的利率适合你。
二变量数据表
如果你想查看公式中两个变量的不同值将如何改变该公式的结果，可以使用双变量数据表。换句话说，使用双变量数据表，你可以确定更改两个输入如何更改单个输出。借助示例，你将了解这一点。
例子
有50,000,000的贷款。你想知道利率和贷款期限的不同组合将如何影响月供 (EMI)。
用二元数据表分析
使用二元数据表进行分析需要分三步进行：
步骤 1 ： 设置需要的背景。
步骤 2 ： 创建数据表。
步骤 3 ：进行分析。
第一步：设置所需的背景
-
假设利率为 12%。
-
列出所有必需的值。
-
为包含值的单元格命名，以便公式具有名称而不是单元格引用。
-
使用 Excel 函数设置 EMI 计算 – PMT .
你的工作表应如下所示：
你可以看到 C 列中的单元格被命名为 D 列中相应单元格中给出的名称。
第 2 步：创建数据表
-
Type =EMI 在单元格 F2 中。
-
键入输入值的第一个列表，即 F 列下的利率，从公式下方的单元格开始，即 F3。
-
输入第二个输入值列表，即第 2 行的付款次数，从公式右侧的单元格开始，即 G2。
数据表如下：
使用假设分析工具数据表进行分析
-
选择包含公式和要替换的两组值的单元格区域，即选择区域 - F2:L13。
-
单击功能区上的数据选项卡。
-
单击数据工具组中的假设分析。
-
从下拉列表中选择数据表。
出现数据表对话框。
- 单击行输入单元格框中的图标。
- 单击单元格 NPER ，即 C3。
- 再次单击行输入单元格框中的图标。
- 接下来，单击列输入单元格框中的图标。
- 单击单元格 Interest_Rate，即 C2。
- 再次单击列输入单元格框中的图标。
你将看到 Row 输入单元格为 $C$3，Column 输入单元格为 $C$2。单击确定。
数据表填充了两个输入值的每个组合的计算结果：
如果你可以支付 54,000 的 EMI，那么 12.2% 的利率和 288 的 EMI 适合你。这意味着贷款的期限为 24 年。
数据表计算
每次重新计算包含它们的工作表时，都会重新计算数据表，即使它们没有更改。要加快包含数据表的工作表中的计算，你需要将计算选项更改为 自动重新计算 工作表，而不是数据表，如下一节所述。
加快工作表中的计算
你可以通过两种方式加速包含数据表的工作表中的计算：
- 从 Excel 选项。
- 从丝带。
从 Excel 选项
- 单击功能区上的文件选项卡。
- 从左侧窗格的列表中选择选项。
出现 Excel 选项对话框。
-
从左侧窗格中，选择 Formulas .
-
选择选项 自动，数据表除外 under 工作簿计算 在计算选项部分。单击确定。
从丝带
-
单击功能区上的公式选项卡。
-
点击 计算选项 在计算组中。
-
Select 自动，数据表除外 在下拉列表中。
