【现货速发】Excel数据透视表应用大全(数据高效汇总与分析)

01实际案例剖析 —— 低效率与高效率　1

案例 1-1　一个工作簿内多个工作表汇总分析　2

案例 1-2　多个工作簿内多个工作表汇总分析　4

案例 1-3　历年销售数据分析　5

02创建数据透视表的准备工作——数据整理与清洗　7

2.1　什么样的表格才能创建数据透视表　8

2.1.1　表格结构必须规范　8

2.1.2　表格数据必须规范　8

2.2　快速整理表格数据实用技能与技巧　9

2.2.1　清除数据中的垃圾　9

2.2.2　修改非法日期　12

2.2.3　将编码类的纯数字转换为文本型数字　15

2.2.4　将文本型数字转换为能够计算的纯数字　16

2.2.5　空单元格填充数据　18

2.2.6　快速删除表单中的空行　21

2.2.7　快速删除小计行　23

2.2.8　删除重复数据　24

2.2.9　数据分列　25

2.2.10　将二维表格转换为一维表单　30

03 以一个一维表单制作数据透视表　3

3.1　以一个一维表单的全部数据制作数据透视表　38

3.1.1　数据区域固定的情况——常见也简单　38

3.1.2　数据区域变动的情况——使用动态名称　41

3.1.3　数据区域变动的情况——使用现有连接工具　46

3.2　以一个一维工作表的部分数据制作数据透视表　49

3.2.1　任何一个版本都可以使用的 Microsoft Query 方法　50

3.2.2　在 Excel 2016 版本中使用的 Power Query Power Pivo方法　53

3.3　在不打开工作簿的情况下制作数据透视表　59

3.3.1　现有连接方法　59

3.3.2　Microsoft Query 方法　59

3.3.3　Power Pivot 方法　59

04 以多个一维表单制作数据透视表　6

4.1　以一个工作簿里的多个一维工作表制作数据透视表　64

4.1.1　现有连接 SQL 语句方法　64

4.1.2　SQL 基本知识简介　68

4.1.3　Power Query Power Pivot 方法　70

4.1.4　仅抽取每个表都有的几列数据制作透视表　76

4.2　以多个工作簿里的多个一维工作表制作数据透视表　77

4.2.1　VBA 方法　77

4.2.2　Power Query 方法　79

05 以二维表格制作数据透视表　91

5.1　以一个二维表格数据制作数据透视表　92

5.2　以多个二维表格数据制作数据透视表　94

5.2.1　创建单页字段的数据透视表　94

5.2.2　创建多页字段的数据透视表　100

5.3　以多个工作簿的二维表格制作数据透视表　105

5.4　结构不一样的二维表格制作数据透视表　109

5.5　大量二维表格的透视分析　111

5.5.1　每个表格只有一列数字　111

5.5.2　每个表格有多列数字　113

06 以多个关联工作表制作数据透视表　116

6.1　利用 Microsoft Query 工具　118

6.2　利用 Power Pivot 工具　124

6.2.1　在当前工作簿上制作数据透视表　124

6.2.2　不打开源数据工作簿，在新工作簿上制作数据透视表　127

07 以其他类型文件制作数据透视表　131

7.1　以文本文件数据制作数据透视表　132

7.1.1　使用外部数据源　132

7.1.2　自文本工具　137

7.1.3　现有连接工具　138

7.1.4　Power Pivot 工具　139

7.1.5　Microsoft Query 工具　140

7.2　以数据库数据制作数据透视表　144

08制作数据透视表方法总结　145

8.1　制作数据透视表的普通方法　146

8.1.1　制作方法总结　146

8.1.2　如何查看或修改数据源　146

8.2　利用现有连接 SQL 语句制作数据透视表方法　148

8.2.1　制作方法总结　148

8.2.2　如何查看或修改数据源　149

8.3　多重合并计算数据区域数据透视表方法　150

8.3.1　制作方法总结　150

8.3.2　如何查看及编辑数据源　151

8.4　Power Query Power Pivot 制作数据透视表方法　151

