Excel在数据管理与分析中的应用课件

《Excel在数据管理与分析中的应用课件》由会员分享，可在线阅读，更多相关《Excel在数据管理与分析中的应用课件（101页珍藏版）》请在金锄头文库上搜索。

1、第8章 数据审核与数据分析,本章学习目标,1、了解加载宏的使用方法 2、掌握数据审核的方法 3、掌握模拟运算表的建立方法 4、掌握单变量求解的方法 5、掌握方案分析的方法 6、掌握线性规划求解的方法 7、掌握数据分析工具的应用方法,8.1 加载宏与分析工具安装,加载宏的概念 加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为 Excel 添加命令和函数，扩充Excel的功能。 Excel加载宏的扩展名是.xla或.xll。,8.1 加载宏与分析工具安装,2、Excel内置加载宏,8.1 加载宏与分析工具安装,3、加载宏的安装 选择“工具”|“加载宏”菜单,8

2、.2 数据审核及跟踪分析,1、概念 数据审核是一种查找单元格数据错误来源的工具，通过它可以快速地找出具有引用关系的单元格，借此分析造成错误的单元格。 数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。 2、数据审核的方式 追踪引用单元格 追踪从属单元格,8.2 数据审核及跟踪分析,3、追踪引用单元格示例,8.2 数据审核及跟踪分析,4、 数据有效性 限制数据的范围 案例 某班要建立一个成绩登记表，为了减少成绩输入错误，可以对成绩表中数据的输入类型及范围进行限制。 限制学号为8位字符，不能小于8位，也不能多于8位。 限制所有学科成绩为0100之间的整数。 限制科目列标题的取值

3、范围，如“高数”不能输入为“高等数学”。,8.2 数据审核及跟踪分析,成绩表示例,8.2 数据审核及跟踪分析,限制方法 选择“数据”|“有效性”,限定数据类型,限定方式,设置数据大小范围或长度,8.2 数据审核及跟踪分析,5、圈释无效数据,8.3 模拟运算表,1、概念 模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。 2、模拟运算表的类型 基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；单模拟运算表 基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响双模拟运算表,8.3 模拟运算表,1、单变量模拟运算表 概念

4、在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格”。所谓输入单元格，就是被替换的含有输入数据的单元格 案例 假设某人正考虑购买一套住房，要承担一笔250 000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。,8.3 模拟运算表,1、建立模拟运算表,3、C4的公式中引用了B4 单元格，在实际计算 时，将用B列B5:B11 的值逐一代替公式中 的B4。,2、选择“数据”|“模拟运算表”,8.3 模拟运算表,案例 假设某人想贷款45万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷

5、款金额。假设要查看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%，偿还期限为10年、15年、20年、30年、35年时，每月应归还的贷款金额是多少,8.3 模拟运算表,模拟运算表的设置,=PMT(B1/12,B2*12,D1),8.4 单变量求解,概念 所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值. 案例 某公司想向银行贷款900万元人民币，贷款利率是8.7%，贷款限期为8年，每年应偿还多少金额？如果公司每年可偿还120万元，该公司最多可贷款多少金额？,8.4 单变量求解,单变量求解方法 1、建立求解公式

6、：,2、设置求解公式,3、求解结果,8.5 方案分析,1、概念 方案是已命名的一组输入值，是 Excel 保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。 2、案例 已知某茶叶公司2001年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竟争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低。 最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、红茶的销售成本分别增长10%、6%、10%、7%。,8.5 方案分析,建立方案解决工作表,建立方法如下，输入下表A列、B列及

7、第3行的所有数据；在C4单元格中输入公式“=B4*(1+$B$16)”，然后将其复制到D4F4；在C7中输入公式“=B7*(1+$B$17)”,并将其复制到D7F7；在C8中输入公式“=B8*(1+$B$18)”，并将其复制到D8和F8；在C9中输入公式“=B9*(1+$B$19)”，并将其复制到D9F9；在C10中输入公式“=B9*(1+$B$20)”，并将其复制到D10F10；第11行数据是第7，8，9，10行数据对应列之和；净收入是相应的总销售额和销售成本之差，E19的总净收入是第13行数据之和。,8.5 方案分析,建立方案 （1）选择“工具”|“方案”菜单,单击添加按钮， 然后在添加方

8、案对话框中输入方案名,8.5 方案分析,输入方案变量,8.5 方案分析,建立方案报告,8.5 方案分析,建立方案透视图,8.6 线性规划求解,1、规划求解问题的特点： 问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时间等。 问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等。 问题有直接或间接影响约束条件的一组输入值。,8.6 线性规划求解,2、Excel规划求解问题由以下3部分组成 （1）可变单元格 （2）目标函数 （3）约束条件,8.6 线性规划求解,3、案例 某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。

9、该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。 该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下各表所示。 问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？,8.6 线性规划求解,表2 生产肥料的库存原材料,表1 各肥料成品用料及其价格表,表3单位原材料成本单价,8.6 线性规划求解,规划求解第一步建立求解工作表,8.6 线性规划求解,规划求解第二步

