当前位置:
文档之家› Excel与数据处理数据分析工具及应用
Excel与数据处理数据分析工具及应用
1、概念
数据审核是一种查找单元格数据错误来源的工具,快速 地找出具有引用关系的单元格,借此分析造成错误的单 元格。
数据审核使用追踪箭头,通过图形的方式显示或追踪单 元格与公式之间的关系。
2、数据审核的方式
追踪引用单元格[见ch7.xls—追踪引用单元格]
操作方法:选定菜单“工具”—“审核” —显示‘审核’ 工具栏—选择要追踪引用的含公式单元格—“审核”工 具栏中“追踪引用单元格”按钮—再次单击“追踪引用 单元格”按钮提供数据的下一级单元格
2021/3/5
5
7.1 分析工具的安装
2、Excel内置加载宏
加载宏 分析工具 库 条件求和 向导 欧元工具
查阅向导
ODBC 加 载宏
2021/3/5
描述 添加财务、统计和工程分析工具和函数
对于数据清单中满足指定条件的数据进行求和 计算 将数值的格式设置为欧元的格式,并提供 EUROCONVERT函数以用于转换货币 创建一个公式,通过数据清单中的已知值查找 所需数据 利用安装的 ODBC 驱动程序,通过开放式数据 库互连(ODBC)功能与外部数据源相连
2021/3/5
17
7.3 模拟运算表
4、双变量模拟运算表 概念:
单变量模拟运算表只能解决一个输入变量对一个或多个公式 计算结果的影响,要查看两个变量对公式计算结果的影响, 就要用到双变量模拟运算表。所谓双模拟变量,就是指公式 中有两个变量。公式中两个变量所在的单元格是任取的。可 以是工作表中任意空白单元格。
为“高等数学”。
2021/3/5
13
7.2 数据审核及跟踪分析
4、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据,但对已 输入数据的区域,不能显示出有误的数据。采用圈释无效 数据的方法,可以显示不满足有效性规则的错误单元格。
操作方法:(选择数据区域—设置数据有效性规 则)—选择“工具”菜单—“审核”—选择“显示审核 工具栏”—选中有效性检测的数据区域—单击“审核” 工具栏的“圈释无效数据”按钮
2021/3/5
7
7.1 分析工具的安装
3、安装分析工具 选择“工具”|“加载宏”菜单—在对话框中选择所需 工具,按确定 注:若在安装EXCEL系统时没有安装加载宏,则必须重 新启动EXCEL的安装程序,选择其中的“添加/删除” 命令,安装EXCEL的加载宏。
2021/3/5
目8 录
7.2 数据审核及跟踪分析
方案是已命名的一组输入值,是 Excel 保存在工作表中并可 用来自动替换某个计算模型的输入值,用来预测模型的输 出结果。
例:
已知某茶叶公司2004年的总销售额及各种茶叶的销售成本, 现要在此基础上制订一个五年计划。由于市场竞争的不断 变化,所以只能对总销售额及各种茶叶销售成本的增长率 做一些估计。最好的方案当然是总销售额增长率高,各茶 叶的销售成本增长率低。
2021/3/5
10
7.2 数据审核及跟踪分析
3、 数据有效性 数据有效性:对数据进行检验和检查的有效方法,把错误 限制在数据输入阶段。
限定数据类型和有效范围: 如:限定数据大小范围、日期的范围、输入字符的个数、 单元格的公式
2021/3/5
11
7.2 数据审核及跟踪分析
数据限制的操作方法:选择“数据” —“有效性”—在对 话
Excel与数据处理
2021/3/5
1
本章教学目的与要求
1、掌握宏的加载方法 2、掌握追踪从属或引用单元格的方法 3、掌握限定单元格数据的范围及圈释无效数据的
应用方法 4、掌握模拟运算表及变量求解的应用 5、掌握方案的建立和应用 6、掌握规划求解工具的应用 7、了解假设检验和回归分析等工具的应用
2021/3/5
2021/3/5
12
7.2 数据审核及跟踪分析
例:[见ch7.xls—限定数据范围] 某班要建立一个成绩登记表,为了减少成绩输入错 误,可对成绩表中数据的输入类型及范围进行限制。
限制学号为8位字符,不能小于8位,也不能多于8 位。
限制所有学科成绩为0~100之间的整数。 限制科目列标题的取值范围,如“高数”不能输入
例: [见ch7.xls—单变量求解] 某公司想向银行贷款900万元人民币,贷款利率是8.7%, 贷款限期为8年,每年应偿还多少金额? 如果公司每年可偿还120万元,该公司最多可贷款多少 金额?
2021/3/5前一问题可用PMT函数, 后一问题可用单变量求解。 2目0 录
7.5 方案分析
1、概念
2021/3/5
4
7.1 分析工具的安装
1、加载宏的概念
加载宏是一种可选择性地安装到计算机中的软件组 件,用户可根据需要决定是否安装。其作用是为 Excel 添加命令和函数,扩充Excel的功能。
Excel加载宏的扩展名是.xla或.xll。
在默认情况下,Excel将下表列出的加载宏程序安装 在如下某一磁盘位置:“Microsoft Office\Office”文 件夹下的“Library”文件夹或其子文件夹,或 Windows 所在文件夹下的“Profiles\用户名 \Application Data\Microsoft\AddIns”文件夹下。网络 管理员也可将加载宏程序安装到其他位置。
一列中。同时,单变量模拟表中使用的公式必须引用 “输入单元格”。
输入单元格,就是被替换的含有输入数据的单元格
操作步骤:
1、在工作表中建立模拟运算表的结构;
2、输入模拟运算表要用到的公式;
3、选择包括公式、引用单元格和运算结果单元格区域(3 部分);
4、选择“数据”菜单—“模拟运算表”选项;
5、在“模拟运算表”对话框中输入引用单用格(行或列一 种) —确定
2021/3/5
16
7.3 模拟运算表
例:[见ch7.xls—单变量模拟运算表] 假设某人正考虑购买一套住房,要承担一笔250 000 元的贷款,分15年还清。现想查看每月的还贷金额, 并想查看在不同的利率下,每月的应还贷金额。
若贷款额分别为400 000,550 000,800 000元,每月 的应还贷金额又是多少?
2、模拟运算表的类型 ①基于一个输入变量的表,用这个输入变量测试它对 多个公式的影响;——单模拟运算表
②基于两个输入变量的表,用这两个变量测试它们对 于单个公式的影响——双模拟运算表
2021/3/5
15
7.3 模拟运算表
3、单变量模拟运算表
概念
在单变量模拟运算表中,输入数据的值被安排在一行或
例:[见ch7.xls—双变量模拟运算表]
假设某人想贷款45万元购买一部车,要查看在不同 的利率和不同的偿还年限下,每个月应还的贷款金 额。假设要查看贷款利率为5%、5.5%、6.5%、7%、 7.5%、8%,偿还期限为10年、15年、20年、30年、 2021/33/55 年时,每月应归还的贷款金额是多少 ?
2、规划求解问题的特点:
问题有单一的目标,如求运输的最佳路线、求生产的 最低成本、求产品的最大盈利,求产品周期的最短时 间等。
问题有明确的不等式约束条件,例如生产材料不能超 过库存,生产周期不能超过一个星期等。
问题有直接或间接影响约束条件的一组输入值。
2021/3/5
25
7.6 线性规划求解
框中操作:
限定文本长度: “设置”选项卡中“允许” 下拉列表中 选择文本长度。
限定数据的有效范围:“设置”选项卡中“允许”下拉列 表中选择整数/小数-- 确定最大/小值
设置单元格有效范围:“设置”选项卡中“允许”下拉列 表中选择序列—输入序列值
设置输入提示信息: “输入信息”选项卡中输入要显示 的信息
移去引用单元格追踪箭头:
操作方法:选择“审核”工具栏中“移去引用单元格中追
2021/3/5踪箭头”
9
7.2 数据审核及跟踪分析
追踪从属单元格[见ch7.xls—追踪从属单元格] 某单元格公式引用了其它单元格,则该单元格为从属单元
格。 操作方法:选定菜单“工具”—“审核” –显示‘审核’ 工具栏—选择要追踪从属单元格的单元格—“审核”工具 栏中“追踪从属单元格”按钮—再次单击“追踪从属单元 格”按钮提供从属的的单元格 移去引用单元格追踪箭头: 操作方法:选择“审核”工具栏中“移去从属单元格中追 踪箭头”
4、建立方案透视图[见ch7.xls—方案数据透视图] 选择“工具” —“方案”菜单—选择“方案管理器”对话 框中的某一方案—单击 “总结”按钮—在“方案总结” 对话框中结果类型中.6 线性规划求解
1、概述 EXCEL提供的规划求解工具,可求解出线性与非线性两种 规划求解问题,规划求解问题常用于解决产品比例、人员 调度、优化路线、调配材料等方面问题。
2021/3/5
18
7.3 模拟运算表
操作步骤:
1、在工作表中建立模拟运算表的结构;
2、在行列交叉处输入模拟运算表要用到的公式;
3、选择包括公式,引用单元格和运算结果单元格区 域(3部分);
4、选择“数据”菜单—“模拟运算表”选项;
5、在“模拟运算表”对话框中输入公式中行和列引 用的单用格—确定
2
本章重点、难点及学时数
重点: 掌握数据审核的方法 掌握模拟运算表的应用 掌握单变量求解的应用 掌握方案的应用 掌握规划求解的应用
难点: 掌握规划求解的应用 学时数: 12学时(上机6学时)
2021/3/5
3
本章目录
7.1 分析工具的安装 7.2 数据审核及跟踪分析 7.3 模拟运算表 7.4 单变量求解 7.5 方案分析 7.6 线性规划求解 7.7 数据分析工具库 小结 思考与练习
22
7.5 方案分析
输入方案变量值如下图所示:
2021/3/5
23
7.5 方案分析
2、显示方案 选择“工具” —“方案”菜单—选择“方案管理器”对话 框中的某一方案—单击 “显示”按钮