2019-01-24

统计函数（Subtotal）

 1、基本用法

Subtotal并不是“一个函数“，它包含”一群函数“

（1）1-AVERAGE：=SUBTOTAL(1,B2:B8) 平均值

（2）2-COUNT：=SUBTOTAL(2,B2:B8) 统计个数

（3）3-COUNTA：=SUBTOTAL(3,B2:B8) 非空单元格数量

（4）4-MAX：=SUBTOTAL(4,B2:B8) 最大值

（5）5-MIN：=SUBTOTAL(5,B2:B8) 最小值

（6）6-PRODUCT：=SUBTOTAL(6,B2:B8) 括号内数据的乘积

（7）7-STDEV.S：=SUBTOTAL(7,B2:B8) 标准偏差

（8）8-STDEVP.P：=SUBTOTAL(8,B2:B8) 标准偏差

 （9）9-SUM.S：=SUBTOTAL(9,B2:B8) 求和 备注：此函数是Excel中唯一一个能统计用户可见单元格的函数

 2、Subtotal隐藏值

（1）9-SUM.S（求和含隐藏值）:=SUBTOTAL(9,B2:B8)

（2）109-SUM.S（求和不含隐藏值）:=SUBTOTAL(109,B2:B8)

 3、Subtota与Sum的区别

（1）SUBTOTAL，只对筛选数据结果数据进行求和 （2）SUM，不受筛选结果影响，会对所有数据进行求和

 4、Subtotal计数

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

（2）COUNTA返回参数列表中非空值的单元格个数

 5、筛选后填充 =SUBTOTAL(3,$B$2:B2)*1（输入Subtotal公式，选3，按Shift+:,绝对引用）

 6、避开同类计算

统计函数（Countif Countifs）

 1、基本用法

 1）Count、CountA、Countblank

（1）Count: 计算参数列表中的数字项的个数 （2）CountA: 计算参数列表中非空的项个数 （3）Countblank: 计算参数列表中空单元格的个数

 2）Countif

 （1）COUNTIF函数的第一参数绝对引用，是为了公式向下填充时，保持引用范围不变；

（2）COUNTIF函数的第二参数直接使用相对引用待统计单元格，公式向下填充时，D2依次 变为D3、D4……

3)Countifs

（1）)COUNTIFS函数将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。即多条件计数。

（2）如果条件为文本，需要使用双引号引起来；如果条件为数字，则无需使用双引号。

 2、模糊条件计数

（1）统计张姓次数：=COUNTIF(明细表!C:C,"张*")

（2）统计“张”姓A产品次数：=COUNTIFS(明细表!C:C,"张*",明细表!D:D,"A") 3、文本 非空 真空数据个数 （1）统计文本单元格个数: =COUNTIF($A$2:$A$10,"*") （2）统计非空数据个数：=COUNTIF($A$2:$A$10,"<>")

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

（4）注意：* 代替任意多个字符；<> 代表非空数据；= 代表真空数据

4、按产品统计序号 COUNTIF函数的统计区域是$C$2:C2，第一个C2是行绝对引用，第二个C2，是相对引用。 =COUNTIF($C$2:C2,C2) =C2&COUNTIF($C$2:C2,C2)

5、一对多查询

（1）步骤1 在左表前插入一列并设置公式，用countif函数统计客户的销售额并用&连接成 产品名称+序号的形式。 （2）步骤2 在F9设置公式并复制即可得到F2单元格中产品的所有销售记录。 （3）公式：=IFERROR(VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(B1),0),"")

6、数据有效性

 （1）不能录入重复的姓名 数据→数据验证→录入公式（=COUNTIF($B$2:$B$21,B2)=1）→确定 （2）只能输入以A开头的文本 数据→数据验证→录入公式（=COUNTIF(F2,"A*")=1）→确定