Excel技能树系列04：函数的套路

本篇开始之前，我们想一想何为Excel公式？所谓公式通俗的说就是一种运算法则。以数字或者文本等作为参数，通过定义好的运算法则输出确定的某种结果。在02篇中我们讲过三大单元格引用模式，即所谓的绝对引用，相对引用和混合引用，讲引用的目的就是为公式篇打基础的。电子表格的单元格就是数据的容器，这个数据可以是文本，数字啥的。现在我们归纳一下：Excel的单元格就是一种数据容器，所谓的Excel公式就是一种定义好的运算法则，该运算法则以数字或者文本等数据为参数，得出某种确定的结果，这种运算法则就是公式。公式可以直接以常量为参数直接运算，比如在A1单元格中直接输入“=2+3”，结果就是5。

本文不是教读者某个具体的公式，而是给读者构建一个公式的框架，这样即使读者需要用到全新的公式时，套用框架以及加上Excel本身的帮助（按F1就出来了），足以快速理解任何需要用到的公式的用法。上面说过公式是一种定义好的运算法则，而单元格是数据的容器，就不难理解公式可以直接用数字或者文本作为参数计算，也可以用单元格作为参数，引用单元格中的数据来计算，也可以同时用单元格和数字已经文本混合起来计算。具体的数字和文本我们称之为常量，后面也这么叫了。

看到这里，读者大概也明白公式的基本概念了。其实可以用作Excel公式参数的主要是以下几种：

常量（包括数字和文本，逻辑值）

单个单元格

单元格区域

条件表达式（其实也是逻辑值，就是那种不大于多少，不小于多少的那种格式，后面讲到逻辑类函数的时候会给大家说的）

四大类。

其实Excel中函数与公式就是一个东西，叫法不一样而已。现在我举个不是很简单，但是特别实用的函数来作为例子，就是VLOOKUP函数。在Excel中，除非是函数本身的要求，不然尽量不要用常量作参数，而是用单元格代替，然后在单元格中填入相应的数据。这样后面函数计算结果不对的时候，可以通过追踪单元格，而常量作参数错了，数据量一大了很难排错。没有比自带的说明更权威的公式和函数用法说明了，读者学习公式的过程中应该要善加利用帮助文档。按F1打开帮助文件，Excel 2016直接在搜索框中搜索，搜索VLOOKUP。在出来的结果中，我们看到VLOOKUP函数需要四个参数：

VLOOKUP(你想要查找的内容，查找范围，你需要的结果在查找区域的多少列，精确匹配0表示或者模糊匹配1表示)

第一个参数：单个单元格

第二个参数：单元格区域，比如A1：C10

第三个参数：你需要的结果所在的列数

第四个参数就是一个逻辑值，1或者true代表模糊匹配，一般不常用；0或者false代表精确匹配

注意：VLOOKUP有个限制，就是第一个参数的值只能是第二个参数单元格区域的最左侧那一列中，也就是区域第一列的值，不然会报错。

VLOOKUP函数

特别留意：一个可能容易犯的错是函数中用到的逗号，双引号，一定要是英文输入法下输入的，不然函数可能会出错，这点尤其注意。

下面这张动图就演示了Excel中VLOOKUP函数的工作套路，是不是很简单？这个函数实用而且参数比较多。这个函数的套路都会的话，其他的函数也就差不多一个套路。首先知道函数的功能是干嘛的，需要几个参数，每个参数是单个单元格还是常量，亦或者是单元格区域还是逻辑值，搞清楚了这个，直接往函数里面一套，想要的结果就出来了。

下面总结了常用函数的套路与参数模式，读者理解记忆一下，用到的时候直接按Alt + M打开公式选项卡找出来直接用或者按Shift + F3插入公式，也可以用鼠标点击公式菜单下面的插入公式。

---

1、算术类公式

求和函数SUM(单元格区域或者常量)：这个函数的参数就是一个或者多个单元格区域，也可以是逗号隔开的一组数字，比如：

= SUM(A1:A6)

=SUM(A1:A6, B1:B6)

=SUM(2, 4, 6, 8, 10)

都是正确的用法，用哪种根据需要即可

条件求和函数SUMIF(条件单元格区域，条件，求和区域)：这个函数的第一个参数就是一个单元格区域，第二个参数是条件，第三个参数就是求和区域，比如：

=SUMIF(A1:F1, "苹果"，A2:F2)

条件求和

取平均数函数AVERAGE(单元格区域或者常量)：这个函数的参数就是一个或者多个单元格区域，也可以是逗号隔开的一组数字，比如：

=AVERAGE(A1:A6)

=AVERAGE(A1:A6, B1:B6)

=AVERAGE(2, 4, 6, 8, 10)

都是正确的用法，用哪种根据需要即可

条件求平均数函数AVERAGEIF(条件单元格区域，条件，求平均数区域)：这个函数的第一个参数就是一个单元格区域，第二个参数是条件，第三个参数就是求平均数区域，比如：

