SUBTOTAL函数是Excel中一个功能强大的工具,主要用于筛选和隐藏后的数据统计。这个函数不仅能够帮助用户快速得到所需的数据汇总,还能在处理包含隐藏数据的表格时表现出其独特的灵活性。以下是SUBTOTAL函数的详细使用方法及实例解析,旨在帮助那些对SUBTOTAL函数使用感兴趣的读者。
SUBTOTAL函数在Excel中的语法结构为:SUBTOTAL(function_num, ref1, [ref2], ...),其中function_num用于指定分类汇总时要使用的函数类型,如求和、计数、求平均值等,而ref1, [ref2], ...则是需要进行分类汇总计算的单元格区域或命名区域。值得注意的是,function_num的取值范围有两个:1至11和101至111。这两个范围的区别在于,当function_num取1至11时,SUBTOTAL函数会对筛选后的数据进行统计,包括手动隐藏的数据;而当function_num取101至111时,SUBTOTAL函数仅对可见的数据进行统计,忽略筛选掉的数据和手动隐藏的数据。
SUBTOTAL函数支持多种函数类型,具体对应关系如下:
1 (101): AVERAGE,求平均值
2 (102): COUNT,计数非空单元格
3 (103): COUNTA,计数非空单元格(包括字母)
4 (104): MAX,求最大值
5 (105): MIN,求最小值
6 (106): PRODUCT,求乘积
7 (107): STDEV,基于样本的标准偏差值(忽略逻辑值和文本)
8 (108): STDEVP,基于总体的标准偏差值
9 (109): SUM,求和
10 (110): VAR,基于样本的方差(忽略逻辑值和文本)
11 (111): VARP,基于总体的方差
假设A1:C11区域存储了各业务员的销售额数据,我们希望通过SUBTOTAL函数来进行计数和求和操作。
计数实例:
在F1单元格输入公式:=SUBTOTAL(3, $A$2:$A$11),用于计算A列中非空单元格的数量。当未筛选数据时,SUBTOTAL函数返回值为“10”。当筛选出“销售1部”的数据时,SUBTOTAL函数返回值为“4”,仅计算筛选后非空单元格的数量。
求和实例:
在F1单元格输入公式:=SUBTOTAL(9, $C$2:$C$11),用于计算C列中销售额的总和。当未筛选数据时,SUBTOTAL函数返回值为“550”。当筛选出“销售1部”的数据时,SUBTOTAL函数返回值为“220”,仅计算筛选后数据的总和。
在某些情况下,数据表中的某些行可能被隐藏。SUBTOTAL函数在处理隐藏数据时表现出不同的行为,取决于function_num的取值。
隐藏后计数实例:
在F1单元格输入公式:=SUBTOTAL(103, $A$2:$A$11),此时所有数据均未隐藏,SUBTOTAL函数返回值为“10”。当隐藏第2、3行数据后,SUBTOTAL函数返回值为“8”,说明它忽略了隐藏的行。
隐藏后求和实例:
在F1单元格输入公式:=SUBTOTAL(109, $C$2:$C$11),此时所有数据均未隐藏,SUBTOTAL函数返回值为“550”。当隐藏第2、3行数据后,SUBTOTAL函数返回值为“520”,忽略了隐藏行中的销售额。
在数据表中,有时需要为筛选后的数据生成连续的序号。通过SUBTOTAL函数可以实现这一点。
在A2单元格输入公式:=SUBTOTAL(103, $B$2:B2)*1,然后拖动填充柄向下复制公式,在A2:A11生成连续序号。当筛选出“销售1部”的数据时,序号仍保持连续,这是因为SUBTOTAL函数在生成序号时忽略了隐藏或筛选掉的行。
通过SUBTOTAL函数与其他函数的组合,可以实现更复杂的统计需求,如对筛选后的数据进行条件计数。
假设在B2:B11区域存储了部门信息,C2:C11区域存储了各业务员的销售额。我们希望计算筛选出销售额大于等于50的各部门
《鳄鱼小顽皮爱洗澡》内购版是一款深受玩家喜爱的休闲益智类游戏...
《鳄鱼小顽皮爱洗澡》免费购买版是一款极富趣味性的休闲益智游戏...
所有通话免费版是一款提供全球范围内免费通话服务的通讯应用程序...
音乐搜索器多站合一是一款集成了多个音乐平台搜索功能的强大工具...
所有通话app安卓版是一款集成了语音、视频通话功能的即时通讯...
19.20M美团点餐助手
19.20M美团点餐助手智能版
19.20M点餐助手app
792K所有通话 直接下载安装
19.20M点餐收银系统
59.01Mponytown 中文版下载
59.01Mponytown 下载安装
59.01Mponytown手机版
792K所有通话
13.99M慧学星教师端
本站所有软件来自互联网,版权归原著所有。如有侵权,敬请来信告知 ,我们将及时删除。 琼ICP备2023003481号-5