Excel 数据处理思维带你高飞

本书是零基础入门教程，阅读本书不需要任何Excel基础。书中各章节之间相互独立，读者可以根据需要选择自己感兴趣的部分阅读。

本书通过具体而又鲜活的案例从多个角度系统深入地介绍了Excel数据处理思维。

本书以介绍数据处理思维为主旨，帮助读者厘清数据处理的基本方法和思路，不依赖于特定的Excel版本，掌握数据处理思维。本书的案例介绍更关注数据处理过程中的思考过程和问题分析，对思考过程进行了细致的介绍，而不是简单地给出操作步骤和结果，试图让读者能够掌握数据处理的一般过程和方法。在本书中，部分案例尝试了采用从多个角度去解决问题的方式，目的在于让读者能够从不同的角度更清晰地去了解问题的逻辑和解决思路。

本书适合初学者自学使用；适合具有一定Excel使用基础，希望更加系统深入地掌握Excel数据处理功能的数据处理人士使用；也可以作为大专院校的教材使用；还可以作为Excel培训教师的参考用书。

李立宗

南开大学硕士，天津职业技术师范大学副教授，CDA兼职讲师。长期致力于Excel的应用和教学研究工作，在Excel数据处理领域具有丰富的教学和实践经验。培训过的企业有：花旗银行、恒安标准人寿、我爱我家等单位。在Excel教学中采用的实时数据案例教学法，改变了传统以知识点为导向的教学方式，直接抓取数据网站实时数据，对数据进行综合分析处理，极大提高了学习者的学习效率和学习兴趣，深受广大学习者的喜爱和好评。

本书适合初学者自学使用；适合具有一定Excel使用基础，希望更加系统深入地掌握Excel数据处理功能的数据处理人士使用；也可以作为大专院校的教材使用；还可以作为Excel培训教师的参考用书。

本书以介绍数据处理思维为主旨，试图帮助读者厘清数据处理的基本方法和思路，不依赖于特定的Excel版本，尝试让读者掌握Excel数据处理思维和方法。

前言

Excel在工作中扮演着越来越重要的角色，它是我们工作中的好帮手，如果使用得当，会让我们的工作事半功倍，充满欢乐；如果使用不当，则会让我们的工作事倍功半，充满烦恼。所以，用好Excel是一件非常重要的事情。

认识两个Excel学习误区

? 误区1：Excel只要多用，就可能成为专家

怎样更好地使用Excel，是被很多人关注和思考的问题。有一个“一万小时”理论，认为我们在一个领域内投入一万个小时，就会成为这个领域的专家。很多人对“一万小时”理论存在很深的误解，认为只要在某个领域投入一万个小时的工作时间，就一定会成为这个领域的专家。所以，很多人对用好Excel的理解就是“多用”，用多了自然就有感觉了，就成专家了。很多专家也告诉我们，学好Excel的真谛就是“我亦无他，唯手熟尔”。多用，对于理解和掌握Excel确实是有好处的。但是，如果仅仅停留在“多用”上，即使时间达到了一万个小时，也不能够成为Excel专家，甚至不能熟练掌握Excel。就像我做了那么多年的“吃货”，也没能成为美食家一样。

同样一个问题，解决方案是多种多样的，但一定有最优方案。例如，我们去国外旅游、去单位上班、去楼下超市购物，比较合理的出行方案是，坐飞机去国外旅游、开车去单位上班、走路去楼下超市购物。换一种方式能不能实现呢？当然是可以的，比如我走路去国外也能走到，但很费劲。在Excel中同样存在这样的问题，虽然解决方案是多种多样的，但不同的解决方案是有效率差异的。我们要通过不断学习提高使用Excel的效率，更高效地处理数据、解决问题。这其中必然伴随着不断思考的过程，不是简单地使用Excel，而是要不断地去研究它。因此，仅仅“多用”是不够的，还必须要不断地研究数据处理背后的逻辑，要有理性的思考，去钻研数据处理背后的经验和规律，研究数据处理的基本方法。这一切都必须经过刻意的训练才能完成，光是“多用”是没有用的。

? 误区2：Excel没有必要刻意学习，遇到问题网上搜索即可

