统计函数

E战到底特训营第14天 2019.4.12 阴

统计函数

一、统计函数（Subtotal）

统计函数

1. 基本用法：=SUBTOTAL(function_num,ref1,…)

统计函数 统计函数

TIPS：

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

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

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

2. Subtotal隐藏值

统计函数

3. Subtotal与Sum的区别：

统计函数

注意：

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

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

4. Subtotal计数

统计函数

注意：

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

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

5. 筛选后填充：

统计函数

注意：

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

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

6. 避开同类计算

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

统计函数

二、统计函数（Countif、Countifs）

统计函数

1. 基本用法

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

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

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

1.1 COUNT：计算区域中数字的单元格个数。

①如果参数为数字、日期或者代表数字的文本，则将被计算在内；

②逻辑值和直接键入到参数列表中代表数字的文本被计算在内；

③如果参数为错误值或不能转换为数字的文本，则不会被计算在内；

④如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。

1.2 COUNTA：计算区域中非空单元格的个数。

①参数值可以是任何类型,可以包括空字符(""),但不包括空白单元格；

②如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略；

③如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。

1.3 COUNTBLANK：计算区域中空单元格的个数。

①包含返回 ""（空文本）的公式的单元格会计算在内；

②包含零值的单元格不计算在内。

统计函数

2. 模糊条件计数

2.1基本用法1

统计函数

注意：

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

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

2.2基本用法2

统计函数

注意：

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

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

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

COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)　

COUNTIFS(条件区域1，条件1，条件区域2，条件2…)

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

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

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

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

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

统计函数

注意：

* 代替任意多个字符

<> 代表非空数据

= 代表真空数据

4. 按产品统计序号

统计函数 统计函数

5. 一对多查询

统计函数 统计函数

操作步骤：

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

掌握这个思路很重要

6. 数据有效性

=COUNTIF($B$2:$B$21,B2)=1

统计函数

=COUNTIF(F2,"A*")=1

统计函数

以上就是今天的学习内容。