8.4.1　制作方法总结　151

8.4.2　如何查看或编辑数据源　152

09布局数据透视表　153

9.1　“数据透视表字段”窗格　154

9.1.1　改变“数据透视表字段”窗格布局　154

9.1.2　字段列表　155

9.1.3　筛选　155

9.1.4　行　157

9.1.5　列　158

9.1.6　值　159

9.1.7　“数据透视表字段”窗格与普通报表的对应关系　160

9.2　数据透视表的布局　161

9.2.1　布局基本方法　161

9.2.2　布局的快速方法　161

9.2.3　存在大量字段时如何快速找出某个字段　161

9.2.4　直接套用常见的数据透视表布局　162

9.2.5　延迟布局更新　162

9.2.6　恢复经典的数据透视表布局方式　163

9.3　数据透视表工具　164

9.3.1　“分析”选项卡　164

9.3.2　“设计”选项卡　167

9.4　数据透视表的快捷菜单命令　169

9.5　显示或隐藏透视表右侧的“数据透视表字段”窗格　170

10数据透视表的设置与美化　171

10.1　设计透视表的样式　172

10.1.1　套用一个现成的样式　172

10.1.2　清除样式是常见的设置　173

10.2　设计报表布局　174

10.2.1　以压缩形式显示　175

10.2.2　以大纲形式显示　176

10.2.3　以表格形式显示　176

10.3　修改字段名称　177

10.3.1　在单元格直接修改字段名称　177

10.3.2　在字段设置对话框里修改　177

10.4　显示 / 隐藏字段的分类汇总　178

10.4.1　设置某个字段的分类汇总　179

10.4.2　设置所有字段的分类汇总　179

10.5　显示 / 隐藏行总计和列总计　180

10.5.1　列总计和行总计的定义　180

10.5.2　显示或隐藏列总计和行总计　181

10.6　合并 / 取消合并标签单元格　182

10.6.1　合并标签单元格　183

10.6.2　取消合并标签单元格　184

10.7　显示 / 隐藏字段无数据的项目　184

10.8　对行字段和列字段的项目进行重新排序　185

10.8.1　手工排序　185

10.8.2　自定义排序　187

10.9　设置值字段的汇总依据　190

10.10　设置值字段的数字格式　191

10.10.1　设置常规数字格式　191

10.10.2　设置自定义数字格式　191

10.11　数据透视表的其他设置　192

10.11.1　重复项目标签　192

10.11.3　更新数据透视表时不自动调整列宽　195

10.11.4　在每个项目后面插入空行　195

10.11.5　将筛选字段垂直或水平布局排列　195

10.11.6　显示 / 不显示“折叠 / 展开”按钮　197

10.11.7　刷新数据透视表　198

11利用数据透视表分析数据的实用技能　　199

11.1　对数据透视表进行重新布局　200

11.1.1　示例数据　200目　　录

11.1.2　制作基本的分析报告　201

11.2　排序筛选找出前 N 大（小）项目　203

11.2.1　制作基本的数据透视表　203

11.2.2　数据排序做排名分析　204

11.2.3　数据筛选寻找好（差）的几个项目　204

11.2.4　快速筛选保留选中的项目　206

11.2.5　清除排序和筛选　207

11.3　设置字段汇总依据　207

11.3.1　设置字段汇总依据的基本方法　208

11.3.2　应用案例 1——员工工资分析　208

11.3.3　应用案例 2——员工信息分析　210

11.3.4　应用案例 3——销售分析　211

11.4　设置值字段显示方式　213

11.4.1　占比分析　214

11.4.2　差异分析　222

11.4.3　累计分析　223

11.4.4　恢复默认的显示方式　227

11.5　组合字段　227

11.5.1　组合日期，制作年、季度、月度汇总报告　227

11.5.2　组合时间，跟踪每天、每小时、每分钟的数据变化　230

11.5.3　组合数字，分析指定区间内的数据——员工信息分析　234

11.5.4　组合数字，分析指定区间内的数据——工资数据分析　236

