一、统计函数一：Subtotal
1、认识Subtotal函数
Subtotal函数返回列表货数据库中的分类汇总，包括求和、计数、平均值、最大值、最小值、标准差、方差等多种统计方式。其基本语法为：
SUBTOTAL(function_num,refl,[refl2]…）
function_num:必需。数字1~11或101~111，用于指定要为分类汇总使用的函数。
注意：此函数并不是“一个函数”，而是“一群函数”，也是Excel中唯一一个能统计用户可见单元格的函数
1~11分类汇总含义
2、Subtotal隐藏值
注意：使用1~11，将包括手动隐藏行；使用101~111，排除手动隐藏的行，始终排除已筛选掉的隐藏行
包括与排除隐藏行
3、Subtotal与Sum的区别
SUBTOTAL：只对筛选后的数据结果数据进行求和
SUM，不受筛选结果影响，会对所有数据进行求和
Sum与Subtotal
4、Subtotal计数：Count与Counta
Count：返回包含数字以及包含参数列表中的数字的单元格的个数
Counta：返回参数列表中非空值的单元格个数
Count&Counta
5、筛选后生成连续序号
=Subtotal(3,$B$2:B2)*1
第一参数使用3，表示使用Count函数的计算规则，统计B列单元格数量。直接使用Subtotal函数时，筛选状态下Excel会默认将末行当做汇总行，最后*1，是为了避免筛选时导致末行序号出错
未*1的筛选效果
*1的效果
6、避开同类计算
SUBTOTAL函数遇到同类就避开了，就是不会统计由SUBTOTAL计算出来的数值。
避开同类
二、单条件统计函数：Countif
1、认识Count
函数功能
（1）Count: 计算参数列表中的数字项的个数
①如果参数为数字、日期或者代表数字的文本，则将被计算在内；
②逻辑值和直接键入到参数列表中代表数字的文本被计算在内；
③如果参数为错误值或不能转换为数字的文本，则不会被计算在内；
④如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。
（2）CountA: 计算参数列表中非空的项个数
①参数值可以是任何类型,可以包括空字符(""),但不包括空白单元格；
②如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略；
③如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。
（3）Countblank: 计算参数列表中空单元格的个数
①包含返回""（空文本）的公式的单元格会计算在内；
②包含零值的单元格不计算在内。
Count函数功能
2、认识Countif
Countif函数对区域中满足单个指定条件的单元格进行计数，其基本语法为：
Countif(range,ctrteria)
range：必需。表示要统计数量的单元格，range可以包含数字、数组、或数字的引用
ctrteria：必需。用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字串符。
3、Countif的基本用法
countif基本用法
三、多条件计数函数：Countifs
Countifs函数的作用是对区域中满足多个条件的单元格计数。其基本语法为：
Countifs(criteria_range1,criterial,[criteria_range2,criterial2,]_)
criteria_range1:必需。在其中计算关联条件的第一个区域
criterial：必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。
Countifs基本用法
四、模糊条件计数
使用模糊条件时，可使用通配符“*”代表任意多个字符，“?”代表任意一个字符
模糊条件计数
五、文本、非空、真空数据个数计数
注意：
“*” 代替任意多个字符
“<>” 代表非空数据
“ =” 代表真空数据
理解“*/<>/=”表示
六、按产品统计序号（顺序/乱序排列）
步骤：
1、首先，先使用Countif给产品排序
=COUNTIF($D$2:D2,D2)
注意：COUNTIF函数的统计区域是$C$2:C2，第一个C2是行绝对引用，第二个C2，是相对引用；当公式向下复制时，就会变成$C$2:C3、$C$2:C4……一个不断扩展的区域，从这个动态区域中统计C列产品的个数
2、再使用名称&序号来进行组合，命名产品序号
（1） =D2&A2
（2） =D2&COUNTIF($D$2:D2,D2)
即使产品是按照乱序排列方法也一样
序号&产品序号组合
乱序
七、Countif函数+Vlookup函数一对多查询
以下图要求为例：
例子
步骤：
1、首先，增加辅助列来未产品进行命名排序，使产品序号唯一，方便进行查找
2、明确我们的查找值为”含有A*"的产品，需要生成以"A1、A2……"的连续序号去查找，可使用函数ROW（）来生成，表示现在所在的的单元格的所处的行
3、生成目标产品连续序号后，使用Vlookup来进行精确查找，此时向右拖动数据时会出现”#N/A“，此时，嵌套使用函数Column（）列来调整
4、为了将错误值屏蔽，最后我们使用IFERROR函数来进行隐藏
=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$15,COLUMN(B1),0),"")
Vlookup嵌套Row与Column
IFERROR隐藏
七、数据验证
1、不能录入重复
数据--数据验证--自定义--使用Countif函数进行设定
2、限定文本字段开头/结尾
网友评论