类别
(多项)
求和项:净销售额 行标签
华东 安徽 低 中 高 江苏 山东 低 高 中
华南 总计
列标签
零售
批发 总计
9811 24881 34692 4859 5861 10720 8218 2311 10529 648 1396 2044
8805 25193 33998 744 18671 19415 16402 1748 18150 1790 38989 40779 51277 119050 170327
另一种是利用数据透视表直接从数据库中查询 并汇总数据。
20
【例3-3】利用Excel获取外部数据功能， 获取ABC公司2010年的销售数据，使用 Excel数据透视表功能，制作如图3-7所示 的分类汇总表，并制作如图3-30所示的数 据透视图。
21
第一步，获得数据列表。 空白工作薄-数据-获取外部数据-自其他来源
03版：编辑—定位—定位18 条件—可见单元格
数据透视表汇总数据 数据透视表的灵活性 数据透视图的灵活性 利用数据透视表生成时间序列 统计各销量组销售次数的频率分布 计算百分比 多重区域数据的合并汇总
19
利用数据透视表进行汇总有两种方法：
一种是先将数据导入Excel成为数据列表，再 对数据列表进行汇总；
39
产品名称 (全部)
年
(全部)
月
(全部)
行标签 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 总计
计数项:数量 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%
37
【例3-5】利用 数据透视表，对 Northwind公司 的销售数据按月 汇总各产品的销 售额。
分组：步长-同时 选中月、年
产品名称 (全部)
行标签
求和项:'销售额'
1996年
7月
27861.89
8月
25485.27
9月
26381.40
10月
37515.72
11月
45600.04
12月
45239.63
选项 设计
35
创建数据透视图的灵活性
利用数据列表创建数据透视图； 直接创建数据透视图； 利用数据透视表（图）向导创建数据透视图。
分类字段、汇总字段调整
36
按照一定时间间隔汇总的数据序列，称为 时间序列。
时间序列可以帮助企业了解经营状况、预 测未来的变化趋势。
数据透视表能从不同的角度汇总数据，它 也是生成时间序列的有效工具。
600 400 200
0
Northwind公司销售额的时间序列
y = 54.287x - 70.843 R²= 0.4325
6
1996年7月 1996年8月 1996年9月 1996年10月 1996年11月 1996年12月 1997年1月 1997年2月 1997年3月 1997年4月 1997年5月 1997年6月 1997年7月 1997年8月 1997年9月 1997年10月 1997年11月 1997年12月 1998年1月 1998年2月 1998年3月 1998年4月
24
求和项:净销售额
50000 45000 40000 35000 30000 25000 20000 15000 10000
5000 0
儿童用品
服装
类别
省份
安徽 广东 江苏 江西 山东
食品 体育用品 艺术品 自行车
25
【例3-3】直接利用Excel数据透视表的获 取外部数据功能，获取并汇总数据，制作 出如图3-7所示的分类汇总表。
高级筛选需首先在工作表中设定筛选条件。
分类汇总功能
可自动计算汇总字段的总计值。
11
【例3-1】利用Excel的数据列表功能和 SUM函数，将ABC公司的销售数据，按 照图3-7的形式，汇总出2010年各省份各 类别商品的净销售额总计值。
利用Excel数据列表功能和SUM函数分 类汇总。
12
第一步，获得数据列表
17
把汇总数据复制到另一张工作表，需要EXCEL 的一个工具---“选择可见单元格”
该工具只选中显示出的单元格，不选中隐藏 单元格。
07版：office图标---EXCEL选项（右下角）-自定义—“从下列位置选择命令”—“不 在功能区的命令”—“选定可见单元格”— 添加—确定
OR：开始—编辑—查找和选择—定位条件可见单元格
40%
35%
30%
销 25%
售 频
20%
率 15%
10%
5%
0%
Northwind公司1997年白米销量频率图
1-10 11-20 21-30 31-40 41-50 51-60 61-70 71-80 81-90 91-100 销量组
8
Excel数据列表功能 数据透视表 D函数家模拟运算表
9
数据列表被定义为“包含相关数据的一系列 工作表数据行”。
100.00%
利用数据透视表的分组功能，企业可以按照 销量、次品数量、销售额等分组，统计事件 发生频率。
第一步：创建数据数据透视表。 Northwind 公司数据“产品-产品名称”、“订单-订购 日期”、“扩展订单明细-数量”。产品— 报表筛选，订购日期—行标签，数量—数值 区域
40
第二步，数据透视表按销量分组。 按日期创建组，“分组”-选择“月”“年”。
2010年ABC公司各省各类别产品的销售额
安徽 广东 江苏 江西 山东
儿童用品 39686 524 2044 40255 24367
服装
16255
47196
食品
32855 472 2856 16404 19269
体育用品 10850 1080 5113 13970 3869
艺术品 42009 2893 4119 43932 24990
ABC公司各类别产品净销售额
45000 40000 35000 净 30000 销 25000 售 20000 额 15000 10000 5000
0
39686 儿童用品
16255 服装
32855
42009
10850
12522
食品 体育用品 艺术品 自行车
4
利用分类汇总，企业可以获得销售额排行榜、各种商品质 量指标排行榜、销售人员完成销售任务排行榜、各种产品 库存量与库存积压资金排行榜……
1997年
1月
61258.07
2月
38483.63
3月
38547.22
4月
53032.95
5月
53781.29
38
频率信息可以帮助管理人 员预测未来情况，合理安 排生产和销售活动。
【例3-6】利用数据透视 表，对Northwind公司的 销售数据按照产品、年月 汇总，统计各规模销量组 销售次数的频率分布。
30
分类字段的调整 分类字段值的调整 汇总字段的调整 数据透视表工具的功能
31
分类字段的调整
利用报表筛选区域筛选数据：
将数据透视表字段列表中的字段直接拖至报表筛选 区域；
从行标签或列标签区域，拖动字段至报表筛选区域； 报表筛选区域，可以放置一个或多个字段； 可改变多个字段的排列位置； 可挑选一个值或多个值来进行筛选； 可将报表筛选区域的字段拖动到其他区域； 可删除“报表筛选”区域的字段。
32
分类字段值的调整
改变分类字段值的位置； 分类字段值的组合； 行、列标签区域字段值的筛选； 折叠与展开汇总数据。
33
汇总字段的调整
新增、删除和修改汇总字段：
新增汇总字段； 改变汇总字段的位置； 修改汇总字段名称； 删除汇总字段。
改变汇总字段的汇总方式。
34
数据透视表工具的功能 数据透视表工具的两个选项卡
数据列表的首行为字段名，首行下的各行是 各个记录。
数据列表中不能出现空行。 数据列表可以像数据库中的表一样使用，行
对应于表中的记录，列对应用于表中的字段。
10
排序功能
可以按照某个字段的升序或降序对数据列表中的 所有记录进行排序。
筛选功能
筛选就是通过设定条件，挑选出满足条件的记录：
自动筛选功能； 高级筛选功能。
数在总次数中所占比重，即概率分布
42
【例3-7】Northwind公司计划，根据客户总销售额， 将客户划分为不同级别。销售额超过10000元的客户 为重要客户， 10000元以下的为普通客户。
新建一个空白工作薄，数据—获取外部数 据—自其他来源—Microsoft Query，查询 ABC公司所销售数据的“日期”，“省份”， “类别”，“净销售额”字段，将数据返回 EXCEL列表。
这样建立的数据列表能随着外部数据更新而更 新。
13
第二步，数据排列 补充知识：拆分；冻结 （视图—窗口） EXAMPLE 要利用SUM函数汇总，需要对这些数据进行排
序
多条件排序：数据—排序与筛选EXAMPLE
14
第三步，筛选数据 自动筛选—日期
第四步，汇总数据 工作表sheet2更名 补充知识：函数、数组、名称
公式“=sum(ABC公司销售数据！D70：D117)” 公式“=sum(安徽视频)”CTRL+SHIFT+ENTER 方法：定义“名称”（公式—定义名称）
-来自Microsoft Query-ABC公司的数据日期、 省份、渠道、类别、价位、净销售额、毛销 售额-保存至EXCEL
22