从原始数据到分析报告(Excel数据透视表高效达人养成记)

本书结合大量的实际案例，分两篇来讲解内容。第一篇重点介绍数据透视表的各种实用操作技能技巧，以及数据分析的理念和思路；第二篇重点介绍公式与函数。这些案例都是笔者在实际培训中接触到的企业实际案例，通过实际操练，不仅能学习掌握更多的数据透视表技能，也为综合利用数据透视表、函数和图表来制作有说服力的分析报告提供更多的实际案例启发。

韩小良，中旭商学院（中旭文化网）高级讲师，资深实战型Excel培训讲师，对Excel及Excel VBA在企业管理中的应用有着很深的造诣，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用，具有丰富的管理经验和极强的Excel应用开发能力，已经出版了20余部关于Excel应用方面的著作，陆续开发出了财务、销售、人力资源、工资等管理软件。

前 言Preface

自我的第一本Excel 数据透视表专著于2014 年面世以来，已经再版了多次，深

受广大读者的喜爱，也把透视表的各项技能应用到实际工作中，不仅提高了办公效率，

也解决了烦琐数据的分析问题。

数据透视表是Excel 最强大最实用的工具之一，数据透视表的使用，不像函数那

样非常烧脑，你只需能够熟练操作就可以了。正因如此，很多人对数据透视表非常喜

爱。随着Excel 版本的不断升级，数据透视表的功能越来越完善，操作起来也越来越

方便。

实际上，数据透视表的应用，不仅仅是拖拖字段，拉拉鼠标那样简单。数据透

视表应该应用到更有价值的数据深度分析中，因为我们可以从各个角度对数据进行

分类组合，从而从各个角度来分析数据，找出数据背后的秘密，为企业经营决策服

务。鉴于此，本书结合大量的实际案例，分两篇来讲解内容。第一篇重点介绍数据

透视表的各种实用操作技能技巧，以及数据分析的理念和思路；第二篇重点介绍公

式与函数。这些案例都是我实际培训中接触到的企业实际案例，很多人对此也许并

不陌生，通过实际操练，不仅能够学习掌握更多的数据透视表技能，也为综合利用

数据透视表、函数和图表来制作有说服力的分析报告提供更多的实际案例启发。

期望本书让那些Excel 初学者快速掌握数据透视表同时能掌握函数应用处理各种

数据，也期望本书让具有一定Excel 基础的读者温故而知新，学习更多的数据透视表

与函数分析数据的技能和思路。

感谢十余年来Excel 培训班中的各位同学，正是你们提出的各种实际问题，为大

家提供了更多的实际案例和解决问题思路；感谢E 讯东方的各位同人，让数据透视表

能够在网络上直播授课，为更多的人学习Excel 提供便利；感谢中国铁道出版社的编

辑，正是你们的辛勤编辑加工，能够让读者尽快地看到此书。

作 者

2017 年7月

目 录Contents

PART 01 数据透视表篇

CHAPTER 01 制作数据透视表的准备工作

1.1 不规范表格结构的重新搭建..........................................................................3

1.1.1 把多行标题的数据区域转换为数据清单（案例1-1）......................3

1.1.2 将二维表格转换为数据清单（案例1-2）........................................6

1.1.3 将多列文字描述转换为一个列表清单（案例1-3）.........................8

1.2 数据分列........................................................................................................9

1.2.1 利用分隔符对数据分列（案例1-4、案例1-5）.............................10

1.2.2 利用固定宽度对数据分列（案例1-6）..........................................11

1.2.3 利用文本函数对数据分列（案例1-7）..........................................12

1.2.4 利用全角字符和半角字符特征对数据分列（案例1-8）................13

1.2.5 利用文本和数字特征对数据分列（案例1-9）...............................14

1.2.6 利用关键词对数据分列（案例1-10）............................................14

1.3 清除数据中的垃圾.......................................................................................16

1.3.1 快速删除文本中的空格...................................................................16

1.3.2 快速删除数据中眼睛看不见的特殊字符（案例1-11）..................16

1.4 修改非法日期和非法时间...........................................................................17

1.4.1 使用分列工具快速修改非法日期（案例1-12）.............................17

1.4.2 使用函数处理非法时间（案例1-13）............................................18

1.4.3 考勤数据的快速处理（案例1-14）................................................18

1.5 快速填充空单元格.......................................................................................19

1.5.1 快速填充上一行数据（案例1-15）................................................20

1.5.2 快速填充下一行数据（案例1-16）................................................21

1.5.3 快速往空单元格填充数字0 ............................................................22

1.5.4 快速取消合并单元格并填充数据....................................................23

CHAPTER 02 制作数据透视表的基本方法

2.1 以一个表格数据创建数据透视表................................................................25

2.1.1 以一个固定的数据区域制作数据透视表（案例2-1）....................25

2.1.2 以一个变动的数据区域制作数据透视表.........................................27

2.2 以多个二维表格数据创建数据透视表.........................................................28

2.2.1 创建单页字段的多重合并计算数据区域透视表（案例2-2）........29

2.2.2 创建多页字段的多重合并计算数据区域透视表（案例2-3）........33

2.3 以多个多维表格数据创建数据透视表.........................................................37

2.3.1 利用现有连接+SQL数据查询汇总多个多维工作表（案例2-4）.37

2.3.2 小知识：关于SQL语句..................................................................40

