Excel数据透视表高手真经PDF|Epub|txt|kindle电子书版本网盘下载

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 Excel248

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

11.1.2 共享协作，轻松自如248

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

11.2.1 下载PowerPivot for Excel 2010249

11.2.2 安装PowerPivot for Excel 2010249

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中的宏与VBA345

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