学习Excel的另一个误区是头痛医头、脚痛医脚。曾经有朋友跟我讲，现在搜索引擎这么方便，根本不需要在学习Excel上花时间，因为凡是我们遇到的问题，一定有人已经遇到过并在网络上提问了，并且已经有人给出了完美的解决方案。与其花时间去研究Excel，学一些可能还用不上的技巧，倒不如在出现问题时，直接去网上搜索解决方案更方便。善于使用搜索引擎解决问题，这种想法是正确的。但是，因为搜索引擎能帮我们解决问题，所以不需要学习Excel，这在逻辑上是有漏洞的。如果大家都抱着这种想法，那么网络上关于Excel的问题谁来回答呢？其实，等出了问题再去找解决方案，是非常低效的一种工作方式，高效的工作方式是未雨绸缪。通过搜索引擎找到某个问题的答案后，问题解决了，茅塞顿开，好像这种方式不错。但是，这种方式是不系统、不连贯的，再遇到类似的问题可能还是无法解决，还需要再次借助搜索引擎查找答案，这样无疑是十分低效的。学好Excel的基本技能就和会背诵九九乘法表一样，是数据处理的基础技能，只有熟练并系统地掌握了基本的数据处理方法和规律，才能更好地处理数据，才能使用最高效的方式解决问题，避免一有问题就要借助搜索引擎四处寻找答案。要全面、系统地学习Excel的目的不是为了掌握某一两个知识点，或者解决某一两个问题，而是希望通过学习这些问题，探究数据处理背后的思路和方法，能够用数据处理思维高效地去解决遇到的新问题。

强调几个学习Excel数据处理的思路

? 刻意训练

经常会有人告诉我们Excel是一门艺术，这可能会让我们产生误解，认为我们无缘成为艺术家，也就永远无法掌握好Excel。如果我们把艺术定义为创造性的、无法重复的工作，那么无疑我们很难成为艺术家，毕竟成为这样的艺术家是要靠天分的。而实际上，Excel中的数据处理虽然具有创造性，但是这些工作都是可以经过刻意训练完成的，都是可重复（无差异再造）的。从这个角度来讲，Excel更像匠术。匠术不同于艺术，通过刻意训练，就可以成为工匠之师。所以，学好Excel的关键是要经过刻意训练。本书尽量避免使用枯燥的理论介绍，而是通过大量的案例达到刻意训练的目的，帮助大家提高Excel技术水平。

? 超越版本的数据处理逻辑

在培训过程中，经常会有受训单位的HR跟我反复确认培训时使用的Excel版本。因为他们单位使用的Excel是某某版本的，就希望我上课时也使用某某版本的Excel。这时，我会告诉他们，有这种心态，是学不好Excel的。我们学习Excel的目的并不是为了知道每个按钮到底在什么位置、操作步骤是怎样的，而是要了解Excel数据处理背后的思维逻辑。就像我们去学开车，教练用的是帕萨特汽车，但这并不会影响将来我们到底是要买宾利汽车还是要买夏利汽车。我们学的是驾驶的基本方法和技巧，要掌握的是驾驶规律，而不是去学习雨刷的位置在哪里、如何控制雨刷的速度。所以，学习Excel数据处理，要理解的是超越版本的数据处理逻辑，而不是掌握按钮和菜单的位置。本书选用Excel 2016版本示范操作，但本书内容并不是针对Excel 2016来介绍每个操作按钮的位置和操作步骤，而是针对数据处理背后的规律和逻辑进行介绍。所以，在学习本书时不用担心所使用的Excel版本问题。

当然，任何软件的版本都在不断地更新换代。虽然人们对新版本的更新总是充满期待而又默默失望，但不可否认的是，软件版本的每次更新都会让功能变得更强大，使用变得更方便。有些问题在低级版本中可能要弄上几个小时才能解决，而在高级版本中单击几下鼠标就解决了。所以，如果有条件，一定要尽快升级软件版本。

? 关注思考过程和问题分析

很多人在学习Excel时，会按照课本上给出的步骤按部就班地操作，在得到结果后就心满意足地认为已经掌握了数据处理的方法。但当遇到类似问题时，往往还是手足无措。这是因为单纯按照操作步骤操作一遍，只是掌握了操作方法，对数据处理的逻辑并不清楚。所以，在遇到类似问题时还是无法解决。正确的学习方法是，不仅要关注操作的具体步骤，而且要更多地将注意力放在处理逻辑上。本书中，在解决具体问题时，会对知识点进行归纳和总结；注重解决问题的思考过程，对思考过程进行了细致的介绍，而不是简单地给出操作步骤和结果。解决相对比较复杂的问题时，都是先分析问题涉及的知识点，在此基础上进一步分析问题的解决思路，并采用试错的方式，先用可能想到的常规解决问题的方式与思路来解决问题，并对出现的错误进行分析，在此基础上再给出正确的解决方案。尽量避免直接给出问题的解决方案与答案，更注重让读者参与问题的分析与思考过程。

