函数语法解析 1、函数定义: 返回一个数据列表或数据库的分类汇总。 2、使用格式: SUBTOTAL(function_num,ref1,[ref2],...) SUBTOTAL(要使用函数的数字代码,要计算的区域) 3、参数说明: ①、Function_num:必需。数字1-11或101-111,用于指定要为分类汇总使用的函数。如果使用1-11,将包括手动隐藏的行,如果使用101-111,则排除手动隐藏的行;始终排除已筛选掉的单元格。 ②、Ref1:必需。要对其进行分类汇总计算的第一个命名区域或引用。 ③、Ref2,…:可选。要对其进行分类汇总计算的第2个至第254个命名区域或引用。 4、注意事项: ①、如果在ref1、ref2…中有其他的分类汇总(嵌套分类汇总),将忽略这些嵌套分类汇总,以避免重复计算。 ②、当function_num为从1到11的常数时,SUBTOTAL函数将包括通过“隐藏行”命令所隐藏的行中的值; 当function_num为从101到111的常数时,SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值。 ③、SUBTOTAL函数忽略任何不包括在筛选结果中的行,不论使用什么function_num值。 ④、SUBTOTAL函数适用于数据列或垂直区域。不适用于数据行或水平区域。 ⑤、如果所指定的某一引用为三维引用,函数SUBTOTAL将返回错误值#REF!。 5、说明: SUBTOTAL函数始终需要数字参数(1至11、101至111)作为它的第一个参数。该数字参数被应用于参数指定的值的分类汇总(单元格区域、命名区域)中。 函数示例 ▲ 01 经典展示 看动画,观察第一参数使用1-11和使用101-111的区别: 备注:其中使用PRODUCT时其结果超过11位数字,所以显示为科学记数。 ▲ 02 筛选数据的序号 公式: =SUBTOTAL(103,B$3:B3)*1 103代表函数COUNTA,表示忽略隐藏行计算非空单元格的个数,也就是说计算可见区域非空单元格的个数。 至于为什么要乘1,想知道原因的就自己动手试试,看看乘1与不乘1有啥区别? 当然方法多种,与文本型数字转数值方法相似。 效果动画: ▲ 03 筛选数据的求和 公式: =SUBTOTAL(109,D3:D10) 109代表函数SUM,计算可见单元格区域的和。 效果动画: ▲ 04 小计与合计 大部分人做表都喜欢在表格中插入小计与合计 合计公式: =SUBTOTAL(109,C3:C12) 如果小计你是直接用SUM函数求和,那么合计不能直接用=SUM(C3:C12),这样得到的结果有误; 但使用SUBTOTAL函数求和后,你就放心大胆的选区域C3:C12。 不信就自己动手试试吧!!! ▲ 05 去掉最高分最低分求平均 该例子在比赛中是非常常见的,不用专业的修剪函数,常规解法就是:(总分-最高分-最低分)/去除后的评委个数 公式: =(SUM(B3:G3)-MAX(B3:G3)-MIN(B3:G3))/(COUNT(B3:G3)-2) 而SUBTOTAL函数集这些函数于一身,如果用它来又该怎么解决呢? 公式: =SUM(SUBTOTAL({9,4,5},B3:G3)*{1,-1,-1})/(COUNT(B3:G3)-2) SUBTOTAL函数功能非常强大,也很实用,隐藏的数据也能处理是它最大的亮点。 该函数还有很多非常好玩的玩法,这里就不一一介绍了,有需要的可以报我们的函中班系统学习,今晚就上课了哦,已经报名的学员请准备上课,其他有意向报名的赶紧联系我们的客服:linlin748242 作者:仰望~星空 |
|