模拟运算表(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)两点说明
由于模拟运算表的计算结果为数组形式,所以应将结果中的所有值转换为常量,便于以后在其他环境下调用。具体操作方法是:
①在模拟运算表中选中所有计算结果。
②单击“常用”工具栏上的“复制”按钮,并选中粘贴区域的左上角单元格。
③单击“粘贴”旁边的箭头,再单击“数据”按钮。
当要清除模拟运算表计算的结果时,也要按清除数组的方式进行,即选中整个数组进行清除数组的方式进行,即选中整个数组进行清除。
每当重新计算工作表时,也会同时重新计算模拟运算表,而不管它们是否被更改过。若要加速包含模拟运算表的工作表的计算速度,可以更改工作表的“重新计算”选项,使工作表被自动重新计算时不计算其中的模拟运算表。
免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。