统计不再难

统计函数 subtotal  COUNT COUNTIF COUNTIFS

11月30日

这一群函数的SUBTOTAL还是第一次接触到，居然还可以这么神奇。COUNT 函数是它序号3的的函数。COUNTIF 与COUNTIFS，与SUMIF SUMIFS，用于单条件和多条件。比较容易记忆。

- 基本用法

1.SUBTOTAL的函数结构 =SUBTOTAL(function_num,ref1,…)

与其他函数不一样的地方，他不是一个函数，他是一群函数。是唯一一个统计可见单元格的函数。分别有以下序号的代表函数。

录入SUBTOTAL,会跳出函数，如果求平均值，就按1，选定数据区域，即求平均值。

要记住每一个数字的含义。

统计不再难

2.数据显示的含义代表：

统计不再难

3.COUNT 的基本用法和知识

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

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

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

Count: 计算参数列表中的数字项的个数

CountA: 计算参数列表中非空的项个数

Countblank: 计算参数列表中空单元格的个数

- COUNTIF的函数结构=（判断区域，计数的条件）， =COUNTIF(明细表!C:C,A2)

5. COUNTIFS的函数结构=（条件区域1，计数条件1，条件区域2，计数条件2），=COUNTIFS(明细表!C:C,A2,明细表!D:D,B2)

注意：

1、如果条件为文本，需要使用双引号引起来；

2、如果条件为数字，则无需使用双引号。

-        隐藏值

101与1 对应都是AVERAGE

区别在于隐藏值。求和时，如果遇到隐藏数据，会有不同的结果。

统计不再难

- Subtotal与Sum的区别

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

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

- 计数

1.SUMTOTAL

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

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

COUNTA文本型数字也会计数在内。

2.模糊条件计数

用COUNTIF COUNTIFS ,用通配符*号。文本需要用英文状态下的双引号双起来。

文本非空真空 数据个数

统计不再难

注意：

*  代替任意多个字符

<> 代表非空数据

= 代表真空数据

- 筛选后填充

筛选后的序号填充要求自动变动。操作方式法：

=SUBTOTAL(3,$B$2:B2)，第1个B2绝对引用。

筛选时发现最后一行，会是系统默认为累计行。如何去掉累计行。在原有公式的基础上=SUBTOTAL(3,$B$2:B2)* 1，即可解决。

- 避开同类计算

指月度销售的的合计行，用SUBTOTAL(9,B2:B9),会忽略同类计算项。（如已经有小计项用了SUBTOTAL）

- 按产品统计序号

先统计出序号，再结合产品型号用连接符序号，即可成为产品序号。

统计不再难

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

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

这次产品乱序了，要求按产品统计序号。

统计不再难

- COUNTIF 与VLOOKUP 一对多查询

操作步骤：

步骤1 在左表前插入一列并设置公式，用countif函数统计客户的销售额并用&连接成 产品名称+序号的形式。

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

步骤2 在F9设置公式并复制即可得到F2单元格中产品的所有销售记录。

=IFERROR(VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(B1),0),"")

解决一对多查找的关键点是把重复的值用Countif变成不同的，然后用Vlookup函数再进行查找。

掌握这个思路很重要  。

IFERROR:是不要显示错误值，会自动填充为空白值单元格。

COLUMN(B1),是指VLOOKUP取列数据，在寻找第几列的自动列数匹配，不需要手动数序列。

- 数据有效性

通过COUNTIF 公式，对数据验证-自定义-录入公式

根据要求如：不能录入重复的姓名、只能输入以A开头的文本的操作公式：

=COUNTIF($B$2:$B$21,B2)=1      =COUNTIF(F2,"A*")=1