|
Excel中有求和、平均值、计数和最大值、最小值等函数，但是这些函数一遇到筛选和隐藏的问题，就爱莫能助了。今天小编excel小课堂(ID：excel-xiaoketang 长按复制)就为各位介绍一个全能函数 Subtotal，函数结果随着筛选结果的变化而变化，是不是很棒? 分类汇总函数SUBTOTAL SUBTOTAL函数返回列表或数据库中的分类汇总。SUBTOTAL 函数忽略任何不包括在筛选结果中的行，不论使用什么 function_num 值。SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。例如，当 function_num 大于或等于 101 时需要分类汇总某个水平区域时，例如 SUBTOTAL(109,B2:G2)，则隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。如果所指定的某一引用为三维引用，函数 SUBTOTAL 将返回错误值 #REF!。 语法：SUBTOTAL(function_num,ref1,[ref2],...) Function_num 必需。 数字 1-11 或 101-111，用于指定要为分类汇总使用的函数。 如果使用 1-11，将包括手动隐藏的行，如果使用 101-111，则排除手动隐藏的行；始终排除已筛选掉的单元格。 Ref1 必需。要对其进行分类汇总计算的第一个命名区域或引用。 Ref2,... 可选。要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。如果在 ref1、ref2…中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。 案例：对隐藏值的计算和忽略 现在，让我们用实际行动来证明当参数设置为1-11或者101-111时，函数的计算结果是有差别的。没有隐藏时，三个公式的计算结果完全相同，而当马超同学调皮地把自己隐藏起来的时候，只有公式“=SUBTOTAL(109,B2:B12)”火眼金睛，迅速发现，并更新计算结果。 案例：对筛选值的计算和忽略 如果隐藏行列数据属于无意，那么“筛选”就完全是表哥表妹的常规操作了。筛选出魏国的所有将领，这个时候subtotal函数的Function_num参数无论是1还是101，都只计算筛选出的结果，而AVERAGE则计算了数据区域的所有数值。 案例：永远连续的序号 一筛选、一隐藏，原本连续的序号就全乱了，打印结果很不方便，该怎么办呢？凡事都要努力在前，休息在后，这次也一样。在B2输入单元格“=SUBTOTAL(103,$C$2:C2)”，复制填充至B11，新的序号已制作完成。 筛选列表取消“吴”，看看序号现在如何？直接1、2……这样的普通序号最后一个序号为10，而用subtotal的序号变为7，且是连续的。挑战升级，把吕布同学隐藏起来，普通序号仍为10，而subtotal变为6.公式说明“=SUBTOTAL(103,$C$2:C2)”，参数103所对应的函数为：Counta。统计非空单元格的个数，并忽略隐藏的行。整个公式统计的就是从C2开始到当前单元格所在行累计非空单元格数。如果不想忽略隐藏行，可以将参数设置为3。 |
|
|
