EXCEL数据分析工具应用

《EXCEL数据分析工具应用》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、数据分析工具应用本章学习目标1、了解加载宏的功能和用法2、掌握数据审核的方法3、掌握模拟运算表的建立方法4、掌握单变量求解的方法5、掌握方案建立和应用方法6、掌握规划求解的方法7、掌握分析工具库中的常用工具9.1数据审核及跟踪分析1、概念数据审核是一种查找单元格数据错误来源的工具，通过它可以快速地找出具有引用关系的单元格，借此分析造成错误的单元格。数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。2、数据审核的方式追踪引用单元格追踪从属单元格9.1.1追踪引用单元格引用单元格是指被其他单元格中的公式引用的单元格，即为指定公式提供数据的单元格。当

2、使用“追踪引用单元格”工具时，Excel会用蓝色追踪箭头指明活动工作表中为公式提供数据的单元格。如果选定单元格引用了其他工作表或工作簿中的单元格，Excel会用黑色追踪箭头从工作表图标指向选定的单元格。【例9.1】某商场的销售记录和销售汇总表如图所示。查看销售汇总表中各数据的来源是否正确。9.1.1追踪引用单元格单击公式选项卡单击包括有公式的单元格单击追踪引用单元格9.1.2追踪从属单元格从属单元格如果某个单元格中的公式引用了其它单元格，那么此单元格就称为从属单元格。当公式中所引用单元格的值发生变化时，公式所在单元格（从属单元格）中的值也会随之变化。例如，若B3单

3、元格中包含公式“=A3+A4”，则B3就是A3和A4的从属单元格。当A3或A4单元格的值发生变化时，B3单元格的值就会发生变化。9.1.1追踪引用单元格单击公式选项卡单击包括要追踪的单元格单击追踪从属单元格9.1.3数据有效性检验数据有效性数据有效性检验是Excel为减少错误、核查数据正确性而提供的一种数据检验工具。利用该工具可以设置单元格数据输入的类型和范围，对错误的输入数据进行告警，并拒不接受，把错误限制在输入阶段。此外，它还能对已经完成的数据表设置有效性检验规则，并据此标识出其中的错误数据。案例【例9.2】某班要建立一个成绩登记表，为了减少成绩输入错误，可对

4、成绩表中数据的输入类型及范围进行限制。限制学号为8位字符，不能小于8位，也不能多于8位。限制所有学科成绩为0~100之间的整数。限制科目列标题的取值范围，如“高数”不能输入为“高等数学”。9.1.3数据有效性检验成绩表示例9.1.3数据有效性检验限制方法单击“数据”选项卡中的“数据有效性”按钮限定数据类型限定方式设置数据大小范围或长度9.1.3数据有效性检验圈释无效数据1、按前面介绍的方法首先设置数据的有效值范围:0-100；2、单击“数据有效性”右边的下箭头，选择“圈释无效数据”Excel就会将不符合有效性规则的数据圈释出来9.1.3数据有效性检验设置输入提示信

5、息和错误警告信息这些信息都需要通过“数据有效性”对话框进行设置。9.2模拟运算表1、概念模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。2、模拟运算表的类型①基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；——单模拟运算表②基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响——双模拟运算表9.2.1单变量模拟运算表单变量模拟运算表概念在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格”。所谓输入单元格，就是被替换的含有输入数据

6、的单元格案例【例9.3】假设某人正考虑购买一套住房，要承担一笔250000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。9.2.1单变量模拟运算表1、建立模拟运算表3、C4的公式中引用了B4单元格，在实际计算时，将用B列B5:B11的值逐一代替公式中的B4。2、选择“数据”→“假设分析”→“数据表”9.2.1单变量模拟运算表【例9.4】对于例9.3而言，如果要查看在同等利息情况下，分别贷款￥250000，￥400000，￥550000，￥800000的每月还贷金额，则可建立如图9.5所示的模拟运算表。9.2.2双变量模拟

7、运算表单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响，如果想查看两个变量对公式计算的影响就需要使用双变量模拟运算表。案例假设某人想贷款45万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷款金额。假设要查看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%，偿还期限为10年、15年、20年、30年、35年时，每月应归还的贷款金额是多少9.2.2双变量模拟运算表单击“数据”→“假设分析”→“数据表”=PMT(B1/12,B2*12,D1)9.3单变量求解1、概念所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整

8、可变单元格