=AVERAGEIF(A1:F1, "苹果"，A2:F2)

计数函数COUNT和COUNTIF的参数跟上面一样，查一下帮助就明白怎么用了

求幂函数POWER(单个单元格或数字， 幂次方数)，比如：“=POWER(6,8)”就是求6的8次方，比“=6^8”这种写法优雅，或者计算A1内数字的8次方，写成“=POWER(A1，8)”

求积函数PRODUCT，参数模式跟SUM差不多，可用来代替“=A2*B2”这种写法，比如：”=PRODUCT(A2，B2)“，”=PRODUCT(A1:A6)"

求模函数MOD，绝对值函数ABS，开方函数SQRT等数学类函数都很简单，不多解释。查看帮助就懂了，差不多跟上面的SUM, AVERAGE, POWER一个模式，直接按Alt + M跳到公式选项卡，数学和三角函数里面的函数都差不多这个模式

---

2、查找和引用类函数

VLOOUP：上面已经有了例子和动图，还有一个对应的HLOOKUP也差不多一个模式，2016版本的Excel引入了XLOOKUP，只需要三个参数，更优雅美观

INDEX：查询帮助可知这个函数有两种模式，简单讲讲常用的第一种模式吧。第一种模式看帮助文档可知在区域中通过相对应的行和列来引用该区域内某个单元格的值；比如：“=INDEX(A1:D10, 5, 4)”就会返回A1到D10这个区域的第5行，第四列的值。可以配合MATCH函数实现查找功能，比VLOOKUP灵活，但也稍微复杂点，下一篇专门讲几个实用的函数组合的时候会讲这个组合

HLOOKUP：跟VLOOKUP差不多，理解了VLOOKUP查帮助一看就会了

INDIRECT：间接引用函数，这个函数其实挺实用的，但很多人都不用，打个比方，我需要通过C4单元格引用A1单元格可以在C4单元格中存入"A1"，然后通过“=INDIRECT(C4)”这样的方式来实现这个需求

INDERECT函数

ADDRESS也是很实用的，有需求可以查看帮助研究一下，可以跟其他查找函数组合使用，下一篇介绍

MATCH函数下一篇讲解INDEX组合查询时介绍，其实理解了VLOOKUP的套路和INDEX的套路，很简单的

OFFSET函数也是可以配合其它查找函数使用，有个印象即可

ROW， COLUMN等套路都很简单，查询帮助即可，可以配合其他查询函数一起使用，也可以配合INDIRECT实现间接引用

3、逻辑判断类函数

IF函数：

查询帮助我们知道IF函数的套路如下：IF(条件表达式，条件表达式为真时的结果，条件表达式为假的结果)

所谓的条件表达式无外乎5种:

判断是否等于：比如A1=10时，则在B1单元格显示符合条件，A1不等于10时则显示不符合条件，写出函数就是“=IF(A1=10, ”符合条件“，”不符合条件“)”

是否大于:比如A1大于10，条件表达式为A1>10

是否小于:比如A1小于10，条件表达式为A1<10

不超过或不大于：比如A1不大于10，条件表达式为A1<=10

不低于或不小于：比如A1不小于10，条件表达式为A1>=10

所有带IF的函数COUNTIF, SUMIF, AVERAGEIF的条件表达式都是这个套路

IF函数

AND函数：多个条件表达式都为真时结果才为真，且的关系，套路为AND(条件表达式1， 条件表达式2，……)

OR函数：参数同AND，但是条件中有一个为真结果就是真，或的关系

NOT函数：参数只有一个条件表达式，但结合AND或者OR可以实现多条件反转，表示非的关系，就是条件为真时结果为假

说明：这几个函数可以给带IF的算术函数或者IF函数组合使用，实现多条件求和，判断，查找等

---

Excel的函数非常多，有好几百个，但是咋们用不了那么多。某些函数用到的时候知道查帮助文档就可以了，以上三类函数比较能够代表函数套路和模式。所有稍微举例说明这些函数，但本篇的目的始终是构建一个通过帮助文档立马理解函数套路，从而学会应用函数的思路。指望这么短小的篇幅介绍完所有的常用函数也不是不可以，但这种东西Excel自带帮助里面有，而且还很详细，写成函数流水账也不是我的目的。第五篇会介绍几个常用的函数组合，都是比较实用的组合。函数套路就介绍这么多，想成为高手的唯一办法就是多用，多试，某些函数组合起来说不定不经意间就解决了你的麻烦。思路远比工具更重要，但同时也要明白工欲善其事，必先利其器的道理。下面以第二篇介绍的需要掌握的快捷键动图操作作为本篇的结语。

---

用到的快捷键：

Ctrl + D

Ctrl + R

Ctrl + Enter

Ctrl + 方向键

Ctrl + Shift + 方向键

模拟快捷键的使用，数据都是瞎编的。

快捷键使用