10、设置求解参数 选择“工具”|“规划求解”菜单，设置求解的各项参数，如下图所示。,8.6 线性规划求解,规划求解第3步求解,8.6 线性规划求解,修改资源 肥料厂接到一个电话：只要公司肯花10元的运费就能得到150个单位的矿物。这笔交易稍稍降低了矿物质的平均价格，但这些矿物质值10元吗？ 解决该问题的方法是，将库存矿物3 500改为3 650，用规划求解重新计算最大盈余。看除去￥10的成本后，盈余是否增加,8.6 线性规划求解,修改约束条件 肥料厂接到一个电话，一个老顾客急需25个单位的上层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料。决定增加约束条件，为他生产25个单位的上层肥料。

11、,增加的约束条件,8.6 线性规划求解,规划求解的结果报告 运算结果报告,8.6 线性规划求解,2、敏感性报告,8.6 线性规划求解,3、极限报告,8.7 数据分析工具库,1、概述 分析工具库由Excel自带的加载宏提供，如果启动Excel后，在Excel的“工具”菜单中没有“数据分析”菜单项，就需启动“工具”中的“加载宏”菜单项，将“分析工具库”加载到Excel系统中。 Excel的“分析工具库”加载宏还提供了一些统计函数、财务函数和工程函数。这些函数只有在安装了“分析工具库”后才能使用,8.7 数据分析工具库,2、Excel分析工具库中的工具,8.7 数据分析工具库,3、 统计分析 Exc

12、el的分析工具库提供了3种统计观测分析的工具：指数平滑分析、移动平均分析和回归分析 。 三种工具的用法相同，以指数平滑分析为例说明其用法。 （1）在工作表的一列上输入各时间点上的观察值，如下图A列所示。,8.7 数据分析工具库,（2）选择“工具”“数据分析”菜单项，从弹出的对话框中选择“指数平滑”。 （3）设置“输入”、“输出”选项，如下图所示。,8.7 数据分析工具库,4、假设检验 Excel的分析工具库中也提供了一些假设分析工具，如：t-检验、z-检验、F-检验。 运用检验工具可以轻松地完成均值、方差的假设检验。 案例 某种子公司为比较两个稻种的产量，选择了25块条件相似的试验田，采用相同

13、的耕种方法进行耕种试验，结果播种甲稻种的13块田的亩产量（单位：市斤）分别是：880、1 120、980、885、828、927、924、942、766、1 180、780、1 068、650；播种乙稻种的12块试验田的亩产量分别是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。问这两个稻种的产量有没有明显的高低之分。,8.7 数据分析工具库,输入A、B两列的样本数据,8.7 数据分析工具库,设置t检验的各项参数,8.7 数据分析工具库,5、回归分析 Excel通过对一组观察值使用“最小二乘法”直线拟合，进行线性回归分析，该回归分析

14、可同时解决一元回归与多元回归问题。 案例 下图所示的工作表中，列出了美国19561970年间历年的人均可支配收入和人均可消费支出的数据。试用图中的数据拟合模型。 模型中的趋势变量t，用于反映除人均收入之外的所有其他因素对人均消费的影响,8.7 数据分析工具库,8.7 数据分析工具库,设置回归分析模型的参数,8.7 数据分析工具库,回归分析结果,The End,第9章 外部数据的导入与分析,第9章 外部数据的导入与分析,Excel是一个极好的数据分析和图表分析工具，也是一个高效的数据输入工具。将它和专业数据库应用系统相结合，作为数据库系统的输入工具或数据分析工具，能解决数据库系统应用过程中的许多

15、问题，极大地提高工作效率。,本章学习目标,1、了解Excel与外部数据交换的方法 2、掌握Excel访问外部数据的条件 3、了解MsQuery的功能及用法 4、掌握MSQuery单表和多表查证方法 5、掌握Excel与ACCESS、VFP交换数据的方法 6、掌握Excel与文本文件交换数据的方法,9.1 导入Web数据,概述 Excel提供了Web页数据的导入功能 ，能够将因特网中的网页数据导入Excel进行分析。 导入Web数据的方法 1）保证计算机已经与网络连接。 2）选择Excel的“数据”|“导入外部数据”|“新建Web查询”，Excel会弹出“新建Web查询”对话框，如下图9所示,9

16、.1 导入Web数据,3）在图中“地址”文本框中输入Web页的网络地址，单击图中的“转到”按钮。稍后系统就会在该对话框中显示完整的Web页。图中显示的是新浪网的股票交易Web页。,9.1 导入Web数据,4）单击Web页数据区域中要导入数据前面的按钮。 5）单击图中的“导入”按钮后，Excel会弹出数据存放位置的设置对话框，如图（a）所示。图（b）是导入到Excel中的Web数据。,9.2 Excel与Word的数据交换,概述 Miscrosoft Office系统内的各种软件之间的数据交换非常方便，通过复制和粘贴就能够传递数据。Excel与Word是Microsoft Office系统中最常用的两种办公应用软件，Word常用于处理文字，Excel常用于表格计算。 方法 （1）选中Excel中的表格数据区域，然后单击工具栏中的“复制”按钮（或按Ctrl+C键）。 （2）单击Word文档中要插入Excel表格的位置，然后单击Word工具栏中的“粘贴”按钮（或按Ctrl+V键）。,9.3 Excel与文本文件的数据转换,文本文件概述 文本文件是计算