Excel常用函数讲解应用大全

今天我们介绍这个系列最后4个函数的应用方法，主要包括vlookup函数以及函数嵌套的进阶用法

多条件计数函数（COUNTIFS函数）

参数criteria_range1, criteria_range2,…：用于条件判断的多个单元格区域，做多127个

参数criteria1, criteria2,…：为确定哪些单元格将被计算在内的多个条件，最多127个条件

其形式可以为数字、表达式或文本。为表达式或文本时应加英文半角双引号；为数字时双引号可省略。

例如，条件可以为32，”32”, “>32”，”apple”或B4

简单滴说，COUNTIFS函数的功能是在各个criteria_range区域中进行条件判断，统计工作表中同时满足所有条件的个数。

实例演示5：

统计此次考试中数学和计算机成绩均在90分以上的学生人数

操作方法：单击G2单元格，输入公式“=COUNTIFS(C2:C8,">=90",D2:D8,">=90")”，确认即可，返回值为3

公式的含义：criteria_range1区域为C2:C8，criteria1条件为“>=90”，criteria_range2区域为D2:D8，criteria2条件为“>=90”，同时满足这2个条件的人数有3个

总 结

我们分别介绍了COUNTIF/COUNTIFS两个函数的功能、格式和参数

COUNTIF函数的功能是在第1个参数指定的范围中，统计满足第2个参数给定的条件的单元格个数

COUNTIFS函数的功能是在各个criteria_range区域中进行条件判断，统计工作表中同时满足所有条件的个数

查找函数（VLOOKUP函数）

（以上内容看不懂也没问题，看了我们的演示和说明之后，再回来看即可）

这里是不是也看晕了？别怕，给你看我们的表述

VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）

请看演示实例：

查找指定学号的学生的总分数

单击I2单元格，输入公式“=VLOOKUP(H2,$A$2:$F$8,6,FALSE)”，返回值为266，即QB002学号学生对应的总分数

Tips：这里还要分享一个输入公式的小技巧，当我们输入公式时Excel开始在下来框中给我提示他所包含的公式了，如果置顶的是你需要的公式，此时你按下Tab键，Excel就会把公式帮你补全

当你在公式中引用一个单元格的范围，想将这个范围变成绝对引用的时候，你只需要输入范围，然后按下F4键，就会全部变成绝对引用，本例中范围的绝对引用就是这么输入的，否则你还要每个字符前输入一个$，这是方便的快捷键。当然Excel还有很多方便的快捷键，我们会在后续整理和分享的。

公式的含义：

1.“H2”查找值，在指定的单元格区域的首列中查找的值，本例中就是$A$2:$F$8范围，首列就是A列，也即要查找的是A列中与H2单元格内容相匹配的值，所以VLOOKUP函数也称为纵向查表函数。

2.“$A$2:$F$8”查找范围，这里为什么要用绝对引用呢，因为通过绝对引用后，可以对公式进行下拉，这样统计其他学号的总分就不用重复输入函数，下拉即可，因为其他学号的总分，也是在这个范围内进行查找。

3.“6”，这里的数字6是代表需要返回值所在的列数，引用范围是“$A$2:$F$8”所以A列是第1列，B列是第二列，以此类推，“总分”所在的列是第6列，倘若引用是从B列开始，那这个位置就是数字5，注意这里的列数是从引用范围的第一列做为1，而不是A列。

4.“FALSE” 查找方式为精确匹配或者模糊匹配，0或FALSE为精确匹配，TRUE为模糊匹配；实际工作中，大部分都使用的是精确的匹配到我想要查询的值，千万不要使用true，因为模糊匹配会给你带来意想不到的结果，我们还是不去使用的为好。

本例中我们使用“FALSE”的精确查找

这个公式通俗的讲就是对H2单元格中的学号在A列中查找他所在的行，找到这一行以后，就将这一行第6列的值返回，这个过程中是精确匹配的，必须严格一致。

然后我们对公式进行下拉，就可以查找其他学号的总分了。

总 结

我们介绍了VLOOKUP函数的功能、格式和参数

结合我们的理解给出了VLOOKUP函数的理解形式，即VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）

通过实例演示，对VLOOKUP参数逐个进行了解释和应用

实际工作中，我们还会用VLOOKUP函数对众多产品中的某个产品历史销量进行精确查找、对大量库存产品的存储仓位等内容进行精确查找。

排序序列函数（RANK函数）

参数1：number，需要找到排名的数字

参数2：ref，为数字列表数组或对数字列表的引用。ref中的非数值型参数将被忽略

参数3：order，为一个数字，指明排名的方式。Order为0或省略时，对数字的排名是按照降序排列的；不为0，则是按照升序排列