11.5.5　组 合 数 字， 分 析 指 定 区 间 内 的 数 据 ——销售分析　238

11.5.6　对文本进行组合，增加更多的分析维度　240

11.5.7　组合日期时应注意的问题　243

11.5.8　组合数字时应注意的问题　244

11.5.9　某个字段内有不同类型数据时不能自动分组　244

11.5.10　页字段不能进行组合　244

11.5.11　取消组合　245

12使用切片器快速筛选报表　246

12.1　插入并使用切片器　248

12.1.1　插入切片器的按钮　248

12.1.2　插入切片器的方法　248

12.1.3　切片器的使用方法　250

12.2　设置切片器样式　250

12.2.1　套用切片器样式　250

12.2.2　新建切片器样式　251

12.2.3　修改自定义切片器样式　254

12.2.4　设置切片器的项目显示列数　254

12.2.5　布局数据透视表和切片器　255

12.3　用切片器控制数据透视表（数据透视图）　256

12.3.1　多个切片器联合控制一个数据透视表（数据透视图）　256

12.3.2　一个或多个切片器控制多个数据透视表（数据透视图）　256

12.3.3　删除切片器　257

12.4　筛选日期的自动化切片器——日程表　257

12.4.1　插入日程表　258

12.4.2　日程表的使用方法　259

13为数据透视表添加自定义计算字段和计算项　　261

13.1　自定义计算字段　262

13.1.1　添加计算字段的基本方法　262

13.1.2　修改计算字段　265

13.1.3　删除计算字段　265

13.1.4　列示出所有自定义字段信息　265

13.1.5　可以改变计算字段的汇总方式吗　266

13.2　自定义计算项　266

13.2.1　添加计算项的基本方法　266

13.2.2　自定义计算项的几个重要说明　269

13.2.3　修改自定义计算项　269

13.2.4　删除自定义计算项　270

13.2.5　列示出所有自定义计算项信息　270

13.3　添加计算字段和计算项的注意事项　270

13.3.1　分别在什么时候添加计算字段和计算项　270

13.3.2　同时添加计算字段和计算项的几个问题　271

13.3.3　哪些情况下不能添加自定义计算字段和计算项　271

13.3.4　自定义计算字段能使用工作簿函数吗　272

13.3.5　自定义计算字段能使用单元格引用和名称吗　272

13.4　综合应用案例　272

13.4.1　在数据透视表里进行排位分析　272

13.4.2　两年同比分析　275

13.4.3　两年客户流动分析　276

13.4.4　动态进销存管理　278

14快速制作明细表　282

14.1　每次制作一个明细表　284

14.2　一次批量制作多个明细表　286

15数据透视图与数据透视表的联合应用　　289

15.1　创建数据透视图　290

15.1.1　在创建数据透视表时创建数据透视图　290

15.1.2　在现有数据透视表的基础上创建数据透视图　291

15.1.3　数据透视图的结构　291

15.1.4　关于数据透视图分类轴　292

15.1.5　关于数据透视图的数据系列　292

15.2　数据透视图的美化　293

15.2.1　数据透视图的常规美化　293

15.2.2　数据透视图的特殊处理　293

15.3　利用数据透视图分析数据　293

15.3.1　通过布局字段分析数据　294

15.3.2　通过筛选字段分析数据　294

15.3.3　利用切片器控制数据透视图　295

15.4　数据透视图和数据透视表综合应用案例　296

15.4.1　一个简单的二维表格动态分析　296

15.4.2　一个稍复杂的流水数据分析　298

16数据透视表其他应用案例　302

16.1　生成不重复的新数据清单　303

16.1.1　在某列中查找重复数据并生成不重复的新数据清单　303

16.1.2　在多列中查找重复数据并生成不重复的新数据清单　304

16.1.3　从多个工作表中查找重复数据并生成不重复的新数据清单　305

16.2　快速核对数据　305

16.2.1　单列数据的核对　305

16.2.2　多列数据的核对　307

16.3　转换表格结构　309

16.3.1　二维表格转换为一维表格　309

16.3.2　将多列文字描述转换为一个列表清单　309