Excel数据透视表高手真经PDF电子书下载

Chapter 1 识数据透视表方知Excel真本领 2

1.1 数据透视表，分析大量的数据 2

1.1.1 什么是数据透视表 2

1.1.2 数据透视表能做什么 3

1.1.3 什么时候使用数据透视表 4

1.1.4 Excel各版本数据透视表区别与使用 4

1.2 哪些数据可以作为数据透视表的数据源 7

1.3 规范不符合要求的数据源 8

1.3.1 删除数据区域中大量的空行或者空列 9

［案例应用］删除北京市城乡人口普查数据表中的空行 9

1.3.2 将同一列中的分类行单独列出来 11

［案例应用］将北京市城乡人口普查数据表中的区县单独作为一列 11

1.3.3 删除数据区域中的小计行 13

［案例应用］删除北京市城乡人口普查数据表中的小计行 13

1.3.4 处理表头中的合并单元格 16

1.4 了解数据透视表的四大区域 16

1.5 了解数据透视表的一些常用术语 17

1.6 认识数据透视表布局的主要工具 18

1.6.1 显示“数据透视表字段列表”窗格 19

1.6.2 使“数据透视表字段列表”窗格中显示更多字段 19

1.6.3 使用“数据透视表字段列表”窗格进行排序和筛选 20

1.6.4 将“数据透视表字段列表”窗格中的字段按升序排序 20

1.7 使用“数据透视表工具”选项卡组 21

1.7.1 “选项”选项卡功能介绍 21

1.7.2 “设计”选项卡功能介绍 23

1.8 创建第一个数据透视表 23

［案例应用］创建数据透视表分析近7届奥运会奖牌情况 24

Chapter 2 轻松变出新的数据透视表 28

2.1 移动字段生成新数据透视表 28

［案例应用］统计各省份和客户已经收到的回款额 28

2.2 使用数据透视表的筛选区域 31

2.2.1 将数据透视表的筛选区域设置为多项 31

［案例应用］分析各省份各等级瓦斯的国有煤矿占比情况 31

2.2.2 改变多个筛选字段的排列方式 33

［案例应用］使筛选字段每行显示两个 33

2.2.3 将每一个筛选项的筛选结果保存在一个表中 34

［案例应用］将各城市的家电销售情况分布在新的表格中 34

2.3 自定义字段 35

2.3.1 重命名字段 36

2.3.2 重新调整字段的位置 37

2.3.3 删除字段 38

2.3.4 隐藏字段标题 39

2.3.5 展开和折叠活动字段 39

2.4 按要求设置数据透视表的布局 40

2.4.1 调整数据透视表的报表布局 40

2.4.2 更改分类汇总的显示方式 42

2.4.3 使用空行分隔不同的组 43

2.4.4 禁用与启用总计 44

［案例应用］在分析各国家地区近7届奥运奖牌情况时计算各奖牌总数 44

2.4.5 合并且居中排列带标签的单元格 45

2.5 返本还源，获取数据透视表的数据源 46

2.5.1 获取整个数据透视表的数据源 46

［案例应用］通过数据透视表获取固定资产清单 46

2.5.2 获取统计结果的数据明细 49

Chapter 3 改变数据源时数据透视表要同步变 51

3.1 手动刷新数据透视表 51

［案例应用］在更新源数据后手动刷新数据透视表 51

3.2 自动刷新数据透视表 52

3.2.1 设置在打开文件时自动刷新数据 52

3.2.2 使用VBA代码设置自动刷新 53

［案例应用］员工基本信息更新后自动刷新数据透视表 53

3.3 在后台刷新外部数据源的数据透视表 55

3.4 定时刷新外部数据源的数据透视表 56

3.5 同时刷新工作簿中的所有数据透视表 56

3.6 推迟布局更新 56

［案例应用］在更改数据透视表布局时不刷新数据透视表 57

3.7 刷新数据透视表后保持列宽不变 58

3.8 刷新数据时的注意事项 59

