《excel_数据分类汇总分析》由会员分享，可在线阅读，更多相关《excel_数据分类汇总分析（70页珍藏版）》请在金锄头文库上搜索。
1、Excel预测与决策分析第三章第三章 数据分类汇总分析数据分类汇总分析n基础篇n数据分类汇总分析的意义和作用n数据分类汇总的四种方法n数据透视表nD函数和模拟运算表n提高篇n利用DSUM函数和控件控制分类汇总结果n分类汇总数据的应用nExcel中的多维数据分析OLAP内容简介：第一节 数据分类汇总分析的意义和作用n获得销售额分类统计值ABC公司1995年不同省份不同类别商品的销售额总计值01000020000300004000050000艺术品 自行车服装食品 儿童用品体育用品瓷器类别销售额江西安徽江苏 广东 福建 浙江山东第一节 数据分类汇总分析的意义和作用（续）n获得各类销售额排行榜Nor
2、thwind公司前十大客户销售额03000600090001200015000正人资源五洲信托嘉业艾德高科技幸义房屋迈多贸易百达电子悦海上河工业池春建设客户公 司名称销售额Northwind公司前十大产品销售额03000600090001200015000猪肉猪肉干鸭肉绿茶白米苏澳奶酪运动饮料山渣片墨鱼牛肉干产品 名称销售额第一节 数据分类汇总分析的意义和作用（续）n获得各类销售额的时间序列Northwind公司不同产品的销售额时间序列y = 39.401x - 39.299 R2 = 0.4649020040060080010001200140016001996年7月1996年9月1996年
3、11月1997年1月1997年3月1997年5月1997年7月1997年9月1997年11月1998年1月1998年3月时间销售额花生第一节 数据分类汇总分析的意义和作用（续）n获得各经济量之间的相关性Northwind公司分段平均运货费与平均销售金额的依赖关系y = 3.0652x + 292.37 R2 = 0.846902004006008001000020406080100120140160180200平均 运货费平均销售额第一节 数据分类汇总分析的意义和作用（续）n获得各种产品需求量的频率分布Northwind公司1997年白米月销量分布图012345203550658095110
4、125 140 155 170 185 200 销量分组销售发生次数第二节数据分类汇总的四种方法n数据查询的统计值功能n数据清单功能n数据透视表nD函数与模拟运算表第二节数据分类汇总的四种方法n汇总字段 ：待汇总的变量或字段。n销售额、销售数量、工作时间等。n分类字段 ：对数据进行汇总时，以某个 变量或字段的不同值为参考来对汇总字段 进行汇总。n参考不同产品类别 (分类字段) 汇总销售额（汇总字 段）n参考不同的生产人员姓名（分类字段）汇总工作时 间 （汇总字段）第二节 数据分类汇总的四种方法（续）ABC公司1995各省份、各类别商品净销售额总计值 （图3-7）一、数据查询的统计值功能n从Ex
5、cel中启动Microsoft Query，利用Query的统计 值功能，将数据汇总后返回Exceln【例3-1】 ABC公司销售数据.dbf中保存着公司从 1994年到1997年三年的销售数据，利用Query软件 的统计值功能汇总该公司的销售数据，并按照图3-7 的形式，汇总出1995年不同省份、不同类别商品的 净销售额。一、数据查询的统计值功能（续） 二、Excel数据清单功能n数据清单 数据清单是包含相关数据的一系列工作表 数据行 。 数据清单的首行为字段名，首行下的各行 是各个记录，数据清单中不能出现空行 。n数据清单的基本功能n排序n筛选n分类总计二、Excel数据清单功能（续）n利
6、用Excel数据清单处理功能进行分类汇 总的一般步骤：n获得数据清单；n设定筛选条件；n筛选数据，将筛选结果复制到工作表其他位 置；n对经过筛选的数据按分类字段进行排序；n对排序后的数据进行分类汇总。二、Excel数据清单功能（续）n【例3-2】利用Excel的数据清单功能，将ABC公司的销售数 据按照图3-7的形式，汇总出1995年不同省份、不同类别商 品的净销售额总计值。n 将上述汇总表选 中第3级汇总，然后 选中所需数据区域， 单击“选定可见单元 格”（工具-自定义-编 辑），再单击“复制” ，将光标移到空白数 据区域，单击粘贴， 得到如图数据。n 根据右图数据再 加以调整，便可得到 如
7、图3-7的数据。第三节 数据透视表n数据透视表的基本功能n数据透视表结构的灵活性n数据透视图的灵活性n利用数据透视表生成时间序列n统计不同规模销量的发生次数以及概率分布n计算占同列数据总和的百分比一、数据透视表的基本功能n数据透视表分类汇总的两种方法n先将数据导入Excel成为数据清单，利用数 据透视表汇总对数据清单进行汇总n利用数据透视表直接从数据库中查询、并汇 总数据一、数据透视表的基本功能（续）n【例3-3】利用Excel获取外部数据功能，获取 ABC公司1995年的销售数据，使用Excel数据透 视表功能制作如图3-7所示的分类汇总表。提示：启动excel-数据数据透视表和数据透视图
8、导入外部数据新建数据库查询，在query 获得汇总数据后返回excel，单击数据透视图的 “图表向导”，生成数据透视图。一、数据透视表的基本功能（续）一、数据透视表的基本功能（续）05000100001500020000250003000035000400004500050000儿童用品服装食品体育用品艺术品自行车请将页字段拖至此处求和项:净销售额类别安徽 广东 江苏 江西 山东省份n【例3-4】直接利用Excel数据透视表功能从数据库中查询数据，并根据查询结果汇 总，制作出如图3-7所示的分类汇总表。n提示：启动excel-数据数据透视表和数 据透视图外部数据源二、数据透视表结构的灵活性n改
9、变数据透视表的行列结构n利用页域对数据进行分页汇总n新增、删除和修改行域、列域或页域字段（分类 字段）n新增、删除和修改汇总字段n改变汇总字段的汇总方式n同一个字段既作分类字段又作汇总字段二、数据透视表结构的灵活性（续）n改变分类字段值的位置n对分类字段的值加以合并n隐藏行域、列域的字段值n展开汇总值，显示详细数据三、数据透视图的灵活性n变换分类字段的位置n将行、列分类字段换成页分类字段n新增、删除分类字段n新增、删除汇总字段n改变被汇总方式n显示分类字段的部分值四、利用数据透视表生成时间序列n时间序列: 按照一定时间间隔汇总的数据序列n【例3-5】利用数据透视表，对Northwind Tra
10、der公司的销售 数据按月汇总种各产品销售额。例3-5 操作方法n数据进行数据查询-query，获得Northwind公司的订 购日期，产品名称，销售额(订单明细.单价*数量*(1-折 扣)n光标停留在目标数据的任意单元格，数据数据透视 表和数据透视图Microsoft Excel数据列表或数据库 数据透视表，next，新建工作表，next。n订购日期拖至行域，销售额拖至列域，产品名称拖至页 域。n右击目标数据任意单元格，组及显示明细数据组合 ，起始于1996-7-1，终止于1998-5-31，依据为先月，后 年。五、统计不同规模销量的发生次数及频率分布n【例3-6】利用数据透 视表，对Nor
11、thwind公 司的销售数据按照不 同产品、不同时间段 ，统计不同规模销售 数量的发生次数，并 计算其频率分布。例3-6操作方法n利用外部数据获得Northwind公司产品名称，订购日期，数量。n选定数据清单任意单元格，数据-数据透视表和数据透视图，默认 选项next。n订购日期拖至行域，产品名称拖至页域，数量拖至数据域。n双击“求和项：数量”，汇总方式选“计数”-确定。n按例3-5方法对年月进行分组，然后将“年”和“订购日期”拖到页域 ，从数据透视表字段列表中将“数量”拖至行域，在数量列任意单元 格右键，组及显示明细数据-组合，起始于1，终止于130，确定。n右击“数量”数据任意单元格，表格
12、选项，去掉行、列总计。nD6单元格输入“销量分组”，E6输入“频率”，D7输入“=A7”，E7输 入“B7/SUM($B$7:$B$19)”，将E7数据格式设为百分数，小数点 保留一位，并将D7和E7复制到D19和E19单元格。n绘制频率分布图：选中D6到E19，单击图表向导，选柱状图，next ，next。五、统计不同规模销量的发生次数及频率分布Northwind公司不同规模销量的频率分布0%5%10%15%20%25%30%35%1-1011-2021-3031-4041-5051-6061-7071-8081-9091-100101-110111-120121-130产品名称 (全部)年
13、 (全部)订购日期 (全部)计数项:数量数量汇总在此处放置系列字段第四节 D函数和模拟运算表nD函数n模拟运算表n生成时间序列n统计不同规模销量的发生次数以及概率 分布一、 D函数nD函数：对数据清单或数据库中的数 据进行分析有 12个数据库内建函数 。n常用的D函数包括：nDSUM()nDAVERAGE()nDCOUNT()nDCOUNTA()一、 D函数（续）nD函数名称(数据清单,统计字段,条件范围) n统计满足条件记录制定字段的总计值。n数据清单：包含相关数据的工作表中的一 系列数据行n统计字段：被汇总字段。可以使用字段名 、字段名所在的单元格、或者字段在数据 清单中所在列的序号。n条
14、件范围：规定函数的匹配条件的一个工 作表区域。D函数只对满足该条件范围所 规定条件的记录进行汇总。一、 D函数（续）n【例3-8】利用ABC公司的销售数据，应用 DSUM()函数，生成一个与图3-7相同的数据 分类汇总表，列出ABC公司1995年不同省份不同类别净销售额总计值。例3-8操作方法n1、获得数据清单。日期，省份，类别，净销售额，毛 销售额。n2、命名一空白工作薄为DSUM()函数用法，在单元格 B2:E2输入：日期、日期、省份、类别。在B3:E3输入 ：=95/1/1、模拟运算表，将省份和类别依次 输入模拟运算表的引用行$D$3、列$E$3单元格 ，确定。以下内容部分不作要求二、模
15、拟运算表（续）n利用控件控制汇总条件分类字段n【例3-10】在例3-9的基础上，利用DSUM()函数和 模拟运算表，计算ABC公司不同省份、不同类别净销售额总计值，使用窗体中的组合框对省份进行控 制，当选择组合框中的不同省份时，模拟运算表计 算出该省份净销售额总计值，并以柱形图显示该结 果。二、模拟运算表（续）各个省份不同类别净销售总计值图020000400006000080000100000120000140000儿童用品 服装食品体育用品 艺术品自行车 类别净 销 售 额全部第五节 利用DSUM函数和控件控制分类汇总结果n利用控件控制汇总条件年份n n【例例3-113-11】 在例在例3-
16、103-10的的 基础上，使基础上，使 用组合框控用组合框控 制汇总的年制汇总的年 份，并以柱份，并以柱 形图形式显形图形式显 示各个年份示各个年份 各个类别的各个类别的 净销售额总净销售额总 计值，如图计值，如图3 3 - 90- 90所示。所示。各年各省份不同类别净销售总计值图0100002000030000400005000060000儿童用品 服装食品体育用品 艺术品自行车类别净销售额安徽1996n利用控件控制汇总字段第五节 利用DSUM函数和控件控制分类汇总结果（续）【例例3-123-12】 在例在例3-113-11的的 基础上，使基础上，使 用列表框控用列表框控 制汇总字段制汇总字段 为为“ “净销售净销售 额额” ”或或“ “毛销毛销 售额售额” ”，并，并 以柱形图形以柱形图形 式显示结果式显示结果 ，如图，如图3-923-92 所示所示 。各个省份不同类别净销售总计值图0100020003000400050006000儿童用品 服装食品体育用品 艺术品自行车类别净销售额
