Excel 2010数据透视表大全.pdf

Excel 2010数据透视表大全.pdf
 

书籍描述

编辑推荐
《Excel 2010数据透视表大全》是“Excel疑难千寻千解”丛书之一,为读者展示了在Excel2010中运用数据透视表解决疑难问题的实战技巧。

名人推荐
据爆料,数据分析师将成为21世纪最性感的职业,但性感的背后充满艰辛,数据分析工作中70%~80%的时间在处理数据,如果能很好地掌握Excel中的数据处理及数据透视技术,将大大节约工作时间,本书从案例入手,用通俗易懂的语言让读者更加熟悉Excel数据透视表的操作技术,是数据分析师身边的好帮手。
——数据挖掘与数据分析,数据分析师
近些年来,办公电子化给我们带来了便利,同时也造成了巨大的烦恼。这本书的内容恰好能给你带来帮助,只是随意翻阅,你就会发现大量有用的内容,适合所有需要Excel数据处理的人士!
——我爱ppt
看到书稿之前,我实在不相信数据透视表部分还能写成一本书。看完后我不仅信服了,还学到了很多。本书深入浅出的讲解方式也是我喜欢的。作为一个每天花80%时间和Excel 打交道的资深“表叔”,我向你推荐此书:看完后你可以节约大把时间去刷微博、玩微信,本书绝对值得你拥有!
——数据化管理,零售数据分析研究者,《数据化管理》咨询顾问、培训师黄成明
HR千万不可把Excel只当作简单的表格排版工具来使用。Excel的强大之处并不仅仅在于它能帮你把一堆繁杂的数据整理得井井有条,它还能够进行各种复杂的数据处理、统计分析,甚至辅助决策操作。Excel应用必将成为HR借以提高工作效率的不二法宝。
——人力资源第一微博 粟明林
从纷繁复杂的数据中统计、汇总和筛选,形成自己要的报表,是每位数据分析师必备的技能。本书系统介绍了Excel 2010数据透视表的原理,并结合很多典型的案例,由浅入深、手把手指导掌握一个个技术技巧、解决一个个疑难问题,全面掌握Excel 2010的数据透视表技术。适合数据分析新手及中级、高级用户,本书可直接作为工作中的参考手册。
——数据分析精选
万变不离其宗。只要掌握数据分析原理,即使面对大量错综复杂的数据,也可以利用数据分析利器——数据透视表来实现,只需鼠标拖拉几下,就能玩转数据,而本书能够帮助你全面了解、掌握数据透视表。
——小蚊子乐园,《谁说菜鸟不会数据分析》作者张文霖
商业领域最大的魅力在于从实践中获得价值,本书用疑难案例精选的方式给读者最有力的指导与解决方案,强力推荐。
——由天宇,艾瑞咨询高级分析师由天宇
数据透视表是Excel软件极具特色也是功能最强大的数据分析工具。通过数据透视表,不必写入复杂的公式就可以创建一个交互式的表格来自动提取、组织和汇总数据。审计过程中涉及上万行数据,且显得很杂乱,使用数据透视表对数据进行分析处理后可以帮助审计人员快速找到数据中存在的问题,快速找到审计方向。
——天职国际合伙人、成都分所所长申军
本书从用户角度出发,针对各行业棘手问题,借助经典实用的案例,结合实际应用场景给出最优解决方案,深入浅出地讲解数据透视表的各种强大功能,不仅图文并茂,还对每个案例加以原理分析及知识扩展,适用于多行业、各层次人员。
——Excel_函数与公式,数据分析师运营经理李锐
本书完全从实用角度着手,基于各种实际工作中的例子,让您可以快速找到适合自己的方式方法,“工欲善其事,必先利其器”,你,准备好拥有“利器”了吗?
——微软全球最有价值专家MVP 程传魏 (网名:kevinchengcw)
深入浅出的讲解,引人入胜的描述,经典的场景和贴心的知识拓展,多思路并举的操作方法,不知不觉中便使人沉浸于Excel 无穷的技巧中,让人钦佩作者的独具匠心和非凡智慧。无论是Excel 新手,或是Excel 高手,相信本书都可给人以耳目一新的感觉。
——微软全球最有价值专家MVP 周建平(网名:守柔)
虽然我对Excel 2010 已经不陌生了,但打开本书,仍有一种全新的感觉。本书一改Office帮助的那种“菜单式说教”,而是将日常运用过程中经常会遇到的疑难问题一一列出并详加说明解决方案,有的甚至还列出多项解决方案,这无疑给使用者带来巨大的方便。本书不仅可以作为Excel 2010 爱好者学习的自学教材,还可以作为经常使用Excel 2010 办公人士的解疑字典,在遭遇奇怪现象时可以直接找到解决问题的捷径。
——微软全球最有价值专家MVP 方洁影(网名:小妖)
Excel功能很多,其数据透视表功能在我们工作中最为常用,如何使用数据透视表提升工作效率呢?本书提供了最有效的方法,通过提供超实用的实例解决方案,快速帮大家解决工作中经常遇到的各种问题,既可当作案头指导书,又可以作为工作必备工具书,值得Office达人收藏,强烈推荐!
——无忧PPT

作者简介
荣胜军,网名rongjun,微软全球最有价值专家(MVP),ExcelTip.net技术社区总版主,网络工程师,长期从事建筑工程造价、工程管理工作,精通Excel操作技巧、函数与公式、图表、数据透视表,具有丰富的实战经验,致力于专研Excel应用技术,提高工作效率。
黄朝阳,网名apolloh,Exceltip.net站长,微软全球最有价值专家(MVP)。拥有十多年丰富的企业信息化实践和管理经验,精通Excel和MS SQL在企业中的应用。早期曾在多个Office技术社区担任版主,参与过多部Excel畅销书的编著工作。

