白话聊Excel函数应用100例

翟振福，某世界500强公司数据分析师，2014-2019年度微软有价值专家（MVP），擅长于在工作中建立Excel模板及工具，充分利用函数提高工作效率及准确性。ExcelHome论坛版主，ExcelHome云课堂《白话Excel函数100例》讲师，《Excel 2013函数与公式应用大全》《Excel 2016函数与公式应用大全》《Excel 2016应用大全》作者之一。
周庆麟，网名Kevin，新浪微博@周庆麟，中国资深的微软全球有价值专家（MVP）之一，ExcelHome创始人、站长，曾参与策划和编写了几十本畅销Office技术图书。同时也是一名优秀的技术顾问和培训讲师，有丰富的授课经验。

1. 千锤百炼：案例精选自ExcelHome热门课程的实战案例，源自4年的实战教学和2年的反复打磨 。
2. 触类旁通：精选70余个常用函数，以深入浅出的代码讲解剖析，从单个函数用法到组合用法，使“浅者不觉深，深者不觉浅”。
3. 首屈一指：由微软全球有价值专家潜心打造，多年职场经验和教学经验无保留分享。
4. 融会贯通：轻松诙谐的教学方式和层层递进的讲解让读者看得懂、学得会、用得巧！

考查一个人Excel的水平，其实重点就是考查Excel函数与公式的运用能力。
为什么这么说呢？
因为函数与公式是Excel数据计算与分析的核心功能。与包括数据透视表在内的其他Excel功能相比，几百个函数的任意组合，可以面向各种数据形式，在指定的任意单元格（区域）里面返回任意指标的计算。这个特点，决定了函数与公式既可以计算非常简单的指标，比如区域求和；也可以计算非常复杂的指标，比如按条件多表查询汇总；还适用于制作任意布局的报表模板——一劳永逸地解决周期性的报表制作问题。
我在给企业员工上Excel课的时候，哪怕只有半天的课程，通常也会被要求讲一些常用的Excel函数应用，大家对于Excel函数的重要性认识还是很深刻的。
既然如此，为什么很多人（其实我想说的是大多数人）的Excel函数水平总是难以提高呢？是因为Excel函数很难学吗？
是的，很难。
Excel Home技术论坛有近1 000万个帖子，图1展示了不同版块的帖子数量。实际上，基础应用版块和其他各版块里面也有大量关于函数应用的帖子，所以关于函数应用的帖子的占比肯定超过了36%从这组数据里，我们可以看出，大部分上网寻求解决方案的人问的都是哪些问题。如果函数不难，就不会有这么多问题了。

另外，我在多年的授课过程中，发现一个比较有趣的现象。当我教授一些短、平、快的操作技巧时，学员们通常热情高涨，眉开眼笑，因为他们很快就能弄明白、学会了。而当我开始函数与公式方面的讲解时，尽管我已经使出浑身解数，但随着案例的深入，学员们的面色仍免不了越来越凝重，还会经常要求我就某个知识点再讲一次，甚至最后干脆把答案拍照保存了事。
这么难学，又必须学，怎么办呢？其实，只要有正确的方法，完全可以降低学习难度。
回想当初我自己学习Excel函数时，进度也是异常缓慢。我现在还清楚地记得， 1998年的春节前夕，我需要在一张报表中完成一组按条件统计的计算，SUM函数的功能在当时明显不够用。我翻了一遍手头唯一的一本Excel教材，这是一本外国作者写的“傻瓜书”，里面只讲了最简单的几个函数。那时候市面上讲Excel的图书很少，上网也非常不方便；就算能上网，也只能用雅虎搜索数量非常有限的网页，百度、谷歌还没问世。那时，身边也没有可以请教的同事。当时虽然不知道怎么完成这个计算，但我坚信用Excel肯定可以。于是我就细细地翻看Excel的帮助文件，终于发现了两个比SUM更高级的求和函数——SUMIF函数和DSUM函数。我把这两个函数的帮助文件打印出来，反复揣摩，并在计算机上对照示例做数据实验。讲到这里，不得不“吐槽”一下Excel帮助文件，里面写的内容真的让人难以理解，特别是对于Excel “小白”来说，只是把示例文件实现出来都会费很大的劲。
在接下来的两天里，我一有时间就在脑海中琢磨。还记得那天天气非常冷，地面还有积雪，我因为想得过于投入，走在路上时还差点滑倒了。听着脚踩在积雪上发出的“咯吱咯吱”的声音，我突然一下子就想明白了。回到办公室，赶紧在计算机上试了一下，果然解决了我的问题，当时心里别提有多高兴了。我有了这次经历，之后再自学其他新函数就顺利多了，对Excel帮助文件的理解能力也提高了很多。现在回想起来，如此简单的函数，我都花费了那么长时间，主要原因是学习资料匮乏，也缺乏可以交流的人。
那么，如今Excel的学习资料随手可取，网上随时可搜，为什么学习起来还这么难呢？我觉得主要是以下两个方面的原因。
1. 学习资料过于泛滥、易得，造成我们的思考力下降。我们对外界的“帮助”过于依赖，计算机里虽然下载了一堆学习资料，却不安排时间进行学习，或者学习的时候总想找捷径，避重就轻，除非到了火烧眉毛的时刻，否则能拖就拖。
2. 资料虽多但质量参差不齐，自学能力较弱的人缺乏高手引导。比如很多人习惯事事用百度，但是没有意识到百度得来的一些碎片化信息中很多都是错的，或者即使是正确的也可能比较片面，对于系统化地建立数据思维是非常有害的。更糟糕的是搜来一个可用的答案，用的时候完全不走心，用过就会忘，下次遇到同样的问题时还要继续搜，永远学不会。真正的高手在学习任何技术的时候，都不会只停留在功能表面，而是独立思考，精确提问，探究背后的逻辑和思路。有了系统化的思维框架，以后的学习就能事半功倍。
Excel Home技术论坛创办至今，有无数的“小白”通过泡论坛、爬帖子，从提问者最终成长为助人为乐的高手。本书的作者小翟老师就是一位“土生土长”的“EH”人，他通过自身的努力，不但成为论坛上的高手，还多次当选微软全球最有价值专家，并且在Excel Home云课堂上开设了自己的Excel函数课程。小翟老师被学员们评价为Excel界最会讲相声的人，因为他不仅在授课过程中常常妙语连珠，他本人长得也非常像著名相声演员冯巩，不信的话您看看他的照片（封面勒口处）。
小翟老师课程的最大特点是以教授Excel函数的运用思路为核心：无论多复杂的任务，他都会教读者剖析背后隐藏的逻辑，然后用最简单的方法搞定；无论多复杂的公式，他都有办法掰开了、揉碎了让读者明白。
比如，课程中有这样一个例子。图2是一张销量记录表，销售人员的奖金是与销量挂钩的，为了避免某位员工由于销量过多而导致超出奖金系数上限，或者销量惨淡没有奖金，规定销量超过120的以120计算，销量低于80的以80计算。


