excel常用函数公式大全(excel常用函数公式说明)
利用IF函数做条件判断,选择性求和,按照指定条件计数,这些都是excel函数的重要功能
今天我们继续介绍4个常用函数的用法,原创码字不易,期待您的更多鼓励
- 条件检测函数(IF函数)
- 条件求和函数(SUMIF函数)
- 多条件求和函数(SUMIFS函数)
- 条件计数函数(COUNTIF函数)
条件检测函数(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,"优秀","")”,确认后,将公式复制到G4:G7区域即可。
实例演示2:
自制一个考试答案评分卡,我们首先将题号与标准答案录入,然后只要填入学生的答案,即可给出相应的评分。
实现方法:只要将学生的答案与标准答案作比较,两者一致即得分,否则不得分。
选取单元格E10,输入公式“=IF(D10=C10,10,0)”或公式“=IF(D10=C10,10,)”,确认后将公式复制到其他区域即可。
演示如下
总 结
我们介绍了Excel中的条件检测函数IF,此函数包含3个参数,其中参数1是判断的条件,条件成立则IF函数返回参数2的值,不成立则返回参数3的值。
参数2和参数3可以是公式、文本,也可以省略不写。
条件求和函数(SUMIF函数)
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,">160000",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,"=300000",B2:B5)”,即在A2:A5单元格区域中判断单元格的值是否等于300000,单元格值等于300000,则对B2:B5区域的对应单元格求和。所以返回值为210000
演示4:sum_range的范围小于range范围时
操作方法:输入公式“=SUMIF(A2:A5,"=300000",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,"=300000",B2:B3)”因为A4符合条件,所以返回B4的值21000。
我再举几个例子来帮你加深这两个区域概念的理解
例子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的演示。
多条件求和函数(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,"开发部",D2:D9,"男")”,回车确认即可。
公式中F2:F9对应函数的sum_range区域,也即需要求和的区域
C2:C9对应criteria_range1用于条件判断的单元格区域
“开发部”对应criteria1,为确定哪些单元格将被相加求和的条件
D2:D9对应criteria_range2用于条件判断的单元格区域
“男”对应criteria2,为确定哪些单元格将被相加求和的条件
总 结
我们介绍SUMIFS函数的功能、格式和参数,并对每个参数的意义给出了详细说明
通过实例演示,给出了SUMIFS函数在实际工作中的应用
需要注意的是:与SUMIF函数中的区域和条件参数不同,SUMIFS中每个criteria_range的大小和形状必须与sum_range相同
将SUMIFS函数与SUMIF函数的功能比对着学习,印象会更加深刻。
条件计数函数(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,">53")”,返回值为3;计算该区域中值为90的单元格个数用“=COUNTIF(A1:A5,90)”,或“=COUNTIF(A1:A5,"90")”,或“=COUNTIF(A1:A5,"=90")”,返回值均为1。
实例演示2:
在如图所示的工作表中,统计总评为“优秀”和“良好”的学生人数,并将结果显示在G2与H2单元格中
操作方法:单击G2单元格,输入公式“=COUNTIF(F2:F8,"优秀")”,确认即可
公式的含义:在F2:F8区域内统计内容为“优秀”的单元格个数
统计“良好”的学生人数操作方法相同
实例演示3:
继续上面的例子,请统计总评为优秀的学生人数与总人数的百分比,并将结果显示在G2单元格中
操作方法:单击G2单元格,输入公式“=COUNTIF(F2:F8,"优秀")/COUNT(E2:E8),确认即可
公式的含义:将F2:F8区域内内容为“优秀”的单元格个数除以E2:E8区域内含有数值的单元格的总个数,就计算出了优秀率
实例演示4:
请统计平均成绩70分到80分的学生人数(含70分,不含80分),并将结果显示在G2单元格中
操作方法:单击G2单元格,输入公式“=COUNTIF(E2:E8,">=70")-COUNTIF(E2:E8,">=80")”,确认即可
公式的含义:公式第一部分统计大于等于70分的人数(包含了大于等于80分的人),第二部分统计大于等于80分的人数,做减法后就求出70-80分之间的人数了。
多条件计数函数(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,">=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区域中进行条件判断,统计工作表中同时满足所有条件的个数