Excel CountIf函数的使用方法,包含与If、Sum组合实现统计不重复与多条件实例

转载 kaixinit  2020-01-10 10:37  阅读 642 次

在 Excel 中,CountIf函数用于统计指定范围内满足某个条件的单元格个数;它只有两个参数,一个是统计范围,另一个是条件,其中条件不能超过 255 个字符,如果超过需要分开。条件中仅为 *、?、>、< 等,可以完成一些特殊的统计。

CountIf函数如果要实现多条件的的统计需要与 If、OffSet、Sum函数组合,并且有时条件需用数组,例如:Sum + If + CountIf函数组合实现统计不为空且不包含重复值的个数,Sum + CountIf + 数组条件实现同一范围内满足两个条件的统计。

 

一、Excel CountIf函数语法

1、表达式:COUNTIF(Range, Criteria)

中文表达式:COUNTIF(统计区域, 条件)

 

2、说明:

A、Range 可以是数组或单元格的引用。

B、条件可以是单个数字或文本,也可以为表达式或函数;但条件中不能超过 255 个字符,否则会返回错误;如果条件超过 255 个字符,可以把它们拆分为多个长字符串,然后用 Concatenate 函数或连接运算符 & 把它们连接起来,例如:"长字符中1……"&"长字符串2……”。另外,条件忽略字母大小写。

C、在条件中,可以使用通配符问号(?)和星号(*),问号表示任意一个字符,星号表示任意一个或一串字符;如果要查找问号或星号,需要在它们前面加转义字符~;如:查找问号,表达式应该这样写 ~?;查找星号,表达式应该这样写 ~*。

D、统计文本个数时,不能包含前导与尾部空格,也不能出现单双引号不一致和非打印字符;否则,CountIf函数可能返回不可预期的值。统计文本前或后有空格时,可以用Trim函数把它们去掉;有非打印字符时,可以用Clean函数把它们去掉 。

 

 

二、Excel CountIf函数的使用方法及实例

(一)条件为文本和统计空值的实例

1、双击 D2 单元格,把公式 =COUNTIF(C1:C11,"经理") 复制到 D2,按回车,返回统计结果 3;双击 D2,把双引号内的“经理”去掉,按回车,返回统计结果 1;操作过程步骤,如图1所示:

图1

2、公式说明:

在 =COUNTIF(C1:C11,"经理") 中,C1:C11 为统计区域,"经理"为条件,意思是:在 C1 至 C11 中统计“经理”的个数;当条件为 "" 时,统计空值的个数,C1:C11 中只有一个空单元格,因此统计结果为 1。

 

(二)条件为表达式的实例

1、假如要统计水果销量大于等于 1000 的个数。双击 D8 单元格,把公式 =COUNTIF(D2:D7,">=1000") 复制到 D8,按回车,返回统计结果 5;操作过程步骤,如图2所示:

图2

2、公式 =COUNTIF(D2:D7,">=1000") 中的条件为 ">=1000",意思是:在 D2:D7 中统计数值大于等于 1000 的个数;另外,">=1000" 还可以这样写 ">"&"1000",即把大于号与数字分开写,再用连接运算符把它们连接起来。

 

(三)条件中有函数的实例

1、假如要统计水果销量大于平均值的个数。双击 D8 单元格,把公式 =COUNTIF(D2:D7,">"&AVERAGE(D2:D7)) 复制到 D8,按回车,返回统计结果 3;操作过程步骤,如图3所示:

图3

2、公式 =COUNTIF(D2:D7,">"&AVERAGE(D2:D7)) 中的条件为 ">"&AVERAGE(D2:D7),条件中的 AVERAGE(D2:D7) 用于求 D2:D7 的平均值(结果为 1790.5),然后用 & 把大于号跟它连起来组成条件,即 ">1790.5"。

 

(四)条件中有通配符 ? 和 * 的实例

1、假如要统计姓李的员工个数。双击 D2 单元格,把公式 =COUNTIF(A2:A10,"李??") 复制到 D2,按回车,返回统计结果 2;双击 D2,把公式中的 ?? 改为 *,按回车,也返回统计结果 2;操作过程步骤,如图4所示:

图4

2、公式 =COUNTIF(A2:A10,"李??") 中的条件为 "李??",意思是“以李字开头且李字后仅有两个字”;当把条件改为"李*"后,意思是“以李字开头即可”,李字后可以有一个或多个任意字符。条件中有通配符 ? 和 * 的情况在前面的篇章中已介绍多次,如果想了解更多的查找方式,可以参考《Excel HLookUp函数的使用方法,包含首行需排序、返回#N/A及与Match组合/非首行实例》一文。

 

3、条件为 * 和 ?* 的实例

A、条件为 * 的实例。双击 E10 单元格,把公式 =COUNTIF(E1:E9,"*") 复制到 E10,按回车,返回统计结果 2;双击 E9,把返回空("")的公式去掉,按回车,统计结果变为 1;再把 E1 中的文字删除,统计结果变为 0,演示如图5所示:

图5

B、条件为 ?* 的实例。双击 E10 单元格,把公式 =COUNTIF(E1:E9,"?*") 复制到 E10,按回车,返回统计结果 1;双击 E9,把 "" 改为" ",统计结果变为 2;再把 E1 中的文字删除,统计结果变为 1,演示如图6所示:

图6

* 和 ?* 的区别:* 可以是一个或多字符(包括空值""),?* 至少要有两个字或空值"",演示中已经说明这两个问题。

 

(五)几个特殊的实例