目录
第1章认识数据透视表1
1.1数据透视表入门1
疑难1如何创建一个简单的数据透视表1
插入数据透视表1
利用向导创建数据透视表2
数据透视表的发展历程3
疑难2如何用含有合并单元格的数据来创建数据透视表4
培养良好的数据录入规范5
疑难3二维表如何创建数据透视表6
多重合并计算的数据透视表6
一维表与二维表的区别7
疑难4如何重命名数据透视表8
重命名数据透视表8
疑难5如何移动数据透视表的位置9
移动数据透视表9
疑难6如何复制数据透视表10
复制数据透视表10
疑难7如何删除数据透视表11
删除数据透视表11
疑难8如何创建非共享缓存的数据透视表12
创建非共享缓存的数据透视表12
共享缓存与非共享缓存的区别13
1.2数据透视表结构14
疑难9如何创建多条件汇总的数据透视表14
“数据透视表字段列表”窗格说明15
疑难10如何显示/隐藏数据透视表字段列表15
显示/隐藏数据透视表字段列表16
疑难11如何在数据透视表字段列表中显示更多的字段17
改变字段列表布局17
疑难12如何隐藏数据透视表中行字段的“+/?”按钮17
显示/隐藏“+/?”按钮18
疑难13如何隐藏数据透视表的字段标题和筛选按钮18
隐藏字段标题和筛选按钮18
疑难14如何升序排列字段列表中的字段19
升序排列字段列表19
疑难15如何快速选取所有的标签并标注为黄色底纹20
快速选取所有标签20
疑难16如何快速选定数据透视表的汇总行并添加绿色底纹21
启用选定内容22
快速选定数据透视表元素22
选定行标签所在项父级的所有数据22
疑难17如何得到数据透视表中某个汇总行的明细数据23
显示明细数据23
1.3练习与思考24

第2章布局和打印25
2.1拖动字段25
疑难18如何在数据透视表中使用鼠标拖放25
设置经典数据透视表布局25
疑难19如何将值字段从行区域移到列区域26
拖动值字段27
疑难20如何将行字段中的某个项目拖动到第一行显示28
改变行字段项目显示顺序28
疑难21如何快速拖动改变行字段的排列顺序29
改变行字段排列顺序29
疑难22如何删除数据透视表中的某个字段30
移除字段30
2.2调整布局31
疑难23如何隐藏数据透视表字段的分类汇总31
隐藏所有字段的分类汇总32
在底部/顶部显示分类汇总32
添加或隐藏某个字段的分类汇总32
疑难24如何为一个字段添加多种分类汇总方式33
一个字段的多种分类汇总33
疑难25如何禁用数据透视表的总计行/列34
禁用总计行和总计列34
删除总计行或总计列35
总计行与列总计、总计列与行总计的关系36
疑难26如何在数据透视表顶部显示列总计数据36
在顶部显示列总计36
疑难27如何以表格形式显示数据透视表37
以表格形式显示报表37
报表布局的三种显示形式及其区别38
疑难28如何调整压缩形式显示下的缩进字符数39
设置行标签字符缩进量39
疑难29如何重复显示行字段的项目标签40
重复所有项目标签40
重复活动字段项目标签41
重复所有项目标签与重复活动字段项目标签的区别41
疑难30如何在数据透视表项目间插入空行42
在每个项目后插入空行42
在指定项目标签后插入空行43
删除每个项目后的空行43
疑难31如何在组的顶部显示分类汇总44
在组的顶部显示分类汇总44
疑难32如何在数据透视表中使用合并单元格标志45
合并居中显示项目标签45
疑难33如何水平并排显示报表筛选区域的字段45
水平并排显示报表筛选区域字段45
设置每行/列报表筛选字段的数量46
疑难34如何设置手动更新数据透视表的布局47
推迟布局更新47
“推迟布局更新”的影响48
疑难35如何在受保护工作表中改变数据透视表布局48
在受保护工作表中允许使用数据透视表48
2.3打印49
疑难36如何设置重复打印标题行49
设置打印标题行49
设置打印顶端标题行50
数据透视表选项与页面布局在设置打印标题行时的区别50
疑难37如何按公司名称分页打印销售业绩汇总表51
行字段分类项目分页打印51
疑难38如何按地区分页打印销售人员的业绩报表52
按页字段数据项分页打印53
2.4练习与思考53
第3章设置数据透视表格式54
3.1应用样式54
疑难39如何为数据透视表应用样式54
自动套用数据透视表样式54
套用表格格式55
启用/关闭实时预览56
应用并清除格式与应用并保留格式及其区别56
疑难40如何自动设置数据透视表的边框57
设置数据透视表边框57
疑难41如何设置镶边行的数据透视表样式58
镶边行与镶边列58
疑难42如何新建数据透视表样式59
新建数据透视表样式59
自定义数据透视表样式中格式的优先顺序60
行副标题与第一列的区别61
第一行条纹、第二行条纹与镶边行的关系61
清除数据透视表元素的格式设置61
疑难43如何修改数据透视表样式62
修改数据透视表样式62
删除自定义数据透视表样式63
疑难44如何导入自定义数据透视表样式64
利用XML文件导入样式64
XML格式简介66
修改扩展名打开Excel文档66
修改数据透视表应用的样式66
通过复制导入样式66
两种自定义样式方法的区别67
疑难45如何设置数据透视表默认样式67
将“数据透视表快速样式”按钮添加到快速访问工具栏68
“表”样式库中设置默认数据透视表样式68
疑难46如何查看数据透视表默认样式68
查看默认样式69
3.2自定义格式69
疑难47如何隐藏数据透视表中的错误值69
设置错误值显示为空70
疑难48如何将数据透视表中的空单元格显示为“—”71
设置空单元格显示为“—”71
取消空单元格自定义显示72
典型的自定义单元格格式72
疑难49如何去掉数据透视表中的“(空白)”数据73
查找替换去除“(空白)”73
重命名去除“(空白)”74
自动更正去除“(空白)”74
疑难50如何让字段标题不显示“求和项:”等统计标识75
查找替换去除“求和项:”75
重命名去除“求和项:”76
疑难51如何将借项下所有数据标记为红色76
突出显示指定项所有数据76
疑难52如何将金额字段按四舍五入保留两位小数77
四舍五入保留两位小数77
单元格格式与数字格式的区别78
疑难53如何将小于100万的销售额显示为红色“不达标”79
标记不达标数据79
在单元格格式中可以设置哪些颜色80
3.3条件格式80
疑难54如何利用图标集将销售数据划分为五个等级80
用图标集划分数据等级80
疑难55如何突出显示成交价最高及最低的整行数据81
突出显示最大值/最小值所在数据行82
以不同颜色突出显示最大与最小值83
疑难56如何突出显示销量增长最多的销售员83
突出显示销量增长最多的销售员84
疑难57如何按查询条件突出显示指定学生的成绩84
突出显示查询结果数据85
疑难58如何按班级交替填充背景颜色86
按班级交替填充背景色86
疑难59如何将百分成绩显示为等第成绩87
按等级级别显示成绩87
条件格式的优先级89
调整条件格式的优先级89
3.4练习与思考89