［案例应用］清除字段下拉列表项中已删除的项 60

Chapter 4 美化数据透视表 63

4.1 省时省力，使用样式美化报表 63

4.1.1 自动套用数据透视表样式 63

［案例应用］为数据透视表应用内置样式 63

4.1.2 自定义数据透视表样式 65

［案例应用］在现有数据透视表的基础上新建数据透视表样式 65

4.1.3 使用其他工作簿中自定义的数据透视表样式 68

4.2 一蹴而就，批量设置数据透视表中某类项目的格式 68

4.2.1 启用选定内容功能 68

4.2.2 批量设置某类数据的格式 69

［案例应用］通过填充颜色突出“冷轧薄板”的存储情况 69

4.3 设置数据透视表中数据的格式 70

4.3.1 通过“开始”选项卡设置数字格式 71

［案例应用］为金额快速设置会计格式 71

4.3.2 通过对话框设置数据格式 72

［案例应用］将欠款额设置为使用括号和红色字体表示负数的会计格式 72

4.3.3 自定义数据格式使数据显示多样化 74

［案例应用］将适合各年龄段的服装样式标记出来 74

4.4 有碍观瞻的数据得处理一下 75

4.4.1 处理行字段中的空白项 75

［案例应用］处理数据透视表中的空白数据项 75

4.4.2 将值区域中的空白数据指定为具体数据 77

［案例应用］将统计商品订单金额报表中的空白值标记为“未订购” 77

4.5 使用更加丰富的格式突出显示数据 78

4.5.1 给特殊数据一个“光芒四射”的效果 78

［案例应用］突出显示超出预算的实际开支费用 79

4.5.2 通过数据条使数据大小更清晰 81

［案例应用］使用数据条表示订单总额的大小 81

4.5.3 通过图标集使数据等级更分明 82

［案例应用］将员工实发工资分为5个等级并获取每个员工的工资等级 82

4.5.4 通过色阶指示数据的分布和变化 84

［案例应用］通过渐变颜色展示销量变化情况 84

4.6 管理数据透视表中使用的条件格式 85

4.6.1 删除数据透视表中的条件格式 85

［案例应用］删除为数据透视表应用的色阶 86

4.6.2 修改已经创建的条件格式规则 87

［案例应用］将三色图标集修改为二色图标集 87

Chapter 5 对报表中的数据进行排序和筛选 91

5.1 让数据按照指定的顺序排列 91

5.1.1 通过字段列表进行排序 91

［案例应用］将行字段标签按降序排序 91

5.1.2 通过字段筛选器排序 92

［案例应用］通过字段筛选器对列字段进行排序 92

5.1.3 手动排序 94

［案例应用］手动调整列字段中的月份排列顺序 95

5.1.4 按笔画排序 96

［案例应用］将销售员销售量统计结果数据项按销售员姓氏笔画排序 97

5.1.5 按值排序 98

［案例应用］将行字段标签按降序排序 99

5.1.6 按自定义序列排序 100

［案例应用］将员工工资按照指定的部门顺序排序 100

5.2 在报表中显示部分统计分析结果 102

5.2.1 通过字段筛选器对标签进行筛选 102

5.2.2 对值区域数据进行筛选 103

［案例应用］筛选3个月总计生产同类产品万件以上的记录 103

5.3 使用切片器筛选数据 105

5.3.1 什么是切片器 105

5.3.2 在现有数据透视表中插入切片器 106

［案例应用］在报表中添加城市、商品切片器 106

5.3.3 设置切片器格式 107

［案例应用］在城市切片器中每行显示3个按钮 107

5.3.4 使用切片器筛选数据 109

5.3.5 断开切片器连接或删除切片器 110

［案例应用］断开切片器与数据透视表的连接 111

5.3.6 使用一个切片器控制多个数据透视表 113

［案例应用］将切片器连接到两个数据透视表 113

Chapter 6 数据太繁杂，让它分组吧 116

6.1 少量或部分数据手动分组 116

