模拟运算表的应用及计算策略分析

模拟运算表（Data Tables）是一个单元格区域，用于显示公式中某些值的更改对公式的影响。模拟运算表提供了一种快捷手段，它可以通过一步操作计算出公式在多种情况下的值，同时它还是一种有效的方法，可以查看和比较由某些值的变化所引起的各种不同结果。由于模拟运算表中用于计算公司结果的参量取值是认为确定的，所以这种方法称为“模拟”。在金融财务领域中，敏感性分析是一种非常有用的分析手段。如贷款期限对利率的敏感性，投资评估对贴现率的敏感性、盈亏平衡点对成本的敏感性等。Excel中的模拟运算表就非常适合于做各种敏感性分析。

将模拟运算表和单变量求解相比较，两者的作用都是了解当可变单元格数据变化时，公式计算结果的变化。但是不同的是，单变量求解是确定公式计算结果以后由Excel通过对可变单元格的不同值进行试算，求得满足条件的解；而模拟运算表则是由使用者预先给可变定单元格的一组数据，分别求出公式的值。此外模拟运算表还可以同时分析两个变量同时变化时公式的结果，据此可以将模拟运算表分为单变量模拟运算表和双变量模拟运算表。调用模拟运算表工具的操作方法很简单，执行菜单条上的“数据—模拟运算表”就可以打开“模拟运算表”对话框。

（1）创建单变量模拟运算表

在使用中，单变量模拟运算表应设计成其输入数值被排在一列或一行中。单变量模拟运算表中使用公式必须引用输入单元格。所谓的“输入单元格”，其含义是设定的一组值将通过该单元格在公式中的位置被公式引用，从而引起公式的变化。工作表中的任意单元格都可以用作输入单元格，只要公式中引用了它。

下面仍然使用分期偿还贷款的示例，讲解单变量模拟运算表的创建过程，如图2．11、图2．12和图2．13所示。

①在一行或一列中，输入要替换工作表上的输入单元格的数值序列，本例中是C3：C7的列，其中是设定的贷款金额。

图2．11

图2．12

②如果模拟运算表是列方向的，则在第一个数值的上一行且位于数值列右边的单元格中键入公式，本例中是D2单元格的公式“＝B7”。

③如果模拟运算表是行方向的，则在第一个数值的上一行且位于数值列下方的单元格中键入公式。

④选定包含公式和需要被替换的数值的单元格区域，本例中是C2：D7。

⑤在“数据”菜单上单击“模拟运算表”。

⑥如果模拟运算表是列方向的，则在“输入引用列的单元格”框中，为输入单元格键入单元格引用，本例中是B3。

⑦如果模拟运算表是行方向的，则在“输入引用行的单元格”框中，为输入单元格键入单元格引用。

按“确定”按钮后，就可以得到如图2．13所示的结果。其中D3：D7显示的是对应于“贷款金额”取C3：C7中的值，按D1中公式计算的“每年还款”的值。当模拟运算表创建完成后，再选择D3：D7范围内的单元格可以发现，在编辑区出现了“｛＝表（，B3）｝”的信息，这表明在该区域内，通过模拟运算表的计算而生成了数值。

图2．13

（2）创建双变量模拟运算表

双变量模拟运算表中的两组数据使用同一个公式。这个公式必须引用两个不同输入单元格。下面参照示例，讲解双变量模拟运算表的创建过程。上一小节在贷款金额取不同数值的情况下，计算了“每年还款”的金额。如果同时变化“贷款年限”，结果会怎样呢？如图2．14所示，仍然保留设定的一组“贷款金额”值。在此基础上再给定一组不同的“贷款年限”值：3年，4年，5年，6年，然后按如下步骤操作：(www.xing528.com)

①在工作表的C9单元格输入公式“＝B7”。

②在公式下方的同一列中C10：C14输入第一组数据——“贷款金额”的不同值。

③在公式右边的同一行中D9：F9输入第二组数据——“贷款年限”的不同值。

④选择包含公式以及数据行和列的单元格区域，即区域C9：F14。

⑤在“数据”菜单上单击“模拟运算表”。

图2．14

⑥在“输入引用行的单元格”框中，输入由行数值替换的输入单元格的引用“B5”，即“贷款年限”。

⑦在“输入引用列的单元格”框中，输入由列数值替换的输入单元格的引用“B3”，即“贷款金额”。

⑧按“确定”按钮后，就可以得到如图2．15所示的结果。其中C9：F14显示的是对应于“贷款金额”取C10：C14中的值，贷款年限采用D9：F9中的值，按C9中公式计算的“每年还款”的值。当模拟运算表创建完成后，再选择C9：F14范围内的单元格可以发现，在编辑区出现了“｛＝表（B5，B3）｝”的信息，这表明在该区域内，通过模拟运算表的计算而生成了数值。

图2．15

（3）两点说明

由于模拟运算表的计算结果为数组形式，所以应将结果中的所有值转换为常量，便于以后在其他环境下调用。具体操作方法是：

①在模拟运算表中选中所有计算结果。

②单击“常用”工具栏上的“复制”按钮，并选中粘贴区域的左上角单元格。

③单击“粘贴”旁边的箭头，再单击“数据”按钮。

当要清除模拟运算表计算的结果时，也要按清除数组的方式进行，即选中整个数组进行清除数组的方式进行，即选中整个数组进行清除。

每当重新计算工作表时，也会同时重新计算模拟运算表，而不管它们是否被更改过。若要加速包含模拟运算表的工作表的计算速度，可以更改工作表的“重新计算”选项，使工作表被自动重新计算时不计算其中的模拟运算表。