subtotal用法

一、基本用法

一定要记住每一个数字代表的意义，很重要。

1--AVERAGE--平均值

2--COUNT--统计个数

3--COUNTA--非空单元格数量

4--MAX--最大值

5--MIN--最小值

6--PRODUCT--括号内数据的乘积

7--STDEV.S--标准偏差

8--STDEVP.P--标准偏差

9--SUM.S--求和

10--VAR.S--方差

11--VAR.P--方差

注意：

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

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

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

二、Subtotal隐藏值

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

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

三、Subtotal与Sum的区别

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

四、Subtotal计数

Subtotal (2,统计数值个数)

Subtotal (3,统计非空单元格个数)

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

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

五、筛选后填充

 =SUBTOTAL(3,$B$2:B2)

注意：

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

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

六、避开同类项求和

SUBTOTAL函数遇到同类就避开了，就是不会统计由SUBTOTAL计算出来的数值。

COUNTIF COUNTIFS用法

一、基本用法

1、函数功能

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

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

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

2、COUNTIF

注意：

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

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

3、COUNTIFS 

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

注意点：A 如果条件为文本，需要使用双引号引起来；

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

条件区域1：必需。在其中计算关联条件的第一个区域。

条件1：必需。要进行计数的第一个条件。

条件区域2：可选。在其中计算关联条件的第二个区域。

条件2：可选。要进行计数的第二个条件。

二、模糊查找

应用单条件或是多条件进行查找。

三、文本，非空，真空数据个数

注意：（重点记忆）

* 代替任意多个字符

<> 代表非空数据

= 代表真空数据

四、按产品统计序号

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

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

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

当公式向下复制时，就会变成$C$2:C3、$C$2:C4……一个不断扩展的区域，从这个动态区域中统计C列产品的个数。

按产品统计序号--乱序也可以

五、一对多查询

操作步骤：

步骤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函数再进行查找。

掌握这个思路很重要

六、数据有效性

不能重复重复的姓名

数据验证中输入 =COUNTIF($B$2:$B$21,B2)=1

只能输入以A开头的文本

在数据验证中输入=COUNTIF(F2,"A*")=1