excel高级应用.docx

《excel高级应用.docx》由会员分享，可在线阅读，更多相关《excel高级应用.docx（5页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、实验5 Excel高级功能一.实验目的1 .掌握Excel软件的几种高级功能，包括单变量求解、模拟运算表和有效性等。二.实验内容及步骤打开Excel全部示例工作表文件（qbsl.xls）,在该表中完成如下实验内容。1 .单变量求解根据数据表中的数据，由自己还款能力反推贷款时间，打开“单变量求解”工作表。 数据表如下5-1所示。表5-1单变量求解AB1贷款数1000002期限1803利率7. 02%4支付=PMT (B3/12,B2,B1)（1）公式的输入B4单元格输入5-1中所示公式。其含义为：B3单元格中的年利率除以12个月变 换为月利率；B2中存储的为贷款月数；B1中存储的为贷款总额；计算

2、结果，即 确定后B4中显示为月还款数额，负数。（2）根据月还款能力反推贷款期限选中B4,打开工具菜单下的“单变量求解”对话框，如图5.2所示。目标单元格选中B4,目标值设为自己的月还款额（可多设几个看结果变化），可 变单元格设为B2 （即期限）。单击确定后，看B2单元格中的变化。图5.1单变量求解对话框（3）根据不同情况，自行设计不同的数据情况，自主练习。2 .双变量模拟运算表根据数据表中的数据，计算在不同贷款年限和不同年利率的情况下，月还款额情况。打开“模拟运算表”工作表,AB1贷款金额2700002贷款年数io3年利率0.04594数据表如下5-2所示。表5-2模拟运算表CD=PMT(B3

3、/12,B2*12,-B1)100. 04590.05580. 0509EFGH贷 款年限15202530（1）输入公式表5-2中数据情况说明：A歹！J：说明文字B1：贷款总额B2：贷款年数B3：年利率C2：计算月还款额的公式，含义参见“单变量求解”部分。（2）准备模拟运算环境合并DI： H1单元格，输入贷款年限分别在DI： H1单元格输入10、15、20、25、30,表示不同的贷款年限（此处 可根据自己的需要输入其他不同年限）分别在C3： C5单元格输入不同的年利率（此处可根据自己的需要输入不同的年 利率）（3）求解模拟运算表选中C2： H5区域单击数据分模拟运算表输入引用行的单元格位置选择

4、B2 （即贷款年限）输入引用列的单元格位置选择B3 （即利率）单击确定，求出的数据即为不同年限和不同利率对应的不同月还款额度。图5.2模拟运算表对话框(4)根据不同情况，自行设计不同的数据情况，自主练习。3 .公式审核打开“公式审核”工作表。(1)追踪引用单元格选中E列中某一单元格，例如E14工具分公式审核令追踪引用单元格观察结果(2)追踪从属单兀格选中C列或D列中某一单元格，例如CIO工具分公式审核令追踪从属单元格观察结果(3)取消追踪工具今公式审核分取消所有箭头4 .保护(1)设置保护工具今保护今设密码(2)解除保护工具今解除保护分输入密码5 .有效性(1)有效性原始选择区设置打开“有效性

5、原始选择区”工作表，如表5-3所示。表5-3有效性原始选择区A B目算学语理科计数英物等级AB选择A2:A4区域插入分名称分定义分输入“科目”(2)按名称设置有效性打开“有效性”工作表，如表5-4所示。表5-4有效性ABCD1学生选课情况2学号姓名选择科目等级3110101小李计算机B4110102小王数学A5110103小张6110104小孙7110105小赵8110106小韩9110107小方10110108小罗11110109小石12110110小巢选中C3： C12数据分有效性设置页分允许9序列，来源输入“二科目”确定单击C3,出现小按钮，单击该按钮，出现下拉列表，可选择科目（3）按输入内容设定有效性选中D3： D12数据少有效性设置页9允许分序列，来源输入“A, B”确定单击D3,出现小按钮，单击该按钮，出现下拉列表，可选择科目（4）设置数字范围选中E3数据f有效性设置页少允许9整数，数据9介于，设置好最小值5最大值10确定分别在E3单元格中输入3、15、7,看结果（5）根据不同情况，自行设计不同的数据情况，自主练习。