? 合理借助辅助列（行）解决复杂问题

辅助列（行）类似于几何中使用的辅助线，合理利用它能够让复杂问题简单化。在解决复杂问题时，通常会将复杂问题分解为若干个方便处理的子问题，再利用辅助列（行）处理分解后的子问题。通过辅助列（行）能够清晰地看到问题处理的过程和逻辑，更方便理解问题的解决思路。本书原计划用单独一章来介绍辅助列（行），但由于书中大量使用了辅助列来协助解决问题，基本每章都有所涉及，所以并没有将辅助列（行）专门列为一章，还请大家通过各章的案例仔细品味其功能与使用方法。

? 从多个角度去分析和理解问题

书中部分案例给出了多种解决方案。可能大家会感到疑惑，这是在教我们茴香豆的“茴”字有几种写法吗？我明明只需要知道一种就可以了呀！为什么还要讲那么多种不同的方式呢？就像我们学习任何一门课程一样，看一本书往往很难把知识点理解得深入透彻，如果把介绍该课程的几本书一起看，那么很快就能将该课程融会贯通。这是因为不同的作者在介绍知识时的角度不一样，多看几本书，我们就能从多个角度看到问题的全貌，加深对问题的理解和掌握。在本书中，部分案例尝试了采用多个角度去解决问题的方式，目的在于让大家能够从不同的角度更清晰地去了解问题的逻辑和解决思路。

? 尝试使用不同的数据源做练习

很多教程的例题都采用学生成绩表等常见的数据列表作为数据处理的范例，这样的数据源对于理解问题非常有帮助。但是，由于我们太熟悉这类表格了，从而导致在学习时使用这类模拟表格处理数据非常得心应手，而一旦换了数据，面对同样的问题，则会显得手足无措。另外，如果例题本身就是一个简单的例子，那么我们学习完了可能根本不知道这个知识点能用在什么地方，从而导致我们在学习完一个知识点后经常会感到疑惑，这个功能在实际处理数据时到底有什么用？就是用来处理成绩的吗？为了帮助大家更好地理解数据处理的功能及作用，本书中对使用的数据列表进行了精心设计，尽量避免使用学生成绩表等常见数据作为数据源。本书模拟生成一些表格，让数据处理更具有实用性。在培训过程中，学员经常会让我帮忙解决所面临的实际问题，本书中有很大一部分数据来源于此。当然，这部分数据都经过了处理，不会暴露相关隐私。在数据处理过程中，我们所面对的数据经常是海量的，其实海量数据与少量数据的处理方式和解决思路是一致的，所以为了说明上的方便，本书中仅仅使用了10条左右的数据作为数据源列表。

读者定位及阅读方式

本书的读者定位是希望提高Excel数据处理技能的数据处理者，但本书并不是一本介绍菜单和命令按钮位置及操作步骤的操作手册。

本书面向零基础读者，阅读本书不需要任何Excel基础。书中各章节之间相互独立，读者在阅读时可以根据需要选取感兴趣的章节阅读。其中每章都会将涉及的背景知识进行简单介绍，力求让读者在零基础的情况下能够单独学习每一章。为保持各章的独立性，部分知识点的介绍可能会在不同的章节间有所重复，对于各章重复出现的知识点，本书尝试从不同的角度去说明，希望这种处理方式能对大家理解知识点有所帮助。

资源包

为了方便大家学习，本书提供了配套的资源包，资源包中包含本书使用的案例等相关资料。资源包按照章节序号排列，例如序号为“12.2.3”的文件表示第12章12.2.3节的资源文件，该文件内包含12.2.3节内的所有案例。

目录
1 引用 1
1．1 引用分类 1
1．2 相对引用案例 5
1．2．1 学生成绩处理 5
1．2．2 产品销售统计 7
1．3 绝对引用案例 8
1．4 混合引用案例 9
1．5 批量填充的引用问题 11

