|
|
2021年5月18日更新，新增1项内容第17节
本文全文12174个字，全是演示实例和干货，适合收藏点赞学习，关注“Excel效率手册”公众号可提供Microsoft office 2016安装包及其他Excel技巧教程
本系列教程均有相应视频课，您可关注我的主页，查看视频课学习
熟练掌握应用了Excel中内置的常用函数，就能解决我们日常学习工作中的绝大多数问题，那些复杂的问题，也都是通过这些函数相互嵌套实现的
本文就系统全面的给大家讲解了Excel中常用的基础函数，及其相互嵌套的进阶用法，相信通过这篇文章的学习，你就能自如的应用Excel解决你遇到的那些问题了。
<hr/>我们将按照如下目录进行介绍和学习
1. Excel函数的基本概念
2. 如何使用函数
3. 求和函数（SUM函数）
4. 求平均值函数（AVERAGE函数）
5. 最大最小值函数（MAX/MIN函数）
6. 计数函数（COUNT/COUNTA函数）
7. 取整函数（INT函数）
8. 舍入函数（ROUND函数）
9. 条件检测函数（IF函数）
10. 条件求和函数（SUMIF函数）
11. 多条件求和函数（SUMIFS函数）
12. 条件计数函数（COUNTIF函数）
13. 多条件计数函数（COUNTIFS函数）
14. 查找函数（VLOOKUP函数）
15. 排序序列函数（RANK函数）
16. Excel函数的进阶用法
17.随机函数（RAND/RANDBETWEEN）的用法
<hr/>1 Excel函数的基本概念
函数是Excel定义好的具有特定功能的内置公式。在公式中可以直接调用这些函数，在调用的时候，一般要提供给它一些数据，即参数，函数执行之后一般给出一个结果，这个结果成为函数的返回值。
（这段可以跳过不看）Excel中提供了大量的可用于不同场合的各类函数，分为财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑和信息等。这些函数极大地扩展了公式的功能，使得我们应用Excel做数据的计算、处理更为容易，更为方便，特别适用于执行繁长或复杂计算的公式。
（以下内容要重点看）函数一般是在公式中调用的。函数以函数名称开始，后面紧跟着圆括号，圆括号中是以逗号隔开的参数。
如前面介绍的用于从身份证号码中提取员工出生日期与年龄的公式，“=YEAR(NOW())-MID(E2,7,4)”的意思是，YEAR(NOW())，year函数是返回某日期对应的年份，这里与NOW函数相结合，返回的就是当前的年份，也即2021年，MID(E2,7,4)的意思是对E2单元格中的字符串从第7位开始向后提取4位，也即提取的是1986，这样2021-1986=35岁，就计算出了员工的年龄了。
函数的参数值可以是数值、文本、单元格或单元格区域的引用地址、名称、标志和函数。有的函数不需要参数，有的则需要输入多个参数，其中有些参数是可选的。用户必须按照正确的次序和格式输入函数参数，参数无大小写之分。
这些模块化的公式让Excel的应用功能十分强大，也让他拥有更广泛的用户基础，不像那些科学计算的程序语言，虽然功能强大、格式灵活，但因为每一个功能的实现都要用一句程序语言去描述，而不是像Excel这样，通过点点鼠标就能实现。因而熟练掌握Excel中公式的参数要求和规定，就成为用好Excel函数的基础。这就像我们在路上开车一样，想要安全快捷的到达目的地，我们就必须遵守交通规则，而Excel中的这些规定，就是我们要遵守的交通规则。
<hr/>2 如何使用函数
对于我们常用的一些求和、求平均值、最大值和最小值等函数，Excel在工具栏里做成了供我们点击的按钮。我给大家一一演示Excel中如何使用函数。
演示输入函数的几种方法
怎么找到我想用的函数呢？请看演示。我们可以通过关键词检索，找到这些函数，点击这个函数名称后，下边就会有对它功能的解释，我们就可以结合我们的需求，去使用这个函数了。
还可以通过Excel函数的分类，进行查找我们需要的函数，演示如下：
接下来，我们对Excel中常用的公式的功能和具体用法，逐一进行详细的介绍。
<hr/>3 求和函数（SUM函数）
Excel中的求和函数估计是众多初学者使用最多，也自认为完全掌握了的函数了。但其实SUM函数还有很多其他功能，我们就从基础讲起，让你用五分钟的时间扎扎实实的把求和函数的功能全掌握。
SUM函数的功能：返回参数列表中所有参数的和，SUM属于数学与三角函数
SUM函数的格式：SUM(number1，number2，……)
SUM函数的参数：number1，number2，……为SUM函数的参数，这些参数为1~255个需要求和的参数。数字、逻辑值、文本数字、单元格和区域的引用地址、名称和标志都可以作为参数。
直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如SUM(3,2)等于5；SUM(“3”,2,TRUE)等于6，因为文本数字”3”在运算时将被自动转换为数值3，逻辑值TRUE被转换为数字1（FALSE为0）
如果参数是单元格或区域的引用，只有其中的数字将被计算，而引用中的空白单元格、逻辑值、文本或错误值等将被忽略。
示例如下：
这个求和函数返回值是1，因为B6单元格是文本，C6虽然是数字，但是文本格式的数字，D6是空白单元格，只有E6单元格中的1被SUM函数计算了，因此返回值是1。
出现#VALUE!的情况，表明公式中所用的某个值是错误的数据类型。如
出现这个错误就是因为参数直接为非数字的文本，不是数字，Excel就会提示你公式中所用的某个值是错误的数据类型。
出现#NAME?的情况，表明无法识别公式中的文本，就出现此错误，如
求和函数的操作很灵活，如我们对如下一组数求和，可以采取多种方式，掌握了这些方式，就可以针对不同的数据特点去求和。
总 结
我们介绍了SUM函数的基本功能、格式和参数，并结合实例，对SUM函数的具体应用进行了演示。解释了我们日常使用公式时遇到的“#VALUE!”与“#NAME?”两种错误的原因。
<hr/>4 求平均值函数（AVERGGE函数）
AVERAGE函数的功能：返回参数列表包含的数据集的算数平均值，AVERAGE属于统计函数
AVERAGE函数的格式：AVERAGE (number1，number2，……)
AVERAGE函数的参数：number1，number2，……为AVERAGE函数的参数，这些参数为1~255个需要计算平均值的参数。数字、逻辑值、文本数字、单元格和区域的引用地址、名称和标志都可以作为参数。
直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如AVERAGE (3,2)等于2.5；AVERAGE (“3”,2,TRUE)等于2，因为文本数字”3”在运算时将被自动转换为数值3，逻辑值TRUE被转换为数字1（FALSE为0）
如果参数是单元格或区域的引用，只有其中的数字将被计算，而引用中的空白单元格、逻辑值、文本或错误值等将被忽略。但是单元格包含0值则计算在内。
示例如下：请注意数据中标记颜色的文本和0值
总 结
我们介绍了AVERAGE函数的基本功能、格式和参数，并结合实例，对AVERAGE函数的具体应用进行了演示。AVERAGE函数的功能与SUM函数的操作很像，可以结合在一起学习。
<hr/>5 最大最小值函数（MAX/MIN函数）
最大最小值函数可以帮助你从纷繁复杂数据中，快速遴选出一组数据的最大值及最小值，可以应用在学生成绩的统计分析，科学计算的最大最小值挑选等工作中。通过下面五分钟的演示，你将熟练掌握这两个函数的基础应用。
函数功能：MAX函数返回参数列表包含的数据集中的最大数值，MIN函数返回参数列表包含的数据集中的最小数值。函数MAX和MIN属于统计函数
函数格式：MAX/MIN(number1，number2，……)
函数参数：number1，number2，……，最多可以有255个参数。数字、逻辑值、文本数字、空白单元格、单元格和区域的引用地址、名称和标志都可以作为参数。
直接键入到参数表中的数字、空白单元格、逻辑值或文本数字都将被包括在内，其中，空白单元格被视为0。如MAX(3,2)等于3；MAX(“3”,2,TRUE)等于3，MIN(“3”,2,TRUE)等于1，因为文本数字”3”在运算时将被自动转换为数值3，逻辑值TRUE被转换为数字1（FALSE为0）
如果参数直接是非数字文本或错误值，将显示错误提示
示例：数字10，8，7，20和5，则“=MAX(E3:I3)”返回值为20，“=MAX(E3:I3,21)”返回值为21，“=MIN(E3:I3,FALSE)”返回值为0，因为FALSE的值为0，“=MAX(-2, ,-1)”返回值为0，因为逗号之间的空值被视为0。
还是用学生成绩的例子来演示函数功能
总 结
我们介绍了MAX/MIN函数的基本功能、格式和参数，并结合实例，对MAX/MIN函数的具体应用进行了演示。并将这两个函数应用于学生成绩单的统计实例中。
<hr/>6 计数函数COUNT和COUNTA
功能：COUNT返回参数中数字项的个数，COUNTA返回参数中非空值的个数。也即，COUNTA函数计数的范围更宽泛。COUNT和COUNTA属于统计函数。
格式：COUNT/ COUNTA（value1，value2…）
参数：value1，value2…为包含或引用各种类型数据的参数，COUNT/ COUNTA函数最多可以有255个参数。
1. COUNT函数：如果参数直接是数字、空白单元格、逻辑值、日期或文本数字，执行时都可以被计算，而非数字的文本或错误值都将被忽略；如果参数是单元格或区域的引用，则只统计引用中的数字、日期值，引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果需要统计逻辑值、文字或错误值，则使用COUNTA函数。
2. COUNTA函数：直接键入的参数可以是任何类型，包括非数字的文本或错误值，执行时都可以被计算；如果参数是单元格或区域的引用，则引用中的数字、日期值、逻辑值文字或错误值都将被统计，只有引用中的空白单元格被忽略。如果不需要统计逻辑值、文字或错误值，则使用COUNT函数。
对以上概念如果看的有点迷糊的话，我们通过下面的示例向你演示他们的具体意思。
示例1：COUNT/ COUNTA函数直接键入参数的情况
公式：=COUNT(你好, ,2021-3-3,100,TRUE,#NAME?)的返回值为4，因为是直接输入，所以空格，日期，数字，逻辑值被计数，而文本值（你好），错误值（#NAME?）被忽略。
公式：=COUNTA(你好, ,2021-3-3,100,TRUE,#NAME?)的返回值是6，因为是直接输入，所以任何类型的数据都将被计算。
示例2：参数是单元格或区域的引用情况
公式：=COUNT(B3:G3)的返回值为2，因为参数是引用的单元格，所以文本类型的数据“你好”，空白单元格，逻辑值TRUE，错误值#NAME?都被忽略了，只有2021年3月3日与100被计算了，所以返回值是2。
公式：=COUNTA(B3:G3)的返回值是5，因为参数是引用单元格，那只有空白单元格被忽略，所以返回值是5，与刚才的直接输入对比可见，直接输入的返回值是6，空白值也被计数了。
实际应用案例1：用COUNT/COUNTA函数，统计学生参加数学考试的应考人数和实考人数。
在D10单元格中输入公式=COUNTA(C10:C16)返回值为6，共计7个学生，其中李明碧休学，因而应考人数为6人
在E10单元格中输入公式=COUNT(C10:C16)返回值为5，去除休学和缺考的2人，所以实考人数为5人
实际应用案例2：用COUNT/COUNTA函数，统计参加考试的报考人数和报考人次
在单元格F19中输入公式=COUNTA(A19:A25)返回值是7，就计算出报考人数是7人
在单元格G19中输入公式=COUNTA(B19:E25)返回值是14，就计算出报考人次是14人次
总 结
通过以上介绍和演示，我们就对Excel中的计数函数有了清晰的认识，结合自己实际数据情况和统计需求，我们就可以快速准确的数出那些以前需要我们爬格子去数的计数需求了。
<hr/>7 取整函数（INT函数）
功能：INT返回参数取整处理的整数值。无论参数是正数还是负数，INT函数总是将要处理数取整为小于等于它的最大整数。INT函数属于数学与三角函数
格式：INT（number）
参数：只有一个参数number，需要进行取整处理的实数。
例如：看一下INT函数的基础功能演示
INT(1.9)=1
INT(-1.9)=-2
引用单元格中的数字也是一样的原理
看一个我们在实际工作中可能遇到的问题
以前我们父辈的工资都是发现金到手里，那拿在手里的一叠钱，才是对自己一个月工作的充分肯定呢。
现在我们就看看这张模拟工资发放表，在该表中，需要计算出每份工资所需的各种面值票面的数量以及每种面值票面的总数量
1. 计算需要100元面值票面的数量，在D3单元格中输入公式“=INT(C3/100)”，并将其复制到D4:D7单元格区域
2. 计算需要50元面值票面的数量，在E3单元格中输入公式“=INT((C3-D3*100)/50)” ，并将其复制到E4:E7单元格区域
3. 计算需要10元面值票面的数量，在F3单元格中输入公式“=INT((C3-D3*100-E3*50)/10)”，并将其复制到F4:F7单元格区域
4. 计算需要5元面值票面的数量，在G3单元格中输入公式“=INT((C3-D3*100-E3*50-F3*10)/5)”，并将其复制到G4:G7单元格区域
5. 计算需要1元面值票面的数量，在H3单元格中输入公式“=INT((C3-D3*100-E3*50-F3*10-G3*5)/1)”，并将其复制到H4:H7单元格区域
<hr/>8 舍入函数（ROUND函数）
功能：ROUND返回某个数字按指定位数四舍五入后的数字。ROUND函数属于数学与三角函数
格式：ROUND（number，num_digits）
参数：两个参数。
第一个参数number：需要进行四舍五入处理的数字
第二个参数num_digits：指定的舍入位数，均为整数（……，-2,-1,0,1,2，……）
如果num_digits大于0，则舍入到指定的小数位
如果num_digits等于0，则摄入到最接近的整数
如果num_digits小于0，则在整数位即小数点左侧进行四舍五入。num_digits是负几，就对整数从又向左的第几位进行舍入。
请看演示：
ROUND(3.15,1)返回3.2，函数作用为将3.15四舍五入到小数点后1位
ROUND(3.148,1)返回3.1，函数作用为将3.148四舍五入到小数点后1位
ROUND(-3.148,2)返回-3.15，函数作用为将-3.148四舍五入到小数点后2位
ROUND(1.9,-1)返回0，函数作用为将1.9四舍五入到小数点前1位，也即对1进行四舍五入，请与下边这2个例子对比来看
ROUND(5.9,-1)返回10，函数作用为将5.9四舍五入到小数点前1位，对个位数5进行四舍五入，所以返回值为10
ROUND(481.89,-2)返回500，函数作用为将481.89四舍五入到小数点前2位，也即对十位数8进行四舍五入，所以返回值为500
总 结
舍入函数操作简便灵活，最重要的是可以对数据进行批量的四舍五入处理。
与以往接触不同的是，还可以对小数点以前的数字进行四舍五入，这是需要我们重点学习理解的一部分内容。
<hr/>9 条件检测函数（IF函数）
功能：IF函数具有判断的能力，通过对作为第一参数的条件进行判断，根据判断结果的真假执行不同的计算，返回不同的结果。IF函数属于逻辑函数。
格式：IF（logical_test, value_if_true, value_if_false）
参数：3个参数，含义如下
参数1：logical_test，数值或表达式，计算结果为逻辑值，表示被判断的条件
参数2：value_if_true，当参数1（logical_test）所代表的条件成立时，IF函数将返回参数2表达式的值。
参数3：value_if_false，当参数1（logical_test）所代表的条件不成立时，IF函数将返回参数3表达式的值。
简单地说：参数1是判断的条件，条件成立，返回参数2；条件不成立，返回参数3。
对于参数2和3还有一些简单的规则，我们总结介绍如下
1.参数2和3也可以是公式或函数，但请注意前面不要等号“=”。当参数2和3是函数的时候，就是函数的嵌套。
2.参数2和3也可以是文本，当在函数对话框中输入参数2和3时，其两侧可以带英文半角双引号，也可以不带。当该文本就是IF 函数的返回结果时，则直接显示该文本。
3.参数2可以省略不写，如果省略，当条件选择应该返回参数2时，则返回0.
4.参数3也可以省略。参数3前边的“，”也可以省略，当“，”一起省略时，条件选择应该返回参数3时，则返回FALSE；当“，”没省略时，则返回0.
请看实例演示1：
在如下学生成绩单中，将平均成绩大于等于90分的学生总体评价显示为优秀，否则什么都不显示。
实现方法：单击G3单元格，输入公式“=IF(F3>=90,&#34;优秀&#34;,&#34;&#34;)”，确认后，将公式复制到G4:G7区域即可。
实例演示2：
自制一个考试答案评分卡，我们首先将题号与标准答案录入，然后只要填入学生的答案，即可给出相应的评分。
实现方法：只要将学生的答案与标准答案作比较，两者一致即得分，否则不得分。
选取单元格E10，输入公式“=IF(D10=C10,10,0)”或公式“=IF(D10=C10,10,)”，确认后将公式复制到其他区域即可。
演示如下
总 结
我们介绍了Excel中的条件检测函数IF，此函数包含3个参数，其中参数1是判断的条件，条件成立则IF函数返回参数2的值，不成立则返回参数3的值。
参数2和参数3可以是公式、文本，也可以省略不写。
<hr/>10 条件求和函数（SUMIF函数）
功能：返回指定区域中满足给定条件的单元格的和。SUMIF函数属于数学与三角函数
格式：SUMIF（range, criteria, sum_range）
参数：3个参数，含义如下
参数1：range用于进行条件判断的单元格区域。（这个概念没什么理解障碍，简单说，这个区域就是包含了很多种类的数据，通过设定条件，可以将这些种类的数据，分门别类的求和）
参数2：criteria为确定哪些单元格将被求和的条件（这个概念也没问题，就是具体筛选的条件是什么），其形式可以为数字、表达式或文本。当该参数为表达式或文本时应加英文半角双引号；为数字时双引号可不加。例如：条件可以表示为40，”40”，”>40”或”沈阳发货”
参数3：sum_range需要求和的实际单元格区域。（重点来了，就是这个需要求和的实际单元格区域与参数1的条件单元格区域之间的关系怎么理解，就是这个函数应用时最关键的问题）
只有在range区域中符合条件的单元格，在sum_range区域中的单元格才能参与求和。（看到这里你是不是觉得，这不就是range区域包含了sum_range区域吗，当然不是这样的，继续看），如果省略sum_range，则对range区域中符号条件的单元格求和。（这些理解起来都没问题）
设定的sum_range与range区域的大小和形状可以不同（让我困扰了一个多小时的问题就是这句话，一会结合实例看，更直观，别急），相加的实际单元格区域通过以下方法确定：使用sum_range中左上角的单元格作为起始单元格（记住理解这句话），然后包括与range区域大小和形状相对应的单元格区域（第一遍看完的时候，我完全不知道这句话在说什么，直到后面的实例让我更加迷惑）
简单滴总结SUMIF函数的功能，就是在range区域中进行条件判断，然后在sum_range给定的区域中对满足条件的对应单元格求和。
请看实例：
演示1：求属性值高于160000的佣金之和。
操作方法：点击E7单元格，输入公式“=SUMIF(A2:A5,&#34;>160000&#34;,B2:B5)”，公式的含义为，对A2:A5的单元格区域中判断单元格的值是否高于160000，单元格值高于160000的，则对B2:B5区域的对应单元格求和。本例中A3/4/5的值高于160000，所以对B3/4/5单元格求和，结果为6300。（怎么样，理解起来一点不难吧，那请继续看）
演示2：省略sum_range的情况（也不难理解）
操作方法：当省略sum_range时，则对A2:A5区域中属性值高于160000的单元格求和，所以返回值是A3+A4+A5=900000
演示3：改变参数2 的条件（也不难理解）
操作方法：输入公式“=SUMIF(A2:A5,&#34;=300000&#34;,B2:B5)”，即在A2:A5单元格区域中判断单元格的值是否等于300000，单元格值等于300000，则对B2:B5区域的对应单元格求和。所以返回值为210000
演示4：sum_range的范围小于range范围时
操作方法：输入公式“=SUMIF(A2:A5,&#34;=300000&#34;,B2:B3)”，按照我之前的理解，条件单元格A2:A5没变，需要求和的实际单元格区域sum_range范围是B2:B3，这就是我困惑的点了，返回值怎么能是21000呢？21000是B4单元格的值，也不在B2:B3的范围内啊，怎么能返回B4的值呢？
针对这个问题，我反复查证，终于找到了这个条件区域与求和区域之间的关系，我简单总结如下：
[sum_range]只起定位求和区域起始单元格的作用。求和区域的大小和形状总是由参数range决定。因此，对于参数sum_range来说，真正重要的是该区域中左上角的那一个单元格，它才是定位求和区域起始单元格的标准。我们看如下的例子来加深理解
如果区域是 并且 sum_range 是 则需要求和的实际单元格是
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1:D4 C1:D4
A1:B4 C1:C2 C1:D4
所以本例中，虽然我们sum_range范围是B2:B3，但因为range区域是A2:A5，所以实际sum_range范围仍然是B2:B5，那这个公式“=SUMIF(A2:A5,&#34;=300000&#34;,B2:B3)”因为A4符合条件，所以返回B4的值21000。
我再举几个例子来帮你加深这两个区域概念的理解
您也可以关注“Excel效率手册”公众号，我们进一步讨论交流
例子1：sum_range区域比range区域大的情况。
单击C20，输入公式“=SUMIF(A13:A17,A12,B13:B19)”，可见sum_range区域比range区域大，然而实际求和区域是B13:B17，起点由B13开始，形状和大小由range决定，在本例也就是一列五行
例子2：sum_range区域比range区域小的情况。
单击C20，输入公式“=SUMIF(A13:A17,A12,B13:B14)”，可见sum_range区域比range区域小，然而实际求和区域是B13:B17，起点由B13开始，形状和大小由range决定，在本例也就是一列五行。
例子3：sum_range只有一个单元格B13的情况。
单击C20，输入公式“=SUMIF(A13:A17,A12,B13)”，sum_range区域只有一个单元格，然而实际求和区域是B13:B17，起点由B13开始，形状和大小由range决定，在本例也就是一列五行。
例子4：sum_range只有一个单元格B15的情况。
单击C20，输入公式“=SUMIF(A13:A17,A12,B15)”，sum_range区域只有一个单元格，此时让我最费解的问题出现了，当只有一个B15的时候，此时函数返回值为什么会是8呢？
现在我们回到sum_range区域确定方法的定义：使用sum_range中左上角的单元格作为起始单元格（记住理解这句话），然后包括与range区域大小和形状相对应的单元格区域。
所以，当单元格为B15时，此时sum_range区域的左上角的单元格为B15，也即此时实际求和区域由B15开始，而形状和大小由range决定，同样占一列五行，为B15:B19。
那按照这种理解，似乎应该是A18的23与A19的32对应的3+4=7啊？
我们仔细想一想，不能是7，因为A18与A19完全不在range区域内，所以这两个单元格对应的B列的数值不可能参与到计算中来。
那怎么会是8呢？原来这两个区域是有这样的对应位置关系的（下图所示）。我们的range区域A13:A17中，符合条件“<60”的是A14/15两个单元格，他们在range区域的位置是2和3，那么当起始单元格变为B15，实际计算区域是B15:B19时，函数返回值也应该返回B列中对应第2和3位置的值的和，所以是7+1=8。
当我们修改起始单元格为B14的时候，返回值是6+1=7。
至此我才感觉自己真正把SUMIF函数的功能弄懂了，否则在实际工作中，当出现例子4这样的情况时，你预期的计算区域与实际Excel计算的区域是不一致的，你把这样的返回值用在工作中去，后果是可怕的。
要知其然，还要知其所以然，才能把这些技能应用的得心应手。
总 结
SUMIF函数是返回指定区域中满足给定条件的单元格的和。这个函数包括3个参数，分别是用于条件判断的单元格区域，具体条件，需要求和的实际单元格区域。
重点：对于sum_range需要求和的实际单元格区域，当sum_range范围起始单元格与range范围起始单元格一致时，sum_range范围都与range范围一致；当起始单元格变化时，则要注意符合条件单元格在range中的位置编号，此时SUMIF函数返回的是sum_range范围与range对应位置的单元格的值。具体请看示例4的演示。
<hr/>11 多条件求和函数（SUMIFS函数）
功能：返回指定区域中满足多重条件的单元格的和。SUMIFS函数属于数学与三角函数
格式：SUMIFS（sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…）
参数：各参数，含义如下
参数1：sum_range需要求和的单元格区域
参数2：criteria_range1, criteria_range2,…用于条件判断的多个单元格区域，最多127个
参数3：criteria1，criteria2,…为确定哪些单元格将被相加求和的多个条件，最多127个条件
与SUMIF函数中的区域和条件参数不同，SUMIFS中每个criteria_range的大小和形状必须与sum_range相同。
SUMIF函数中这两个区域可以不同，上节中我们花了大篇幅来介绍这部分内容，没看懂的小伙伴可以翻看上一节内容，上一节内容是全网对SUMIF函数功能介绍最全面细致的文章，没有之一。
简单地说，SUMIFS函数的功能是在各个criteria_range区域中进行条件判断，然后在sum_range给定的区域中对同时满足所有条件的对应单元格求和。
请看演示实例：
统计开发部男员工的工资总和，并将结果显示在C10单元格中。
操作方法：
在上述样表中，单击单元格C10，输入公式“=SUMIFS(F2:F9,C2:C9,&#34;开发部&#34;,D2:D9,&#34;男&#34;)”，回车确认即可。
公式含义：
公式中F2:F9对应函数的sum_range区域，也即需要求和的区域
C2:C9对应criteria_range1用于条件判断的单元格区域
“开发部”对应criteria1，为确定哪些单元格将被相加求和的条件
D2:D9对应criteria_range2用于条件判断的单元格区域
“男”对应criteria2，为确定哪些单元格将被相加求和的条件
总 结
我们介绍SUMIFS函数的功能、格式和参数，并对每个参数的意义给出了详细说明
通过实例演示，给出了SUMIFS函数在实际工作中的应用
需要注意的是：与SUMIF函数中的区域和条件参数不同，SUMIFS中每个criteria_range的大小和形状必须与sum_range相同
将SUMIFS函数与SUMIF函数的功能比对着学习，印象会更加深刻。
<hr/>12 条件计数函数（COUNTIF函数）
功能：返回指定区域中满足给定条件的单元格的个数。COUNTIF函数属于统计函数
格式：COUNTIF(range, criteria)
参数：2个参数
参数1range：需要计算其中满足条件的单元格数目的单元格区域
参数2criteria：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。当参数2为表达式或文本时应加英文半角双引号；为数字时双引号可以省略。
例如，criteria的条件可以为32，”32”, “>32”，”apple”或B4
简单滴说，COUNTIF函数的功能是在第1个参数指定的范围中，统计满足第2个参数给定的条件的单元格个数。
实例演示1：
如果A1:A5中的数据分别为32,54,77,90,31，则计算区域中值大于53的单元格个数用“=COUNTIF(A1:A5,&#34;>53&#34;)”，返回值为3；计算该区域中值为90的单元格个数用“=COUNTIF(A1:A5,90)”，或“=COUNTIF(A1:A5,&#34;90&#34;)”，或“=COUNTIF(A1:A5,&#34;=90&#34;)”，返回值均为1。
实例演示2：
在如图所示的工作表中，统计总评为“优秀”和“良好”的学生人数，并将结果显示在G2与H2单元格中
操作方法：单击G2单元格，输入公式“=COUNTIF(F2:F8,&#34;优秀&#34;)”，确认即可
公式的含义：在F2:F8区域内统计内容为“优秀”的单元格个数
统计“良好”的学生人数操作方法相同
实例演示3：
继续上面的例子，请统计总评为优秀的学生人数与总人数的百分比，并将结果显示在G2单元格中
操作方法：单击G2单元格，输入公式“=COUNTIF(F2:F8,&#34;优秀&#34;)/COUNT(E2:E8)，确认即可
公式的含义：将F2:F8区域内内容为“优秀”的单元格个数除以E2:E8区域内含有数值的单元格的总个数，就计算出了优秀率
实例演示4：
请统计平均成绩70分到80分的学生人数（含70分，不含80分），并将结果显示在G2单元格中
操作方法：单击G2单元格，输入公式“=COUNTIF(E2:E8,&#34;>=70&#34;)-COUNTIF(E2:E8,&#34;>=80&#34;)”，确认即可
公式的含义：公式第一部分统计大于等于70分的人数（包含了大于等于80分的人），第二部分统计大于等于80分的人数，做减法后就求出70-80分之间的人数了。
<hr/>13 多条件计数函数（COUNTIFS函数）
功能：返回指定区域中满足多重条件的单元格的个数。COUNTIFS函数属于统计函数
格式：COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,…)
参数：参数含义如下
参数criteria_range1, criteria_range2,…：用于条件判断的多个单元格区域，做多127个
参数criteria1, criteria2,…：为确定哪些单元格将被计算在内的多个条件，最多127个条件
其形式可以为数字、表达式或文本。为表达式或文本时应加英文半角双引号；为数字时双引号可省略。
例如，条件可以为32，”32”, “>32”，”apple”或B4
简单滴说，COUNTIFS函数的功能是在各个criteria_range区域中进行条件判断，统计工作表中同时满足所有条件的个数。
实例演示5：
统计此次考试中数学和计算机成绩均在90分以上的学生人数
操作方法：单击G2单元格，输入公式“=COUNTIFS(C2:C8,&#34;>=90&#34;,D2:D8,&#34;>=90&#34;)”，确认即可，返回值为3
公式的含义：criteria_range1区域为C2:C8，criteria1条件为“>=90”，criteria_range2区域为D2:D8，criteria2条件为“>=90”，同时满足这2个条件的人数有3个
总 结
我们分别介绍了COUNTIF/COUNTIFS两个函数的功能、格式和参数
COUNTIF函数的功能是在第1个参数指定的范围中，统计满足第2个参数给定的条件的单元格个数
COUNTIFS函数的功能是在各个criteria_range区域中进行条件判断，统计工作表中同时满足所有条件的个数
<hr/>14 查找函数（VLOOKUP函数）
功能：在制定单元格区域的首列中查找指定的值，并返回该值所在行内制定列好的单元格的值，也称为纵向查表函数。VLOOKUP函数属于查找与引用函数
（以上内容看不懂也没问题，看了我们的演示和说明之后，再回来看即可）
格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
这里是不是也看晕了？别怕，给你看我们的表述
VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）
参数：4个参数，就是上面我们给出的通俗解释了，至于官方发布的那些拗口的解释，读者有兴趣的话可以在看懂我们的演示例子后再去对照学习。
请看演示实例：
查找指定学号的学生的总分数
单击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函数对众多产品中的某个产品历史销量进行精确查找、对大量库存产品的存储仓位等内容进行精确查找。
<hr/>15 排序序列函数（RANK函数）
功能：返回一个数字在数字列表中的排名。数字的排名是其大小与列表中其他值的比值，如果列表已排过序，则数字的排名就是它当前的位置。RANK函数属于统计函数。
格式：RANK(number, ref, order)
参数：3个参数，含义如下
参数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，表示升序排列
总 结
我们介绍了排序序列函数的功能、格式和参数
通过实例演示了排序序列函数的具体用法和解决实际工作中问题的应用
这些嵌套的复杂功能都是基于这些函数的基本功能的，所以我们熟练掌握了基础函数的功能，后续无论是多么复杂的嵌套函数，我们都能按照自己的工作需求去写出来的，别人的复杂函数，我们静下心来，也一定能看懂和学会的。
<hr/>16 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),&#34;不通过&#34;)”，然后将公式复制到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,&#34;优秀&#34;,IF(E2>=80,&#34;良好&#34;,IF(E2>=60,&#34;通过&#34;,&#34;不通过&#34;)))”，再将该公式复制到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级嵌套函数。
<hr/>17 生成随机数字和字母，直到生成随机密码的方法---随机函数
实际工作中，我们可能会遇到下面这些问题
1.当你在做excel学习训练时，你需要一些演示数据，这些数据最好是一些随机的数字，而不是我们自己输入的，这样就可以避免人为输入干扰数据的代表性
2.我们组织一场考试，从众多题库中随机抽取100道题目，同时应用随机数的方式，给考生排座位
3.我们需要设置一个临时用的6位或者更多位字母与数字组合的密码
我们就给大家演示Excel是如何轻易解决上述问题的
随机函数
应用Excel提供的随机函数就可以解决上述问题
Excel提供了2个用于产生随机数的函数
RAND函数： RAND()函数没有参数，它返回的是大于等于0，小于1的随机数，且产生的随机小数几乎不会重复。
RANDBETWEEN函数：该函数有2个参数，分别对应上限和下限，用于确定需要产生随机数的范围，这个函数产生的是随机整数。
1.基础功能演示
① 应用RAND函数产生一组随机小数
请看演示
② 应用RANDBETWEEN函数产生一组50-100之间的整数
请看我们的演示
2.随机生成字母
① 随机生成大写字母
在ANSI字符集中A-Z的代码为65-90，因此我们先产生65-90范围的随机数，然后用CHAR函数，将数字转换为字母，请看我们的演示
② 随机生成小写字母
在ANSI字符集中小写字母a-z的代码为97-122，因此我们先产生97-122范围的随机数，然后用CHAR函数，将数字转换为字母，请看我们的演示
3.生成指定小数位数的随机数
与ROUND函数（按指定位数四舍五入）结合，生成指定小数位数的随机数
①生成4位小数
请看我们的演示
②生成指定大小的小数
请看我们的演示
4.生成随机的数字与大小写字母组合
现在一般要求我们设置的密码都是数字与大小写字母的组合，这样才相对安全一些
在学会这个技能之前，我们先了解这几个基础函数的功能
1. ROW函数，返回一个引用的行号，比如=ROW(C5)的返回值就是5
2. INT函数，取整函数，如INT(4.6)=4
3. LARGE函数，返回数据组中第K个最大值，例如，第5个最大值
4. CHAR函数，根据本机中的字符集，返回由代码数字指定的字符
这个功能中，我们要用到如下的公式：
=CHAR(LARGE(ROW(1:26)+{31,64,96},INT(RAND()*62+1)))
其中，在ANSI字符集里面，数字0-9的代码为48-57，字母A-Z的代码为65-90，小写字母a-z的代码为97-122
上面的公式是利用ROW函数产生1-26的数字再加上{31,64,96}就可以生成32-57、65-90、97-122的字符代码数字集合
再利用LARGE函数，从大到小利用随机函数产生1-62的随机数，从而过滤掉32-47之间的代码值，就必定包含所有的数字和字母的代码值，最后用CHAR函数转换得到结果
请看演示
因为公式太长，我们将写好的公式，复制到工作表中
通过上面的操作，我们产生了6位随机的数字、字符、字母的组合，接下来我们把这个随机组合合并成一个字符串，这个字符串会随着表格的变化而动态更新，请看我们的演示
总 结
随机函数的使用方法很简单，但应用场景却很广泛，可以与很多其他函数结合使用 |
|
发表于 2022-12-13 10:41:24