Excel函数SUBTOTAL(分类汇总)。

下面给大家介绍一下Excel函数SUBTOTAL(分类汇总)

材料/工具

Excel

电脑

Excel函数SUBTOTAL(分类汇总

SUBTOTAL函数的作用是对数据列表或数据库进行分类汇总，语法是：=SUBTOTAL（函数序号，引用1，引用2……），第一参数用不同的数字指代不同的函数，函数序号为1-11时，表示包含隐藏值（指手动隐藏，不是筛选数据后的隐藏），函数序号为101-111时，表示不包含隐转喜么笔煤包斤乱香续藏值进行分类汇总，但是如果数据来自处于筛选状态下，那么SUBTOTAL函数的第一参数无论是1-11还是101-111都只对可见单元格进行分类汇总。

忽略隐藏行统计数据：如图2所示，统计到的7人总薪资为47400元，如果现在手动隐藏4和5两行的数据，再来统计当前的可见员工薪资，如图3所示，用SUBTOTAL函数忽略隐藏值的特性，公式为：=SUBTOTAL(109,F2:F8)，若要包含隐藏员工薪资，公式为：=SUBTOTAL(9,F2:F8)，第一参数9和109都指代SUM求和函数，不同的是，109表示忽略隐藏值，而9表示包含隐藏值。

不能忽略隐藏列统计数据：大家看图3，已隐藏王五所在的列，现在求2月总销量，还是用不沿同区间的序号指代SUM,忽略隐藏值公式为：=SUBTOTAL(109,B3:E3)，包含隐藏值公式为：=SUBTOTAL(9,B3:E3)。大家发现没有，结果是一样的，为什么呢？记住了，SUBTOTAL只能忽略隐藏行而不能忽略隐藏列。

对筛选数据只统计可见单元格：如图4上方表格所示，筛选所有男性员工数据统计总薪资，包含隐藏值公式为：=SUBTOTAL(9,F2:F8)，忽略隐藏值公式为:=SUBTOTAL(109,F2:F8)，大家会同样发现无论是否忽略隐藏值，结果也是一样的。因为对于SUBTOTAL函数来说，对筛选数据进行分类汇总，不管是1-11区间的函数还是101-111区间的，它只对可见单元格进行统呢到感势异喜计，是没有区别的！

SUBTOTAL总计忽略SUBTOTAL小计：如图5所示，分别用SUBTOTAL函数统计1-3月份的销量小计，1月份小计为：=SUBTOTAL(9,C2:C5)，2月份小计为：=SUBTOTAL(9,C7:C10)，3月份小计为：=SUBTOTAL(9,C12:C15)，最后用SUBTOTAL统计三个月份的总销量：=SUBTOTAL(9,C2:C16)，结果是575，不会重复统计SUBTOTAL小计的销量。因此，SUBTOTAL的第二参数引用区域如果包含SUBTOTAL函数统计，那么会自动忽略，不会重复统计。

去掉最高值和最低值取平均值：如图6所倒示，对A级主流的运动型汽车进行5次100-0刹车距离测试政，去掉最高值和最低值然后取平均值，公式为平均值=（总成绩-最高成绩-最低成绩）/去掉最高成绩最低成绩后的成绩个数，利用SUBTOTAL可以简化公式，在G3输入公式：=SUM(SUBTOTAL({9,4,5},B3:F3)*{1,-1,-1})/(COUNTA(B3:F3)-2)，并向下填充。第一参数用{9,4,5}分别指代求和、最高值和最低值，SUBTOTAL({9,4,5},B3:F3)的结果为{182.3,38.8,33.5}，然后乘以{1,-1,-1}后结果为{182.3,-38.8,-33.5}，再用SUM函数对这个数组进行求和，结果为110，COUNTA(B3:F3)-2的结果为3，那么平均值就是110/3=36.67。

填充动态序号：对部门填充序号，要求手动隐藏某些部门后，序号自动连续填充，公式为：=SUBTOTAL(103,$B$2:B2)，103指代COUNTA计数函数。

统计可见单元格的最大值最小值平均值等：基于SUBTOTAL函数忽略隐藏行的特性，可以用来统计诸如可见单元格最大值、最小值、平均值、计数等分类汇总数据，大家根据需要灵活切换函数序号就可以了。能解决Excel大多数问题的10个函数，实例讲解一学就会！如图9所示，统计“隐藏后的员工个数”，公式为:=SUBTOTAL(103,A2:A8),统计“隐藏后的最高薪资”，公式为：=SUBTOTAL(104,F2:F8)，统计“隐藏后的最低薪资”，公式为：=SUBTOTAL(105,F2:F8)，统计“隐藏后的平均薪资”，公式为=SUBTOTAL(101,F2:F8)。