第4章数据透视表排序90
4.1自动排序90
疑难60如何对数据透视表的行字段的内容排序90
按行字段项目降序排序90
数据透视表中行字段排序的几种方法91
疑难61如何按分类汇总值升序排序91
按分类汇总升序排序92
疑难62总分相同的学生如何按单科成绩排序93
多关键字排序93
多关键字排序的先后顺序93
“每次更新报表时自动排序”复选框的作用94
疑难63如何按多个行字段排序94
多个行字段按值字段排序94
4.2自定义排序95
疑难64如何手动排序行字段的项96
手动排序字段项目96
疑难65如何按笔划升序排序行字段项目96
按笔划升序排序96
笔划排序规则97
疑难66如何对数据透视表中的局部数据进行排序98
局部排序98
疑难67如何按行升序排列不合格分类数量99
按行从左到右排序99
疑难68如何按照职务高低排序100
添加自定义序列排序100
启用排序时使用自定义列表101
疑难69如何按数据源顺序排列行字段项目101
按数据源顺序排序字段项目102
4.3练习与思考102

第5章数据透视表筛选103
5.1自动筛选103
疑难70如何筛选成绩大于或等于60的学生名单103
值筛选及格名单103
“值筛选”的筛选条件104
在数据透视表字段列表任务窗格中设置值筛选104
清除“值筛选”的几种方法104
为什么数据透视表有些新的筛选功能不可用105
疑难71如何获得高于平均值的销量数据列表106
自动筛选高于平均值数据106
“值筛选”与数值区域执行自动筛选的区别106
疑难72如何筛选单价最高的3种商品信息107
筛选前N个最大值107
筛选的排位规则108
项、百分比、求和108
疑难73如何筛选包含“茶”字的商品信息108
通过标签筛选包含指定字符的数据109
通过搜索筛选器筛选包含指定字符的数据109
标签筛选与搜索筛选器筛选的区别110
筛选条件中的通配符110
疑难74如何隐藏数据透视表的“(空白)”数据项110
隐藏第一个行字段中空白数据项的方法111
疑难75如何筛选各分店本周的销量数据111
通过日期筛选本周数据111
疑难76如何根据日期字段筛选周六、周日的人数112
筛选周末数据113
数据透视表中的筛选器与工作表中的筛选器的区别114
疑难77如何创建销售额大于200000的商品销售报表114
同一字段的多重筛选114
每个字段允许多个筛选115
搜索筛选器中的4种筛选方式115
疑难78如何为同一字段设置多个筛选条件116
同一字段的叠加筛选116
疑难79如何显示指定系中男生的人数117
多字段并列条件筛选117
疑难80如何获得三科成绩全优的学生列表118
多列值字段并列条件筛选118
疑难81如何筛选页字段中初三学年选手的信息119
报表筛选120
取消报表筛选120
疑难82如何按品名分页显示销售数据120
显示报表筛选页121
疑难83如何在筛选状态下包含数据源中的新增信息121
在手动筛选中包含新项目122
疑难84如何在字段筛选状态下显示无数据的项目123
显示无数据项目123
疑难85如何隐藏字段下拉列表中多余的项目124
隐藏字段下拉列表中多余项目124
5.2切片器筛选125
疑难86如何通过切片器筛选某个业务员的销售业绩125
插入切片器125
删除切片器126
隐藏切片器127
疑难87如何设置切片器的字体格式127
修改切片器样式127
如何新建切片器样式129
切片器元素中各部分含义129
疑难88如何隐藏切片器的标题130
不显示切片器页眉130
自定义切片器标题131
调整切片器大小131
疑难89如何通过切片器筛选若干出版社的教材信息132
切片器筛选多个项目132
切片器与筛选器的优缺点132
在切片器中选择项目的方法132
疑难90如何通过切片器同步筛选两个数据透视表133
切片器同步筛选多个数据透视表133
疑难91如何用切片器实现成绩表的多级联动134
多个切片器联动筛选134
断开切片器连接135
疑难92如何在被保护的工作表中使用切片器136
在受保护的工作表中使用切片器136
疑难93如何让切片器不显示数据源中删除的项目137
切片器不保留数据源删除的项目137
疑难94如何使交叉筛选的切片器不显示无效选项137
隐藏切片器的无效选项139
疑难95为什么删除切片器后还保留其筛选结果139
删除切片器同时清除数据透视表筛选140
疑难96切片器中的项如何自定义排序141
自定义排序切片器项目141
5.3练习与思考142

第6章数据透视表项目分组143
6.1自动分组143
疑难97如何按工龄分组统计各工龄段的员工人数143
等距步长自动组合数值型数据项143
疑难98如何按小时分组统计信鸽返回数量144
按小时自动分组145
疑难99如何按周分组汇总产量145
按周自动分组146
改变周起始日146
疑难100如何按“旬”分组汇总数据147
按旬自动分组147
不等距步长自动分组147
疑难101如何按年、季度、月份分组汇总销售额148
按年、季、月自动分组148
疑难102为什么提示“选定区域不能分组”149
选定区域不能分组的原因151
6.2手动组合151
疑难103如何按等第成绩统计学生人数151
不等距步长手动组合数值型数据项152
疑难104如何按上、下半年汇总销量153
按半年分组汇总154
疑难105二维表创建的数据透视表如何实施列字段组合154
手动组合文本型数据项155
疑难106如何统计同一行政区域的蔬菜销量155
按关键字分组汇总156
疑难107如何取消手动组合项目157
全局取消组合与局部取消组合158
6.4练习与思考159