2 多条数据的处理 13
2．1 多条数据显示的烦恼 13
2．2 显示一条数据 15
2．3 提取指定的若干条记录 18
2．4 交互式图表 19
2．5 迷你组图 25
2．6 通过中间层构造迷你组图 27

3 日期与时间处理 30
3．1 日期的本质 31
3．2 基本处理 33
3．2．1 调整格式 33
3．2．2 文本型日期数据的处理 35
3．3 基本函数 37
3．3．1 TEXT函数 38
3．3．2 DATE函数 40
3．4 绘图相关问题 41
3．5 计算问题 43
3．5．1 基本计算 44
3．5．2 计算特定时间单位间隔 44
3．5．3 跨天时间间隔计算 45
3．5．4 DATEDIF函数 46
3．5．5 计算入职时间 48
3．5．6 时间长度问题 49
3．5．7 计算数字时间差 49
3．6 应用实例 50
3．6．1 包含日期格式的连接 50
3．6．2 文本型转换为日期型 51
3．6．3 提取日期和时间 52
3．6．4 重设日期系统 53

4 身份证信息处理 54
4．1 身份证号码的含义 54
4．2 数据获取 55
4．2．1 直接输入 55
4．2．2 从文本文件获取 56
4．3 提取出生日期 59
4．3．1 函数处理法 59
4．3．2 分列法 61
4．3．3 快速填充法 63
4．4 计算 64
4．4．1 计算年龄 64
4．4．2 判断性别 66
4．4．3 计算退休时间 68

5 重复项处理 72
5．1 标注重复项 72
5．1．1 使用条件格式标注重复项 72
5．1．2 使用公式标注重复项 74
5．1．3 使用高级筛选查看重复项 75
5．2 删除重复项 77
5．2．1 使用菜单删除重复项 77
5．2．2 使用高级筛选删除重复项 79
5．2．3 数据透视表 80
5．2．4 借助MATCH函数删除重复项 84
5．2．5 借助COUNTIF函数删除重复项 87
5．3 计数 89
5．3．1 数据透视表计数 89
5．3．2 使用COUNTIF函数计数 90
5．3．3 标记重复项序号 91
5．3．4 分类汇总统计计数 92
5．4 汇总重复项 95
5．4．1 合并计算 95
5．4．2 应用数据透视表进行汇总 96

6 排序 99
6．1 排序基础 99
6．1．1 基础排序操作 99
6．1．2 按照笔画或字母排序 101
6．1．3 多条件排序 102
6．1．4 按照颜色排序 104
6．1．5 自定义排序 105
6．2 排序的应用 107
6．2．1 标记特定项目 107
6．2．2 使用公式辅助标记特定项目 108
6．2．3 重复行的处理 110
6．2．4 空行的删除 112
6．2．5 按照字符长短排序 115
6．2．6 文本型数字排序 116
6．2．7 以组为单位排序 119
6．2．8 标记组内顺序 121
6．2．9 组内排序 122
6．3 按行排序 126
6．3．1 基本排序 126
6．3．2 标记特定项目 128
6．3．3 使用辅助行设置间隔列格式 128
6．3．4 使用公式辅助标记特定项目 130
6．4 排位函数 132
6．4．1 RANK类函数 133
6．4．2 百分比排位函数PERCENTRANK 137
6．4．3 函数组合 139
6．4．4 中国式排名 140
6．5 需要注意的问题 142
6．5．1 注意公式的问题 143
6．5．2 注意行号的问题 144

7 文本型数据处理 146
7．1 文本型数据与数值型数据 146
7．1．1 文本型数据的输入 147
7．1．2 文本型数据转换为数值型数据 148
7．1．3 数值型数据转换为文本型数据 153
7．1．4 文本型数据的查询问题 154
7．1．5 特殊文本型数据 155
7．2 提取子串 157
7．3 字符与字节 159
7．3．1 含义 159
7．3．2 应用 160
7．4 查找字符 163
7．4．1 FIND函数 164
7．4．2 SEARCH函数 166
7．5 快速填充 168
7．5．1 特殊符号作为分隔符 169
7．5．2 将不同类型的数据分列显示 170
7．5．3 提取子串 171
7．5．4 子串处理 171
7．5．5 批量添加 172
7．5．6 合并不同字符串内的子串 172
7．5．7 开启自动填充功能 173
7．6 替换字符 174
7．6．1 SUBSTITUTE函数 174
7．6．2 REPLACE函数 176
7．6．3 字节函数 179
7．7 使用TEXT函数格式化文本 181
7．7．1 控制显示位数 181
7．7．2 计算时间数据 182
7．7．3 控制时间格式 184
7．7．4 默认条件处理 185
7．7．5 自定义条件 186

