SUMIF 等特殊函数的公式语法和用法.docx
《SUMIF 等特殊函数的公式语法和用法.docx》由会员分享，可在线阅读，更多相关《SUMIF 等特殊函数的公式语法和用法.docx（49页珍藏版）》请在冰豆网上搜索。
SUMIF等特殊函数的公式语法和用法
特殊函数的使用
一、统计函数（参考）
1.COUNT函数
COUNT函数计算包含数字的单元格以及参数列表中数字的个数。
使用函数COUNT可以获取区域或数字数组中数字字段的输入项的个数。
例如，输入以下公式可以计算区域A1:
A20中数字的个数：
=COUNT（A1:
A20）
在此示例中，如果该区域中有五个单元格包含数字，则结果为5。
1）语法：
COUNT（value1,[value2],...）
value1必需。
要计算其中数字的个数的第一个项、单元格引用或区域。
value2,...可选。
要计算其中数字的个数的其他项、单元格引用或区域，最多可包含255个。
2）注释这些参数可以包含或引用各种类型的数据，但只有数字类型的数据才被计算在内。
3）说明
如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如"1"），则将被计算在内。
逻辑值和直接键入到参数列表中代表数字的文本被计算在内。
如果参数为错误值或不能转换为数字的文本，则不会被计算在内。
如果参数为数组或引用，则只计算数组或引用中数字的个数。
不会计算数组或引用中的空单元格、逻辑值、文本或错误值。
若要计算逻辑值、文本值或错误值的个数，请使用COUNTA函数。
若要只计算符合某一条件的数字的个数，请使用COUNTIF函数或COUNTIFS函数。
4）示例
1
2
3
4
5
6
7
8
9
10
A
B
C
数据
销售
2008-12-8
19
22.24
TRUE
#DIV/0!
公式
说明
结果
=COUNT（A2:
A8）
计算单元格区域A2到A8中包含数字的单元格的个数。
3
=COUNT（A5:
A8）
计算单元格区域A5到A8中包含数字的单元格的个数。
2
=COUNT（A2:
A8,2）
计算单元格区域A2到A8中包含数字和值2的单元格的个数
4
2.COUNTIF函数
COUNTIF函数对区域中满足单个指定条件的单元格进行计数。
例如，可以对以某一字母开头的所有单元格进行计数，也可以对大于或小于某一指定数字的所有单元格进行计数。
例如，假设有一个工作表在列A中包含一列任务，在列B中包含分配了每项任务的人员的名字。
可以使用COUNTIF函数计算某人员的名字在列B中的显示次数，这样便可确定分配给该人员的任务数。
例如：
=COUNTIF（B2:
B25,"Nancy"）
1）注释若要根据多个条件对单元格进行计数，请参阅COUNTIFS函数。
2）语法：
COUNTIF（range,criteria）
range必需。
要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。
空值和文本值将被忽略。
criteria必需。
用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。
例如，条件可以表示为32、">32"、B4、"苹果"或"32"。
3）注释
在条件中可以使用通配符，即问号（?
）和星号（*）。
问号匹配任意单个字符，星号匹配任意一系列字符。
若要查找实际的问号或星号，请在该字符前键入波形符（~）。
条件不区分大小写；例如，字符串"apples"和字符串"APPLES"将匹配相同的单元格。
4）示例
示例1：
通用COUNTIF公式
如果将示例复制到一个空白工作表中，可能会更容易理解该示例。
1
2
3
4
5
6
7
8
9
10
A
B
C
数据
数据
苹果
32
橙子
54
桃子
75
苹果
86
公式
说明
结果
=COUNTIF（A2:
A5,"苹果"）
单元格区域A2到A5中包含“苹果”的单元格的个数。
2
=COUNTIF（A2:
A5,A4）
单元格区域A2到A5中包含“桃子”的单元格的个数。
1
=COUNTIF（A2:
A5,A3）+COUNTIF（A2:
A5,A2）
单元格区域A2到A5中包含“橙子”和“苹果”的单元格的个数。
3
=COUNTIF（B2:
B5,">55"）
单元格区域B2到B5中值大于55的单元格的个数。
2
=COUNTIF（B2:
B5,"<>"&B4）
单元格区域B2到B5中值不等于75的单元格的个数。
3
=COUNTIF（B2:
B5,">=32"）-COUNTIF（B2:
B5,">85"）
单元格区域B2到B5中值大于或等于32且小于或等于85的单元格的个数。
3
示例2：
在COUNTIF公式中使用通配符和处理空值
1
2
3
4
5
6
7
8
9
10
11
A
B
C
数据
数据
苹果
是
橙子
否
桃子
否
苹果
是
公式
说明
结果
=COUNTIF（A2:
A7,"*果"）
单元格区域A2到A7中以字母“es”结尾的单元格的个数。
4
=COUNTIF（A2:
A7,"?
果"）
单元格区域A2到A7中以“果”结尾且恰好有7位字符的单元格的个数。
2
=COUNTIF（A2:
A7,"*"）
单元格区域A2到A7中包含任何文本的单元格的个数。
4
=COUNTIF（A2:
A7,"<>"&"*"）
单元格区域A2到A7中不包含任何文本的单元格的个数。
2
=COUNTIF（B2:
B7,"否"）/ROWS（B2:
B7）
单元格B2到B7中“否”选票（包括空单元格）的平均数。
0.333333333
=COUNTIF（B2:
B7,"是"）/（ROWS（B2:
B7）-COUNTIF（B2:
B7,"<>"&"*"））
单元格B2到B7中“是”选票（不包括空单元格）的平均数。
0.5
注释若要以百分比形式查看数字，请选择相应的单元格，然后在“开始”选项卡上的“数字”组中，单击“百分比样式”
。
求及格率、优秀率：
（设学生成绩在B2：
B27）
在B28输入公式：
countif（B2:
B27,”>=60”）/countif（B2:
B27）*100及格率
countif（B2:
B27,”>=90”）/countif（B2:
B27）*100优秀率
3.COUNTIFS函数：
1）说明：
将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。
2）语法：
COUNTIFS（criteria_range1,criteria1,[criteria_range2,criteria2]…）
criteria_range1必需。
在其中计算关联条件的第一个区域。
criteria1必需。
条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。
例如，条件可以表示为32、">32"、B4、"苹果"或"32"。
criteria_range2,criteria2,...可选。
附加的区域及其关联条件。
最多允许127个区域/条件对。
3）要点每一个附加的区域都必须与参数criteria_range1具有相同的行数和列数。
这些区域无需彼此相邻。
注解
每个区域的条件一次应用于一个单元格。
如果所有的第一个单元格都满足其关联条件，则计数增加1。
如果所有的第二个单元格都满足其关联条件，则计数再增加1，依此类推，直到计算完所有单元格。
如果条件参数是对空单元格的引用，COUNTIFS会将该单元格的值视为0。
您可以在条件中使用通配符，即问号（?
）和星号（*）。
问号匹配任一单个字符；星号匹配任一字符序列。
如果要查找实际的问号或星号，请在字符前键入波形符（~）。
4）示例
示例1
1
2
2
3
4
5
6
7
8
A
B
C
D
销售人员
超出苹果汁配额
超出牛奶配额
超出酱油配额
王伟
是
否
否
赵军
是
是
否
张颖
是
是
是
李芳
否
是
是
公式
说明
结果
=COUNTIFS（B2:
D2,"=是"）
计数王伟超出苹果汁、牛奶和酱油销售配额的次数。
1
=COUNTIFS（B2:
B5,"=是",C2:
C5,"=是"）
计算有多少销售人员同时超出其苹果汁和牛奶配额。
2
=COUNTIFS（B5:
D5,"=是",B3:
D3,"=是"）
计数李芳和赵军超出苹果汁、牛奶和酱油销售配额的次数。
1
示例2
1
2
3
4
5
6
7
8
9
10
A
B
C
数据
数据
1
5/1/2008
2
5/2/2008
3
5/3/2008
4
5/4/2008
5
5/5/2008
6
5/6/2008
公式
说明
结果
=COUNTIFS（A2:
A7,"<6",A2:
A7,">1"）
计算1和6之间（不包括1和6）有几个数包含在单元格A2到A7中。
4
=COUNTIFS（A2:
A7,"<5",B2:
B7,"<5/3/2008"）
计算单元格A2到A7中包含小于5的数，同时在单元格B2到B7中包含早于5/3/2008的日期的行数。
2
=COUNTIFS（A2:
A7,"<"&A6,B2:
B7,"<"&B4）
说明与前例相同，但在条件中使用单元格引用而非常量。
2
4.COUNTA
1）说明：
COUNTA函数计算区域（区域：
工作表上的两个或多个单元格。
区域中的单元格可以相邻或不相邻。
）中不为空的单元格的个数。
2）语法：
COUNTA（value1,[value2],...）
value1必需。
表示要计数的值的第一个参数。
value2,...可选。
表示要计数的值的其他参数，最多可包含255个参数。
3）注解：
COUNTA函数可对包含任何类型信息的单元格进行计数，这些信息包括错误值和空文本（""）。
例如，如果区域包含一个返回空字符串的公式，则COUNTA函数会将该值计算在内。
COUNTA函数不会对空单元格进行计数。
如果不需要对逻辑值、文本或错误值进行计数（换句话说，只希望对包含数字的单元格进行计数），请使用COUNT函数。
如果只希望对符合某一条件的单元格进行计数，请使用COUNTIF函数或COUNTIFS函数。
示例：
1
2
3
4
5
6
7
8
9
10
A
B
C
数据
销售
2008-12-8
19
22.24
TRUE
#DIV/0!
公式
说明
结果
=COUNTA（A2:
A8）
计算单元格区域A2到A8中非空单元格的个数。
6
二、求和：
（SUM、SUMIF、SUMIFS）
1.SUMIF函数：
1）说明：
使用SUMIF函数可以对区域（区域：
工作表上的两个或多个单元格。
区域中的单元格可以相邻或不相邻。
）中符合指定条件的值求和。
例如，假设在含有数字的某一列中，需要让大于5的数值相加，请使用以下公式：
=SUMIF（B2:
B25,">5"）
在本例中，应用条件的值即要求和的值。
如果需要，可以将条件应用于某个单元格区域，但却对另一个单元格区域中的对应值求和。
例如，使用公式=SUMIF（B2:
B5,"John",C2:
C5）时，该函数仅对单元格区域C2:
C5中与单元格区域B2:
B5中等于“John”的单元格对应的单元格中的值求和。
注释若要根据多个条件对若干单元格求和，请参阅SUMIFS函数。
2）语法：
SUMIF（range,criteria,[sum_range]）
range必需。
用于条件计算的单元格区域。
每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。
空值和文本值将被忽略。
criteria必需。
用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。
例如，条件可以表示为32、">32"、B5、32、"32"、"苹果"或TODAY（）。
要点任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号（"）括起来。
如果条件为数字，则无需使用双引号。
sum_range可选。
要求和的实际单元格（如果要对未在range参数中指定的单元格求和）。
如果sum_range参数被省略，Excel会对在range参数中指定的单元格（即应用条件的单元格）求和。
3）注释
sum_range参数与range参数的大小和形状可以不同。
求和的实际单元格通过以下方法确定：
使用sum_range参数中左上角的单元格作为起始单元格，然后包括与range参数大小和形状相对应的单元格。
4）例如：
如果区域是
并且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？
可以在criteria参数中使用通配符（包括问号（?
）和星号（*））。
问号匹配任意单个字符；星号匹配任意一串字符。
如果要查找实际的问号或星号，请在该字符前键入波形符（~）。
示例1
1
2
3
4
5
6
7
8
A
B
C
属性值
佣金
数据
100,000
7,000
250,000
200,000
14,000
300,000
21,000
400,000
28,000
公式
说明
结果
=SUMIF（A2:
A5,">160000",B2:
B5）
属性值高于160,000的佣金之和。
63,000
=SUMIF（A2:
A5,">160000"）
高于160,000的属性值之和。
900,000
=SUMIF（A2:
A5,300000,B2:
B5）
属性值等于300,000的佣金之和。
21,000
=SUMIF（A2:
A5,">"&C2,B2:
B5）
属性值高于单元格C2中值的佣金之和。
49,000
示例2
1
2
3
4
5
6
7
8
9
10
A
B
C
类别
食物
销售额
蔬菜
西红柿
2300
蔬菜
西芹
5500
水果
橙子
800
黄油
400
蔬菜
胡萝卜
4200
水果
苹果
1200
公式
说明
结果
=SUMIF（A2:
A7,"水果",C2:
C7）
“水果”类别下所有食物的销售额之和。
2000
=SUMIF（A2:
A7,"蔬菜",C2:
C7）
“蔬菜”类别下所有食物的销售额之和。
12000
=SUMIF（B2:
B7,"西*",C2:
C7）
以“西”开头的所有食物（西红柿、西芹）的销售额之和。
4300
=SUMIF（A2:
A7,"",C2:
C7）
未指定类别的所有食物的销售额之和。
400
2.多条件求和sumifs
说明：
sumifs函数是在excel中用来通过多个条件筛选，将指定区域符合的数据进行求和的一个函数。
在区域（区域:
工作表上的两个或多个单元格。
区域中的单元格可以相邻或不相邻。
）中添加满足多个条件的单元格。
这样看起来有点迷茫，不过不要着急，英文版文档直译就是这么让人费解，下面咱们举个栗子~！
A
B
C
1
苹果
入库
10
2
香蕉
入库
8
3
苹果
出库
6
3
香蕉
出库
3
使用实例
如果要计算，苹果的总入库数，因为源数据都在一个表里面，又要用品名、进出，两种条件进行筛选，所以我们这里单纯用sumif无法满足我们的要求，所以就要用到sumifs函数，计算所有苹果入库的数量：
=SUMIFS（C:
C,A:
A,"苹果",B:
B,"入库"）
计算所有香蕉出库的数量：
=SUMIFS（C:
C,A:
A,"香蕉",B:
B,"出库"）
函数参数：
SUMIFS（sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...）
∙sum_range：
必需。
对一个或多个单元格求和，包括数字或包含数字的名称、名称、区域或单元格引用（单元格引用:
用于表示单元格在工作表上所处位置的坐标集。
例如，显示在第B列和第3行交叉处的单元格，其引用形式为“B3”。
）。
空值和文本值将被忽略。
∙criteria_range1：
必需。
在其中计算关联条件的第一个区域。
∙criteria1：
必需。
条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。
例如，条件可以表示为32、”>32″、B4、”苹果”或“32″。
∙criteria_range2,criteria2,…可选。
附加的区域及其关联条件。
最多允许127个区域/条件对。
总结：
工作中用的比较频繁的一个函数，看似复杂其实很简单，只要会一个条件、就会N（N小于127）个条件，
3.用SUMIF函数实现按指定条件求平均值
例如在年级段总成绩表中计算某科教师所教的所有班级成绩的平均分（如5到8班化学老师的平均分），就可以利用如下方法实现：
在准备放该化学教师所教所有班级平均分的单元格中输入：
=SUMIF（K2:
K132,">4",G2:
G132）/COUNTIF（K2:
K132,">4"）回车即可，这里边用到了SUMIF和COUNTIF两个函数。
（如图1）
SUMIF函数是按给定条件对指定单元格进行求和的函数。
其语法格式是：
SUMIF（range,criteria,sum_range），range是要根据条件进行计算的单元格区域，每个区域中的单元格都必须是数字和名称、数组和包含数字的引用，空值和文本值将被忽略。
criteria是指对range指定的区域实行什么条件，其形式可以为数字、表达式或文本。
如条件可以表示为32、"32"、">32"或"apples";sum_range是要进行相加的实际单元格，如果省略Sum_range，则当区域中的单元格符合条件时，它们既按条件计算，也执行相加。
注意：
Sum_range与Range的大小和形状可以不同，相加的实际单元格从sum_range中左上角的单元格作为起始单元格，然后包括与range大小和形状相对应的单元格。
公式中range是指“K2:
K132”，也就是“班级”这列所有单元格;criteria是指“">4"”，意思是指班级数大于4的5、6、7、8班;而sum_range是指“化学”这列成绩，意思是对符合“班级”条件的化学分数求和。
而整个SUMIF函数公式是计算所有班级为5、6、7和8班的同学的化学成绩的总和。
COUNTIF函数是统计指定区域中满足给定条件的单元格个数的函数。
其语法格式是：
COUNTIF（range,criteria），range是一个或多个要计数的单元格，其中包括数字或名称、数组或包含数字的引用，空值和文本值将被忽略。
criteria是指按什么条件进行统计，其形式可以为数字、表达式、单元格引用或文本。
公式中range是指“K2:
K132”，也就“班级”这列所有单元格;criteria是指“">4"”，意思也指班级数大于4的5、6、7、8班。
整个COUNTIF函数公式是统计班级这列中班级数为5、6、7和8班的同学的总人数。
而整个公式就是等于所有班级为5、6、7和8班的同学的化学成绩总和除以班级数为5、6、7和8班的同学的总人数。
如果每科教师所教的班级一样，还可以在公式中加入$，以便进行拖动填充（$为绝对应用，拖动公式时所引应用的单元格不发生变化），如：
=SUMIF（$K$2:
$K$132,">4",G2:
G132）/COUNTIF（$K$2:
$K$132,">4"）。
（如图2）
使用SUMIF求和，再用COUNTIF求个数，二者相除即可。
4.用SUMIFS函数实现按多指定条件求平均值
求C5-C27中小于C30*1大于等于C30*0.95的算术平均值
求函数公式：
=AVERAGE（IF（（C5:
C27C27>=C30*0.95）,C5:
C27））
按住[Ctrl][Shift]二键再按[Enter]
=SUMIFS（c5:
c27,c5:
c27,">="&c30*.95,c5:
c27,"<"&c30*1）/COUNTIFS（c5:
c27,c5:
c27,">="&c30*.95,c5:
c27,"<"&c30*1）
5.使用SUMIF函数和COUNTIF函数跨表求满足条件的平均值实例
在Excel中求平均数，有一个最为简单最为直接的函数，即Average即可实现！
该函数虽然操作简单，功能强大，仍然美中不足，使用它很难求得满足一定条件的平均数，即智能性不大！
但是在工作当中，我们却屡屡遇到比较棘手的问题，使用一般的函数很难让工作产生效益！
本例：
使用SUMIF函数和COUNTIF函数跨表求满足条件的平均值！
汇总表，表中仅存四列！
成绩表：
表中仅有学生的基本信息及其对应的科目成绩！
纵观以上两表，我们要实现的目标是：
根据汇总表，我们得知，将求取对应教师所教班级对应科目的平均成绩，如“陆青山”这位老师，所教班级为1、2班，所教学科为化学，下面我们以该教师为例，将从成绩表中求取班级为1、2班、科目为化学的学生平均成绩，并将平均分显示到汇总表中！
在汇总表中，选择D2单元格，之后在fX函数输入框中输入如下图的综合函数即可！
代码为：
=SUMIF（成绩表!
A2:
A16,"<=2",成绩表!
G2:
G16）/COUNTIF（成绩表!
A2:
A16,"<=2"）
SUMIF（成绩表!
A2:
A16,"<=2"，<=2代表班级号小于等于2的班级，这里是查找条件；查找的范围为“成绩表!
A2:
A16”，从A2查找到A16，这是查找的数据区域！
成绩表!
G2:
G16，指的是科目成绩查找范围为G列，即化学列，范围从G2到G16，与前面的范围保持一致！
综合函数意思是：
先使用SUMIF求得满足条件的总分，再使用COUNTIF求得满足条件的学生人数，最后使用“/”除号将两者进行相出，即得结果！
SUMIF函数是按给定条件对指定单元格进行求和的函数。
其语法格式是：
SUMIF（range,criteria,sum_range），ra
升级会员 