［案例应用］将销售分析报表中的城市按照地理位置分组 116

6.2 大量有规律数据还是自动分组快一些 118

6.2.1 以“月”为单位组合日期数据 118

［案例应用］以月为单位统计员工生产各种产品的数量 118

6.2.2 以“日”为单位组合日期数据 119

［案例应用］以周为单位统计销售额 120

6.2.3 将数据按等步长分组 121

［案例应用］以1000为步长统计各区间销售额、销售次数和总销售额 121

6.3 有聚有散，组合项目还可以再分开 123

6.3.1 取消自动组合的数据项 123

6.3.2 取消手动组合的数据项 124

6.4 使用函数辅助分组 124

6.4.1 根据数据自身的特点分组 125

［案例应用］分类统计鞋店主营和兼营产品的营业额 125

6.4.2 根据文本的首字符分组 127

［案例应用］为报表添加一些方便折叠的分组 128

Chapter 7 添加计算指标，让分析功能更强大 131

7.1 求和不是汇总的唯一 131

7.1.1 更改数据透视表的字段汇总方式 131

7.1.2 对同一字段采用多种汇总方式 132

［案例应用］分析冰箱销售单价的平均值、最大值和最小值 132

7.2 相同的数据也可以得到不同的结果 134

7.2.1 差异分析 135

［案例应用］分析各项目相比于1月份增减情况 135

［案例应用］分析家电销售额比上一月的增减情况 138

［案例应用］分析开支逐月增长的百分比 139

7.2.2 行／列汇总的百分比分析 140

［案例应用］统计每月各产品中各员工生产数量的占比情况 140

7.2.3 全部汇总百分比分析 141

［案例应用］获取员工销售额在公司销售额中的占比 142

7.2.4 累计汇总分析 145

［案例应用］累计汇总各种产品的产量 145

7.3 在值区域中添加计算字段 147

7.3.1 插入计算字段 147

［案例应用］计算各销售渠道的毛利润 148

［案例应用］计算收取的订单定金 149

［案例应用］根据员工的销售额范围给予不同的提成比例计算提成 150

7.3.2 修改已经插入的计算字段 152

［案例应用］修改员工销售提成比例 152

7.3.3 删除计算字段 154

7.4 在行／列字段中添加计算项 154

7.4.1 插入计算项 154

［案例应用］计算201 7年较2016年各项开支增长情况 154

7.4.2 修改与删除计算项 156

7.4.3 更改计算项的求解次序 156

7.5 获取所有计算字段和计算项的公式 157

7.6 使用计算字段和计算项时应注意 157

7.6.1 应注意计算字段的计算实质 158

7.6.2 应注意汇总是否错误和有意义 159

7.6.3 计算字段和计算项的使用应谨慎 160

7.7 将数据透视表分析结果拿出来使用 161

7.7.1 认识获取数据透视表中数据的函数 161

7.7.2 在引用数据时自动生成GETPIVOTDATA()函数 161

［案例应用］将各城市销售额占比情况填写到表格中 162

7.7.3 为数据透视表添加辅助列 164

［案例应用］计算各种产品的销售额 164

Chapter 8 数据源随时变，你变我也变 168

8.1 通过定义名称创建动态数据透视表 168

8.1.1 定义名称的基本方法和规则 168

［案例应用］为家电销售明细每列定义一个名称 168

8.1.2 定义动态名称需要使用的两个函数 171

8.1.3 使用动态名称制作动态数据透视表 172

［案例应用］创建家电销售明细的动态数据透视表 172

8.2 通过列表创建动态数据透视表 175

8.2.1 创建列表 175

8.2.2 通过列表创建动态数据透视表 176

［案例应用］创建订单分析的动态数据透视表 176

8.3 其他创建动态数据透视表方法简介 178

Chapter 9 源数据不在一起？创建多区域报表 181

9.1 创建多重合并计算的数据透视表 181

9.1.1 制作基于多区域的数据透视表应该这样做 181

