实验6模拟运算

实验6模拟运算1.实验目的掌握Excel单变量模拟运算表和双变量模拟运算表的使用方法。2.实验内容及操作指导所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示计算公式中某些参数值的变化对计算结果的影响，也可以将所有不同的计算结果以列表方式显示出来，便于查看、比较和分析数据。根据分析计算公式中的参数个数，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。下面通过例子来介绍说明Excel中模拟运算的具体操作方法。准备每月存款20000元，5年后连本带利取回。假定有多家银行利率可供参考，应如何选择而使存款收益最高呢？使用Excel提供的单变量模拟运算表和双变量模拟运算表就可以很快得出答案。（1）单变量模拟运算表的操作步骤。其操作步骤如下：1）建立如图4.38所示的工作表。相关数据直接输入即可。5%的利率可看成是基准利率，总期数为60期，即5年，每月存款20000元（具体输入时为负数）。图4.38存款分析表2）选择单元格C6，单击菜单“插入”→“函数”，在函数类别中选择“财务”，在下拉列表框中选择FV函数。在弹出【函数参数】对话框中，如图4.39所示。在rate、nper、pmt文本框中分别输入“A3/12”、“B3”、“C3”，单击【确定】按钮。图4.39FV参数设置FV函数是基于固定利率及等额分期付款方式，返回某项投资的未来值。该函数的语法是:FV(rate，nper，pmt，pv，type)rate表示存款利率，这个利率一般被表示为一个年利率，必须把它除以期数。nper为存款总期数。pmt为各期所应存款的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。如果忽略pmt，则必须包括pv参数。pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略PV，则假设其值为零，并且必须包括pmt参数。type数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。①单元格C6中得到存款本息总和为1,360,121.66元，它是按月计复利而得的。计算方法如下：第1期的结果是20000。第2期的结果是40,083.33，本息总和=第1期的结果+20000×(1+5%÷12×1)。第3期的结果是60,250.35，本息总和=第2期的结果+20000×(1+5%÷12×2)+83.33×5%÷12×1。其他各期依此类推。②计算其他单元格的数据。选择单元格区域B6:C11，单击菜单“数据”→“模拟运算表”，在弹出的“模拟运算表”对话框中，如图4.40所示，单击“输入引用列的单元格”栏右侧的【折叠】按钮。在工作表“存款分析表”中选择单元格A3，单击“模拟运算表”中“输入引用列的单元格”文本框右侧的【折叠】按钮，展开对话框，单击【确定】按钮，如图4.41所示。图4.40参数设置图4.41参数设置结果③单元格区域C7:C11中得到在不同银行的本息和，如图4.42所示。图4.42不同银行的本息和提示：所谓引用列的单元格，即模拟运算表的模拟数据（最左列数据）要代替公式中的单元格地址。本例的模拟运算表是关于利率的模拟数据，所以指定$A$3，即年利率所在的单元格为引用列的单元格。为了方便，通常称其为模拟运算表的列变量。需要注意的是，C6单元格的结果是按利率5.00%计算出来的，并没有按照4.82%的利率计算，如果要按照4.82%的利率计算，需要把A3单元格的利率改为4.82%，结果应是1,353,893.36。（2）双变量模拟运算表的操作步骤。当其他因素不变时，两个参数的变化对目标值产生影响时，需要使用双变量模拟运算表。如果不仅要考虑利率的变化，还要选择不同存款额，分析不同的利率和不同的存款额对收益的影响，这就需要使用双变量模拟运算表。其操作步骤如下：1）建立如图4.43所示的工作表。相关数据直接输入即可。图4.43存款分析表2）选择单元格B6，单击菜单“插入”→“函数”，在函数类别中选择“财务”，在下拉列表框中选择FV函数。在弹出【函数参数】对话框中，如图4.39所示。在Rate、Nper、Pmt文本框中分别输入“A3/12”、“B3”、“C3”，单击【确定】按钮。3）计算其他单元格的数据。选择单元格区域B6:F12，单击菜单“数据”→“模拟运算表”，在弹出的“模拟运算表”对话框中，如图4.44所示，单击“输入引用列的单元格”栏右侧的【折叠】按钮。在工作表“存款分析表”中选择单元格A3，单击“模拟运算表”中“输入引用列的单元格”文本框右侧的【折叠】按钮，展开对话框，类似地，单击“输入引用行的单元格”栏右侧的【折叠】按钮，在工作表“存款分析表”中选择单元格C3，单击“模拟运算表”中“输入引用行的单元格”文本框右侧的【折叠】按钮，展开对话框，单击【确定】按钮，如图4.45所示。图4.44参数设置图4.45参数设置结果4）单元格区域C7:F12中得到在不同银行的存款额。如图4.46所示。图4.46不同存款额的本息和提示：所谓引用行的单元格，即模拟运算表的模拟数据要代替公式中的单元格地址。本例的模拟运算表是关于利率和存款金额的模拟数据，所以引用列的单元格指定$A$3，引用行的单元格指定$C$3，即年利率所在的单元格为引用列的单元格，通常称其为模拟运算表的列变量，每期存款所在的单元格为引用行的单元格，通常称其为模拟运算表的行变量。故称为双变量模拟运算表。虽然B6单元格的数据只是为了其他单元格的计算，在得到结果后可移动位置，但不可将其删除。