第7章更新数据源160
7.1刷新160
疑难108如何手动刷新数据透视表160
手动刷新数据透视表160
疑难109如何在打开工作簿时自动刷新数据透视表162
打开文件自动刷新数据162
疑难110如何在更新数据透视表时保持原格式和列宽不变162
刷新时保留原有格式和列宽163
疑难111如何批量刷新数据透视表163
批量刷新数据透视表163
“数据透视表工具”的“全部刷新”与“数据”选项卡的
“全部刷新”有何区别164
疑难112如何定时刷新数据透视表164
定时刷新数据透视表165
疑难113为什么刷新数据透视表时数据会丢失166
疑难114如何清除字段下拉列表中已删除数据源的项目166
不保留从数据源中删除的项目167
疑难115如何更改现有数据透视表的数据源167
更改数据透视表数据源168
7.2动态数据源169
疑难116如何利用定义名称创建动态数据透视表169
定义名称法创建动态数据透视表169
OFFSET函数语法170
COUNTA函数语法171
疑难117如何利用表功能创建动态数据透视表171
利用表功能创建动态数据透视表171
疑难118如何通过编辑OLEDB查询创建动态数据透视表172
编辑OLEDB查询创建动态数据透视表173

第8章数据透视表的计算175
8.1值汇总方式175
疑难119如何快速汇总销售人员的销售额175
条件求和汇总175
数据透视表对字段类型的自动识别176
数据透视表的计算原理177
疑难120如何统计各高校获不同奖项的个数177
条件计数177
值字段的汇总方式178
疑难121如何统计各班级平均分与最高分178
条件极值和均值179
对同一字段使用多种汇总方式179
改变值字段汇总方式的5种方法180
疑难122为什么数据透视表的汇总方式默认为计数180
更改默认汇总方式180
8.2值显示方式181
疑难123如何统计各商品的销售额占比181
总计的百分比182
15种值显示方式及其功能182
疑难124如何统计全球手机品牌市场占有率183
列汇总的百分比183
疑难125如何统计各部门男女比例184
行汇总的百分比184
疑难126如何计算定基动态比率185
百分比185
疑难127如何统计销售额占父行汇总的百分比186
父行汇总的百分比186
父行汇总百分比与列汇总百分比的异同187
疑难128如何统计各车型的销量占分类汇总的百分比187
父级汇总的百分比187
疑难129如何统计各商品销量与上个月的差异188
差异189
疑难130如何统计商品房成交均价环比增长率190
差异百分比190
疑难131如何按月份累计发货金额191
按某一字段汇总191
疑难132如何统计等第成绩人数累计百分比192
按某一字段汇总的百分比192
疑难133如何按城市统计彩电的销量排名193
升序排列193
疑难134如何按报考职位统计考生的成绩排名194
降序排列195
疑难135如何统计各商品在不同区域的相对重要性196
指数196
8.3计算字段与计算项197
疑难136如何为成绩表添加总分计算字段197
添加计算字段197
二维表的总计列198
疑难137如何修改数据透视表中的计算字段公式198
修改计算字段公式198
删除计算字段199
疑难138如何按月统计各时段用电量占比199
二维表的行总计占比199
疑难139如何判断应付金额与已付金额是否平账200
数据透视表中使用公式返回#VALUE!错误值的原因201
计算字段公式可以使用哪些函数202
疑难140如何计算各商品的加权平均单价202
计算加权平均单价202
疑难141如何计算蔬菜销售的毛利203
再透视法203
计算字段的局限性204
疑难142如何计算商品售罄率205
插入计算项205
计算字段与计算项的区别206
修改计算项206
删除计算项206
疑难143如何统计销售额的年增长率207
隐藏计算项的无效数据行208
计算项的局限性208
疑难144如何统计货运每日延误率及总延误率208
计算项总计不正确的问题210
计算项的求解次序211
8.4数据透视表函数211
疑难145如何根据多列条件查询物品单价211
动态查询数据透视表数据211
GETPIVOTDATA函数的3种语法结构212
禁用自动创建GETPIVOTDATA公式213
GETPIVOTDATA函数特点213
疑难146如何获取自定义分类汇总数据213
查询自定义分类汇总数据214
疑难147如何根据现有数据透视表重置报表格式214
量身定做数据报表214
8.5练习与思考215
第9章多重合并区域数据透视表216
9.1多重合并计算216
疑难148如何创建单个自定义页字段的数据透视表216
单页字段多表合并数据217
使用“多重合并计算数据区域”创建数据透视表的工作原理218
创建单页字段的数据透视表的弊端219
疑难149如何创建多个自定义页字段的数据透视表220
多页字段多表合并数据220
疑难150如何比较两张表格相同科目的差异221
多区域合并比较差异221
疑难151如何合并不同工作簿的销售报表数据222
多工作簿合并数据223
疑难152如何按客户名称合并不同的指标数据224
多表合并不同指标数据224
疑难153如何统计不同员工选择不同选项的个数224
二维表文本计数225
使用“多重合并计算数据区域”创建不具有页字段的数据透视表226
疑难154如何按行总计百分比方式显示销售数据占比226
解决二维表行汇总百分比无法显示的问题226
疑难155如何将二维表格转换为数据列表227
二维表转数据列表227
疑难156如何创建动态多重合并计算数据区域数据透视表228
基于“表”创建的数据透视表229
基于定义名称创建数据透视表229
疑难157如何创建多个行字段的多重合并区域数据透视表229
9.2练习与思考230
第10章获取外部数据创建数据透视表231
10.1使用多维数据集创建数据透视表231
疑难158如何使用OLAP多维数据集创建数据透视表231
使用OLAP多维数据集创建数据透视表232
多维数据集模型233
OLAP源数据与非OLAP源数据的功能差异234
维度的展开与折叠235
疑难159如何创建脱机多维数据集235
创建脱机多维数据集236
疑难160如何利用脱机多维数据集创建数据透视表237
利用脱机多维数据集创建数据透视表238
疑难161如何利用多维数据集函数创建报表239
将数据透视表转换为公式239
利用多维数据集函数创建自定义报表240
多维数据集函数语法241
多维数据集函数列表242
10.2使用其他外部数据创建数据透视表242
疑难162如何从Access数据库导入数据创建数据透视表242
导入Access数据表创建数据透视表243
疑难163如何利用文本文件创建数据透视表243
利用文本文件创建动态数据透视表244
schema.ini文件详解246
疑难164如何连接SQLServer数据库创建数据透视表247
连接SQLServer数据库创建数据透视表247
疑难165如何断开数据透视表的外部数据源连接249
断开数据透视表与外部数据源的连接249
第11章数据透视表与SQL250
11.1SQL语法基础250
疑难166如何查询导入外部数据250
打开OLEDB窗口编写SQL语句251
使用SELECT查询所有数据记录251
什么是SELECT子句252
如何查询单列或多列记录252
疑难167如何表示Excel单元格区域253
选定指定单元格区域的数据作为查询数据源表253
疑难168如何使用列别名把英文字段转换为中文字段254
如何使用列别名255
疑难169如何查询上网流量超过80M的记录256
WHERE条件查询子句257
带有括号的字段名表示方式257
大于、小于等运算258
疑难170如何查询等级在B至E之间的记录258
模糊匹配模式中字符区间的表示259
疑难171如何查询所有姓“林”的员工的记录260
运用LIKE进行模糊匹配261
模糊匹配中不确定长度的字符表示261
模糊匹配模式中长度为1的字符表示262
疑难172如何计算学生总成绩并按总成绩降序排列262
字段的四则运算263
利用ORDERBY子句对记录进行排序263
SQL查询机制步骤详解264
疑难173如何查询满足申请租赁型经适房条件的人员264
同时满足多个条件的查询265
满足多个条件之一的查询265
疑难174如何分离籍贯字段中的城市和区县266
拆分字符串266
INSTR函数的使用267
LEFT函数的使用267
RIGHT函数的使用267
疑难175如何通过多条件分组统计数据268
AVG函数在SQL语句中的使用269
SUM函数在SQL语句中的使用269
GROUPBY子句的使用269
11.2利用OLEDB结合SQL创建数据透视表270
疑难176如何通过OLEDB来创建动态的数据透视表270
使用SQL语句创建动态的数据透视表270
疑难177如何汇总同一个工作簿中多个工作表的数据272
同一工作簿中多工作表的数据汇总272
UNIONALL的使用273
UNIONALL与UNION的区别274
疑难178如何汇总同一个工作簿中不同区域的数据274
同一个工作簿内不同工作表中不同区域的数据汇总275
疑难179如何汇总不同工作簿中不同工作表的数据275
不同工作簿中不同工作表的数据汇总277
疑难180如何汇总列数不等的多个工作表数据278
汇总列数不等的多个工作表数据279
0与Null的区别279
疑难181如何通过关联不同工作表的商品代码汇总销售金额279
工作表名称自定义别名280
多对一连接两个工作表281
疑难182如何连接两个以上工作表来创建数据透视表281
关联两个以上的工作表创建数据透视表282
疑难183如何创建进销存报表的数据透视表283
创建进销存的动态数据透视表284
疑难184如何用数据透视表创建日历285
获取日期的年、月、日、星期286
DatePart函数应用287
疑难185如何将包含空格的借方和贷方金额相减求余额287
IsNull函数的使用288
IIF函数的使用288
疑难186如何修改OLEDB查询路径289
修改OLEDB查询路径289
11.3结合MicrosoftQuery创建数据透视表290
疑难187如何利用Microsoft Query汇总单个数据表290
利用Microsoft Query汇总单个数据表291
如何解决数据源无可见表的问题293
疑难188如何利用Microsoft Query统计不重复数据个数293
利用Microsoft Query统计不重复值个数293
SELECTDISTINCT语句筛选不重复记录295
疑难189如何利用Microsoft Query汇总多个数据表295
利用Microsoft Query汇总多个工作表296
疑难190如何利用Microsoft Query关联汇总多个工作表298
左连接原理说明301
右连接原理说明301
内连接原理说明301
疑难191如何修改“Microsoft Query”查询的连接路径302
修改“Microsoft Query”查询的连接路径302
11.4练习与思考303
第12章数据透视表与PowerPivot304
12.1PowerPivotFor Excel简介304
疑难192如何在Excel中使用PowerPivot304
加载PowerPivotFor Excel304
什么是PowerPivotFor Excel305
疑难193如何使用链接表向PowerPivot添加数据306
创建链接表306
链接表的特点306
疑难194如何复制并粘贴数据到PowerPivot307
复制粘贴数据创建PowerPivot表307
粘贴数据的限制308
PowerPivot表与Excel工作表之间的差异308
疑难195如何使用表导入向导添加数据到PowerPivot308
使用“表导入向导”导入外部数据309
疑难196如何导入满足指定条件的数据310
在导入过程添加筛选器310
12.2在PowerPivot中使用数据透视表312
疑难197如何使用PowerPivot创建销售额汇总报表312
使用PowerPivot创建数据透视表312
PowerPivot数据透视表选项313
PowerPivot字段列表314
疑难198如何让分类汇总值不随着筛选而改变315
汇总值包含筛选项316
疑难199如何汇总比较计划与实际销售额317
创建一对多的表关系317
关系318
创建关系的规则和限制319
疑难200如何根据多列条件汇总比较多表数据320
添加计算列建立表关系320
计算列及其特点321
什么是DAX表达式322
疑难201如何按条件统计不重复数据个数322
添加度量值322
度量值323
统计不重复值个数323
DAX公式输入小技巧324
疑难202如何计算加权毛利率324
计算加权毛利率324
疑难203如何按部门及应发工资分段统计员工人数326
条件计数326
疑难204如何解决父级汇总的百分比不正确问题327
引用分类汇总值328
疑难205如何计算加权平均单价329
计算加权平均单价329
疑难206如何在数据透视表的值区域显示文本330
值区域显示文本330
疑难207如何筛选各蔬菜的最近且最低单价330
筛选最近且最低单价331
逻辑运算符332
疑难208如何让销售额的同比和环比不随筛选而改变333
同比和环比不随筛选而变333
时间智能函数334
疑难209如何在左侧显示分类汇总335
新建集合335
12.3练习与思考336
第13章数据透视表与VBA337
13.1VBA语法基础337
疑难210如何显示“开发工具”选项卡337
显示开发工具选项卡337
疑难211为什么无法运行文件中的代码338
启用宏338
宏的4个安全等级339
疑难212如何执行过程339
运行宏340
运行代码的多种方式340
疑难213如何在立即窗口中获得变量的结果342
查看变量运行结果342
VBA中几种窗口的作用343
疑难214如何用循环语句统计多个工作表中的报考人数343
For
ForEach
Do
疑难215如何获得创建数据透视表的代码347
录制宏347
数据透视表常用代码解析349
AddDataField与AddFields方法350
简化代码的方法350
数据透视表的对象模型351
13.2使用VBA创建数据透视表351
疑难216如何基于本地数据源创建数据透视表351
PivotTableWizard方法352
疑难217如何使用VBA创建动态数据透视表353
行列自动扩展的动态数据透视表354
疑难218如何创建默认汇总方式为求和的数据透视表355
默认汇总方式为求和的数据透视表355
疑难219如何快速汇总多个工作表357
多工作表合并计算357
二维数组创建多重合并计算区域359
疑难220如何创建多行字段的多重合并计算数据透视表359
多行字段多表合并计算360
13.3使用VBA操作数据透视表362
疑难221如何选中数据透视表的元素362
应用Pivot Select方法选取数据透视表中元素363
应用Pivot Fields对象选中数据透视表元素的方法364
疑难222如何隐藏数据透视表中指定的字段366
隐藏字段366
与Pivot Fields相关的几个属性367
疑难223如何用代码刷新数据透视表368
自动刷新数据透视表368
疑难224如何刷新受保护工作表中的数据透视表369
刷新受保护的数据透视表369
疑难225如何批量更改数据透视表的汇总方式370
批量更改汇总方式370
疑难226如何在数据透视表中插入切片器371
插入切片器371
获取切片器中选中的项目373
疑难227如何设置数据透视表不共享缓存374
更改数据透视表缓存374
多个数据透视表共享数据缓存375
新建数据透视表设置非共享缓存376
疑难228如何更改数据透视表数值区域的数字376
启用值区域编辑功能377
13.4练习与思考377
第14章数据透视图378
14.1创建数据透视图378
疑难229如何同时创建数据透视表和透视图378
同时创建数据透视表与透视图378
数据透视表与数据透视图的关系380
疑难230如何在现有数据透视表上创建透视图380
为现有数据透视表创建透视图381
数据透视图无法创建的图表类型383
创建数据透视图的方法比较383
疑难231如何通过VBA创建数据透视图383
VBA创建数据透视图384
数据透视图中数值字段的名称列表386
疑难232如何改变数据透视图的图表类型387
更改数据透视图的图表类型388
疑难233如何更改数据透视图的显示位置388
移动数据透视图389
14.2布局和样式389
疑难234如何隐藏数据透视图中的字段按钮390
隐藏字段按钮390
疑难235如何切换数据透视图中数据行/列显示391
切换数据行/列显示391
疑难236如何调整报表筛选字段至透视图的图例字段393
调整字段布局393
疑难237如何使数据透视图标题与页字段同步394
图表标题与页字段同步395
14.3典型应用案例395
疑难238如何在数据透视图中显示连续月份的趋势395
显示无数据的项目395
疑难239如何通过切片器显示各月销售额396
通过切片器按月份显示销售额397
疑难240如何把数据透视表中的总计反映在数据透视图中398
数据透视图中显示总计398
疑难241如何创建排列图399
排列图的原理及应用400
如何保存自定义图表类型400
14.4练习与思考401
第15章综合应用实战402
15.1汇总同期利润对比报表402
疑难242如何准备数据源403
利用名称定义动态数据源403
疑难243如何创建同期利润对比报表404
多重合并计算利润表数据404
疑难244如何调整同期利润对比报表的项目顺序406
手动排序同期利润对比报表的项目407
疑难245如何计算利润率408
插入利润率计算项408
15.2费用差异分析报表409
疑难246如何按年月汇总各项费用410
按年月汇总各项费用410
疑难247如何组合费用科目412
手动组合费用科目412
疑难248如何进行差异分析计算414
插入差异和增长比计算项414
利用公式计算总计415
15.3收发存汇总报表416
疑难249如何利用SQL创建收发存报表417
利用SQL创建收发存报表417
附录AExcel 2010数据透视表的新增和改进功能421
附录BPowerPivot中的特殊函数423
附录CExcel 2010规范与限制425
附录D光盘内容42

