今天，我将和大家一起分享分类汇总函数SUBTOTAL的用法，用好这个函数，能解决很多问题。
一、SUBTOTAL函数基础知识
SUBTOTAL函数定义：SUBTOTAL函数是指返回列表或数据库中的分类汇总。
在用EXCEL做数据统计时，常见有关于求和、平均值、计数和最大值、最小值等操作，我们需要使用SUM、AVERAGE、MAX和MIN等函数，但如果统计中涉及条件筛选变换统计口径，如果只使用上述几种函数，可能会增加很多工作量。
我们今天要讲的SUBTOTAL函数凭自己的一己之力就可以实现常用的统计功能，包括求和、平均值、计数和最大值、最小值等，而且可以在计算时只统计筛选结果中的行，也就是说，函数计算结果会随着筛选结果的变化而变化，只统计筛选出来的可见部分，是不是感觉它很强大呢？
语法：SUBTOTAL(值,参数1，参数2……)
值：必需参数，数字1-11或101-111，用于指定要为分类汇总使用的函数。如果使用1-11，将包括手动隐藏的行，如果使用101-111，则排除手动隐藏的行。始终排除已筛选出隐藏的单元格。
参数1：必需参数，是指要对其进行分类汇总计算的第一个命名区域或引用。
参数2：可选参数，是指要对其进行分类汇总计算的第2个至第254个命名区域或引用。
当“值”为从1到11的常数时，SUBTOTAL函数将包括通过“隐藏行”命令所隐藏的行中的值，该命令位于EXCEL桌面应用程序中“开始”选项卡上“行和列”组中的“隐藏和取消隐藏”子菜单下面。当需要对列表中的隐藏和非隐藏数字进行分类汇总时，使用1到11的常数。当“值”为从101到111的常数时，SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值，当只想对列表中的非隐藏数字进行分类汇总时，请使用这些常数。
从上图可以看出，不隐藏任何行时，利用=SUBTOTAL(9,B3:B10)和=SUBTOTAL(109,B3:B10)，当我把药品3、4两行隐藏后，=SUBTOTAL(109,B3:B10)的功能就显现出来了，只计算没有隐藏的行的数据；而=SUBTOTAL(9,B3:B10)的计算结果连同隐藏的行一并计算了。
SUBTOTAL函数忽略任何不包括在筛选结果中的行，不论使用什么“值”，都只计算可见的单元格总和。见下图：
SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。例如：当“值”大于或等于101时需要分类汇总某个水平区域如SUBTOTAL(109,A1:E1)，隐藏某一列时不影响分类汇总，但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。
二、SUBTOTAL函数案例实践
清楚了SUBTOTAL函数的定义、语法及相关知识，下面，我们进行几个案例解析。
（一）隐藏行时，让序号始终连续编排
我们要对药品从1开始编号，有时候需要进行筛选，如果直接编序号，在筛选后编号会出现断档的情况，其实，用SUBTOTAL函数就能轻松解决序号的问题。
在A3单元格输入公式：=SUBTOTAL(3,B$3:B3)，按回车键即可计算出第一个编号1，然后向下复制公式到最后一个项目，你会发现，序号已经从1开始编排了。见下图：
上图中，当我们筛选隐藏药品3、5、7后，序号直接跳过隐藏行连续编排，是不是很神奇呢？见下图：
“值”3所对应的函数为COUNTA，统计非空单元格的个数。所以公式=SUBTOTAL(3,B$3:B3)统计的就是从B3开始到当前单元格累计非空单元格数。
（二）计算全年级总分最高分和最低分
某学校在期中考试中，要计算全年级总分最高分和最低分，我们可以在总分列用MAX函数得到最高分，用MIN函数得到最低分，这里用SUBTOTAL函数进行计算。
在单元格中输入公式：=SUBTOTAL(4,E3:E8)，即可计算总分区域中的最大值，因为“值”4对应的是“最大值”；输入公式：=SUBTOTAL(5,E3:E8)，即可计算总分区域中的最小值，因为“值”5对应的是最小值。见下图：
（三）在全年级名单中计算3班的平均分
某学校在期中考试中，统计了八年级所有班级学生的分数，现在要计算3班的平均分，这里用SUBTOTAL函数进行计算，并且筛选3班名单。
在F11单元格输入公式：=SUBTOTAL(101,F3:F8)，按回车键即可计算出全年级平均分，因为“值”101对应的是“算术平均值”。见下图：
本例要计算的是3班的平均分，所以我们只需要筛选班级3班的名单即可，全年级平均分为229分，筛选后，3班的平均分为231分。见下图：
SUBTOTAL函数忽略任何不包括在筛选结果中的行，本例中用到了筛选，所以这里用公式=SUBTOTAL(1,F3:F8)和=SUBTOTAL(101,F3:F8)的计算结果是一样的。
以上就是分类汇总函数SUBTOTAL的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。
评论 (0)