EXCEL数据分类汇总分析.ppt

Excel预测与决策分析 第三章数据分类汇总分析 基础篇数据分类汇总分析的意义和作用数据分类汇总的四种方法数据透视表D函数和模拟运算表提高篇利用DSUM函数和控件控制分类汇总结果分类汇总数据的应用Excel中的多维数据分析 OLAP 内容简介 第一节数据分类汇总分析的意义和作用 获得销售额分类统计值 ABC公司1995年不同省份不同类别商品的销售额总计值 0 10000 20000 30000 40000 50000 艺术品 自行车 服装 食品 儿童用品 体育用品 瓷器 类别 销售额 第一节数据分类汇总分析的意义和作用 续 获得各类销售额排行榜 第一节数据分类汇总分析的意义和作用 续 获得各类销售额的时间序列 第一节数据分类汇总分析的意义和作用 续 获得各经济量之间的相关性 第一节数据分类汇总分析的意义和作用 续 获得各种产品需求量的频率分布 第二节数据分类汇总的四种方法 数据查询的统计值功能数据清单功能数据透视表D函数与模拟运算表 第二节数据分类汇总的四种方法 汇总字段 待汇总的变量或字段 销售额 销售数量 工作时间等 分类字段 对数据进行汇总时 以某个变量或字段的不同值为参考来对汇总字段进行汇总 参考不同产品类别 分类字段 汇总销售额 汇总字段 参考不同的生产人员姓名 分类字段 汇总工作时间 汇总字段 第二节数据分类汇总的四种方法 续 ABC公司1995各省份 各类别商品净销售额总计值 图3 7 一 数据查询的统计值功能 从Excel中启动MicrosoftQuery 利用Query的统计值功能 将数据汇总后返回Excel 例3 1 ABC公司销售数据 dbf中保存着公司从1994年到1997年三年的销售数据 利用Query软件的统计值功能汇总该公司的销售数据 并按照图3 7的形式 汇总出1995年不同省份 不同类别商品的净销售额 一 数据查询的统计值功能 续 二 Excel数据清单功能 数据清单数据清单是包含相关数据的一系列工作表数据行 数据清单的首行为字段名 首行下的各行是各个记录 数据清单中不能出现空行 数据清单的基本功能排序筛选分类总计 二 Excel数据清单功能 续 利用Excel数据清单处理功能进行分类汇总的一般步骤 获得数据清单 设定筛选条件 筛选数据 将筛选结果复制到工作表其他位置 对经过筛选的数据按分类字段进行排序 对排序后的数据进行分类汇总 二 Excel数据清单功能 续 例3 2 利用Excel的数据清单功能 将ABC公司的销售数据按照图3 7的形式 汇总出1995年不同省份 不同类别商品的净销售额总计值 将上述汇总表选中第3级汇总 然后选中所需数据区域 单击 选定可见单元格 工具 自定义 编辑 再单击 复制 将光标移到空白数据区域 单击粘贴 得到如图数据 根据右图数据再加以调整 便可得到如图3 7的数据 第三节数据透视表 数据透视表的基本功能数据透视表结构的灵活性数据透视图的灵活性利用数据透视表生成时间序列统计不同规模销量的发生次数以及概率分布计算占同列数据总和的百分比 一 数据透视表的基本功能 数据透视表分类汇总的两种方法先将数据导入Excel成为数据清单 利用数据透视表汇总对数据清单进行汇总利用数据透视表直接从数据库中查询 并汇总数据 一 数据透视表的基本功能 续 例3 3 利用Excel获取外部数据功能 获取ABC公司1995年的销售数据 使用Excel数据透视表功能制作如图3 7所示的分类汇总表 提示 启动excel 数据 数据透视表和数据透视图 导入外部数据 新建数据库查询 在query获得汇总数据后返回excel 单击数据透视图的 图表向导 生成数据透视图 一 数据透视表的基本功能 续 一 数据透视表的基本功能 续 例3 4 直接利用Excel数据透视表功能从数据库中查询数据 并根据查询结果汇总 制作出如图3 7所示的分类汇总表 提示 启动excel 数据 数据透视表和数据透视图 外部数据源 二 数据透视表结构的灵活性 改变数据透视表的行列结构利用页域对数据进行分页汇总新增 删除和修改行域 列域或页域字段 分类字段 新增 删除和修改汇总字段改变汇总字段的汇总方式同一个字段既作分类字段又作汇总字段 二 数据透视表结构的灵活性 续 改变分类字段值的位置对分类字段的值加以合并隐藏行域 列域的字段值展开汇总值 显示详细数据 三 数据透视图的灵活性 变换分类字段的位置将行 列分类字段换成页分类字段新增 删除分类字段新增 删除汇总字段改变被汇总方式显示分类字段的部分值 四 利用数据透视表生成时间序列 时间序列 按照一定时间间隔汇总的数据序列 例3 5 利用数据透视表 对NorthwindTrader公司的销售数据按月汇总种各产品销售额 例3 5操作方法 数据 进行数据查询 query 获得Northwind公司的订购日期 产品名称 销售额 订单明细 单价 数量 1 折扣 光标停留在目标数据的任意单元格 数据 数据透视表和数据透视图 MicrosoftExcel数据列表或数据库 数据透视表 next 新建工作表 next 订购日期拖至行域 销售额拖至列域 产品名称拖至页域 右击目标数据任意单元格 组及显示明细数据 组合 起始于1996 7 1 终止于1998 5 31 依据为先月 后年 五 统计不同规模销量的发生次数及频率分布 例3 6 利用数据透视表 对Northwind公司的销售数据按照不同产品 不同时间段 统计不同规模销售数量的发生次数 并计算其频率分布 例3 6操作方法 利用外部数据获得Northwind公司产品名称 订购日期 数量 选定数据清单任意单元格 数据 数据透视表和数据透视图 默认选项next 订购日期拖至行域 产品名称拖至页域 数量拖至数据域 双击 求和项 数量 汇总方式选 计数 确定 按例3 5方法对年月进行分组 然后将 年 和 订购日期 拖到页域 从数据透视表字段列表中将 数量 拖至行域 在数量列任意单元格右键 组及显示明细数据 组合 起始于1 终止于130 确定 右击 数量 数据任意单元格 表格选项 去掉行 列总计 D6单元格输入 销量分组 E6输入 频率 D7输入 A7 E7输入 B7 SUM B 7 B 19 将E7数据格式设为百分数 小数点保留一位 并将D7和E7复制到D19和E19单元格 绘制频率分布图 选中D6到E19 单击图表向导 选柱状图 next next 五 统计不同规模销量的发生次数及频率分布 Northwind公司不同规模销量的频率分布 0 5 10 15 20 25 30 35 1 10 11 20 21 30 31 40 41 50 51 60 61 70 71 80 81 90 91 100 101 110 111 120 121 130 计数项 数量 第四节D函数和模拟运算表 D函数模拟运算表生成时间序列统计不同规模销量的发生次数以及概率分布 一 D函数 D函数 对数据清单或数据库中的数据进行分析有12个数据库内建函数 常用的D函数包括 DSUM DAVERAGE DCOUNT DCOUNTA 一 D函数 续 D函数名称 数据清单 统计字段 条件范围 统计满足条件记录制定字段的总计值 数据清单 包含相关数据的工作表中的一系列数据行统计字段 被汇总字段 可以使用字段名 字段名所在的单元格 或者字段在数据清单中所在列的序号 条件范围 规定函数的匹配条件的一个工作表区域 D函数只对满足该条件范围所规定条件的记录进行汇总 一 D函数 续 例3 8 利用ABC公司的销售数据 应用DSUM 函数 生成一个与图3 7相同的数据分类汇总表 列出ABC公司1995年不同省份不同类别净销售额总计值 例3 8操作方法 1 获得数据清单 日期 省份 类别 净销售额 毛销售额 2 命名一空白工作薄为DSUM 函数用法 在单元格B2 E2输入 日期 日期 省份 类别 在B3 E3输入 95 1 1 95 12 31 安徽 儿童用品 在B5输入 净销售额的总计值 D5输入 DSUM 数据清单 被汇总字段 条件范围 数据清单为abc公司销售数据 被汇总字段为净销售额 条件范围为B2 E3 更方便方法 适用EXCEL的区域名 在数据清单选中A1 E3300 在区域名起新名字 原始数据 这时 DSUM 函数可以写成DSUM 原始数据 净销售额 B2 E3 根据上面原理构造图3 7表 一 D函数 续 通过上例知道 利用DSUM函数制作分类汇总表是可行的 只是过于繁琐 需要给汇总表的每个单元格都输入DSUM函数 还要给每个函数设定不同的条件 EXCEL有个更好的工具 就是模拟运算表 将DSUM函数和模拟运算表结合使用 可以避免DSUM函数的重复运算和条件的重复设置 二 模拟运算表 模拟运算表用于计算一系列自变量给定值对应的函数值 自动计算函数值 一维模拟运算表 计算一个自变量的一系列给定值的一个或若干个函数的函数值 二维模拟运算表 计算两个自变量的系列给定值的一个函数的函数值 二 模拟运算表 续 例3 9 在例3 8的基础上 利用DSUM 函数和模拟运算表计算ABC公司不同省份不同类别净销售额总计值 例3 9操作方法 1 获得ABC公司的日期 省份 类别 净销售额 毛销售额 将数据清单命名为 原始数据 2 列出如例3 8的条件 输入DSUM函数公式 3 在G3 G8放置ABC公司类别的名称如儿童用品等 4 一维模拟运算表 汇总不同类别的净销售额 在H2输入 D5 选中G2 H8 数据 模拟运算表 只需引入列 因为我们自变量一序列的值放在列中 E 3 确定 二维模拟运算表 计算不同类别 不同省份的净销售额总计值 同上 先计算出DSUM函数值 先构造出行为省份 列为类别的二维表格 在行列交叉处输入DSUM函数 D5 选中需进行模拟运算的区域 G2 L8 然后 数据 模拟运算表 将省份和类别依次输入模拟运算表的引用行 D 3 列 E 3单元格 确定 以下内容部分不作要求 二 模拟运算表 续 利用控件控制汇总条件 分类字段 例3 10 在例3 9的基础上 利用DSUM 函数和模拟运算表 计算ABC公司不同省份 不同类别净销售额总计值 使用窗体中的组合框对省份进行控制 当选择组合框中的不同省份时 模拟运算表计算出该省份净销售额总计值 并以柱形图显示该结果 二 模拟运算表 续 第五节利用DSUM函数和控件控制分类汇总结果 利用控件控制汇总条件 年份 例3 11 在例3 10的基础上 使用组合框控制汇总的年份 并以柱形图形式显示各个年份各个类别的净销售额总计值 如图3 90所示 利用控件控制汇总字段 第五节利用DSUM函数和控件控制分类汇总结果 续 例3 12 在例3 11的基础上 使用列表框控制汇总字段为 净销售额 或 毛销售额 并以柱形图形式显示结果 如图3 92所示 例3 13 在例3 12的基础上 汇总特定省份和各省平均的净销售额 并以图形显示结果 如图3 94所示 第五节利用DSUM函数和控件控制分类汇总结果 续 例3 14 使用NorthwindTrader公司的销售数据 利用DSUM 和模拟运算表生成净销售额时间序列 创建可以调节的产品列表框 并绘制特定产品销售额的时间序列图形 第五节利用DSUM函数和控件控制分类汇总结果 续 第六节分类汇总数据的应用 企业经常在 观测板 上显示企业最关心的信息 销售额排在前十位产品的当月销售情况 最优秀员工目前的工作进度 销售额增长最快地区的销售增长率 以及销路最差产品的销售情况等等 例3 15 介绍为NorthwindTrader公司发现连续2个季度销量下滑产品的 观测版 的方法 零售超市需要对那些易变质食品打折 越接近保质期的产品打折比例越高 例3 16帮助超市找到需要打折的产品 确定打折比例 第六节分类汇总数据的应用 续 例3 15 NorthwindTrader公司将根据产品的销售情况来调整产品结构 他们决定找到那些连续两个季度销量都在下降的产品 分析产品销售量下降原因 进而决定是否停止这些产品的进货和销售 试为NorthwindTrader公司找到连续两个季度销量下降的产品 并以柱形图的形式显示这些产品最近三个季度的销量 如图3 106所示 第六节分类汇总数据的应用 续 第六节分类汇总数据的应用 续 第一步 获得所需数据 第二步 增加新字段 订购季度 第三步 计算不同季度和各产品的总销量 第四步 利用模拟运算表 计算97年第3季度 97年第4季度和98年第1季度各产品的销售总量 第五步 计算两个季度销量的下降数量 累计下降数量 并按照从小到大的顺序排列 第六步 找出连续两季度销量下降的产品以及其前3季度度 前2季度 前1季度的销量 第七步 绘制柱形图 第六节分类汇总数据的应用 续 例3 16 迅捷是一家便利店 销售日用品 饮料和食品等产品 对于部分易变质的饮料和食品 因为保质期比较短 他们总是先销售先进货的产品 他们为每种产品制订出了打折的开始时间 产品库存数据工作表中的打折提前天数 不同时间对应的打折比例 即每天打折比例 例如产品 康康原味酸奶 在到期的前3天给与15 的折扣 前2天给与30 的折扣 前1天给与45 的折扣 当天给与60 的折扣 如图3 114所示 迅捷便利店希望利用产品库存数据和进货数据 如图3 114和图3 115所示 建立模型 发现需要打折的产品 打折比例 第六节分类汇总数据的应用 续 产品库存数据 进货原始数据 第六节分类汇总数据的应用 续 第一步 在原有进货数据清单中增加新字段 产品进货次数 和 产品ID与订货次数 第二步 计算某产品的累计进货次数 第三步 计算当前库存产品中倒数第2次进货剩余的产品 第四步 计算产品到保质期剩余天数 第五步 找到该产品打折开始时间 计算打折比例 库存总数量 打折产品数量 第六步 利用模拟运算表计算所有产品的打折开始时间 计算打折比例 库存总数量 打折产品数量 第七步 制作不同汇总方式的组合框 第八步 准备绘图数据 第九步 绘图 第七节Excel中的多维数据分析 OLAP 多维数据分析的定义通过关系数据库创建多维数据集 多维数据分析的定义 OLAP定义数据字段和维确定数据字段确定维指定源数据 多维数据分析的定义 续 OLAP定义联机分析处理 OLAP OnlineAnalyticalProcessing 是根据层次分类来预先计算汇总值的一种组织数据的方式 它符合用户检索和分析数据的习惯 按照OLAP组织数据可以提高检索数据的速度 因为在创建或更改报表时 OLAP服务器 而不是MicrosoftExcel 会计算汇总值 并且只有汇总的数据 而非详细数据 送到Excel中 减少了数据量 提高了Excel的运算速度 多维数据分析的定义 续 数据字段和维OLAP多维数据集中包含 汇总值 被称为数据字段 即汇总字段 有关这些值的说明 即分类字段 则被组织成不同级别的明细数据 被称为维 有了数据字段和维 就可以很容易地在报表中显示较高级别的汇总以及低级别的汇总 多维数据分析的定义 续 确定数据字段数据字段是用户希望在报表中出现的汇总字段销售额 销售量 库存量多维数据集可按四种方式汇总数据字段 即求总计 计数 最小和最大值 多维数据分析的定义 续 确定维可以在分类层次上进行组织的说明字段构成了报表中的维 它包含数据某个方面的一组级别 如 地理位置 多维数据分析的定义 续 指定源数据多维数据集中的所有字段都必须是数据字段或维 若要使查询或选定的源数据能更高效地运行 并且使采用这些数据的报表更快更新 则应该只包含那些必须在多维数据集中使用的字段 通过关系数据库创建多维数据集 利用MicrosoftQuery调用 OLAP多维数据集向导 创建多维数据集 例3 17 针对NorthwindTrader公司的销售数据 利用MicrosoftQuery创建多维数据集文件 并通过数据透视表浏览按照客户所在地理位置汇总的销售额 利用Excel调用 OLAP多维数据集向导 创建多维数据集 例3 18 为NorthwindTrader公司创建脱机多维数据集文件 并通过数据透视表显示销售额 该销售额需按照产品的类型维 包括类型和产品名称字段 时间维 供应商地理位置维 包括国家 地区 城市 来汇总 通过关系数据库创建多维数据集 续 例3 17 针对NorthwindTrader公司的销售数据 利用MicrosoftQuery创建多维数据集文件 并通过数据透视表浏览按照客户所在地理位置汇总的销售额 第一步 利用MicrosoftQuery查询数据集中应包含的数据 第二步 创建OLAP多维数据集 第三步 利用数据透视表生成报表 浏览数据 通过关系数据库创建多维数据集 续 通过关系数据库创建多维数据集 续 求和 销售额 类别名称 产品名称 点心 点心汇总 调味品 谷类 麦片 海鲜 年 季度 月 饼干 蛋糕 1996 4 October 248 97 345 4124 2461 5812 November 739 739 6290 2507 1043 December 1497 2194 3143 1997 4 October 698 518 1216 5688 3031 6887 November 301 214 515 3785 5787 7882 December 1483 285 1768 5824 5242 5907 国家 全部 例3 18 为NorthwindTrader公司创建脱机多维数据集 并利用数据透视表显示销售额 该销售额需按照产品的类型维 包括类型和产品名称字段 时间维 供应商地理位置维 包括国家 地区 城市 进行汇总 本章小结 实现分类汇总的四种方法数据查询的统计值功能使用方便 效率高 但缺乏控制的灵活性Excel的数据清单功能步骤多 烦琐 但是很多功能可以应用于其他问题数据透视表功能使用最方便 可以把汇总表 旋转 从不同的 角度 查看数据 还可以筛选数据 合并数据 展开详细数据 或者选择部分数据加以查看D函数和模拟运算表能够汇总数据 并可对汇总数据加以控制 本章小结 续 DSUM 模拟预算表 内建函数可以汇总数据透视表无法提供的数据DSUM函数所提供汇总能力 模拟运算表的自动计算能力再配合Excel的内建函数可以为企业决策提供有利支持 比如 为企业决策者提供重要客户 产品 员工的 观测板 以及时了解对企业发展最为关键的信息 也能够帮助零售企业发现需打折的产品 确定打折比例等 本章小结 续 联机分析处理 OLAP OnlineAnalyticalProcessing 是根据层次分类来预先计算汇总值的一种组织数据的方式 OLAP多维数据集仅提供汇总结果 不提供详细数据 OLAP多维数据集在Excel中只能通过数据透视表或数据透视图显示