序言
自序
寻,是一种尺度。古时八尺为一寻,王安石诗云“飞来峰上千寻塔,闻说鸡鸣见日升”,用以形容飞来峰上塔之极高。
寻,是一种技巧。现今的网络为自学者提供了非常巨大的资源平台,但缺乏技巧的查询和求助,往往“众里寻他千百度”却难有“蓦然回首”的顿悟。而善用关键词搜索,则常可使人体会到“灯火阑珊”之美景。
寻,是一种追求。网络上活跃着这么一群人,他们曾经也是懵懂的菜鸟,在寻解获助时心存感激,在寻师问道时心怀崇敬。渐渐地,他们因互助的理念而聚集,相互扶持,坚持不懈地学习、实践与创新。面对一个个技术的据高点,他们是睿智而无私的“寻者”,壁立千寻敢登攀,寻根究底解疑难!
寻,是一种心境。求知探秘当志存华山论剑之高远,解决问题应意守无剑无招之寻常,这便是“寻者”修炼的心境。来者是客,有问必答,不慕华丽的招式,但求直击问题之根源,用简单的方法办成复杂的事情。
本丛书的作者就是这样的一群“寻者”。在长期的线上答疑、线下交流和培训活动中,他们接触过针对同一类问题千奇百怪的提问,深知许多人苦寻而不得的要害,甚至练就了一手仅根据含混不清的提问进行猜题、解题的本领。通过不断地积累与整理,Excel技巧网(ExcelTip.Net技术社区)的版主们推出了“Excel疑难千寻千解”系列丛书,这些“疑难”都是许多人曾经搜索和提问过的,也是大多数用户在工作中可能会遇到的一些寻常问题。书中案例涉及行政办公、人力资源、金融财务、经济统计、学校教育、工程技术、医疗卫生、质量管理、生产管理等多个领域,并根据Excel的主要功能分册整理成书。
在丛书编著过程中,“实用为王”的原则贯彻始终,所有案例都遵循实际工作场景和知识点解说紧密结合的主线,抛开一些华而不实、艰难晦涩的高深解法,而将基础理论和解决方案寓于寻常技巧之中,让读者能得其用、明其理,以期实现为更多的人解决更多问题的简单愿景。
寻者
序一
Excel是微软公司Office产品家族的一员,是一款功能强大、操作便捷且支持二次开发的制表软件,也是微软办公套装软件的一个重要组成部分。早在1987年第一款适用于Windows系统的Excel问世之初,它就超越了同类表格软件,至10年后发布Excel 97开始,Excel已经被广泛地应用于企事业管理、数据统计和财务金融等众多领域。
随着Excel 2010上市,我们迫切地需要能够全面解读Excel 2010的书籍来满足用户需求,帮助他们尽快了解基本功能、掌握应用技巧,从而更加高效地完成工作、学习及各专业领域中的应用。很开心看到“Excel疑难千寻千解丛书”的出版,目前出版的《Excel 2010操作与技巧》、《Excel 2010函数与公式》到《Excel VBA编程与实践》、《Excel 2010 SQL完全应用》等读本,以各自不同的侧重面展示了Excel 2010的诸多应用技巧,同时,也让人十分期待丛书其他读本的面市。
此系列丛书由多位微软最有价值专家(MVP)和Excel技巧网的版主参与撰写,他们凭借着出众的Excel技术水平,以及乐于奉献的无私精神,长期在Excel技巧网及微软Office技术社区孜孜不倦地帮助用户解决技术难题,积累了丰富的疑难处理经验和写作经验,为写作本丛书奠定了基石。
微软最有价值专家(MVP)是指具备一种或多种微软技术专业知识,并且积极参与在线或离线的社群活动,经常与其他专业人士分享知识和专业技能,受人尊敬、信任,而且平易近人的专家。他们是一群拥有丰富知识和实际经验的微软技术专家。他们不是微软的员工,但是非常乐于通过在线或离线社区的方式帮助他人。MVP项目主要目的在于鼓励形成一个充满活力的全球性社群,使用微软和用户之间建立良好的互动关系、增进相互了解,鼓励更多的社区专家深入到社区中帮助用户解决技术问题。
正由于以微软MVP为首的一群技术专家在Office技术社区的不断贡献,让我们看到了“Excel疑难千寻千解丛书”的出版。这是一套源自于技术讨论社区的书籍,这是一套打破传统模式而以发现问题、解决问题为本的书籍,这是一套汇集了社区专家智慧的书籍,这是一套让你看了爱不释手的书籍,这是一套快速帮您解决Excel技术问题的书籍。
立身以立学为先,立学以读书为本,与大家共勉之,希望大家通过阅读这套丛书而多多受益。
微软社区项目经理
林思绮
序二
与黄朝阳和Excel技巧网技术社区是在年初通过微软社区精英计划而结识的。微软社区精英计划是微软为了协助国内在技术社区上有一定影响力的社区或个人,或是愿意为国内信息技术做出贡献的社区或个人而设立的培训与支持计划,同时也是配合国家培育人才的政策而成立的。很高兴看到朝阳与Excel技巧网能将这些有用的信息集结成书,让更多的国人受惠。也很开心看到微软社区精英计划里的社区组织与意见领袖们能有此成就。
下面有4个如果,如果您正好符合其中一个,您可以考虑在书店里找个安静的角落享受一下,或者是带回家仔细品尝。
如果您还认为Excel只是一个试算表或是办公室软件,那么我会建议您阅读此书,让您将Excel的使用价值提升到另一个高度。
如果您是长期使用Excel的老手,应该不难发现Excel已不再是试算表的概念。经过微软研发团队多年来不断地收集与汇总用户反馈后,集成了中小型企业及大型企业乃至于个人使用者的不同需求,并将商业智能(Business Intelligence)巧妙地揉进里面,透过此书,希望您能发现更多 Excel 的应用技巧,提高您的使用技能。
如果您是使用 Excel 的专家,也许这些通用型功能还不足以满足您的需求,您还可以通过使用VBA(Visual Basic for Application)定制自定义函数、自动化流程等特定功能来满足不同的需求,通过VBA分册,您能发现更多VBA的秘密,让 VBA成为您得力的助手。
如果您在寻找不同场景或是产业链中Excel所扮演的角色,我希望这本书中的案例与实践能帮到您,希望您能将这些案例与实践应用于您具体的场景或产业链中。
开卷有益,开卷有得,每个人读书所获得的都不一样,只要打开书本读书,总是有好处的。读书即未成名,终究人品高雅——与君共勉之。
微软(中国)市场经理 廖浩志
序三
用Excel的人很多,会用Excel的人很少。
类似的情况也同样出现在Excel图书上:介绍Excel的图书很多,真正对读者有用的书很少。
在浏览过琳琅满目、各种类型或各种专题的Excel图书之后,在领略过百科全书式的详细解析或是武功秘籍般的招式演绎之后,依然有这样一套“Excel疑难千寻千解”能够让人眼前一亮。刮目相看的原因在于,这套书把立场和视角真正建立在了读者的基础上——每一个问题都来源于Excel用户可能真实面对的疑惑和困扰,每一个解答都有可能正是许多坐在计算机前束手无策的朋友正在搜寻的目标。这套书真正做到了理解读者在想些什么,体会到了读者正在面对怎样的困境,从而引领我们从四处碰壁的电子迷宫中轻松突围。而本书的作者,这些技术超群的Excel专家们,这些被称为电子世界的罗宾汉们,用他们的专业精神和实用技术,给众多受困于Excel无边苦海中的数据工作者们奉献了一套真正意义上的Excel生存手册。
如果你也正准备踏上征服Excel的旅程,不妨就把这本书也放进你的背包行囊里吧!
微软全球最有价值专家MVP 方骥
序四
我在微软Office开发平台上工作已经有很长一段时间了,从最早在台湾策划的“Office首届开发技术大会”到主持大陆地区的“Office首届开发技术大会”,在过去将近二十年的时间里,协助微软发表过许多重量级的产品,也接触过无数Office用户。当我拿到“Excel疑难千寻千解丛书”时,很自然地想到这些朋友和伙伴需要什么样的鼓励和帮助。
每个时代都在发生变化,我们学习的方式也会有所不同。在选择一本图书学习的时候,我们不仅仅考虑价格,更重要的是考虑这本书的价值,其中包括“谁写的?”、“写的什么内容?”无疑,“Excel疑难千寻千解丛书”的作者不仅在他们各自的领域有着相当丰富的经验,而且其中一些作者还是微软最有价值专家(Microsoft MVP),这使得书籍的内容更具有实用的价值。
在20世纪90年代初期,大多数电脑爱好者都是通过新闻群组(newsgroup)的方式交流经验,这期间不时有许多经典之作和奇思妙想,我当时就在想,为什么没有人把这么多精华结合实务整理出来呢?答案就是没有那么多专业的人力,更重要的是一种完全投入的兴趣与不问收获的持续付出,而现在“Excel疑难千寻千解丛书”的出版使这个问题得到了解决。
我非常认真地翻阅了“Excel疑难千寻千解丛书”的内容和目录,以较高和挑剔的标准替读者把关和审核。我的建议是阅读这系列丛书时,避免将它从第一页开始读到最后一页,这不是一套庸俗之作,它的精华在于目录,因为它就像字典一样,当每一个希望自己在相关领域更上一层的Excel玩家碰到相关的问题时,可以通过它先判断类别,然后搜索目录找到相关的问题,直接跳到那一页,每个问题几乎都有“操作方法”、“原理分析”和“知识扩展”,让读者从一个问题点学习到更多的内容。
如同所有的书籍一样,这套书也有再版时可以改进的空间,但如果有一天,当你在整理房间,并决定丢掉一些过时的计算机书籍时,请你务必留下它,因为某个百思不解的知识很可能就静静地躺在书里的某一页角落,等着与你相遇。