图2 销量记录表
这种情况下，稍微有点儿函数基础的朋友可能第一反应就是用逻辑判断函数IF，而且得用两个才行。但是，小翟老师经过一番剖析，告诉你：不用IF函数，用MIN函数和MAX函数就行了。这个问题表面上是一个条件判断问题，但实际上是一个极值问题，极值问题用MIN函数和MAX函数更简单！所以，I5单元格的公式可以写为：
=MAX(MIN(H5,120),80)
这比用IF函数要简单得多：
=IF(H5>=120,120,IF(H5课程中还有很多类似的例子，这些案例的解题思路是非常有价值的，理解了之后，可以为己所用，一通百通，用少量的函数解决更多的问题。
小翟老师的课程在4年时间里更新了多次，本书在最新的课程内容基础上又反复打磨了两年多。本书的内容是视频课程中的精华部分（我和小翟老师在策划本书之初就定下了图书要100%继承视频课程精华的目标）。视频课程是以视频的形式展现，有些东西更容易表达，有些话也可以讲得更通俗。图书是以图文的形式展现，篇幅有限，但拿在手里能反复观摩、感悟，利于学习。图书和视频教程如果能结合使用，学习效果会事半功倍。
本书经过一遍又一遍地修改和完善终于能呈现给读者了。交稿前一统计，还好，修改了不到1万次（尴尬笑脸.jpg）。
好了，优秀的学习资料有了，领路高手也就位了，接下来就看您的了！
Excel Home创始人、站长 周庆麟

第1篇　基础函数及统计 001
第1章 相对引用与绝对引用 002
第2章 基础统计函数 006
第3章 不同的求和方式 017
第4章 生成有规律的序列数 031
第2篇 文本函数 037
第5章 提取字符函数 038
第6章 替换和查找函数 044
第7章 与字符串长度有关的函数应用 055
第8章 其他常用文本函数 062
第9章 文本函数综合实战 070
第3篇 日期和时间函数 075
第10章 日期和时间本质 076
第11章 日期函数 078
第12章 时间函数 086
第13章 星期及月份函数 091
第14章 DATEDIF函数 096
第4篇 数字处理函数 101
第15章 舍入函数 102
第16章 取整与取余函数 109
第5篇 IF函数 115
第17章 IF函数基础应用 116
第18章 IF函数嵌套与并列 122
第6篇 条件统计函数 131
第19章 COUNTIFS基础应用 132
第20章 COUNTIFS拓展用法 140
第21章 SUMIFS与AVERAGEIFS函数 148
第22章 工作日统计 153
第7篇 SUMPRODUCT函数 159
第23章 SUMPRODUCT函数基础知识 160
第24章 SUMPRODUCT多条件统计 164
第8篇 INDEX加MATCH函数 175
第25章 INDEX函数 176
第26章 MATCH函数精确匹配 184
第27章 INDEX加MATCH扩展应用 191
第9篇 MATCH模糊匹配及LOOKUP函数 199
第28章 MATCH模糊匹配原理 200
第29章 LOOKUP实战：提取数据 209
第10篇 VLOOKUP函数 215
第30章 VLOOKUP函数的语法及应用 216
第11篇 OFFSET与INDIRECT函数 227
第31章 OFFSET函数 228
第32章 公式实用小技巧 235
第33章 INDIRECT函数 239
第12篇 VLOOKUP与模块化结构 247
第34章 透视表基础知识 248
第35章 5分钟完成月报统计 255
第13篇 常用函数组合及数组公式实战应用 271
第36章 一对多查询返回满足条件的全部数据 272
第37章 数组法提取全部数据 280
第38章 其他数组公式应用 285
第14篇 自定义函数 289
第39章 连接函数Context 290
第40章 取值函数GetChar与去重复函数NotRepeat 297
第41章 制作加载宏 304
第15篇 基础知识 309
第42章 知识储备 310
附录 318