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

内容简介

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

作者简介

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

目录

目录Contents

PART01数据透视表篇

CHAPTER01制作数据透视表的准备工作

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

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

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

CHAPTER03布局数据透视表

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

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

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

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

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

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

CHAPTER04数据透视表的设计与美化

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

CHAPTER05自定义计算字段和计算项

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

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

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

前言/序言

前言Preface

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

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

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

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

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

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

方便。

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

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

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

务。鉴于此，《从原始数据到分析报告：Excel数据透视表高效达人养成记》结合大量的实际案例，分两篇来讲解内容。第一篇重点介绍数据

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

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

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

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

期望《从原始数据到分析报告：Excel数据透视表高效达人养成记》让那些Excel初学者快速掌握数据透视表同时能掌握函数应用处理各种

数据，也期望《从原始数据到分析报告：Excel数据透视表高效达人养成记》让具有一定Excel基础的读者温故而知新，学习更多的数据透视表

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

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

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

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

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

作者

2017年7月