双变量数据表 | 投资银行Excel建模分析师手册

如需评估两个输入变量的敏感性，可使用双变量数据表。双变量数据表用来分析其他因素不变时，两个参数的变化对目标值的影响。

例如，要计算一笔贷款的分期偿还额，可使用Excel提供的财务函数PMT。如需同时分析不同利率水平和不同贷款年限对每月分期还款额的影响，可使用双变量数据表进行评估，快速生成不同的利率和贷款年限取值时对应的还款金额表。使用双变量数据表进行敏感性分析的基本步骤如下。

■ 将需评估的两个变量的不同取值分别按行和按列排列，例如，将贷款年限的不同取值横向排列，贷款年利率的不同取值纵向排列。

■ 在数据表表头即行变量和列变量的交叉处引用包含计算公式的目标单元格，本例中的表头位于G2单元格，所需评估的目标单元格位于C9，因此在G2单元格中输入公式“=C9”（见图3-75）。

图3-75 双变量数据表的数据布局

■ 选中包含表头、行变量和列变量的全部数据区域，本例为G2∶M9，使用快捷方式<Alt+A+W+T>键或通过菜单调出“数据表”对话框。

■ 数据表要求输入引用行和引用列所在的单元格，即可变参数所在的单元格。本例包含两个可变参数，其中贷款年限在行，贷款利率在列，因此在数据表对话框中“输入引用行的单元格”内引用贷款年限在原模型中的所在位置即单元格C4，在“输入引用列的单元格”内引用贷款利率在原模型中的所在位置，即单元格C3（见图3-76）。

图3-76 双变量敏感性分析(www.xing528.com)

■ 单击确定，即可获得如图3-77所示的敏感性分析结果。

图3-77 双变量数据表结果

使用双变量数据表的几个注意事项。

■ 双变量数据表的表头（如前例中的G2单元格）必须包含公式，这是Excel数据表根据不同的输入变量进行重新计算的目标单元格。

■ 为检查数据表返回结果是否正确，可对照原模型的计算结果和数据表中对应输入参数的计算结果是否一致，如原模型的计算结果为1319.91，和数据表中20年及5%年利率情况的结果一致。

■ 数据表和评估变量必须位于同一工作表，即“输入引用行的单元格”和“输入引用列的单元格”都不能引用其他工作表中的参数。如上例中，贷款年限和贷款利率变量都应和数据表位于同一工作表内。

■ 不能对数据表输出结果区域进行局部编辑，否则Excel会返回“不能更改数据表的某一部分”的错误信息。如需删除数据表，应选定全部的输出区域，而后使用<Delete>键删除全部数据。