EXCEL第3章_数据分类汇总分析
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
分类字段 ：对数据进行汇总时，以某个 变量或字段的不同值为参考来对汇总字段 进行汇总。
参考不同产品类别 (分类字段) 汇总销售额（汇总 字段）
参考不同的生产人员姓名（分类字段）汇总工作时 间 （汇总字段）
第二节 数据分类汇总的四种方法（续）
1995年ABC公司各省各类别商品的销售额
类别 安徽 广东 江苏 江西 山东
五、统计不同规模销量的发生次数及频率分布
【例3-6】利用数据透 视表，对Northwind公 司的销售数据按照不 同产品、不同时间段， 统计不同规模销售数 量的发生次数，并计 算其频率分布。
产品名称 年 订购日期
计数项:数量 数量 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
Northwind公司前十大产品销售额
销1售50额00
Northwind公司前十大客户销售额
1500102000
12000 9000
9000 6000
6000 3000
3000 0
0
产品
名称 客户公
司名称
正人资源 猪肉
五洲信托 猪肉干 嘉业 鸭肉
艾德高科技 绿茶
幸义房屋 白米
迈多贸易 苏澳奶酪
百达电子 运动饮料 悦海 山渣片
D函数名称(数据清单,统计字段,条件范围) 统计满足条件记录制定字段的总计值。
数据清单：包含相关数据的工作表中的一 系列数据行
统计字段：被汇总字段。可以使用字段名、 字段名所在的单元格、或者字段在数据清 单中所在列的序号。
条件范围：规定函数的匹配条件的一个工 作表区域。D函数只对满足该条件范围所 规定条件的记录进行汇总。
订购日期拖至行域，产品名称拖至页域，数量拖至数据域。
双击“求和项：数量”，汇总方式选“计数”->确定。
按例3-5方法对年月进行分组，然后将“年”和“订购日期”拖到 页域，从数据透视表字段列表中将“数量”拖至行域，在数量列 任意单元格右键，组及显示明细数据->组合，起始于1，终止于 130，确定。
(全部) (全部) (全部)
汇总 28.28% 29.49% 19.19% 9.92% 5.75% 3.34% 1.76% 0.88% 0.28% 0.51% 0.14% 0.37% 0.09%
例3-6操作方法
利用外部数据获得Northwind公司产品名称，订购日期，数量。
选定数据清单任意单元格，数据->数据透视表和数据透视图，默 认选项next。
ABC公司1995各省份、各类别商品净销售额总计值 （图3-7）
一、数据查询的统计值功能
从Excel中启动Microsoft Query，利用Query的统计 值功能，将数据汇总后返回Excel
【例3-1】 ABC公司销售数据.dbf中保存着公司从 1994年到1997年三年的销售数据，利用Query软件 的统计值功能汇总该公司的销售数据，并按照图3-7 的形式，汇总出1995年不同省份、不同类别商品的 净销售额。
光标停留在目标数据的任意单元格，数据－>数据透视 表和数据透视图－>Microsoft Excel数据列表或数据库 －数据透视表，next，新建工作表，next。
订购日期拖至行域，销售额拖至列域，产品名称拖至 页域。
右击目标数据任意单元格，组及显示明细数据－>组合， 起始于1996-7-1，终止于1998-5-31，依据为先月，后年。
五、统计不同规模销量的发生次数及频率分布
产品名称 (全部)
年 (全部) 订购日期 (全部) Northwind公司不同规模销量的频率分布
计数项:数量
35% 30% 25% 20% 15% 10% 5% 0%
在此处放置系列字段
汇总
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函数（续）
【例3-8】利用ABC公司的销售数据，应用 DSUM()函数，生成一个与图3-7相同的数据 分类汇总表，列出ABC公司1995年不同省份 不同类别净销售额总计值。
例3-8操作方法
1、获得数据清单。日期，省份，类别，净销售额，毛 销售额。
三、数据透视图的灵活性
变换分类字段的位置 将行、列分类字段换成页分类字段 新增、删除分类字段 新增、删除汇总字段 改变被汇总方式 显示分类字段的部分值
四、利用数据透视表生成时间序列
时间序列: 按照一定时间间隔汇总的数据序列
【例3-5】利用数据透视表，对Northwind Trader公司的销售 数据按月汇总种各产品销售额。
一、数据查询的统计值功能（续）
A 1 省份 2 安徽 3 安徽 4 安徽 5 安徽 6 安徽 7 安徽 8 广东 9 广东 10 广东 11 广东 12 广东 13 江苏 14 江苏 15 江苏 16 江苏 17 江苏 18 江西
B
类别
总和
儿童用品
服装
食品
体育用品
艺术品
自行车
儿童用品
食品
体育用品
艺术品
产品名称
(全部)
求和项:销售额 年 1996年
1997年
订购日期 7月 8月 9月 10月 11月 12月 1月 2月
汇总 27861.89 25485.27 26381.40 37515.72 45600.04 45239.63 61258.07 38483.63
例3-5 操作方法
数据－进行数据查询->query，获得Northwind公司的 订购日期，产品名称，销售额(订单明细.单价*数量*(1折扣))
儿童用品 39686 524 2044 40255 24367
服装 16255
47196
食品 32855 472 2856 16404 19269
体育用品 10850 1080 5113 13970 3869
艺术品 42009 2893 4119 43932 24990
自行车 12522 746 4211 20383 24728
获得销售额分类统计值
销售额
50000 40000 30000
20000
10000
ABC公司1995年不同省份不同类别商品的销售额总计值
安山东徽
江西 江苏 广东 福建 浙江
0 艺术品 自行车 服装 食品 儿童用品体育用品 瓷器 类别
第一节 数据分类汇总分析的意义和作用（续）
获得各类销售额排行榜
销售额
1996年7月 1996年9月 1996年11月 1997年1月 1997年3月 1997年5月 1997年7月 1997年9月 1997年11月 1998年1月 1998年3月
第一节 数据分类汇总分析的意义和作用（续）
获得各经济量之间的相关性
平均销售额
1000 800
Northwind公司分段平均运货费与平均销售金额的依赖关系
数量
第四节 D函数和模拟运算表
D函数 模拟运算表 生成时间序列 统计不同规模销量的发生次数以及概率
分布
一、 D函数
D函数：对数据清单或数据库中的数 据进行分析有 12个数据库内建函数。
常用的D函数包括：
DSUM() DAVERAGE() DCOUNT() DCOUNTA()
一、 D函数（续）
上河工业 墨鱼
池春建设 牛肉干
第一节 数据分类汇总分析的意义和作用（续）
获得各类销售额的时间序列
销售额 1600 1400 1200 1000
800 600 400 200
0
Northwind公司不同产品的销售额时间序列 花生 y = 39.401x - 39.299 R2 = 0.4649
时间
自行车
儿童用品
食品
体育用品
艺术品
自行车
儿童用品
C 净销售额
39686 16255 32855 10850 42009 12522
524 472 1080 2893 746 2044 2856 5113 4119 4211 40255
二、Excel数据清单功能
数据清单
数据清单是包含相关数据的一系列工作表 数据行 。
数据透视表分类汇总的两种方法
先将数据导入Excel成为数据清单，利用数 据透视表汇总对数据清单进行汇总
利用数据透视表直接从数据库中查询、并 汇总数据
一、数据透视表的基本功能（续）
【例3-3】利用Excel获取外部数据功能，获取 ABC公司1995年的销售数据，使用Excel数据 透视表功能制作如图3-7所示的分类汇总表。
二、Excel数据清单功能（续）
【例3-2】利用Excel的数据清单功能，将ABC公司的销售数 据按照图3-7的形式，汇总出1995年不同省份、不同类别商 品的净销售额总计值。
将上述汇总表选
中第3级汇总，然后
选中所需数据区域，
单击“选定可见单元
格”（工具-自定义-
编辑），再单击“复
制”，将光标移到空
5000
0 儿童用品 服装
食品 体育用品 艺术品
类别
省份 安徽 广东 江苏 江西 山东
自行车
【例3-4】直接利用Excel数据透视表功能 从数据库中查询数据，并根据查询结果汇 总，制作出如图3-7所示的分类汇总表。
提示：启动excel-数据－数据透视表和数 据透视图－外部数据源
二、数据透视表结构的灵活性
5
4
3
2 1
0 20 35 50 65 80 95 110 125 140 155 170 185 200 销量分组
第二节 数据分类汇总的四种方法
数据查询的统计值功能 数据清单功能 数据透视表 D函数与模拟运算表
第二节 数据分类汇总的四种方法
汇总字段 ：待汇总的变量或字段。
销售额、销售数量、工作时间等。
白数据区域，单击粘
贴，得到如图数据。
ቤተ መጻሕፍቲ ባይዱ
根据右图数据再
加以调整，便可得到
如图3-7的数据。
第三节 数据透视表
数据透视表的基本功能 数据透视表结构的灵活性 数据透视图的灵活性 利用数据透视表生成时间序列 统计不同规模销量的发生次数以及概率分布 计算占同列数据总和的百分比
一、数据透视表的基本功能
右击“数量”数据任意单元格，表格选项，去掉行、列总计。
D6单元格输入“销量分组”，E6输入“频率”，D7输入“=A7”， E7输入“＝B7/SUM($B$7:$B$19)”，将E7数据格式设为百分数， 小数点保留一位，并将D7和E7复制到D19和E19单元格。
绘制频率分布图：选中D6到E19，单击图表向导，选柱状图，next， next。
改变数据透视表的行列结构 利用页域对数据进行分页汇总 新增、删除和修改行域、列域或页域字段（分
类字段） 新增、删除和修改汇总字段 改变汇总字段的汇总方式 同一个字段既作分类字段又作汇总字段
二、数据透视表结构的灵活性（续）
改变分类字段值的位置 对分类字段的值加以合并 隐藏行域、列域的字段值 展开汇总值，显示详细数据
数据清单的首行为字段名，首行下的各行
是各个记录，数据清单中不能出现空行 。 数据清单的基本功能
排序 筛选 分类总计
二、Excel数据清单功能（续）
利用Excel数据清单处理功能进行分类汇 总的一般步骤：
获得数据清单； 设定筛选条件； 筛选数据，将筛选结果复制到工作表其他
位置； 对经过筛选的数据按分类字段进行排序； 对排序后的数据进行分类汇总。
y = 3.0652x + 292.37 R2 = 0.8469
600
400
200
0 0
平均 运货费
20 40 60 80 100 120 140 160 180 200
销售发生次数
第一节 数据分类汇总分析的意义和作用（续）
获得各种产品需求量的频率分布
Northwind公司1997年白米月销量分布图
提示：启动excel-数据－数据透视表和数据透视图 －导入外部数据－新建数据库查询，在query 获得汇总数据后返回excel，单击数据透视图的 “图表向导”，生成数据透视图。
一、数据透视表的基本功能（续）
一、数据透视表的基本功能（续）
请将页字段拖至此处
求和项:净销售额
50000 45000 40000 35000 30000 25000 20000 15000 10000
Excel预测与决策分析
第三章 数据分类汇总分析
内容简介：
基础篇
数据分类汇总分析的意义和作用 数据分类汇总的四种方法 数据透视表 D函数和模拟运算表
提高篇
利用DSUM函数和控件控制分类汇总结果 分类汇总数据的应用 Excel中的多维数据分析——OLAP
第一节 数据分类汇总分析的意义和作用
参考不同产品类别 (分类字段) 汇总销售额（汇总 字段）
参考不同的生产人员姓名（分类字段）汇总工作时 间 （汇总字段）
第二节 数据分类汇总的四种方法（续）
1995年ABC公司各省各类别商品的销售额
类别 安徽 广东 江苏 江西 山东
五、统计不同规模销量的发生次数及频率分布
【例3-6】利用数据透 视表，对Northwind公 司的销售数据按照不 同产品、不同时间段， 统计不同规模销售数 量的发生次数，并计 算其频率分布。
产品名称 年 订购日期
计数项:数量 数量 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
Northwind公司前十大产品销售额
销1售50额00
Northwind公司前十大客户销售额
1500102000
12000 9000
9000 6000
6000 3000
3000 0
0
产品
名称 客户公
司名称
正人资源 猪肉
五洲信托 猪肉干 嘉业 鸭肉
艾德高科技 绿茶
幸义房屋 白米
迈多贸易 苏澳奶酪
百达电子 运动饮料 悦海 山渣片
D函数名称(数据清单,统计字段,条件范围) 统计满足条件记录制定字段的总计值。
数据清单：包含相关数据的工作表中的一 系列数据行
统计字段：被汇总字段。可以使用字段名、 字段名所在的单元格、或者字段在数据清 单中所在列的序号。
条件范围：规定函数的匹配条件的一个工 作表区域。D函数只对满足该条件范围所 规定条件的记录进行汇总。
订购日期拖至行域，产品名称拖至页域，数量拖至数据域。
双击“求和项：数量”，汇总方式选“计数”->确定。
按例3-5方法对年月进行分组，然后将“年”和“订购日期”拖到 页域，从数据透视表字段列表中将“数量”拖至行域，在数量列 任意单元格右键，组及显示明细数据->组合，起始于1，终止于 130，确定。
(全部) (全部) (全部)
汇总 28.28% 29.49% 19.19% 9.92% 5.75% 3.34% 1.76% 0.88% 0.28% 0.51% 0.14% 0.37% 0.09%
例3-6操作方法
利用外部数据获得Northwind公司产品名称，订购日期，数量。
选定数据清单任意单元格，数据->数据透视表和数据透视图，默 认选项next。
ABC公司1995各省份、各类别商品净销售额总计值 （图3-7）
一、数据查询的统计值功能
从Excel中启动Microsoft Query，利用Query的统计 值功能，将数据汇总后返回Excel
【例3-1】 ABC公司销售数据.dbf中保存着公司从 1994年到1997年三年的销售数据，利用Query软件 的统计值功能汇总该公司的销售数据，并按照图3-7 的形式，汇总出1995年不同省份、不同类别商品的 净销售额。
光标停留在目标数据的任意单元格，数据－>数据透视 表和数据透视图－>Microsoft Excel数据列表或数据库 －数据透视表，next，新建工作表，next。
订购日期拖至行域，销售额拖至列域，产品名称拖至 页域。
右击目标数据任意单元格，组及显示明细数据－>组合， 起始于1996-7-1，终止于1998-5-31，依据为先月，后年。
五、统计不同规模销量的发生次数及频率分布
产品名称 (全部)
年 (全部) 订购日期 (全部) Northwind公司不同规模销量的频率分布
计数项:数量
35% 30% 25% 20% 15% 10% 5% 0%
在此处放置系列字段
汇总
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函数（续）
【例3-8】利用ABC公司的销售数据，应用 DSUM()函数，生成一个与图3-7相同的数据 分类汇总表，列出ABC公司1995年不同省份 不同类别净销售额总计值。
例3-8操作方法
1、获得数据清单。日期，省份，类别，净销售额，毛 销售额。
三、数据透视图的灵活性
变换分类字段的位置 将行、列分类字段换成页分类字段 新增、删除分类字段 新增、删除汇总字段 改变被汇总方式 显示分类字段的部分值
四、利用数据透视表生成时间序列
时间序列: 按照一定时间间隔汇总的数据序列
【例3-5】利用数据透视表，对Northwind Trader公司的销售 数据按月汇总种各产品销售额。
一、数据查询的统计值功能（续）
A 1 省份 2 安徽 3 安徽 4 安徽 5 安徽 6 安徽 7 安徽 8 广东 9 广东 10 广东 11 广东 12 广东 13 江苏 14 江苏 15 江苏 16 江苏 17 江苏 18 江西
B
类别
总和
儿童用品
服装
食品
体育用品
艺术品
自行车
儿童用品
食品
体育用品
艺术品
产品名称
(全部)
求和项:销售额 年 1996年
1997年
订购日期 7月 8月 9月 10月 11月 12月 1月 2月
汇总 27861.89 25485.27 26381.40 37515.72 45600.04 45239.63 61258.07 38483.63
例3-5 操作方法
数据－进行数据查询->query，获得Northwind公司的 订购日期，产品名称，销售额(订单明细.单价*数量*(1折扣))
儿童用品 39686 524 2044 40255 24367
服装 16255
47196
食品 32855 472 2856 16404 19269
体育用品 10850 1080 5113 13970 3869
艺术品 42009 2893 4119 43932 24990
自行车 12522 746 4211 20383 24728
获得销售额分类统计值
销售额
50000 40000 30000
20000
10000
ABC公司1995年不同省份不同类别商品的销售额总计值
安山东徽
江西 江苏 广东 福建 浙江
0 艺术品 自行车 服装 食品 儿童用品体育用品 瓷器 类别
第一节 数据分类汇总分析的意义和作用（续）
获得各类销售额排行榜
销售额
1996年7月 1996年9月 1996年11月 1997年1月 1997年3月 1997年5月 1997年7月 1997年9月 1997年11月 1998年1月 1998年3月
第一节 数据分类汇总分析的意义和作用（续）
获得各经济量之间的相关性
平均销售额
1000 800
Northwind公司分段平均运货费与平均销售金额的依赖关系
数量
第四节 D函数和模拟运算表
D函数 模拟运算表 生成时间序列 统计不同规模销量的发生次数以及概率
分布
一、 D函数
D函数：对数据清单或数据库中的数 据进行分析有 12个数据库内建函数。
常用的D函数包括：
DSUM() DAVERAGE() DCOUNT() DCOUNTA()
一、 D函数（续）
上河工业 墨鱼
池春建设 牛肉干
第一节 数据分类汇总分析的意义和作用（续）
获得各类销售额的时间序列
销售额 1600 1400 1200 1000
800 600 400 200
0
Northwind公司不同产品的销售额时间序列 花生 y = 39.401x - 39.299 R2 = 0.4649
时间
自行车
儿童用品
食品
体育用品
艺术品
自行车
儿童用品
C 净销售额
39686 16255 32855 10850 42009 12522
524 472 1080 2893 746 2044 2856 5113 4119 4211 40255
二、Excel数据清单功能
数据清单
数据清单是包含相关数据的一系列工作表 数据行 。
数据透视表分类汇总的两种方法
先将数据导入Excel成为数据清单，利用数 据透视表汇总对数据清单进行汇总
利用数据透视表直接从数据库中查询、并 汇总数据
一、数据透视表的基本功能（续）
【例3-3】利用Excel获取外部数据功能，获取 ABC公司1995年的销售数据，使用Excel数据 透视表功能制作如图3-7所示的分类汇总表。
二、Excel数据清单功能（续）
【例3-2】利用Excel的数据清单功能，将ABC公司的销售数 据按照图3-7的形式，汇总出1995年不同省份、不同类别商 品的净销售额总计值。
将上述汇总表选
中第3级汇总，然后
选中所需数据区域，
单击“选定可见单元
格”（工具-自定义-
编辑），再单击“复
制”，将光标移到空
5000
0 儿童用品 服装
食品 体育用品 艺术品
类别
省份 安徽 广东 江苏 江西 山东
自行车
【例3-4】直接利用Excel数据透视表功能 从数据库中查询数据，并根据查询结果汇 总，制作出如图3-7所示的分类汇总表。
提示：启动excel-数据－数据透视表和数 据透视图－外部数据源
二、数据透视表结构的灵活性
5
4
3
2 1
0 20 35 50 65 80 95 110 125 140 155 170 185 200 销量分组
第二节 数据分类汇总的四种方法
数据查询的统计值功能 数据清单功能 数据透视表 D函数与模拟运算表
第二节 数据分类汇总的四种方法
汇总字段 ：待汇总的变量或字段。
销售额、销售数量、工作时间等。
白数据区域，单击粘
贴，得到如图数据。
ቤተ መጻሕፍቲ ባይዱ
根据右图数据再
加以调整，便可得到
如图3-7的数据。
第三节 数据透视表
数据透视表的基本功能 数据透视表结构的灵活性 数据透视图的灵活性 利用数据透视表生成时间序列 统计不同规模销量的发生次数以及概率分布 计算占同列数据总和的百分比
一、数据透视表的基本功能
右击“数量”数据任意单元格，表格选项，去掉行、列总计。
D6单元格输入“销量分组”，E6输入“频率”，D7输入“=A7”， E7输入“＝B7/SUM($B$7:$B$19)”，将E7数据格式设为百分数， 小数点保留一位，并将D7和E7复制到D19和E19单元格。
绘制频率分布图：选中D6到E19，单击图表向导，选柱状图，next， next。
改变数据透视表的行列结构 利用页域对数据进行分页汇总 新增、删除和修改行域、列域或页域字段（分
类字段） 新增、删除和修改汇总字段 改变汇总字段的汇总方式 同一个字段既作分类字段又作汇总字段
二、数据透视表结构的灵活性（续）
改变分类字段值的位置 对分类字段的值加以合并 隐藏行域、列域的字段值 展开汇总值，显示详细数据
数据清单的首行为字段名，首行下的各行
是各个记录，数据清单中不能出现空行 。 数据清单的基本功能
排序 筛选 分类总计
二、Excel数据清单功能（续）
利用Excel数据清单处理功能进行分类汇 总的一般步骤：
获得数据清单； 设定筛选条件； 筛选数据，将筛选结果复制到工作表其他
位置； 对经过筛选的数据按分类字段进行排序； 对排序后的数据进行分类汇总。
y = 3.0652x + 292.37 R2 = 0.8469
600
400
200
0 0
平均 运货费
20 40 60 80 100 120 140 160 180 200
销售发生次数
第一节 数据分类汇总分析的意义和作用（续）
获得各种产品需求量的频率分布
Northwind公司1997年白米月销量分布图
提示：启动excel-数据－数据透视表和数据透视图 －导入外部数据－新建数据库查询，在query 获得汇总数据后返回excel，单击数据透视图的 “图表向导”，生成数据透视图。
一、数据透视表的基本功能（续）
一、数据透视表的基本功能（续）
请将页字段拖至此处
求和项:净销售额
50000 45000 40000 35000 30000 25000 20000 15000 10000
Excel预测与决策分析
第三章 数据分类汇总分析
内容简介：
基础篇
数据分类汇总分析的意义和作用 数据分类汇总的四种方法 数据透视表 D函数和模拟运算表
提高篇
利用DSUM函数和控件控制分类汇总结果 分类汇总数据的应用 Excel中的多维数据分析——OLAP
第一节 数据分类汇总分析的意义和作用