注意，函数RANK对重复值的排名相同，如90分是第3名，87分是第4名，但有2个90分的同学，那他俩都是第3名，87分的同学就是第5名了。下面例子中刚好有这样的情况。

实例演示1：

对学生成绩按总分排序

实现方法：单击F2单元格，输入公式“=RANK(E2,$E$2:$E$8,)”我们通过Tab键补全公式，通过F4键快捷完成绝对引用，然后将该公式复制到F3:F8即可。

公式含义：确定第一个学生的总分数E2单元格的值，在所有学生的总分E2:E8单元格区域中的排名。因为对任何一个学生的排名所在的排名区域是相同的，不随目标单元格的改变而改变，所以函数的第二个参数我们采用绝对引用。

实例演示2：

rank函数对不连续单元格排名：不连续的单元格，第二个参数需要用括号和逗号形式连接起来。

计算一季度销量在4个季度中的排名情况

单击D12单元格，输入公式“=RANK(B15,(B15,B19,B23,B27),0)”，即返回排名。

公式含义：

第一个参数B15单元格是需要排名的数字

第二个参数是不连续的单元格，需要用括号和逗号形式连接起来

第三个参数0，表示升序排列

总 结

我们介绍了排序序列函数的功能、格式和参数

通过实例演示了排序序列函数的具体用法和解决实际工作中问题的应用

这些嵌套的复杂功能都是基于这些函数的基本功能的，所以我们熟练掌握了基础函数的功能，后续无论是多么复杂的嵌套函数，我们都能按照自己的工作需求去写出来的，别人的复杂函数，我们静下心来，也一定能看懂和学会的。

Excel函数的进阶用法-函数的嵌套

我们和大家一起共同学习了Excel常用的基础函数，我们继续学习一下这些基础函数的进阶用法。

也即是通过相互的嵌套，实现更加复杂的功能，这里我们只举几个例子来演示函数嵌套的方法，后续我们还会推出一系列应用函数嵌套来解决实际问题的课程。

在公式中套用函数

公式可以由单一函数构成，但更多的情况下，公式是由运算符、常量、单元格引用以及函数共同组成的。

演示实例1：

在学生成绩工作表中，求每个学生的数学成绩与该课程的平均成绩之差

操作方法：

单击C2单元格，输入公式“=B2-AVERAGE($B$2:$B$8)”，确认后将公式复制到C3:C8区域即可。

上面就演示了Excel中的公式是由运算符、常量、单元格引用以及函数共同组成的。

函数的嵌套

当以函数作为参数的时候，称为函数的嵌套。

在公式中最多可以包含64级嵌套函数。当函数B用做函数A的参数时，函数B称为二级函数。如果函数B 中还有函数C作为参数，则函数C称为三级函数。

演示实例2：

当学生3门课平均分低于60分时记为“不通过”，大于60时则返回3门课的总成绩

操作方法：

单击E2单元格，输入公式“=IF(AVERAGE(B2:D2)>=60,SUM(B2:D2),"不通过")”，然后将公式复制到E3:E8单元格。

公式中使用了嵌套的AVERAGE函数和SUM函数。在此AVERAGE函数和SUM函数为二级函数。

公式含义：用AVERAGE函数计算出B2:D2单元格的平均值，并将它与60比较，当返回值为TRUE时，即用SUM函数求B2:D2区域数据的和，否则返回“不通过”。

演示实例3：

根据学生的平均成绩计算出每个学生的成绩等级，并填写在“总评”一栏。成绩等级的评判标准是：90分（含90分）以上为“优秀”；80-90分（含80分不含90分）为“良好”；60-80分（含60分不含80分）为“通过”，60分（不含60）一下为“不通过”。

操作方法：

单击F2单元格，输入公式“=IF(E2>=90,"优秀",IF(E2>=80,"良好",IF(E2>=60,"通过","不通过")))”，再将该公式复制到F3:F8区域即可。

公式中使用了3级IF函数的嵌套。最外层IF函数的含义是，判断F2如果>=90，则返回“优秀”，否则成绩小于90时，还不能确定等级，需要进一步判断，因此外层IF函数的第三个表达式又是一个IF函数，也即二级函数。在这个额嵌套的IF函数中，表达式“E2>=80”如果成立，说明此时成绩大于等于80且小于90，该IF函数的返回值是“良好”，否则还需要进一步判断，因此它的第三个表达式也是一个嵌套的IF函数，这个IF函数就是第三级IF函数。同理，如果第三季IF函数的表达式“E2>=60”成立，说明成绩大于等于60且小于80，该IF函数的返回值是“通过”，否则无需再进一步判断，返回值为“不通过”。

总 结

单一的函数可以称为公式，但更多的情况下，公式是由运算符、常量、单元格引用以及函数共同组成的。

当以函数作为参数的时候，称为函数的嵌套。在公式中最多可以包含64级嵌套函数。