描述
开 本: 16开纸 张: 胶版纸包 装: 平装-胶订是否套装: 否国际标准书号ISBN: 9787302492924
《Excel 2016数据处理与分析实战秘籍》内容丰富实用，操作详细具体，既适合Excel 2016初、中级读者阅读，也适合公司或企业中从事文秘、管理、市场、营销、财务等工作的办公人员学习，同时还可作为高等院校和社会培训机构的相关教材。
第1章 数据填充
1.1.
鼠标拖放或双击以填充数据 1
1.1.1
启用填充柄和单元格拖放功能 1
1.1.2
填充柄使用之三部曲 3
1.1.3
使用左键拖放填充 3
1.1.4
使用右键拖放填充 12
1.1.5
使用左键双击填充 15
1.2 自定义序列填充 16
1.2.1
添加自定义序列“星期” 16
1.2.2
导入自定义序列“学历” 17
1.2.3
根据标题自动填充“个人信息” 18
1.2.4
通过“序列”对话框填充序列 19
1.3 使用填充命令填充相同内容 20
1.4 使用公式填充序号、编号 21
1.4.1
填充无空行数据的连续编号 21
1.4.2
填充有空行数据的连续编号 23
1.4.3
规避特殊数字填充编号 24
1.4.4
填充连续的英文字母 25
1.5 快速填充 26
1.5.1
启用记忆式键入和快速填充功能 26
1.5.2
记忆式键入现有条目 26
1.5.3
预览快速填充的长度数字 27
1.5.4
使用命令快速填充宽度数字 28
1.5.5
快速提取带度量单位的高度数字 30
1.5.6
从数字中提取数字或插入字符 31
1.5.7
合并多列内容并添加符号 34
1.5.8
将姓名分成姓和名两列 34
1.5.9
排序后多次快速填充 35
1.5.10
调整字符串的前后顺序 36
1.5.11
复制相同的内容 37
1.5.12
灵活转换大小写 37
1.6 定位填充 38
1.6.1
填充空白单元格 38
1.6.2
填充上行内容 39
第2章 数据验证
2.1 “数据验证”对话框介绍 42
2.1.1
打开“数据验证”对话框 42
2.1.2
“设置”选项卡简介 42
2.1.3
“输入信息”选项卡简介 43
2.1.4
“出错警告”选项卡简介 44
2.1.5
“输入法模式”选项卡简介 45
2.2 为非序列设置数据验证 47
2.2.1
为数值型数据设置数据验证 47
2.2.2
使用公式自定义数据验证 54
2.3 为一个序列设置数据验证 71
2.3.1
直接输入一个序列设置数据验证 71
2.3.2
引用区域设置有序列的数据验证 72
2.3.3
使用名称设置有序列的数据验证 75
2.3.4
设置可以增缩序列的数据验证 81
2.3.5
设置可模糊输入序列的数据验证 87
2.4 二级序列实例：从组别中选择人员 95
2.4.1
建立表头 95
2.4.2
完善数据表 96
2.4.3
完善辅助列 96
2.4.4
设置数据验证 97
2.4.5
试用数据验证 99
2.5 三级序列实例：填写籍贯 99
2.5.1
获取行政区划数据 99
2.5.2
整理行政区划数据 100
2.5.3
制作三级序列 106
2.6 可拓展的三级序列：填写籍贯 112
2.6.1
建立辅助表 112
2.6.2
定义名称 114
2.6.3
建立三级序列 115
2.7 可缩减的五级序列：填写地址 116
2.7.1
获取行政区划及代码 117
2.7.2
获取五级行政区划 121
2.7.3
制作五级序列 128
2.7.4
五级序列的缩减 132
2.8 数据验证的其他问题 132
2.8.1
数据验证的查找 132
2.8.2
数据验证的更改 134
2.8.3
数据验证的清除 134
2.8.4
数据验证区域的扩展 135
2.8.5
圈释无效数据 135
2.8.6
数据验证无效的原因排查 137
2.8.7
数据验证命令不可用的原因排查 138
第3章 数据排序
3.1 调用排序命令 140
3.1.1
在“数据”选项卡直接调用 140
3.1.2
在“开始”选项卡直接调用 140
3.1.3
启用“筛选”功能间接调用 141
3.1.4
创建表格间接调用 142
3.1.5
创建数据透视表间接调用 142
3.1.6
通过右键快捷菜单直接调用 143
3.2 快速地对数据排序 144
3.2.1
快速地对语文成绩排序 144
3.2.2
快速地对出生日期排序 145
3.2.3
快速地对姓名排序 145
3.3 指定条件对数据排序 146
3.3.1
按总分和语文成绩排序 146
3.3.2
按数学成绩的字体颜色排序 148
3.3.3
按语文成绩的单元格颜色排序 149
3.3.4
按总分的单元格图标排序 150
3.3.5
按姓名的笔画数排序 150
3.3.6
对字母区分大小写排序 151
3.3.7
对无标题行的数据表排序 153
3.3.8
对数据表按行排序 154
3.4 自定义列表排序 155
3.4.1
创建自定义列表“高中低” 155
3.4.2
按自定义列表对身高排序 156
3.5 排序规则和问题 157
3.5.1
排序规则列表 157
3.5.2
对混合型数字排序 158
3.5.3
处理文本中的空格 163
3.5.4
重新应用排序 164
3.5.5
排序的常见问题 165
3.6 排序功能的拓展应用 166
3.6.1
选定区域排序 166
3.6.2
序号不参与排序 168
3.6.3
返回排序前的状态 169
3.6.4
按内容长短排序 169
3.6.5
按字母和数字排序 170
3.6.6
字母区分大小写排序 173
3.6.7
对数据随机排序 174
3.6.8
对数据隔行排序 175
3.6.9
隔行填充列标题 176
3.6.10
对合并单元格排序 177
3.6.11
多列分别排序 180
3.6.12
清除多处空行 183
3.6.13
VBA指定规则排序 184
3.7 在数据透视表中排序 186
3.7.1
创建数据透视表 186
3.7.2
按班级总分项排序 190
3.7.3
按班级和个人总分排序 191
3.7.4
按总分和数学成绩排序 192
3.7.5
对局部数据排序 195
3.7.6
按汉字笔画排序 196
3.7.7
按自定义列表排序 197
3.7.8
对行排序 197
3.7.9
手动排序 198
第4章 数据筛选
4.1 灵活自如的自动筛选 201
4.1.1
启用自动筛选的方法 201
4.1.2
在筛选状态下排序 204
4.1.3
按颜色（图标）筛选 205
4.1.4
文本按条件筛选 206
4.1.5
数字按条件筛选 209
4.1.6
日期按条件筛选 211
4.1.7
奇妙的关键字筛选 214
4.1.8
通过值列表筛选 218
4.1.9
多列多次自动筛选 219
4.1.10
通过右键快捷菜单筛选 220
4.1.11
固化自动筛选结果 220
4.1.12
重新应用自动筛选 222
4.1.13
自动筛选的退出和清除 223
4.2 功能强大的高级筛选 227
4.2.1
高级筛选对话框和条件区域 227
4.2.2
使用单条件高级筛选 229
4.2.3
使用多条件“并”的高级筛选 230
4.2.4
使用多条件“或”的高级筛选 231
4.2.5
使用多条件“并”“或”结合的高级筛选 231
4.2.6
使用公式模糊筛选 232
4.2.7
使用公式精确筛选 233
4.2.8
使用逻辑条件值筛选 233
4.2.9
使用通配符实现模糊筛选 237
4.2.10
撤销或清除高级筛选结果 239
4.2.11
重组数据表字段或减少字段 240
4.2.12
巧妙筛选不重复记录 243
4.2.13
按特定条目筛选（多对多筛选） 246
4.2.14
对两组数据进行比对式筛选 247
4.2.15
使用名称进行高级筛选 251
4.2.16
自动实时刷新筛选结果 257
4.3 使用SUBTOTAL函数处理筛选结果 263
4.3.1
自动筛选后得到连续编号 263
4.3.2
统计筛选后的不重复人数和总人数 264
4.3.3
返回筛选后人和年龄值 266
4.4 在数据透视表中筛选 267
4.4.1
建立数据透视表 267
4.4.2
通过标签筛选某人之后的人员 268
4.4.3
通过值筛选大于或等于60岁的人员 269
4.4.4
通过搜索框筛选包含某字的人员 270
4.4.5
通过值列表筛选 271
4.4.6
对同一字段多次筛选 272
4.4.7
数据透视表的刷新 273
4.4.8
数据透视表中的自动筛选 275
4.4.9
锦上添花的切片器筛选 276
4.5 使用记录单查询 279
4.5.1
添加“记录单”按钮 279
4.5.2
使用单个条件查询 280
4.5.3
使用运算符查询 281
4.5.4
使用通配符查询 282
4.5.5
使用多条件组合查询 283
4.6 在外部数据库中筛选 283
4.6.1
准备Excel数据库 283
4.6.2
安装ODBC驱动程序 284
4.6.3
Microsoft Query查询向导 286
4.6.4
Microsoft Query窗口查询 290
4.6.5
Microsoft Query参数查询 294
4.6.6
编辑查询和修改参数 297
第5章 条件格式
5.1 新建条件格式的两种方式 299
5.1.1
通过快速格式化新建条件格式 300
5.1.2
通过高级格式化新建条件格式 301
5.2 基于各自值设置所有单元格的格式 302
5.2.1
设置色阶样式的条件格式 302
5.2.2
设置图标样式的条件格式 305
5.2.3
设置数据条样式的条件格式 310
5.3 只为包含以下内容的单元格设置
格式 314
5.3.1
为单元格值设置单元格格式 315
5.3.2
为特定文本设置单元格格式 317
5.3.3
为发生日期设置单元格格式 319
5.3.4
为错误值设置单元格格式 321
5.4 仅对排名靠前或靠后的数值设置
格式 322
5.4.1
通过高级格式化标识末端数据 322
5.4.2
通过快速格式化标识末端数据 323
5.5 仅对高于或低于平均值的数值设置
格式 324
5.5.1
通过高级格式化标识靠边数据 324
5.5.2
通过快速格式化标识靠边数据 325
5.6 仅对值或重复值设置格式 326
5.6.1
通过高级格式化标识重复值 326
5.6.2
通过快速格式化标识重复值 327
5.7 使用公式确定要设置格式的
单元格 328
5.7.1
公式中的单元格引用 328
5.7.2
为数据设置自动框线 333
5.7.3
判断所录数据的正确性 337
5.7.4
比较两列数据的异同 340
5.7.5
提醒生日等重要日期 341
5.7.6
按月份汇总加班时数 346
5.7.7
多种方法定位查找到的记录 346
5.7.8
标识隔行或隔项着色的记录 351
5.7.9
标识距离矩阵的对称性 353
5.7.10
标识宣传任务的完成情况 355
5.7.11
标识号码的历史中奖情况 357
5.7.12
科学校验身份证号码 357
5.7.13
标识不同类别的学生 359
5.7.14
标识双重重复项记录 361
5.8 使用条件格式制作图表图像 361
5.8.1
制作项目进度图 361
5.8.2
制作保质期横条图 364
5.8.3
制作数据环比图 368
5.8.4
制作多色方块图 370
5.8.5
制作旋风图 374
5.8.6
构造地板砖和人物头像 375
5.9 使用“快速分析”以应用条件
格式 376
5.10
条件格式的复制、查找和清除 377
5.10.1
条件格式的复制 377
5.10.2
条件格式的查找 380
5.10.3
条件格式的清除 381
5.11
条件格式的管理 383
5.11.1
新建条件格式规则 384
5.11.2
编辑条件格式规则 384
5.11.3
删除条件格式规则 385
5.11.4
调整规则的优先级 386
第6章 数据透视表
6.1 创建数据透视表 388
6.1.1
创建简单的数据透视表 388
6.1.2
创建多条件汇总的数据透视表 390
6.1.3
对二维表创建数据透视表 392
6.2 数据透视表的设置和布局 396
6.2.1
显示/隐藏数据透视表字段列表 396
6.2.2
升序排列数据透视表字段 397
6.2.3
调整数据透视表字段布局 398
6.2.4
在区域节调整字段区域 399
6.2.5
隐藏字段标题和筛选箭头 401
6.2.6
选择数据透视表的区域 402
6.2.7
改变字段项的位置 405
6.2.8
更改分类汇总布局 406
6.2.9
更改总计行布局 409
6.2.10
更改报表布局 414
6.2.11
对字段进行分组 422
6.2.12
巧获多行多列的值 424
6.3 数据透视表计算 425
6.3.1
值汇总方式 425
6.3.2
值显示方式 428
6.3.3
计算字段和计算项 438
6.3.4
多重合并计算 441
6.3.5
数据透视表函数 446
6.4 数据透视表对象的操作 453
6.4.1
数据透视表的重命名 453
6.4.2
数据透视表的移动 454
6.4.3
数据透视表的复制 454
6.4.4
数据透视表的删除 455
大家知道，Excel是微软公司办公套装软件Microsoft Office的一个重要组成部分，可以处理各种数据、统计分析和辅助决策，广泛地应用于统计、财经、金融、管理等众多领域。自1987年款适用于Windows系统的产品诞生以来，Excel即将迎来而立之年，版本为Excel 2016，又称为Excel 15。
Excel是办公自动化中非常重要的一款软件。在数据统计分析领域，Excel独占鳌头，是当之无愧的世界领导者，至今还没有一款软件在普及程度、应用水平等综合性能上能撼动Excel的领导地位。可以这么说，Excel博大精深，是一座取之不尽的金矿，也是一部值得拥有的数据统计分析领域的“葵花宝典”。
Excel 2000刚出来的时候，由于工作需要，笔者开始接触Excel。后来，随着Excel版本的更新和使用的增加，逐渐喜欢上Excel。经过不断学习和深入研究，到现在，算得上略有心得、不离不弃。
在工作中，笔者以熟练掌握和应用Excel为己任，近年来加大了研究力度，4项市、区级相关课题成功结题，在公开期刊上发表论文10余篇。平时，笔者不时观察别人如何使用Excel，想从中偷学一招半式。但很多时候发现，本来直截了当的操作，由于操作者对一些技巧不了解或不熟悉，走了一些弯路，枉费了不少精力。于是，笔者便萌生了写一本专著的想法。本书既适合Excel初、中级用户学习，又适合高级用户借鉴；既适合个人自学，也适合作为高中、高职和办公自动化培训机构的选修或培训教材。
应该说，Excel用户群在应用水平上呈金字塔分布，多数用户居于塔基和塔身部位。俗话说，“磨刀不误砍柴工”。如果初、中级用户能够挤出一些时间学习Excel，那么，通过提高工作效率而节省下来的时间可以远远多于学习时间，何乐而不为呢？俗话又说，“基础不牢，地动山摇”，基础的重要性是显而易见的。基本知识和基本能力是学习任何一门科学的基础。熟练的Excel基本功是修炼Excel这部“武学秘籍”的下盘功夫。熟练的Excel技巧可以破解一些疑难困境，不至于临阵磨枪。夯实了Excel基础，用户的水平何愁不能得到质的提升呢？
在日常工作中，Excel数据填充、数据验证、数据排序、数据筛选、条件格式和数据透
II
视表，是Excel大众群体和初、中级用户常使用的、容易掌握的，能够快速、显著提高
数据处理与分析工作效率的六大技术，堪称Excel数据处理和分析的六项关键技术，也可以
说是Excel数据处理和分析的“六脉神剑”，可以匹配其实际需求。运用Excel处理和分析数
据，要实现从新手到高手的华丽转身，必须掌握这六项关键技术。当然，这六项关键技术并
非截然分离，它们之间互相补充，提升了Excel的综合实力。从总体上来说，前两者侧重数
据录入的快速、规范，后四者侧重数据的处理、分析，多数技巧属于初、中级技巧。Excel
用户牢固掌握了这六大技术，无异于占据高效办公和深入学习的支点，可以举重若轻，四两
拨千斤。退，可以坐享其成数年；进，就有了腾飞的发射平台。学习这六大技术，投入少、
容易学、周期短、见效快、终身受益。
基于以上缘由，本书共分为6章。
第1章介绍数据填充功能，主要包括使用鼠标拖放或双击填充、自定义序列填充、使用
填充命令填充、快速填充、定位填充等内容。
第2章介绍数据验证功能，包括“数据验证”对话框设置，非序列设置，序列设置，二
级序列实例、三级序列实例、五级序列实例，数据验证的查找、更改、清除、扩展等内容。
第3章介绍数据排序功能，包括简单排序、指定条件排序、自定义列表排序，排序规则
和问题，排序功能的拓展应用，在数据透视表中排序等内容。
第4章介绍数据筛选功能，包括自动筛选、高级筛选、使用SUBTOTAL 函数处理筛选结
果、在数据透视表中筛选、使用记录单查询、在外部数据库中筛选等内容。
第5章介绍条件格式功能，包括基于各自值设置所有单元格的格式，只为包含特定内容
的单元格设置格式，使用公式确定要设置格式的单元格，使用“快速分析”以应用条件格
式，条件格式的复制、查找、清除和管理等内容。
第6章介绍数据透视表功能，包括创建简单的、多条件汇总的或多重合并区域的几种数
据透视表，数据透视表的多样化设置和布局，数据透视表的值汇总和显示方式，为数据透视
表添加新的计算字段和计算项、进行多重合并计算、使用数据透视表函数进行计算，数据透
视表对象的操作等内容。
本书围绕Excel数据处理和分析的六项关键技术，聚焦于实战技巧，专注于讲透讲全，
使读者醍醐灌顶，豁然开朗，了然于胸。在详解、细讲、深研的基础上，辐射了自定义格
式、表格、函数、名称、去重、分列、宏、VBA等功能，使本书成为Excel数据处理和分析
六大基本功能的工具书，更成为拓展、延伸学习Excel的立交桥。Excel 2016比起以往版本，
在界面、功能、命令、对话框等方面均有较大的变化，因此初学者未必有接触或接触不多。
有些用户可能直接从Excel 2003跳升到Excel 2016，跨度很大。基于此，对几大功能的介绍是
从零开始的
2 选择功能区“开始”选项卡。
3 在“编辑”组中，单击“排序和筛选”按钮。
4 在下拉菜单中选择各种排序选项。
操作过程如图3-4所示。
1234
图3-4 在“开始”选项卡中调用排序命令
3.1.3 启用“筛选”功能间接调用
启用“筛选”功能间接调用的具体操作过程如下：
1 选择数据区域中的任意一个单元格。
2 选择功能区“数据”选项卡。
3 在“排序和筛选”组中，单击“筛选”按钮；或在“开始”选项卡中的“编辑”组中单击“排序和筛选”按钮，在下拉菜单中选择“筛选”选项，都可以启用筛选功能，同时也开启了排序通道。
4 单击任意列标题（字段）上的筛选箭头，可以调用排序命令。
操作过程如图3-5所示。
1234
23410图3-5 启用“筛选”功能调用排序命令
绩降序排序，应该如何操作呢？具体操作过程如下：
1 在要排序的数据区域中选择任意一个单元格，比如A2单元格。
2 选择“数据”选项卡。
3 在“排序和筛选”组中，单击自定义“排序”按钮，显示“排序”对话框。此时，数据表会自动处于选中状态。
4 在“主要关键字”下拉列表框中选择“总分”选项，“排序依据”保持“数值”选项不变。
5 在“次序”下拉列表框中选择“降序”（数字从大到小）选项。如果“总分”要从小到大排序，则选择“升序”（数字从小到大）选项。
6 单击“添加条件”按钮。
7 在“次要关键字”下拉列表框中选择“语文”选项，“排序依据”保持“数值”选项不变。
8 在“次序”下拉列表框中选择“降序”（数字从大到小）选项。如果“语文”要从低到高排序，则选择“升序”（数字从小到大）选项。
9 单击“确定”按钮，完成排序。
操作过程及排序效果如图3-12所示。
23647851
图3-12 对数值指定排序
9
Excel 2016数据处理与分析
150
3.3.4 按总分的单元格图标排序
将复制的一个“数据”工作表更名为“图标”。数据表中，已将“总分”设置为“3个三角形”的条件格式。如果要对“总分”列按单元格图标排序，应该如何操作呢？按单元格图标排序与按单元格颜色排序、按字体颜色排序的操作过程完全相同。
事先在要排序的数据区域中选择任意一个单元格，打开“排序”对话框。然后进行如下操作：
1 在“排序”对话框“列”下的“主要关键字”框中选择要排序的“总分”列。
2 在“排序依据”下，选择排序类型“单元格图标”选项。
3 在“次序”下，单击按钮旁边的箭头，选择单元格图标“绿色正三角形”选项。
4 在右侧框中，选择位置“在”选项。
5 单击2次“复制条件”按钮。
6 将“次要关键字”的“次序”项下对应的框中更改为“黄色虚线三角形”选项。
7 再将“次要关键字”的“次序”项下对应的框中更改为“红色倒三角形”选项。
8 单击“确定”按钮，完成排序操作。
操作过程及排序效果如图3-15所示。
12367854
图3-15 按总分的单元格图标排序
3.3.5 按姓名的笔画数排序
对姓名进行排序时，中国人喜欢按“姓氏笔画”升序排序。将复制的一个“数据”工作表更名为“笔画”，去掉“ 张吉”名字中的空格。如果要对“姓名”列按笔画数量从少到多排序，应该如何操作呢？
Excel 2016数据处理与分析
图3-17 不区分大小写排序的效果
再进行区分大小写的排序。事先在要排序的数据区域中选择任意一个单元格，打开“排序”对话框。然后进行如下操作：
1 在“排序”对话框“列”下的“主要关键字”框中选择要排序的“编号”列。
2 在“排序依据”下，保持排序类型“数值”选项不变。
3 在“次序”下，保持排序顺序“升序”选项不变。
4 单击“选项”按钮。
5 在打开的“排序选项”对话框中，勾选“区分大小写”复选框。
6 单击“确定”按钮2次，完成排序操作。
操作过程及排序效果如图3-18所示。
123456
图3-18 区分大小写排序的效果
可以看出，不区分大小写排序时，编号为“A12”的数据在第3行，而区分大小写排序时，它排在了第4行。
Excel 2016数据处理与分析
3.3.8 对数据表按行排序
有时不需要对列排序，而是对行排序。将复制的一个“数据”工作表更名为“行序”。如果要对“语文”“数学”成绩第5行按升序排序，应该如何操作呢？
事先在要排序的数据区域中选择任意一个单元格，打开“排序”对话框。具体操作过程如下：
1 选择E1:F8区域。这里以部分数据为例。
2 选择“数据”选项卡。
3 在“排序和筛选”组中，单击“排序”按钮。
4 在“排序”对话框，单击“选项”按钮。
5 在打开的“排序选项”对话框中，在“方向”组下，单击“按行排序”单选按钮。
6 单击“确定”按钮，返回到“排序”对话框。
7 在“行”下的“主要关键字”框中，选择要排序的行。本例选择“行5”。
8 在“排序依据”下，选择“数值”选项。这里还可以选择“单元格颜色”“字体颜色”或“单元格图标”。
9 在“次序”下，选择“升序”选项。这里还可以选择“降序”或“自定义序列”选项。若要按单元格颜色、字体颜色或单元格图标排序，请先在“排序依据”的下拉列表中选择单元格颜色、字体颜色或单元格图标，然后在“次序的”下拉列表中选择“在左侧”或“在右侧”。
10 单击“确定”按钮，完成排序。
操作过程及排序效果如图3-20所示。
17894326510
图3-20 对行排序
Excel 2016数据处理与分析
表3-1 Excel默认的排序规则
数据类型
排序规则
数值型
数字按从小的负数到的正数进行排序
日期按从早的日期到晚的日期进行排序
文本型
文本及包含存储为文本的数字的文本按以下次序排序:
（空格） ! ” # $ % & ( ) * , . / : ; ? @ [ ] ^ _ ` { | } ~ < = > 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
注释：
1.撇号 (‘) 和连字符 (-) 会被忽略。但例外情况是：如果两个文本字符串除了连字符不同外其余都相同，则带连字符的文本排在后面
2.字母、数字、符号、文字等组成的文本，按从左到右的顺序逐字符进行排序。例如，“A1”“A100” “A11”，在第3字符位置区分
3.如果已通过“排序选项”对话框将默认的排序次序更改为区分大小写，则字母的排序次序为：a A b B c C d D e E f F g G h H i I j J k K l L m M n N o O p P q Q r R s S t T u U v V w W x X y Y z Z
评论
还没有评论。