8 合并单元格 188
8．1 批量拆分合并单元格 188
8．2 合并单元格的名称及使用 191
8．3 合并同类项目单元格 193
8．4 合并单元格排序 204
8．4．1 取消合并单元格后排序 204
8．4．2 合并单元格结构不变排序 206

9 随机数的产生及其应用 210
9．1 RAND函数 210
9．1．1 RAND函数的基本使用 210
9．1．2 使用RAND函数实现随机排序 211
9．1．3 使用RAND函数确定随机排位 213
9．1．4 随机分组 214
9．2 RANDBETWEEN函数 216
9．2．1 使用RANDBETWEEN函数生成随机日期 216
9．2．2 使用RANDBETWEEN函数生成随机时间 218
9．2．3 RANDBETWEEN函数与CHOOSE函数结合使用 219
9．2．4 RANDBETWEEN函数与INDEX函数结合使用 222
9．2．5 随机生成姓名 223
9．3 随机生成练习数据 231

10 筛选 233
10．1 筛选功能 233
10．1．1 筛选基础 234
10．1．2 按照颜色或图标筛选 237
10．1．3 使用通配符筛选 239
10．2 高级功能 243
10．2．1 高级筛选基础 243
10．2．2 同一列的“或”关系 247
10．2．3 同时使用“与”“或”关系 249
10．2．4 使用公式进行筛选 250

11 数组公式 255
11．1 数组公式基础 255
11．1．1 计算结果为单一值 255
11．1．2 计算结果为数组 257
11．2 数组公式的运算 258
11．2．1 数组与单个数值运算 259
11．2．2 同方向的一维数组之间的运算 261
11．2．3 不同方向的一维数组之间的运算 262
11．2．4 一维数组与二维数组间的运算 263
11．2．5 二维数组之间的运算 266
11．3 应用案例 268
11．3．1 计算指定条件的和 268
11．3．2 求3个最大值 273
11．3．3 随机安排面试 275

12 名称 279
12．1 基本概念 279
12．1．1 命名单个单元格 280
12．1．2 命名区域单元格 281
12．2 自动命名 283
12．3 命名公式 285
12．3．1 公式命名基础 285
12．3．2 公式中引用名称 286
12．4 应用案例 288
12．4．1 联想功能 288
12．4．2 交互图表 291
12．4．3 动态汇总 301

13 图表 309
13．1 图表基础知识 309
13．1．1 选择图表类型 309
13．1．2 科学绘图 310
13．2 简化图像 312
13．3 图表制作的基本技术 314
13．3．1 行和列的关系 314
13．3．2 手动创建图表 316
13．3．3 图形组合 321
13．3．4 双坐标轴 322
13．4 横坐标处理 324
13．4．1 处理空缺数据 324
13．4．2 时间刻度坐标轴 325
13．5 时间序列展示 327
13．6 比例数据展示 328
13．6．1 图表类型 328
13．6．2 复合饼图 329
13．6．3 父子饼图 330
13．6．4 最值处理 335
13．6．5 瀑布图 338
13．7 关系数据展示 340
13．7．1 气泡图 340
13．7．2 对称图表 341
13．8 差异数据绘图 346
13．8．1 特殊标记的折线图 347
13．8．2 热点图 349
13．8．3 小而多的迷你组图 350

14 数据透视表 352
14．1 数据透视表基础知识 352
14．1．1 改变数据透视表的结构 353
14．1．2 切断与数据源的联系 355
14．1．3 拆分筛选页 356
14．2 显示方式 358
14．2．1 日期分组显示 358
14．2．2 数值分组显示 359
14．2．3 文本分组显示 360
14．2．4 值汇总依据 361
14．2．5 值显示方式 362
14．2．6 同比和环比 363
14．3 筛选器 365
14．3．1 切片器 366
14．3．2 日程表 367
14．3．3 多表间数据联动 368
14．4 计算字段和计算项 370
14．4．1 含义与区别 370
14．4．2 计算字段 375
14．4．3 计算项 376
参考文献 379