Excel中的SUBTOTAL函数,因其强大的功能而被人所熟知,其一共可以实现11个函数的功能。
其中常用的当属9和109——求和功能。
SUBTOTAL函数介绍中,9是求和包括隐藏的行、109是求和忽略隐藏的行,所以很多人就将参数为9下的SUBTOTAL函数等价于SUM函数,这种理解并不完全正确,在以下两种情况当中,两者天差地别。
始终排除已筛选掉的单元格下面我们用三个函数(SUBTOTAL的9和109以及SUM函数),来求和一列数据。
下图中可以看出,在没有隐藏数据的状态下,三者求和结果一致。
接着我们手动隐藏第二行,可以发现SUBTOTAL参数9和SUM函数结果未发生变化,参数109的忽略了隐藏的数据行,这里参数9状态下的SUBTOTAL等价于SUM函数,这与我们平时理解的一致。
但是,在筛选隐藏数据的时候可以发现,SUBTOTAL不管是9还是109,都未将隐藏的行数据计算在内,这种情况下参数9状态下的SUBTOTAL并不等价于SUM函数。
可以看出,SUBTOTAL函数在参数在1-11时,仅包括手动隐藏的行,始终排除被筛选掉的单元格。
忽略嵌套在内的SUBTOTAL函数除了排除被筛选掉的数据之外,SUBTOTAL函数还不计算嵌套内部的SUBTOTAL函数结果数据。
举个例子,下图中分别利用SUBTOTAL函数的9和109以及SUM函数,计算了2022年上半年的一季度合计、二季度合计以及全年合计。
注意年度合计的求和范围是C2:C8,包含2个季度合计值,但是SUBTOTAL函数的两种参数状态下全部返回60(1-3月30 4-6月30),意味着两季度的合计值并未计算在内。
反观SUM函数,求和C2:C8,结果返回120,数值翻倍,单元格的数据全部计算在内,包括2个季度的合计值。
这也是参数9状态下的SUBTOTAL函数与SUM函数的区别点。
小结综上所述,SUBTOTAL函数参数1-11时,将包括手动隐藏的行,如果使用101-111,则排除手动隐藏的行,始终排除已筛选掉的数据;同时SUBTOTAL不计算嵌套内的SUBTOTAL数据。
好了, 以上就是今天的分享,希望对你有所帮助,我们下期再见,喜欢的小伙伴记得点个关注哟。
,