2.3.3 利用现有连接+SQL数据查询创建数据透视表应注意的问题.......42

2.4 以多个关联工作表数据创建数据透视表.....................................................43

2.4.1 使用Query 工具（案例2-5）.........................................................43

2.4.2 注意事项.........................................................................................47

2.5 以工作表部分数据创建数据透视表（案例2-6）.......................................47

2.6 在不打开工作簿的情况下创建数据透视表.................................................50

2.6.1 利用现有连接工具创建数据透视表................................................50

2.6.2 利用Query 工具创建数据透视表....................................................51

2.7 以数据库数据创建数据透视表....................................................................51

2.7.1 以Access数据库的一个数据表制作数据透视表（案例2-7）......51

2.7.2 以Access数据库的多个数据表制作数据透视表（案例2-8）......53

2.7.3 以Access数据库的部分数据表制作数据透视表...........................54

2.8 以文本文件数据直接创建数据透视表（案例2-9）...................................54

2.9 查看/更改数据源.........................................................................................58

2.9.1 以一个Excel表格制作的数据透视表.............................................58

2.9.2 以多个Excel表格制作的数据透视表（现有连接方法）...............58

2.9.3 以数据库或文本文件制作的数据透视表（Query方法）...............58

CHAPTER 03 布局数据透视表

3.1 数据透视表字段窗格...................................................................................60

3.2 数据透视表布局的几种方法........................................................................61

3.3 恢复经典的数据透视表布局方式................................................................61

3.4 数据透视表工具..........................................................................................62

3.4.1 “分析”选项卡中各功能组介绍....................................................63

3.4.2 “设计”选项卡中各功能组介绍....................................................64

CHAPTER 04 数据透视表的设计与美化

4.1 设计透视表的样式.......................................................................................66

4.1.1 套用数据透视表样式（案例4-1）..................................................66

4.1.2 细雕数据透视表样式.......................................................................67

4.1.3 删除默认的数据透视表样式............................................................67

4.2 设计透视表的布局.......................................................................................68

4.2.1 以压缩形式显示..............................................................................68

4.2.2 以大纲形式显示..............................................................................68

4.2.3 以表格形式显示..............................................................................69

4.2.4 重复、不重复项目标签（案例4-2）..............................................70

4.2.5 在每个项目后插入、删除空行........................................................71

4.2.6 显示、隐藏报表的行总计和列总计................................................72

4.3 设置透视表的选项.......................................................................................72

4.3.1 合并字段的项目标签单元格............................................................72

4.3.2 处理透视表中的错误值（案例4-3）..............................................73

4.4 设置字段......................................................................................................74

4.4.1 修改值字段名称..............................................................................74

4.4.2 设置值字段的汇总依据...................................................................74

4.4.3 设置值字段的数字格式...................................................................74

4.4.4 设置值字段的条件格式（案例4-4）..............................................75

4.4.5 显示、隐藏字段的分类汇总............................................................76

4.4.6 对分类字段项目进行自动排序........................................................77

4.4.7 对分类字段项目进行手动排序（案例4-5）...................................77

4.4.8 对分类字段项目进行自定义排序（案例4-6）...............................78

4.4.9 显示没有数据的分类字段项目（案例4-7）...................................80

4.4.10 显示、隐藏分类字段标题.............................................................81

4.5 数据透视表的其他设置...............................................................................81

4.5.1 刷新数据透视表..............................................................................81

4.5.2 显示、隐藏折叠/展开按钮..............................................................82

4.5.3 显示、隐藏数据透视表字段窗格....................................................82

4.5.4 选择数据透视表..............................................................................82

4.5.5 复制数据透视表..............................................................................83

4.5.6 移动数据透视表..............................................................................83

4.5.7 清空数据透视表..............................................................................83

4.5.8 将数据透视表转换为普通报表........................................................83

CHAPTER 05 自定义计算字段和计算项

5.1 自定义计算字段..........................................................................................85

5.1.1 添加计算字段（案例5-1）.............................................................85

5.1.2 修改计算字段..................................................................................86

5.1.3 删除计算字段..................................................................................86

5.1.4 列示出所有自定义字段信息............................................................87

5.1.5 可以改变计算字段的汇总方式吗....................................................87

5.2 自定义计算项..............................................................................................87

5.2.1 添加计算项（案例5-2）.................................................................87

5.2.2 自定义计算项的几个重要说明........................................................89

5.2.3 修改自定义计算项...........................................................................89

5.2.4 删除自定义计算项...........................................................................89

5.2.5 列示出所有自定义计算项信息........................................................90

5.3 添加计算字段和计算项的几个注意问题.....................................................90

5.3.1 什么时候添加计算字段，什么时候添加计算项..............................90

5.3.2 同时添加计算字段和计算项的几个问题（案例5-3）....................90

5.3.3 哪些情况下不能添加自定义计算字段和计算项..............................91

5.3.4 自定义计算字段是否能使用工作簿函数.........................................91

5.3.5 自定义计算字段是否能使用单元格引用和名称..............................91

5.4 综合应用案例..............................................................................................92

5.4.1 预算与实际执行情况的对比分析（案例5-4）...............................92

5.4.2 动态进销存管理（案例5-5）.........................................................93

CHAPTER 06 通过设置字段显示方式分析数据

6.1 占比分析......................................................................................................97