数据分析工具.ppt

第9章数据分析工具的应用 Excel与数据处理第3版 本章学习目标 1 了解加载宏的功能和用法2 掌握数据审核的方法3 掌握模拟运算表的建立方法4 掌握单变量求解的方法5 掌握方案建立和应用方法6 掌握规划求解的方法7 掌握分析工具库中的常用工具 9 1数据审核及跟踪分析 1 概念数据审核是一种查找单元格数据错误来源的工具 通过它可以快速地找出具有引用关系的单元格 借此分析造成错误的单元格 数据审核使用追踪箭头 通过图形的方式显示或追踪单元格与公式之间的关系 2 数据审核的方式追踪引用单元格追踪从属单元格 9 1 1追踪引用单元格 引用单元格是指被其他单元格中的公式引用的单元格 即为指定公式提供数据的单元格 当使用 追踪引用单元格 工具时 Excel会用蓝色追踪箭头指明活动工作表中为公式提供数据的单元格 如果选定单元格引用了其他工作表或工作簿中的单元格 Excel会用黑色追踪箭头从工作表图标指向选定的单元格 例9 1 某商场的销售记录和销售汇总表如图所示 查看销售汇总表中各数据的来源是否正确 9 1 1追踪引用单元格 单击公式选项卡 单击包括有公式的单元格 单击追踪引用单元格 9 1 2追踪从属单元格 从属单元格如果某个单元格中的公式引用了其它单元格 那么此单元格就称为从属单元格 当公式中所引用单元格的值发生变化时 公式所在单元格 从属单元格 中的值也会随之变化 例如 若B3单元格中包含公式 A3 A4 则B3就是A3和A4的从属单元格 当A3或A4单元格的值发生变化时 B3单元格的值就会发生变化 9 1 1追踪引用单元格 单击公式选项卡 单击包括要追踪的单元格 单击追踪从属单元格 9 1 3数据有效性检验 数据有效性数据有效性检验是Excel为减少错误 核查数据正确性而提供的一种数据检验工具 利用该工具可以设置单元格数据输入的类型和范围 对错误的输入数据进行告警 并拒不接受 把错误限制在输入阶段 此外 它还能对已经完成的数据表设置有效性检验规则 并据此标识出其中的错误数据 案例 例9 2 某班要建立一个成绩登记表 为了减少成绩输入错误 可对成绩表中数据的输入类型及范围进行限制 限制学号为8位字符 不能小于8位 也不能多于8位 限制所有学科成绩为0 100之间的整数 限制科目列标题的取值范围 如 高数 不能输入为 高等数学 9 1 3数据有效性检验 成绩表示例 9 1 3数据有效性检验 限制方法单击 数据 选项卡中的 数据有效性 按钮 限定数据类型 限定方式 设置数据大小范围或长度 9 1 3数据有效性检验 圈释无效数据 1 按前面介绍的方法首先设置数据的有效值范围 0 100 2 单击 数据有效性 右边的下箭头 选择 圈释无效数据 Excel就会将不符合有效性规则的数据圈释出来 9 1 3数据有效性检验 设置输入提示信息和错误警告信息这些信息都需要通过 数据有效性 对话框进行设置 9 2模拟运算表 1 概念模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算 测试使用一个或两个变量的公式中变量对运算结果的影响 2 模拟运算表的类型 基于一个输入变量的表 用这个输入变量测试它对多个公式的影响 单模拟运算表 基于两个输入变量的表 用这两个变量测试它们对于单个公式的影响 双模拟运算表 9 2 1单变量模拟运算表 单变量模拟运算表概念在单变量模拟运算表中 输入数据的值被安排在一行或一列中 同时 单变量模拟表中使用的公式必须引用 输入单元格 所谓输入单元格 就是被替换的含有输入数据的单元格案例 例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双变量模拟运算表 单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响 如果想查看两个变量对公式计算的影响就需要使用双变量模拟运算表 案例假设某人想贷款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通过调整可变单元格中的数值 使之按照给定的公式来满足目标单元格中的目标值 案例 例9 6 某公司想向银行贷款900万元人民币 贷款利率是8 7 贷款限期为8年 每年应偿还多少金额 如果公司每年可偿还120万元 该公司最多可贷款多少金额 9 3单变量求解 2 单变量求解方法 1 建立求解公式 2 设置求解公式 3 求解结果 单击 数据 假设分析 单变量求解 9 4方案分析 1 概念方案是已命名的一组输入值 是Excel保存在工作表中并可用来自动替换某个计算模型的输入值 用来预测模型的输出结果 2 案例 例9 7 已知某茶叶公司2001年的总销售额及各种茶叶的销售成本 现要在此基础上制订一个五年计划 由于市场竟争的不断变化 所以只能对总销售额及各种茶叶销售成本的增长率做一些估计 最好的方案当然是总销售额增长率高 各茶叶的销售成本增长率低 最好的估计是总销售额增长13 花茶 绿茶 乌龙茶 红茶的销售成本分别增长10 6 10 7 9 4方案分析 建立方案解决工作表 建立方法如下 输入下表A列 B列及第3行的所有数据 在C4单元格中输入公式 B4 1 B 16 然后将其复制到D4 F4 在C7中输入公式 B7 1 B 17 并将其复制到D7 F7 在C8中输入公式 B8 1 B 18 并将其复制到D8和F8 在C9中输入公式 B9 1 B 19 并将其复制到D9 F9 在C10中输入公式 B9 1 B 20 并将其复制到D10 F10 第11行数据是第7 8 9 10行数据对应列之和 净收入是相应的总销售额和销售成本之差 E19的总净收入是第13行数据之和 9 4方案分析 建立方案 1 选择 数据 方案 菜单 单击添加按钮 然后在添加方案对话框中输入方案名 1 单击 数据 2 单击 假设分析 方案管理器 3 单击 添加 4 输入 方案名称 5 指定 方案可变单元格 6 确定后见到下页 9 4方案分析 输入方案变量 9 4方案分析 建立方案报告 9 4方案分析 建立方案透视图 9 5加载宏工具的安装 加载宏的概念加载宏是一种可选择性地安装到计算机中的软件组件 用户可根据需要决定是否安装 其作用是为Excel添加命令和函数 扩充Excel的功能 Excel2007加载宏的扩展名是 xlam 在默认情况下 Excel将表9 1列出的加载宏程序安装在如下某一磁盘位置 MicrosoftOffice Office 文件夹下的 Library 文件夹或其子文件夹 或Windows所在文件夹下的 Profiles 用户名 ApplicationData Microsoft AddIns 文件夹下 网络管理员也可将加载宏程序安装到其他位置 9 5加载宏工具的安装 2 Excel内置加载宏 9 5加载宏工具的安装 3 安装分析工具 1 单击Office按钮钮 然后单击 Excel选项 2 单击 加载项 然后在 管理 框中 选择 Excel加载宏 然后单击 转到 Excel会弹出图示的 加载宏 对话框 3 在 加载宏 框对话中 选中要安装的加载宏 9 6线性规划求解 1 规划求解问题的特点 问题有单一的目标 如求运输的最佳路线 求生产的最低成本 求产品的最大盈利 求产品周期的最短时间等 问题有明确的不等式约束条件 例如生产材料不能超过库存 生产周期不能超过一个星期等 问题有直接或间接影响约束条件的一组输入值 9 6线性规划求解 2 Excel规划求解问题由以下3部分组成 1 可变单元格 2 目标函数 3 约束条件 9 6 2建立规划求解模型 3 案例 例9 8 某肥料厂专门收集有机物垃圾 如青草 树枝 凋谢的花朵等 该厂利用这些废物 并掺进不同比例的泥土和矿物质来生产高质量的植物肥料 生产的肥料分为底层肥料 中层肥料 上层肥料 劣质肥料4种 为使问题简单 假设收集废物的劳动力是自愿的 除了收集成本之外 材料成本是低廉的 该厂目前的原材料 生产各种肥料需要的原材料比例 各种肥料的单价等如下各表所示 问题 求出在现有的情况下 即利用原材料的现有库存 应生产各种类型的肥料各多少数量才能获得最大利润 最大利润是多少 9 6 2建立规划求解模型 表2生产肥料的库存原材料 表1各肥料成品用料及其价格表 表3单位原材料成本单价 9 6 2建立规划求解模型 规划求解第一步 建立求解工作表 9 6 2建立规划求解模型 规划求解第二步 设置求解参数 2 单击 规则求解 若此处无 规则求解 命令 须按9 5节的介绍安装 规则求解 加载宏 单击 数据 选项卡 2 设置 规则求解参数 9 6 3规划求解 规划求解第3步 求解 9 6 4修改资源 肥料厂接到一个电话 只要公司肯花10元的运费就能得到150个单位的矿物 这笔交易稍稍降低了矿物质的平均价格 但这些矿物质值10元吗 解决该问题的方法是 将库存矿物3500改为3650 用规划求解重新计算最大盈余 看除去 10的成本后 盈余是否增加 9 6 5修改约束条件 修改约束条件肥料厂接到一个电话 一个老顾客急需25个单位的上层肥料 公司经理在检查打印结果后 发现没有安排生产上层肥料 决定增加约束条件 为他生产25个单位的上层肥料 增加的约束条件 9 6 6规则求解报告 规划求解的结果报告运算结果报告 9 6 6规则求解报告 敏感性报告 9 6 6规则求解报告 极限报告 9 6 8求解不等式 求解不等式某工厂生产甲 乙两种产品 假设生产甲产品1吨 要消耗9吨煤 4千瓦电力 3吨钢材 获利0 7万元 生产乙产品1吨 要消耗4吨煤 5千瓦电力 10吨钢材 获利1 2万元 按计划国家能提供给该厂的煤为360吨 电力200千瓦 钢材300吨 问应该生产多少吨甲种产品和乙种产品 才能获得最大利润 9 6 8求解不等式 假设生产甲种产品x1吨 生产乙种产品x2吨 这个问题的数学建模如下 最大利润是求S 0 7x1 1 2x2的最大值 用EXCEL规划求解如下 9 6 8求解不等式 不等式工作表设计 9 6 8求解不等式 9 7数据分析工具库 1 概述分析工具库由Excel自带的加载宏提供 如果启动Excel后 在Excel的 工具 菜单中没有 数据分析 菜单项 就需启动 工具 中的 加载宏 菜单项 将 分析工具库 加载到Excel系统中 Excel的 分析工具库 加载宏还提供了一些统计函数 财务函数和工程函数 这些函数只有在安装了 分析工具库 后才能使用 9 7数据分析工具库 2 Excel分析工具库中的工具 9 7 1统计分析 统计分析工具库Excel的分析工具库提供了3种统计观测分析工具 指数平滑分析 移动平均分析和回归分析三种工具用法相同 以指数平滑分析为例说明其用法 1 在工作表的一列上输入各时间点上的观察值 如下图A列所示 9 7 1统计分析 2 选择 数据 数据分析 命令 从弹出的对话框中选择 指数平滑 3 设置 输入 输出 选项 如下图所示 9 7 2假设分析 假设检验Excel的分析工具库中也提供了一些假设分析工具 如 t 检验 z 检验 F 检验 运用检验工具可以完成均值 方差的假设检验 案例 例9 10 某种子公司为比较两个稻种的产量 选择了25块条件相似的试验田 采用相同的耕种方法进行耕种试验 结果播种甲稻种的13块田的亩产量 单位 市斤 分别是 880 1120 980 885 828 927 924 942 766 1180 780 1068 650 播种乙稻种的12块试验田的亩产量分别是 940 1142 1020 785 645 780 1180 680 810 824 846 780 问这两个稻种的产量有没有明显的高低之分 9 7 2假设分析 输入A B两列的样本数据 9 7 2假设分析 设置t检验的各项参数 单击 数据分析 选择 t 检验 双样本 设置各项参数 9 7 3相关性分析工具 Excel的分析工具库提供了 协方差 和 相关系数 两个分析工具 运用它们进行相关分析非常简单 例9 11 有一组物理实验的测试数据如图9 24的A3 F12区域所示 现在要计算各组实验之间的相关系数 9 7 3相关性分析工具 源数据表 相关性分析结果 9 7 3相关性分析工具 分析过程 1 输入实验的数据 如图9 24的A1 F12数据区域 2 在 数据 选项卡的 分析 组中 单击 数据分析 命令 从弹出的 数据分析