Excel中条件统计函数介绍

本文转载自公众号：Excel表哥之家，作者：大表哥。

相信大家在Excel中都会用到Sum、Count、Average、Max、Min等统计类函数，如果想仅仅对区域中符合一定条件的单元格统计，该怎么办呢？这就需要用到本文介绍的条件统计函数，主要如下：

函数名	函数功能
SUMIF	条件求和
SUMIFS	多条件求和
COUNTIF	条件计数
COUNTIFS	多条件计数
AVERAGEIF	条件平均值
AVERAGEIFS	多条件平均值
MINIFS	特定条件求最小值(仅Excel 2016版本)
MAXIFS	特定条件求最大值(仅Excel 2016版本)

【1】SUMIF与SUMIFS函数

• SUMIF(range，criteria，[sum_range])

  函数的参数如下：

  Range：条件区域，用于条件判断的单元格区域

  Criteria：求和条件，由数字、逻辑表达式等组成的判定条件

  Sum_range：实际求和区域，需要求和的单元格、区域或引用

  当省略Sum_range时，则条件区域就是实际求和区域

  
  

  
  

  特别说明：Criteria参数中可以使用通配符（包括问号 (?) 和星号 (*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，在该字符前键入波形符 (~)。
  

  
  

• SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  函数的参数如下：

  sum_range ：需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用。忽略空白值和文本值；

  criteria_range1：第一个条件区域

  criteria1：第一个条件，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为66、“66”、“>66”、A2、'张？？'

  criteria_range2：第二个条件区域

  criteria2：第二个条件，必须与条件区域成对出现
  

举例：

【2】COUNTIF与COUNTIFS函数

• COUNTIF(range,criteria)

  用途：对指定区域中符合指定条件的单元格计数

  函数的参数如下：
  

  Range：用于进行条件计数的单元格区域

  Criteria：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本

• COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)

举例：

一、求各种类型单元格的个数

(1) 求真空单元格个数:=COUNTIF(数据区,'=')

(2) 非真空单元格个数: =COUNTIF(数据区,'<>') 相当于counta()函数

(3) 文本型单元格个数: =COUNTIF(数据区,'*') 假空单元格也是文本型单元格

说明：

真空单元格：指什么内容也没有的单元格

假空单元格：指0字符的空文本

二、求大于或小于某个值的单元格个数

(1) 大于50=COUNTIF(数据区,'>50')

(2) 大于A3单元格的值 =COUNTIF(数据区,'>'&$A$3)

(3) 等于A3单元格的值 =COUNTIF(数据区,$A$3)

(4) 小于A3单元格的值 =COUNTIF(数据区,'<'&$A$3)

三、等于或包含某N个特定字符的单元格个数

(1) 两个字符 =COUNTIF(数据区,'??')

(2) 两个字符并且第2个是B=COUNTIF(数据区,'?B')

(3) 包含B =COUNTIF(数据区,'*B*')

(4) 第2个字符是B =COUNTIF(数据区,'?B*')

注：countif()函数对英文字母不区分大小写，通配符只对文本有效

【3】AVERAGEIF与AVERAGEIFS函数

• AVERAGEIF(range, criteria, [average_range])

  函数的参数如下：

  Range：要计算平均值的单元格区域

  Criteria：条件值，形式为数字、表达式、单元格引用或文本的条件

  Average_range：可选。 计算平均值的实际单元格组。 如省略，则使用 range。

  
  

• AVERAGEIFS(average_range,criteria_range1,criteria1,crileria_range2,criteria2,...)

说明：

忽略区域中包含 TRUE 或 FALSE 的单元格。

如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。

如果 range 为空值或文本值，AVERAGEIF 将返回错误值 #DIV0! 。

如果条件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值。

如果区域中没有满足条件的单元格，AVERAGEIF 将返回错误值 #DIV/0! 。

【4】MINIFS与MAXIFS函数

大家熟悉的MIN()和MAX()，就是获取某区域的最小最大值用的。Excel 2016版本新增加的MINIFS()、MAXIFS()，是为方便获取特定条件下的最小最大值增加的。

• MAXIFS(max_range, criteria_range1,criteria1, [criteria_range2, criteria2], ...)

• MINIFS(min_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)

举例： 

统计设计部，男员工的年龄最小值、最大值：

=MINIFS(E:E,B:B,'设计部',C:C,'男')

=MAXIFS(E:E,B:B,'设计部',C:C,'男')

需要特别注意的是，MINIFS()、MAXIFS()只是在Excel 2016版本中才有的函数，如果你使用的Excel版本较低，就不能使用这两个函数进行条件统计。为满足大家实际需要，本文提供低版本的替代方案如下：

• 条件最大值公式：

{=MAX(数值区域*条件1*条件2*...)}

{=MAX(E:E*(B:B='设计部')*(C:C='男'))}

• 条件最小值公式：

{=MIN(IF(条件1*条件2*...),数值区域)}

{=MIN(IF((B:B='设计部')*(C:C='男')),E:E)}

说明：

• 上述替代方案使用的数组公式，输入完公式后需要同时按下Ctrl Shift Enter三个键才行，数组公式可参考《Excel数组公式》学习；

• 条件最小值比条件最大值多加个IF判断，是为了剔除不符合条件计算出来的0值；

• 可结合《Excel中最大值和最小值函数介绍》灵活应用。