2018-09-13 统计函数

统计函数（Subtotal）

1.基本用法

 =SUBTOTAL(function_num,ref1,…)

显示公式                      说明

 =SUBTOTAL(1,B2:B8)平均值

 =SUBTOTAL(2,B2:B8)统计个数

 =SUBTOTAL(3,B2:B8)非空单元格数量

 =SUBTOTAL(4,B2:B8)最大值

 =SUBTOTAL(5,B2:B8)最小值

 =SUBTOTAL(6,B2:B8)括号内数据的乘积

 =SUBTOTAL(7,B2:B8)标准偏差

 =SUBTOTAL(8,B2:B8)标准偏差

 =SUBTOTAL(9,B2:B8)求和

TIPS：

·在单元格输入=Subtotal( ，就会提示上述语法

·此函数并不是“一个函数”，而是“一群函数”

·此函数是Excel中唯一一个能统计用户可见单元格的函数

2.Subtotal隐藏值

9-SUM.S（含手动隐藏值）

109-SUM.S（不含手动隐藏值）

3.Subtotal与Sum的区别

SUBTOTAL，只对筛选数据结果数据进行求和

SUM，不受筛选结果影响，会对所有数据进行求和

4.Subtotal计数

COUNT，返回包含数字以及包含参数列表中的数字的单元格的个数

COUNTA，返回参数列表中非空值的单元格个数（包括文本）

5.筛选后填充

 =SUBTOTAL(3,$B$2:B2)，筛选后的序号自动变化。

但subtotal默认最后一行为汇总行，筛选后也会显示出来。

解决方法： =SUBTOTAL(3,$B$2:B2)*1

6.避开同类计算

SUBTOTAL函数遇到同类就避开了，不会统计由SUBTOTAL计算出来的数值。（比如小计行也用subtotal就不会重复统计了）

统计函数（Countif、Countifs）

1.基本用法

COUNT 计算数字项个数，不能转换为数字的文本、空白单元格、逻辑值、错误值都不计算在内。

COUNTA 计算非空项个数，参数值可以是任何类型,包括空字符(""),但不包括空白单元格。

COUNBLANK 计算空单元格个数，空白单元格和空文本（""）会被计算在内。

=COUNTIF(明细表!C:C,A2)

=COUNTIFS(明细表!C:C,A2,明细表!D:D,B2)

2.模糊条件计数，用*

3.文本 非空 真空数据个数

统计文本单元格个数  =COUNTIF($A$2:$A$10,"*")

统计非空数据个数  =COUNTIF($A$2:$A$10,"<>")

统计真空数据个数  =COUNTIF($A$2:$A$10,"=")

注意：

*  代替任意多个字符

<> 代表非空数据

 = 代表真空数据

4.按产品统计序号

 =COUNTIF($C$2:C2,C2)

 =C2&COUNTIF($C$2:C2,C2)

COUNTIF函数的统计区域是$C$2:C2，第一个C2是行绝对引用，第二个C2，是相对引用。

5.一对多查询

加辅助列： =C2&COUNTIF($C$2:C2,C2) 生成唯一的序号

按照唯一的序号用vlookup函数查找

=IFERROR(VLOOKUP($E10,$A$1:$D$15,COLUMN(B3),0),"")

注意：列的绝对引用，column函数引出列数，IFERROR公式把空值屏蔽。

或者一步计算出的公式  =IFERROR(VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(B1),0),"")

6.数据有效性和函数的结合

不能录入重复的姓名： =COUNTIF($B$2:$B$21,B2)=1

只能输入以A开头的文本： =COUNTIF(F2,"A*")=1