文摘
版权页:

Excel 2010数据透视表大全

插图:

Excel 2010数据透视表大全

度量值是多维数据集中基于多维数据集事实表中的列的一组值,通常为数值。它是多维数据集中经过预处理、聚合和分析的核心值。常见示例包括销售额、利润、收入和成本等。
KPI
KPI是一种特殊的计算度量值。在服务器上定义,允许用户跟踪包括状态(当前值是否达到特定数字)和趋势(一段时间后的值是多少)在内的“关键绩效指标”。当显示这些内容时,服务器可以发送与Excel条件格式图标集相似的相关图标,以指示在状态级别以上或以下(如“停止”指示灯图标)或者某个值的趋势是向上还是向下(如方向箭头图标)。
知识扩展
OLAP源数据与非OLAP源数据的功能差异
在使用OLAP和非OLAP源数据创建数据透视表时,其差异主要表现在如下几方面。
数据检索
每次更改报表布局时,OLAP服务器都会将新的数据返回到Excel中。对其他类型的外部源数据,用户可以查询所有源数据,或者按条件查询某几个字段的数据,用户还可以设置不同的刷新方式刷新报表。
在基于OLAP源数据的报表中,报表筛选字段设置不可用,后台查询不可用,并且内存的优化设置也不可用。
字段类型
对于OLAP源数据,维度字段只能用作行(系列)、列(类别)或者页字段。度量值字段只能用作值字段。对于其他类型的源数据,所有字段都可用于报表的任何部分。
访问明细数据
对于OLAP源数据,服务器将确定什么级别的明细数据可用,并计算汇总值,因此可能无法使用构成汇总值的明细数据记录。但是,服务器也能提供可以显示的属性字段。其他类型的源数据没有属性字段,但用户可以显示数据字段数值和项的基本明细数据,也可显示没有数据的项。
OLAP报表筛选字段可能没有“全部”项,并且“显示报表筛选页”命令不可用。
初始的排序顺序
对于OLAP源数据,项最初是按OLAP服务器返回它们的顺序显示的。用户可以对项进行排序或手动调整。对于其他类型的源数据,在新报表中第一次显示时,项是按照项名称升序排序的。
计算
OLAP服务器直接为报表提供汇总值,因此,用户不能更改值字段的汇总函数。对于其他类型的源数据,可以更改值字段的汇总函数,而且可以为相同的值宇段使用多个汇总函数。用户不能在包含OLAP源数据的报表中创建计算字段或计算项。

内容简介
《Excel疑难千寻千解丛书:Excel 2010数据透视表大全》是“Excel 疑难千寻千解”丛书之一,为读者展示了在Excel 2010中运用数据透视表解决疑难问题的实战技巧。《Excel疑难千寻千解丛书:Excel 2010数据透视表大全》共15章,包含249个疑难案例,每个疑难都包含一个或多个解决方案,并融入一个或多个知识点讲解,贴切实际,深入浅出。在解决问题的同时,引导读者学习数据透视表应用的原理和知识扩展,适合各个层次的Excel用户,既可以作为初学者的入门教材,又可作为中、高级用户的参考手册。书中案例多数来源于各行各业中的实际案例,读者可以直接应用于实际工作中。阅读《Excel疑难千寻千解丛书:Excel 2010数据透视表大全》后,可以帮助读者快速提高数据处理能力和工作效率。

购买书籍

当当网购书 京东购书 卓越购书

PDF电子书下载地址

相关书籍

搜索更多