Day16统计函数(Subtotal、countif、countifs)
一、统计函数SUBTOTAL
不是一个函数,而是一群函数,共有9种用法,是Excel中唯一一个能统计可见单元格的函数
(一)、基本用法,常用的如下:每个数字代表不同含义用法
1、平均值:subtotal(1,数据区域绝对值)
2、统计非空格数:subtotal(3,数据区绝对值)
3、求和:subtotal(9,数据区域绝对值)=sum(数据区域)
(二)、隐藏值的用法:
平均值:subtotal(1,数据区域)与subtotal(101,数据区域)区别,前者包含隐藏值(虽然隐藏,数据参与计算),后者不包含隐藏值(隐藏之不参与计算)
(三)、求和:subtotal(109,数据区域)与sum函数区别:
subtotal(9,数据区域)=sum函数,
前者包含隐藏值(虽然隐藏,数据参与计算),不受筛选结果影响,会对所有数据进行求和
后者不包含隐藏值(隐藏之不参与计算),只对筛选数据结果数据进行求和
(四)、subtotal—2.count与subtotal—3.counta区别
2.count 返回包含数字以及包含参数列表中的数字的单元格的个数
3.counta 返回参数列表中非空值的单元格个数
(五)、筛选后填充
目的:将筛选后序号连续显示
筛选后序号公式为: subtotal(3,$b$2:b2)*1
乘以1,是因为subtotal默认最后一行为合计,序号即为1—6
(六)、避开同类计算
小计行公式:subtotal(9,b2:b4),subtotal(9,b6:b8)
合计行公式为SUBTOTAL(9,B2:B9),也不会将小计行数据加入。
二、统计函数COUNTIF
(一)、基本用法
COUNT:计算数字项个数 (不能转换为数字的文本、空白单元格、逻辑值、错误值都不计算在内)
COUNTA:计算非空项个数 (参数值可以是任何类型,包括空字符(""),但不包括空白单元格)
COUNBLANK:计算空单元格个数 (所见是空白的,空白单元格和空文本("")会被计算在内)
(二)、countif函数:出现次数统计,单条件统计
第一参数要绝对引用,第二参数相对引用
(三)、countifs函数:多条件统计
(四)、模糊条件统计:利用通配符
=COUNTIF(明细表!C:C,"张*") =countifs(明细表!销售员,“张*”,明细表!产品,“A”)
(五)、统计文本单元格个数: =COUNTIF($A$2:$A$10,"*")
* 代替任意多个字符;<> 代表非空数据; = 代表真空数据
(六)、按产品统计序号:顺序统计
1、填表序号:=COUNTIF($C$2:C2,C2),第一个c2一定要绝对引用,因为总是从这个单元格数起
2、产品序号:=C2&A2,将A列与C列结合即为产品编号
3、将一步二步结合:=C2&COUNTIF($C$2:C2,C2)
(七)、一对多产品统计表制作:借助辅助列,将产品编号,然后产品排序复制黏贴即可
row函数:返回所选单元格所在行数,问题待解决中
(八)、不能录入重复的数据(countif与数据有效性结合)
选中区域—数据—数据验证—设置(允许:序列、来源:=countif(C1:C21,C2)=1)
试了一下,我做的比老师多了个箭头,待解决
网友评论