9.1.2 将每个区域作为一个分组创建报表 181

［案例应用］统计分析员工1～3月的工资数据 181

9.1.3 自定义页字段可以对区域进行有效分组 185

［案例应用］分年和季度分析员工工资 185

［案例应用］使用数据透视表合并分析多个区域中的数据 190

9.1.4 合理使用页字段对比分析不同区域数据 192

［案例应用］分析各项目利润逐月变化情况 192

9.1.5 不同工作簿中的数据也可以合并计算 195

［案例应用］汇总分析不同城市的销售情况 195

9.2 多列文本的列表也可以合并分析 198

9.2.1 像数据库表一样进行列表区域操作 198

［案例应用］分析3月和4月的销售数据 198

9.2.2 导入数据也可以添加新字段 202

［案例应用］在统计分析城市GDP时生成区域字段 202

9.2.3 不是列表中的所有字段都必须导入 205

［案例应用］导入开票明细中的部分字段进行分析 205

9.2.4 分析数据时还可以做到自动排除重复项 208

［案例应用］统计客户购买的商品种数和商品购买的客户数 208

9.3 关联数据一表打尽 211

9.3.1 将多表相关数据汇总到一张表中 211

［案例应用］统计员工的工资数据 211

9.3.2 安装多个版本Excel时Microsoft Query工具的使用问题 214

9.3.3 记录不一致可以编辑连接方式来确定谁主谁辅 215

［案例应用］统计员工的考勤情况 215

9.4 优中选优，数据导入＋SQL最全能 218

［案例应用］使用SQL语句实现汇总关联数据表 219

9.5 多区域数据汇总的“笨”办法 222

［案例应用］使用公式辅助创建多区域数据透视表 222

Chapter 10 外部数据也可透视分析 226

10.1 拿来主义，外部数据先拿来再分析 226

［案例应用］使用数据透视表分析股票数据 226

10.2 透视分析数据库中的数据 228

10.2.1 透视分析单个Access数据库中的数据 228

［案例应用］分析药品销售管理数据库中的药品销售数据 229

［案例应用］使用数据透视表分析药品的库存情况 232

［案例应用］通过向导创建基于数据库的数据透视表 234

10.2.2 透视分析多个Access数据库中的数据 237

［案例应用］透视分析各分店销售数据 237

10.3 透视分析文本文档中的数据 240

［案例应用］透视分析文本文档中的数据 241

10.4 外部文件位置改变后如何刷新 243

［案例应用］查看光盘文件时更改连接属性 244

Chapter 11 数据关系太复杂？使用PowerPivot for Excel吧 248

11.1 为什么要用PowerPivot for Excel 248

11.1.1 提升数据处理水平很简单 248

11.1.2 共享协作，轻松自如 248

11.2 兵马未动，粮草先行——准备要做足 249

11.2.1 下载PowerPivot for Excel 2010 249

11.2.2 安装PowerPivot for Excel 2010 249

11.2.3 停用与启用PowerPivot加载项 250

11.2.4 使用PowerPivot应注意 250

11.3 创建数据透视表没有数据是不行的 251

11.3.1 使用Excel链接表添加数据 251

11.3.2 使用表导入向导添加数据 253

［案例应用］导入罗斯文数据库中的订单明细相关数据 253

11.3.3 通过复制粘贴导入数据 255

11.4 在多个数据表间牵线搭桥 256

［案例应用］创建员工资料与工资核算表之间的关系 256

11.5 创建基于PowerPivot的数据透视表 258

［案例应用］创建分析员工工资数据的数据透视表 259

11.6 在数据透视表中进行计算 261

11.6.1 计算列，对同行数据的分析很方便 261

［案例应用］添加计算员工应发工资的计算列 261

11.6.2 度量值，数据分析随意行 264

［案例应用］在员工考勤基础上给予全勤员工全勤奖 265

11.7 将多个字段作为一个整体操作 270

［案例应用］将同一个部门的员工作为整体使用 271