1、双击 B11 单元格,把公式 =COUNTIF(B2:B10,">=!") 复制到 B11,按回车,返回统计结果 7;双击 B10,它是一个空单元格,再双击 B9,里面有一个公式,双击 B7,按空格键在“财务部”前加一个空格,按回车,统计结果变为 7;把公式 =COUNTIF(B2:B10,"><") 复制到 B12,按回车,返回统计结果 6,双击 B7,把“财务部”前的空格删除,B11 和 B12 中的统计结果都变为 7;双击 B13,把公式 =COUNTIF(B2:B10,">""") 复制到 B13,按回车,返回统计结果 7,双击 B7,再次在“财务部”前加一个空格,B11、B12 和 B13 的统计结果都变为 7;双击 C11,把公式 =COUNTIF(B2:B10,"<>") 复制到 C11,按回车,返回统计结果 8;操作过程步骤,如图7所示:

图7

2、公式说明:

公式 =COUNTIF(B2:B10,">=!")、 =COUNTIF(B2:B10,"><") 和 =COUNTIF(B2:B10,">""") 的统计结果一样,它们都不包含有公式的单元格、空单元格和文字前有空格的单元格;而公式 =COUNTIF(B2:B10,"<>") 包含有公式的单元格,不包含空单元格。

 

三、Excel CountIf函数的扩展使用实例

(一)Sum + If + CountIf函数组合实现统计不为空且不包含重复值的个数

1、双击 B9 单元格,把公式 =SUM(IF(B2:B8<>"",1/COUNTIF(B2:B8,B2:B8))) 复制到 B9,按 Ctrl + Shift + 回车,返回统计结果 3,操作过程步骤,如图8所示:

图8

 

2、公式 =SUM(IF(B2:B8<>"",1/COUNTIF(B2:B8,B2:B8))) 说明:

A、B2:B8 用于以数组形式返回 B2 到 B8 中的所有值,即 {"财务部";"销售部";"行政部";"销售部";"行政部";"财务部";"行政部"}。

B、COUNTIF(B2:B8,B2:B8) 执行时,第一次从 B2:B8 中取出 B2(即“财务部”),然后统计 B2 在 B2:B8 中的个数,结果为 2;第二次统计 B3 在 B2:B8 中的个数,结果为 2;其它的以此类推,最后返回 {2;2;3;2;3;2;3}。

C、1/COUNTIF(B2:B8,B2:B8) 变为 1/{2;2;3;2;3;2;3},接着用 1 除以数组中的每一个元素,结果返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333};这一步的作用是把重复值的总数看为 1,例如重复次为 2 的,每个 1/2 等于 0.5,进一步计算再把两个 0.5 加起来,恰好是 1,即去掉一个重复项。

D、B2:B8<>"" 意思是:每次从 B2:B8 中取出一个元素,如果不为空,返回 True,否则返回 False;最后返回{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}。

E、则公式变为 =SUM(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333})),接着从 If 的条件数组中取出第一个元素 TRUE,由于为真,所以返回第二个数组中对应的元素 0.5;直到取完条件数组中的每个,最后返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333};则公式变为 =SUM({0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}),最后用 Sum 对数组中的每个元素求和,结果为 3。

 

(二)OffSet + CountIf函数组合使用实现分段统计

OffSet + CountIf 实现分段统计请查看《Excel OffSet函数的使用方法,含与Sum、Match、CountIf、If、Or、Row组合实现动态求和、一个重复多个、分段统计和断码检查》一文。

 

(三)CountIf函数多条件统计

1、假如要统计销售额在 5 万到 8 万的销售员个数。双击 C9 单元格,把公式 =SUM(COUNTIF(C2:C8,{">=50000",">80000"})*{1,-1}) 复制到 C9,按回车,返回统计结果 3,操作过程步骤,如图9所示:

图9

 

2、公式 =SUM(COUNTIF(C2:C8,{">=50000",">80000"})*{1,-1}) 说明:

A、C2:C8 为 CountIf 的统计范围,{">=50000",">80000"} 为条件,该条件为数组,共包含两个条件,第一个条件为 ">=50000",第二个条件为 ">80000",意思是:先在 C2:C8 中统计出大于等于 50000 的个数,结果为 4;再统计出大于 80000 的个数,结果为 1。

B、则公式变为 =SUM({4,1}*{1,-1}),接着,把两个数组的对应元素相乘,公式变为 =SUM({4,-1}),最后对数组求和,结果为 3。这个方法的关键为:先统计出所有大于等于 5 万的个数,再统计出大于 8 万的个数,然后用前者减后者。

 

3、统计其它范围的公式分别为:

A、大于 50000 且小于 80000:=SUM(COUNTIF(C2:C8,{">50000",">=80000"})*{1,-1})

B、大于等于 50000 且小于 80000:=SUM(COUNTIF(C2:C8,{">=50000",">=80000"})*{1,-1}) 或 =SUM(COUNTIF(C2:C8,">="&{50000,80000})*{1,-1})

C、大于 50000 且小于等于 80000:=SUM(COUNTIF(C2:C8,{">50000",">80000"})*{1,-1}) 或 =COUNTIF(C2:C8,">50000")- COUNTIF(C2:C8,">80000")

本文地址:http://www.kaixinit.com/info/office/3851.html
温馨提示:文章内容系作者个人观点,不代表开心电脑网对观点赞同或支持。
版权声明:本文为转载文章,来源于 kaixinit ,版权归原作者所有,欢迎分享本文,转载请保留出处!

发表评论


表情