EXCEL中SUBTOTAL函数的高级用法

SUBTOTAL函数是分类汇总函数，从字面看好像只是求和函数，但其功能强大，远不止这一个。

此函数允许有多个参数，但至少必须有两个参数，第一个参数指明函数功能，从雁注第二个参数始，是数据区域引用，最多可以有254个，最少必须有1个，在现在的版本中，当输八民盆入函数名后，会跟随第一参数，可看到各数字代表的功能。

第一参数中各数字代表的功能如下图，用得比较多的是3和9这两个数字，也就是求非空单元格个数和求和这两个函数，对应的103和109这两个数字，代表的功能与上面的完全一样，只是前者包含隐藏行，后者不包含隐藏行在内，注意下，这里的“隐藏行”，是指通过“隐藏”命令隐藏的行，如果只有筛选，而没有“隐藏行”，两组数据的结果是相同的。

比如对下表中数字分数在80分以上的求均值，即可筛选后用公式：=SUBTOTAL(1,D2:D19)，再将公式中的1改为101，可以看到结果相同阅侮：=SUBTOTAL(101,D2:D19)。

除了这些基本用法，可利用函数进行一些高级应用，最常见的对筛选结果添加顺序序号。如果用常规的公式：=COUNTA(B$2:B2)添加序号，筛选后，序号就不连续。这是因为此函数是对所有数据而不仅仅对可见数据进行统计。

而将公式改为：=SUBTOTAL(3,B$2:B2)，再进行筛选，即可看到连续的序号，这样才便于对筛选结果进行打印。

有时需要返回筛选出第一行数据出现的行号，可以用公式：=MATCH(1,SUBTOTAL(3,OFFSET(A1,ROW(1:20),0)),0)+1，当然这是数组公式，即同时按住Ctrl+Shift+Enter结束公式编辑，会自动生成一对“｛｝”。

有一批货，由几人分别完成，假如各人每天能完成的量不同，求各人能在几天内完成，用常规的方法，需要用辅助表，累加出各人前若干天已经完成的量，再与计划数进行比较，可得到可在哪天完成。

但这样做比较烦琐，利用SUBTOTAL函数，再进行其它函数就可设计出公式一步到位：=LOOKUP(1,0/(SUBTOTAL(9,OFFSET(B2,,,,COLUMN(A:G)))<I2),COLUMN(B:H))。

还可用INDEX函数直接返回具体是哪一天：=INDEX(B$1:H$1,LOOKUP(1,0/(SUBTOTAL(9,OFFSET(B2,,,,COLUMN(A:G)))<I2),COLUMN(B:H)))，此公式嵌套了多个函数的用法，有关其它函数的用法可参看其它经验中的介绍。