［案例应用］将销售数量和销售额的平均值创建为集 274

11.8 使用切片器分析数据 278

Chapter 12 表格太单调，还可使用数据透视图 280

12.1 创建数据透视图的基本方法 280

12.1.1 在已有数据透视表上创建 280

［案例应用］使用图表展示服装款式与年龄段匹配情况 280

12.1.2 与数据透视表同时创建数据透视图 282

［案例应用］使用数据透视图分析开支情况 282

12.1.3 在使用向导时同时创建数据透视图 283

［案例应用］使用向导创建数据透视图 284

12.2 认识数据透视图的特殊之处 286

12.2.1 认识数据透视图的特殊结构 286

12.2.2 数据透视图与常规图表的一些区别 287

12.3 将数据透视图换个位置显示 288

12.3.1 复制粘贴以快速移动数据透视表 288

12.3.2 通过快捷菜单移动数据透视表 289

12.3.3 通过功能区按钮移动数据透视表 289

12.4 对数据透视图进行布局 290

12.4.1 合理使用图表类型 290

［案例应用］使用数据透视图对比上半年各项目开支情况 291

12.4.2 合理使用图表元素 293

［案例应用］为饼图添加数据标签 293

［案例应用］为数据透视图添加图表标题 296

［案例应用］对比各月项目支出的同时分析支出发展趋势 299

［案例应用］在数据透视图中同时分析数量关系和发展趋势 301

12.4.3 通过数据布局改变图表布局 304

12.5 美化数据透视图 307

12.5.1 对数据透视图字体的美化 307

12.5.2 将较小的数据系列“拉长” 308

［案例应用］为条形图添加次要坐标轴 308

12.5.3 更改数据系列的样式 310

12.5.4 设置图表区和绘图区的底色 311

12.5.5 使用图表样式快速美化数据透视图 311

12.6 使用模板，制图一劳永逸 312

12.6.1 将图表保存为模板 312

12.6.2 使用模板创建数据透视表 312

12.7 永久保留数据透视图的分析结果 313

12.7.1 图表变图片，使用很方便 313

12.7.2 删除透视表，想变也很难 314

12.7.3 将数据透视表变普通表 314

12.8 数据透视表之动态分析技巧 315

［案例应用］使用数据透视图动态分析生产数据 315

Chapter 13 自己玩不过瘾？与他人分享吧 319

13.1 将分析结果制作为网页文件 319

［案例应用］将北京人口结构分析结构制作为网页文件 319

13.2 云工作，Excel“飞”起来 321

［案例应用］在互联网上共享煤矿数据透视分析表 322

13.3 在演讲中进行数据透视分析 325

13.3.1 认识OWC组件 325

13.3.2 在幻灯片中制作数据透视表 326

［案例应用］在年度销售总结幻灯片中使用数据透视表 326

13.3.3 放映幻灯片时实时布局数据透视表 331

13.3.4 在幻灯片中使用动态图表 335

［案例应用］在幻灯片中使用动态图表分析 335

［案例应用］分析上半年各月各项目开支情况 341

Chapter 14 宏与VBA，数据透视表的优化 345

14.1 认识Excel中的宏与VBA 345

14.1.1 宏，让繁复操作自动化 345

14.1.2 VBA，让自动化操作更灵活 345

14.1.3 添加“开发工具”选项卡 346

14.2 了解数据透视表对象 347

14.2.1 对象浏览器，对象相关全知道 348

14.2.2 录制宏，学习VBA操作数据透视表最佳方法 349

［案例应用］录制一段创建并布局数据透视表的宏 349

14.2.3 常用数据透视表对象及相关方法简介 352

14.3 通过代码创建数据透视表 353

14.3.1 通过缓存创建数据透视表 354

［案例应用］通过VBA代码创建数据透视表 355

14.3.2 调用向导创建数据透视表 358

［案例应用］调用向导创建数据透视表 359

14.4 实现数据透视表的自动化 361

14.4.1 操作数据透视表字段 361

