EXCEL数据分析工具应用

数据分析工具应用

Page 1

本章学习目标 1、了解加载宏的功能和用法 2、掌握数据审核的方法 3、掌握模拟运算表的建立方法 4、掌握单变量求解的方法 5、掌握方案建立和应用方法 6、掌握规划求解的方法 7、掌握分析工具库中的常用工具

Page 2

9.1 数据审核及跟踪分析1、概念– 数据审核是一种查找单元格数据错误来源的工具，通过它 可以快速地找出具有引用关系的单元格，借此分析造成错 误的单元格。 – 数据审核使用追踪箭头，通过图形的方式显示或追踪单元 格与公式之间的关系。

2、数据审核的方式– 追踪引用单元格 – 追踪从属单元格

Page 3

9.1.1追踪引用单元格 引用单元格– 是指被其他单元格中的公式引用的单元格，即为指定公式 提供数据的单元格。 – 当使用“追踪引用单元格”工具时，Excel会用蓝色追踪 箭头指明活动工作表中为公式提供数据的单元格。如果选 定单元格引用了其他工作表或工作簿中的单元格，Excel 会用黑色追踪箭头从工作表图标指向选定的单元格。【例9.1】某商场的销售记录和销售汇总表如图所示。查看销售汇总表 中各数据的来源是否正确。

Page 4

9.1.1追踪引用单元格单击追踪引用单元格

公 式选 项 卡 单击包括有公式的单元格

单 击

Page 5

9.1.2追踪从属单元格 从属单元格– 如果某个单元格中的公式引用了其它单元格，那么此单元 格就称为从属单元格。 – 当公式中所引用单元格的值发生变化时，公式所在单元格 (从属单元格)中的值也会随之变化。例如，若B3单元格 中包含公式“=A3+A4‖,则B3就是A3和A4的从属单元格 。当A3或A4单元格的值发生变化时，B3单元格的值就会 发生变化。

Page 6

9.1.1追踪引用单元格单击追踪从属单元格

公 式选 项 卡

单 击

单击包括要追踪的单元格

Page 7

9.1.3 数据有效性检验数据有效性– 数据有效性检验是Excel为减少错误、核查数据正确性而提供的一种数 据检验工具。利用该工具可以设置单元格数据输入的类型和范围，对 错误的输入数据进行告警，并拒不接受，把错误限制在输入阶段。此 外，它还能对已经完成的数据表设置有效性检验规则，并据此标识出 其中的错误数据。 – 案例【例9.2】某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表 中数据的输入类型及范围进行限制。 限制学号为8位字符，不能小于8位，也不能多于8位。 限制所有学科成绩为0~100之间的整数。 限制科目列标题的取值范围，如“高数”不能输入为“高等数学”。

Page 8

9.1.3 数据有效性检验 成绩表示例

Page 9

9.1.3 数据有效性检验

限制方法– 单击“数据”选项卡中的―数据有效性”按钮

限定数据类型 限定方式设置数据大小范围或长度

Page 10

9.1.3 数据有效性检验 圈释无效数据

1、按前面介绍的方 法首先设置数据的 有效值范围:0-100;

2、单击“数据有效性” 右边的下箭头，选择 “圈释无效数据”

Excel就会将不 符合有效性规则 的数据圈释出来Page 11

9.1.3 数据有效性检验 设置输入提示信息和错误警告信息– 这些信息都需要通过“数据有效性”对话框进行设置。

Page 12

9.2 模拟运算表1、概念– 模拟运算表是对工作表中一个单元格区域内的数据进行模 拟运算，测试使用一个或两个变量的公式中变量对运算结 果的影响。

2、模拟运算表的类型– ①基于一个输入变量的表，用这个输入变量测试它对多个 公式的影响；——单模拟运算表– ②基于两个输入变量的表，用这两个变量测试它们对于单 个公式的影响——双模拟运算表

Page 13

9.2.1 单变量模拟运算表 单变量模拟运算表概念 在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同 时，单变量模拟表中使用的公式必须引用“输入单元格”。所谓输 入单元格，就是被替换的含有输入数据的单元格

– 案例 【例9.3】假设某人正考虑购买一套住房，要承担一笔250 000元的 贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的 利率下，每月的应还贷金额。

Page 14

9.2.1 单变量模拟运算表1、建立模拟运算表

2、选择“数据”→“假设分析” →“数据表” 3、C4的公式中引用了B4 单元格，在实际计算 时，将用B列B5:B11 的值逐一代替公式中 的B4。Page 15

9.2.1 单变量模拟运算表【例9.4】对于例9.3而言，如果要查看在同等利息情况下，分别贷款 ￥250 000,￥400 000,￥550 000,￥800 000的每月还贷金额，则 可建立如图9.5所示的模拟运算表。

Page 16

9.2.2 双变量模拟运算表

单变量模拟运算表只能解决一个输入变量对一个或 多个公式计算结果的影响，如果想查看两个变量对 公式计算的影响就需要使用双变量模拟运算表。 案例– 假设某人想贷款45万元购买一部车，要查看在不同的利率 和不同的偿还年限下，每个月应还的贷款金额。假设要查 看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%,偿还 期限为10年、15年、20年、30年、35年时，每月应归还 的贷款金额是多少

Page 17

9.2.2 双变量模拟运算表

=PMT(B1/12,B2*12,D1)

单击“数 据”→“假设 分析”→“数 据表”Page 18

9.3 单变量求解1、概念– 所谓单变量求解，就是求解具有一个变量的方程，Excel 通过调整可变单元格中的数值，使之按照

给定的公式来满 足目标单元格中的目标值.

案例– 【例9.6】某公司想向银行贷款900万元人民币，贷款利率 是8.7%,贷款限期为8年，每年应偿还多少金额？如果公 司每年可偿还120万元，该公司最多可贷款多少金额？

Page 19

9.3 单变量求解2、单变量求解方法(1)建立求解公式：

单击“数据”→“假 设分析”→“单变量 求解”

(2)设置求解公式

(3)求解结果

Page 20

9.4 方案分析1、概念– 方案是已命名的一组输入值，是 Excel 保存在工作表中并可用来自动替换 某个计算模型的输入值，用来预测模型的输出结果。

2、案例– 【例9.7】已知某茶叶公司2001年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竟争的不断变化，所以只能 对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是 总销售额增长率高，各茶叶的销售成本增长率低。 – 最好的估计是总销售额增长13%,花茶、绿茶、乌龙茶、红茶的销售成本 分别增长10%、6%、10%、7%。

Page 21

本文来源：https://www.bwwdw.com/article/rg8e.html