前言 Excel数据处理和数据分析的核心工具之一，是函数公式。 说起函数公式，很多人觉得用起来太难了，嵌套太难了，单个函数会用，复杂点的表格就不会用了。 之所以觉得函数公式很难，是因为很多人只是机械套用，没有去理解函数公式中内在的逻辑思维和解决问题的逻辑思路。 在使用函数创建计算公式之前，首先要从函数的逻辑原理去了解及认识函数。 任何一个函数，都有自己内在的逻辑，其背后的源代码也是相应的逻辑计算。因此，要学好、用好函数，首先要从函数的逻辑原理上去认识函数、了解函数、运用函数。 例如，IF是什么？用一句通俗的白话来说就是：如果怎么怎么，那么就怎么怎么，否则就怎么怎么。如果你好好学习，就能快速进步，否则就蜗牛踏步。 说得简单点，IF函数就是指定一个条件，判断数据是否满足这个条件，如果满足了这个条件，就给一个结果A，如果不满足这个条件，就给另一个结果B。 SUM函数是求和，是无条件求和，不管是什么数字，全部加总起来。但是，如何给它介绍一个对象IF呢？SUM+IF=SUMIF，就构成了单条件求和，也就是，只有这个条件满足了才去求和，否则一边待着去。 如果给SUM多介绍几个对象IFS呢？SUM+IFS=SUMIFS，就构成了多条件求和，也就是必须同时满足这几个条件才求和，只要一个条件不满足，就不去理会它。 但是，SUMIF函数也好，SUMIFS函数也好，能不能用数组判断，用数组求和呢？肯定不行的啊，因为在函数的条件判断参数的名称与实际求和参数的名称里，都有一个关键词Range，那么Range是什么意思呢？是单元格区域的意思。例如，我们可以使用这样的公式“=SUMIF(A2:A6,"彩电",B2:B6)”，但不能做出这样的公式“=SUMIF(LEFT(A2:A6,2),"彩电",B2:B6)”，因为这个公式里，第1个参数不是引用的单元格区域，而是一个用LEFT函数得到的数组“LEFT(A2:A6,2)”。 如果给SUM介绍一个对象PRODUCT呢？什么是PRODUCT？就是乘积啊，就是几个数组的乘积，因此，SUM+PRODUCT=SUMPRODUCT，就是把几组数的乘积加起来的意思。仔细看看这个函数的参数，不叫Range，而是叫Array，Array就是数组，数组可以是工作表上的实际区域，也可以是自己设计的数组，因此，SUMPRODUCT更加通用。 再比如，VLOOKUP函数的 V 代表什么？HLOOKUP函数的 H 代表什么？它们各自适用于什么样的表格？这点不清楚，就会天下大乱了。还有，VLOOKUP函数也 好，HLOOKUP函数也好，LOOKUP函数也好，从名字上看，就是“找”的意思。那 么，根据什么条件找，从哪里找？怎么找？这不就是它们的基本逻辑吗？再仔细看看它们的参数名称，不是Range，而是Array。这就有点意思了，可以从实际数据区域里找，也可以构建数组，从数组里找，例如，我们可以使用LOOKUP函数查找某列最后一行的数据，查找最右边一列的数据，这是很简单的，你只要构建一个条件Array就可以了。 例如，直接使用鼠标来引用单元格的公式“=单元格”，这叫作直接引用。那么，能不能间接引用单元格呢？首先要弄明白，什么叫间接引用？肯定是通过一个中介来引用啊！那么，中介在哪里？怎么寻找中介？这就是间接引用，使用INDIRECT函数，想办法成立一个中介，让中介去帮我们干活。 再比如，MATCH是干什么的？单词的直译就是匹配，那么，匹配什么？为什么要匹配？其实，称呼“定位”更恰当一些，把指定的数据，在指定的数组中，定位出它藏在哪里，也就是位置。再仔细看，这个函数的第二个参数是不是Array？既可以是工作表的某列或某行，也可以是一个数组。 很多函数，单纯从名字上就可以了解一个大概，再仔细观察每个参数的名称及定义，可以说就不会有学不会函数基本用法的。这里只能先说到是基本用法，而不是灵活应用。 在实际数据处理和数据分析中，我们必须结合具体表格来做公式，才有意义。如果要彻底学会函数，能够灵活运用函数设计公式，就肯定是离不开具体表格了，因为公式只有存在于表格中才能存活，才有生命力。 不同的应用场景、不同结构的表格、不同的思路、不同人的喜好，使用的函数是不一样的，因此做出的公式也是五花八门的。例如，对于图1所示的表格，至少有以下几种解决公式，所使用的函数各不相同： 图1 查找指定地区指定产品的数据 公式1，从左往右查找，使用VLOOKUP函数（使用MATCH函数列方向定位）： =VLOOKUP(J3,B4:F9,MATCH(J4,B3:F3,0),0) 公式2，从上往下查找，使用HLOOKUP函数（使用MATCH函数行方向定位）： =HLOOKUP(J4,C3:F9,MATCH(J3,B3:B9,0),0) 公式3：通过两个坐标查找，联合使用INDEX函数和MATCH函数： =INDEX(C4:F9,MATCH(J3,B4:B9,0),MATCH(J4,C3:F3,0)) 公式4，通过偏移行和偏移列查找，联合使用OFFSET函数和MATCH函数： =OFFSET(B3,MATCH(J3,B4:B9,0),MATCH(J4,C3:F3,0)) 公式5，通过间接查找，联合使用INDIRECT函数和MATCH函数： =INDIRECT("R"&MATCH(J3,B:B,0)&"C"&MATCH(J4,3:3,0),0) 公式6，通过求和函数SUM查找，条件满足就是自己本身，构建数组公式： =SUM((B4:B9=J3)*(C3:F3=J4)*C4:F9) 公式7，通过求和函数SUMPRODUCT查找，条件满足就是自己本身，构建普通公式： =SUMPRODUCT((B4:B9=J3)*(C3:F3=J4)*C4:F9) 公式8，通过左右偏移求和查找，联合使用SUMIF函数、OFFSET函数和MATCH函数： =SUMIF(B4:B9,J3,OFFSET(B4,,MATCH(J4,C3:F3,0),6,1)) 公式9，上下偏移求和查找，联合使用SUMIF函数、OFFSET函数和MATCH函数： =SUMIF(C3:F3,J4,OFFSET(C3,MATCH(J3,B4:B9,0),,1,4)) 公式10：先从列上匹配地区，得到指定地区下的各个产品的数据，再从行上匹配产品，获取指定产品数据，使用嵌套XLOOKUP函数构建公式： =XLOOKUP(J5,C3:F3,XLOOKUP(J4,B4:B9,C4:F9)) 公式11：先从行上匹配产品，得到指定产品下的各个地区的数据，再从列上匹配地区，获取指定地区数据，使用嵌套XLOOKUP函数构建公式： =XLOOKUP(J4,B4:B9,XLOOKUP(J5,C3:F3,C4:F9)) 可见，即使是同一个表格，由于思路的不同，解决问题的切入点不同，使用的函数是不一样的，因此做出的公式也是不一样的，公式有简单，也有复杂，有高效，也有低效。 下面是另外一个很简单的例子，一个学生问了这样一个问题：如何从B列中，将费用名称和项目名称提取出来，分两列保存？如图2所示。 图2 要求从B列提取费用名称和项目名称 我们的任务是：从B列中，将费用名称和项目名称分别提取出来，那么就要分析B列中费用名称和项目名称的特征是什么了。 仔细观察B列数据特征，凡是带着“费”字的，就是费用名称，否则就是项目名称。 那么，如何判断某个单元格有“费”字呢？我们知道有一个函数就可以解决这样的问题：FIND函数。 FIND函数就是从一个字符串中，查找指定字符出现的位置，如果有指定的字符，函数的结果就是一个表示出现位置的序号。例如，下面公式的结果就是3，因为在字符串“保险费用”中，字符“费”出现在第3个位置上： =FIND("费","保险费用") 这样，不管指定字符出现在什么位置，只要存在，结果就是一个数字，那么就可以使用ISNUMBER函数来判断FIND函数结果是否为数字，如果是数字，就表示是费用名称。 提取费用名称还有一个问题，如果B列含有“费”字，就是费用名称，那么不含有“费”字呢？如何在该行单元格输入费用名称？我们已经在上一行单元格判断并提取出了费用名称，那么下一行单元格填充为上一行单元格已经提取出的费用名称，就可以了。 因此，单元格F2提取费用名称的公式就可以做出来了，如下所示： =IF(ISNUMBER(FIND("费",B2)),B2,F1) 提取项目名称的公式，是判断B列单元格是否没有“费”字。那么，何谓“没有”？使用FIND函数查找指定字符，如果存在就是一个数字，如果不存在，就是一个错误值，所以只要判断是不是错误值就可以了，此时，使用ISERROR函数来判断FIND函数的结果是否为错误值。因此，单元格G2提取项目名称的公式如下： =IF(ISERROR(FIND("费",B2)),B2,"") 这两列的公式都并不复杂，逻辑也是很简单的，就是考察你是不是彻底了解表格结构、数据特征，找出了解决问题的逻辑思路。 我不止一次给学生们说过，学习Excel函数公式的核心是学习逻辑思路，而不是机械套用，正如上面介绍的几个公式，就是不同逻辑思路的体现。你能看出这些公式的逻辑思路有什么不同吗？ 逻辑思路，永远是函数公式的核心。 本书不仅介绍了常规Excel版本所共有的函数，也详细介绍了Excel 365及Excel 2019以后版本所特有的功能更加强大的函数，使你能掌握更多的实用工具。更重要的是，训练自己的数据处理和数据分析的逻辑思维，提升解决问题的能力。 学习需要持之以恒，也需要不断总结和提升。 祝愿各位使用Excel的朋友，工作愉快，学习快乐，每天都有进步。 扫描下方二维码，获取案例素材及原图 Excel函数公式综合应用实践案例视频精讲 Excel函数公式综合应用实践案例视频精讲

• 暂无课件
• 样章下载
• 暂无网络资源

扫描二维码
下载APP了解更多

版权图片链接