［案例应用］更改数据透视表字段布局 363

14.4.2 设置数据透视表的布局方式 365

14.4.3 添加计算字段 365

14.4.4 设置字段汇总与显示方式 366

14.5 善待幕后英雄——数据透视表缓存 370

14.5.1 获取缓存索引和内存使用量 370

14.5.2 清理缓存中的垃圾条目 371

14.6 保护数据透视表 373

14.6.1 限制字段下拉列表的使用 373

14.6.2 禁止操作数据透视表中的任何字段 374

14.6.3 禁用显示明细功能 376

14.7 自动创建数据透视图 378

14.7.1 两种图表对象 378

14.7.2 图表对象的常用属性和方法 378

14.7.3 通过代码创建图表 379

14.7.4 获取数据透视表区域 382

14.7.5 创建数据透视图 382

14.8 高效处理数据透视表 384

14.8.1 一次性删除所有图表 384

14.8.2 保存图表为图形文件 385

14.8.3 仅打印所有的图表 385

14.8.4 浏览工作簿中的所有图表 386

Chapter 15 将分析结果全部打印出来 388

15.1 数据透视表行数多，给每页一个标题 388

15.2 让同一项目中的数据打印在同一页中 390

15.3 分别打印每一分类的数据 393

［案例应用］打印北京各区县的人口数据 393

15.4 根据筛选项打印数据透视表 395

15.4.1 显示筛选页后打印筛选页 395

［案例应用］分部门打印员工工资 395

15.4.2 单个筛选字段的VBA打印控制 397

附录A 数据透视表常见问题解答 399

问：如何查看数据透视表的名称？如何重命名数据透视表？ 399

问：如何使用相同的数据源创建非共享缓存的数据透视表？ 399

问：在大纲和表格布局的数据透视表中如何重复显示标签？ 400

问：如何在数据透视表中添加多种汇总分类方式？ 401

问：如何在受保护的工作表中允许使用数据透视表？ 401

问：如何隐藏数据透视表中的错误值？ 402

问：如何在数据透视表中实现拖动字段布局数据透视表？ 402

问：如何使数据透视表刷新时不改变已经设置的格式？ 403

问：在数据透视表中提示字段名称重复该怎么办？ 403

问：如何为数据透视表中的数据添加批注？ 404

问：如何将数据透视表的数据保存为表？ 404

问：如何快速清除数据透视表中的所有字段？ 405

问：如何手动更新数据透视表的数据源？ 405

问：在创建数据透视表的时候，出现“数据透视表字段名无效”提示怎么办？ 406

问：为什么在使用有些数值字段的时候，默认的汇总方式是“计数”而不是“求和”？ 406

问：在对字段进行分组的时候，提示“选定区域不能进行分组”怎么办？ 406

问：如何对筛选字段进行分组？ 407

问：为什么有时在数据透视表中，一个字段的同一个值会被当做两项显示在数据透视表中？ 407

问：刷新数据透视表之后，为什么数据透视表中的部分字段直接消失了？ 407

问：为什么在数据透视表无法执行排序筛选操作？ 408

问：如何创建出结构层次分明的数据透视表？ 408

问：如何将数据透视表中的数据只使用图标表示？ 409

问：在数据透视表中可以使用高级筛选吗？ 410

问：在数据透视表中对值字段进行筛选和在数据透视表区域对值筛选有什么区别吗？ 410

问：如何避免使用Excel 2010创建的数据透视表在Excel 2003及其之前版本中打不开的问题？ 411

问：在数据透视表中，当数据列中没有任何数据的时候，如何将这些行或列隐藏？ 411

问：在计算字段中，只能够实现求和的汇总方式，对于其他运算就无能无力了。但是在数据透视表中，又经常需要进行这些运算。那么，这些运算应该如何进行呢？ 411

问：在数据透视表中，当在行字段中添加较多字段时，这些字段将堆积在同一列中，如何将其显示在不同的列中？ 414

附录B Excel常用快捷键 415