今天是统计函数(Subtotal)6个知识点及(Countif、Countifs)6个知识点的内容学习:
Subtotal
一基本用法
=Subtotal(Function_num,ref1,…)
1.Average 平均值 =SUBTOTAL(1,B2:B8)
2.Count 统计个数 =SUBTOTAL(2,B2:B8)
3.Counta 非空单元格数量 =SUBTOTAL(3,B2:B8)
4.MAX 最大值 =SUBTOTAL(4,B2:B8)
5.MIN 最小值 =SUBTOTAL(5,B2:B8)
6.Product 括号内数据的乘积 =SUBTOTAL(6,B2:B8)
7.Stder.s 标准偏差 =SUBTOTAL(7,B2:B8)
8.Stdevp.p 标准偏差 =SUBTOTAL(8,B2:B8)
9.SUM.S 求和 =SUBTOTAL(9,B2:B8)
10.VAR.S 方差 =SUBTOTAL(10,B2:B8)
11.VAR.P 方差 =SUBTOTAL(11,B2:B8)
注意:
在单元格输入=Subtotal(,就会提示上述语法
此函数并不是“一个函数”,而是“一群函数”
此函数是Excel中唯一一个能统计用户可见单元格的函数
二、Subtotal隐藏值
求和
=SUBTOTAL(9,B2:B8) 晗隐藏值
=SUBTOTAL(109,B2:B8) 不含隐藏值
晗隐藏值
1.Average 平均值 =SUBTOTAL(1,B2:B8)
2.Count 统计个数 =SUBTOTAL(2,B2:B8)
3.Counta 非空单元格数量 =SUBTOTAL(3,B2:B8)
4.MAX 最大值 =SUBTOTAL(4,B2:B8)
5.MIN 最小值 =SUBTOTAL(5,B2:B8)
6.Product 括号内数据的乘积 =SUBTOTAL(6,B2:B8)
7.Stder.s 标准偏差 =SUBTOTAL(7,B2:B8)
8.Stdevp.p 标准偏差 =SUBTOTAL(8,B2:B8)
9.SUM.S 求和 =SUBTOTAL(9,B2:B8)
10.VAR.S 方差 =SUBTOTAL(10,B2:B8)
11.VAR.P 方差 =SUBTOTAL(11,B2:B8)
不晗隐藏值
101.Average 平均值 =SUBTOTAL(101,B2:B8)
103.Counta 非空单元格数量 =SUBTOTAL(103,B2:B8)
104.MAX 最大值 =SUBTOTAL(104,B2:B8)
105.MIN 最小值 =SUBTOTAL(105,B2:B8)
106.Product 括号内数据的乘积 =SUBTOTAL(106,B2:B8)
107.Stder.s 标准偏差 =SUBTOTAL(107,B2:B8)
108.Stdevp.p 标准偏差 =SUBTOTAL(108,B2:B8)
109.SUM.S 求和 =SUBTOTAL(109,B2:B8)
110.VAR.S 方差 =SUBTOTAL(110,B2:B8)
111.VAR.P 方差 =SUBTOTAL(111,B2:B8)
三、Subtotal与Sum的区别
Subtotal,只对筛选数据结果数据进行求和
=SUBTOTAL(9,B2:B8) 统计个数
SUM,不受筛选结果影响,会对所有数据进行求和
=SUM(B2:B8) 非空单元格数量
四、Subtotal计数
Count返回包含数字以及包含参数列表中的数字的单元格的个数
=SUBTOTAL(2,B2:B8) 统计个数
Counta返回参数列表中非空值的单元格个数
=SUBTOTAL(3,B2:B8) 非空单元格数量
五、筛选后填充
=Subtotal (3,$B$2:B2)
六、避开同类计算
= Subtotal(9,B2:B9)
Subtotal函数遇到同类就避开了,就是不会统计由SUBTOTAL计算出来的数值。
Countif、Countifs
函数功能
Count:计算参数列表中的数字项的个数
CountA:计算参数列表中非空的项个数
Countblank:计算参数列表中空单元格的个数
1.Count:计算区域中数字的单元格个数。
①如果参数为数字、日期或者代表数字的文本,则将被计算在内;
②逻辑值和直接键入到参数列表中代表数字的文本被计算在内;
③如果参数为错误值或不能转换为数字的文本,则不会被计算在内;
2. CountA:计算区域中非空单元格的个数。
①参数值可以是任何类型,可以包括空字符(""),但不包括空白单元格;
②如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略;
③如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。
3. Countblank:计算区域中空单元格的个数。
①包含返回 ""(空文本)的公式的单元格会计算在内;
②包含零值的单元格不计算在内。
一、Countif的基本用法、Countifs的基本用法
=COUNTIF(明细表!C:C,A2)
注意:
1.COUNTIF函数的第一参数绝对引用,是为了公式向下填充时,保持引用范围不变;
2. COUNTIF函数的第二参数直接使用相对引用待统计单元格,公式向下填充时,D2依次变为D3、D4……
注意:
1.如果条件为文本,需要使用双引号引起来;
2. 如果条件为数字,则无需使用双引号。
COUNTIFS函数将条件应用于跨多个区域的单元格,并计算符合所有条件的次数。即多条件计数。
COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)
COUNTIFS(条件区域1,条件1,条件区域2,条件2…)
条件区域1:必需。在其中计算关联条件的第一个区域。
条件1:必需。要进行计数的第一个条件。
条件区域2:可选。在其中计算关联条件的第二个区域。
条件2:可选。要进行计数的第二个条件。
二、模糊条件计数
统计“张”姓次数
输入=Countif(---选取明细表里对应销售员C列---逗号---“张*”---回车
=Countif(明细表!C:C,"张*")
统计“张”姓A产品次数
输入=Countifs(---选取明细表里对应销售员C列--逗号---“张*”---选取产品名称D列---逗号---“A” ---回车
=Countifs(明细表!C:C,"张*",明细表!D:D,"A")
三、文本 非空 真空数据个数
注意:
* 代替任意多个字符
<> 代表非空数据
= 代表真空数据
四、按产品统计序号
按产品统计序号
=COUNTIF($C$2:C2,C2)
按产品统计产品序号
=C2&COUNTIF($C$2:C2,C2)
注意:
COUNTIF函数的统计区域是$C$2:C2,第一个C2是行绝对引用,第二个C2,是相对引用。
当公式向下复制时,就会变成$C$2:C3、$C$2:C4……一个不断扩展的区域,从这个动态区域中统计C列产品的个数。
五、一对多查询
我们需要用到辅助列来进行计算
日期前加个辅助(生成产品唯一的序号)对这个产品唯一的序号进行查找就可以统计F:H列所有销售情况。
输入公式=F2 F2要绝对引用,因为它要往下拖动=$F$2---连接符& shift+7---COUNTIF---输入C2按shift+; 第一个C2一定要绝对引用($C$2:C2)---回车
唯一产品辅助列序号就生成出来了
=$F$2&COUNTIF($C$2:C2,C2)
①Row函数可以生成连续的序号 输入=row 它是所在的单元格序号
可以指定单元格序号=ROW(A1) 1
②可以指定产品单元格序号=$F$2&ROW(A1) A1
③再对Vlookup进行查找
=VLOOKUP($E9,$A$1:$D$15,COLUMN(B1),0) COLUMN(B1)第2列
=IFERROR(VLOOKUP($E9,$A$1:$D$15,COLUMN(B1),0),"")
IFERROR是屏蔽我们的错误值,屏蔽空白
六、数据有效性
不能录入重复的姓名
选取数据区域---数据 数据验证---公式 =COUNTIF($B$2:$B$21,B2)=1---确定
只能输入以A开头的文本
选取数据区域---数据 数据验证---公式=COUNTIF(F2,"A*")=1---确定