在 Excel 中,SubTotal函数用于计算分类汇总,它至少要有两个参数,其中一个为函数序号,另一个为命名区域或引用;函数序号共支持11 个函数,并且这 11 个函数被设置两种编号,分别为 1-11 和 101-111,其中 1-11 包含隐藏值,101-111 忽略隐藏值;命名区域或引用最多支持 254 个。
SubTotal函数可与 OffSet、SumProduct、If、Sum、Row 等函数组合使用,其中 SubTotal + OffSet + SumProduct + Row 用于筛选态下求乘积之和,即不包含筛选外的值;Sum + If + OffSet + SubTotal 用于求指定条件的和。
一、Excel SubTotal函数语法
1、表达式:SUBTOTAL(Function_Num, Ref1, [Ref2], ...)
中文表达式:SUBTOTAL(函数序号, 命名区域或引用1, [命名区域或引用2], ...)
2、说明:
A、Function_Num 用于指定要分类汇总的函数,一共有 11 个函数,每个函数有两个序号,其中 1-11 表示包含隐藏值,101-111 表示忽略隐藏值,具体如下:
![f7eddb7f9720bca2edea5e8575f999c1.png](https://i-blog.csdnimg.cn/blog_migrate/25d27a35259ce21ce806b594a4bfc328.jpeg)
B、至少要有一个命名区域或引用,最多只能有 254 个命名区域或引用。如果指定有三维引用,SUBTOTAL函数将返回引用错误值 #REF!。
C、如果参数 ref 中有嵌套分类汇总,它们将被忽略,以避免重复计算;不在筛选结果中的行也被忽略。
D、参数 Function_Num 为 1-11 包含隐藏值是指通过“隐藏”选项(即手动隐藏)所隐藏行的值;参数 Function_Num 为 101-111 忽略隐藏值是指忽略通过“隐藏”选项所隐藏行的值。
E、参数 Function_Num 为 101-111 时,当分类汇总水平区域时,隐藏某一列不影响分类汇总;但隐藏垂直区域中的某一行就会对分类汇总产生影响。
二、Excel SubTotal函数的使用方法及实例
(一)求和汇总
1、假如要汇总服装销量之和。选中 E9 单元格,输入公式 =Subtatol(9,e2:e9),按回车,返回求和结果 5028;操作过程步骤,如图1所示:
![0ee1bf70090c8d7aa16d44131c23be77.gif](https://i-blog.csdnimg.cn/blog_migrate/c25d56e1ab4dda2538b7ef28248a2af9.gif)
图1
2、公式 =Subtatol(9,e2:e9) 中 9 表示求和,e2:e9 为求和区域。
(二)忽略嵌套分类汇总
1、已按“分类”进行了分类汇总,现在再用SubTotal函数分类汇总。双击 D12 单元格,把公式 =SUBTOTAL(3,D2:D11) 复制到 D12,按回车,返回统计结果 7;操作过程步骤,如图2所示:
![70aef5a7e2d935db6aa795e09e9cab47.gif](https://i-blog.csdnimg.cn/blog_migrate/1fcdf5d839ca32e9e555f526b8d53cde.gif)
图2
2、公式 =SUBTOTAL(3,D2:D11) 中 3 表示 CountA函数,即统计不为空的单元格数,返回结果为 7,说明并没有包含嵌套的分类汇总单元格,因为 D2 至 D11 一共有 10 个非空单元格,有三个是嵌套分类汇总结果。
(三)忽略不包含在筛选结果中的行
1、选中 E9 单元格,把公式 =SUBTOTAL(9,E2:E8) 复制到 E9,按回车,返回求和结果 5028;框选 E2:E8 单元格,选择“数据”选项卡,单击“筛选”,则 E2 单元格添加上“筛选”图标,单击该图标,在弹出的菜单中依次选择“数字筛选”→ 大于,打开“自定义自动筛选方式”窗口,在“大于”右边输入 600,单击“确定”,则筛选出销量大于 600 的服装,E9 中的值自动变为 4500,说明忽略了不包含在筛选结果中的行;操作过程步骤,如图3所示:
![9fa90b44-3f55-eb11-8da9-e4434bdf6706.png](http://p01.5ceimg.com/content/9fa90b44-3f55-eb11-8da9-e4434bdf6706.png)
图3
(四)包含隐藏行与忽略隐藏行
1、选中 E9 单元格,把公式 =SUBTOTAL(2,E2:E8) 复制到 E9,按回车,返回统计有数值单元格个数为 7;选中第二至第四行,右键第二行行号 2,在弹出的菜单中选择“隐藏”,则所选中的三行被隐藏,E9 中的数值仍是 7;双击 E9,把公式中的 2 改为 102,按回车,返回统计结果 4;操作过程步骤,如图4所示:
![fe1301d2d7d4770c1fec2ee5e8dfe0e2.gif](https://i-blog.csdnimg.cn/blog_migrate/e7f519d9dd6f2785c4594184ffb7b0e7.gif)
图4
2、公式 =SUBTOTAL(2,E2:E8) 中的 2 表示 Count函数,当隐藏三行后,统计结果仍为 7,说明参数 Function_Num 为 1-11 时包含隐藏行;当把公式中的 2 改为 102 后,统计结果变为 4,说明参数 Function_Num 为 101-111 时忽略隐藏行。
(五)隐藏行或列对分类汇总结果的影响
1、选中 E10 单元格,把公式 =SUBTOTAL(109,C2:E9) 复制到 E10,按回车,返回求和结果 14091;选中第二行和第三行,右键第二行行号 2,在弹出的菜单中选择“隐藏”,则第二三行被隐藏;E10 中的值变为 12014;按 Ctrl + Z 取消隐藏行,右键第四列列号 D,在弹出的菜单中选择“隐藏”,则第四列被隐藏,E10 中的值仍为 14091;操作过程步骤,如图5所示:
![bf836b5d0635d6db5358b4c1628ce468.gif](https://i-blog.csdnimg.cn/blog_migrate/14fb80d718d52338186347b4a7059160.gif)
图5
2、当隐藏行时,公式 =SUBTOTAL(109,C2:E9) 忽略了被隐藏的行;而隐藏列时,公式 =SUBTOTAL(109,C2:E9) 把隐藏的列也计入求和。
(六)同时引用多个区域
1、假如要求服装表中任意两种服装(如“衬衫”和“裤子”)的平均销量。选中 E10 单元格,把公式 =SUBTOTAL(1,C2:C9,E2:E9) 复制到 E10,按回车,返回 560.5;操作过程步骤,如图6所示:
![f298f779ea20db5d7822efe577dbe3ab.gif](https://i-blog.csdnimg.cn/blog_migrate/9d690fd3b875d5a00e86c4d749e580a4.gif)
图6
2、公式 =SUBTOTAL(1,C2:C9,E2:E9) 中 1 表示求平均值,C2:C9 和 E2:E9 为求平均值区域。
三、Excel SubTotal函数扩展使用实例
(一)求和到当前行
1、双击 G2 单元格,把公式 =SUBTOTAL(9,F$2:F2) 复制到 G2,按回车,返回求和结果 329;再次选中 G2 单元格,把鼠标移到 G2 右下角的单元格填充柄上,按住左键,往下拖,拖到 G3,求和到 G2,再拖到 G4,求和到 G4;操作过程步骤,如图7所示:
![1b3bd87b95c8e62152d0d597968422da.gif](https://i-blog.csdnimg.cn/blog_migrate/3e2ef558409c1e88fab98382228f162c.gif)
图7
2、公式 =SUBTOTAL(9,F$2:F2) 中的 F$2:F2 表示对单元格 F2 的引用,$2 表示对行的绝对引用,当往下拖时,F$2 不变,F2 会变为 F3、F4、……。
(二)在筛选状态下求乘积之和用 SumProduct + SumTotal + OffSet 组合
1、假如要求筛选状态下营业额。选中 F1 单元格,把公式 =SUMPRODUCT((D2:D8)*(E2:E8)*SUBTOTAL(3,OFFSET(E1,ROW(1:7),))) 复制到F1,按回车,返回求和结果 478295.3;框选 E1:E8,选择“数据”选项卡,单击“筛选”,则 E1 添加上“筛选”图标,单击该图标,在弹出的菜单中选择“数字筛选”→ 大于,打开“自定义自动筛选方式”窗口,在“大于”右边输入 700,单击“确定”,则筛选出大于 700 的服装,F1 中的值随即变为 371917.6,说明被隐藏的第二、三、五行没有被计入求乘积之和;操作过程步骤,如图8所示:
![bc29e78e77d2cdb7abe34aaef9f66193.gif](https://i-blog.csdnimg.cn/blog_migrate/72b6ab62fa02c0ec826d9e32741ea98f.gif)
图8
2、公式说明:
A、公式 =SUMPRODUCT((D2:D8)*(E2:E8)*SUBTOTAL(3,OFFSET(E1,ROW(1:7),))) 执行过程如图9所示:
![6c6c33ca1dec87c48e335ddde19b3da3.gif](https://i-blog.csdnimg.cn/blog_migrate/47968f9602521a79fa7a952d7bc1b39e.gif)
图9
公式执行过程说明:选中 F1,按住 Alt,按一次 M,按一次 V,打开“求值”窗口,按回车求值,每按一次回车求一次值。
B、ROW(1:7) 用于返回 1 到 7 的数组,即 {1;2;3;4;5;6;7};则 OFFSET(E1,ROW(1:7),) 变为 OFFSET(E1,{1;2;3;4;5;6;7},),执行时,以 E1 为基准,第一次从数组中取出 1 作为参数 Rows,由于 OffSet 省略了参数 Cols、返回单元格的高度和宽度,因此返回 E1 下一行的单元格 E2 的值;第二次从数组中取出 2 作为参数 Rows,返回 E1 下二行的单元格 E3 的值,其它的以此类推;最后返回数组{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},#VALUE! 代表 E2:E7 中的数值,它可以用公式 =SUM(N(OFFSET(E1,ROW(1:7),))) 验证(输入公式后需按 Ctrl + Shift + 回车),演示如图10所示:
![da8d82420595dffaf4b44b50313bf369.gif](https://i-blog.csdnimg.cn/blog_migrate/8837cd07b119b6684c9ffc20b517237b.gif)
图10
C、则 SUBTOTAL(3,OFFSET(E1,ROW(1:7),)) 变为 SUBTOTAL(3,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}),执行时,依次从数组中取出每一个值,如果它被隐藏(即不是筛选结果),返回 0,否则返回 1,最后返回 {0;0;1;0;1;1;1},符合第二、三、五行筛选时被隐藏,注意这里的范围为 E2:E8,所以 E2 是第二行,因此数组第一个元素为 0。
D、D2:D8 以数组形式返回 D2 到 D8 中的数值,E2:E8 以数组形式返回 E2 到 E8 中的数值,然后把两个数组对应元素相乘,最后返回数组 {28590.1;42187.2;177606;35600.4;62484;34609.6;97218}。
E、则公式变为 =SUMPRODUCT({28590.1;42187.2;177606;35600.4;62484;34609.6;97218}*{0;0;1;0;1;1;1}),再把两个数组对应元素相乘,然后求每个乘积之和。
(三)用 Sum + If + SubTotal + OffSet函数按条件求不包含隐藏值的和
1、假如要求“男装”的销量之和。双击 E9 单元格,把公式 =SUM(IF(C2:C8="男装",SUBTOTAL(109,OFFSET(E1,ROW(1:7),)))) 复制到 E9,按 Ctrl + Shift + 回车,返回求和结果 1879;右键第三行行号 3,在弹出的菜单中选择“隐藏”,则第三行(分类为男装)被隐藏,E9中的值随即变 982,说明被隐藏的行没有参数求和;操作过程步骤,如图11所示:
![5d466e97074cb97cdb7d1a3912e9c61a.gif](https://i-blog.csdnimg.cn/blog_migrate/8c42f68c1e22ff3584b3b41adfd5fd4d.gif)
图11
2、公式说明(以隐藏第 3 行后为例):=SUM(IF(C2:C8="男装",SUBTOTAL(109,OFFSET(E1,ROW(1:7),))))
A、OFFSET(E1,ROW(1:7),) 在上例中已经分析过,最后返回 {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。
B、SUBTOTAL(109,OFFSET(E1,ROW(1:7),)) 变为 SUBTOTAL(109,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}),接着,如果数组中的值被隐藏返回 0,否则返回具体数值,最后返回 {329;0;528;638;762;892;982}。
C、C2:C8="男装" 为 If 的条件,意思是:如果 C2 至 C8 中的文本等于“男装”,返回 True,否则返回 False;例如 C2 为“女装”,因此返回 False,C3 为“男装”,因此返回 True;最后返回数组 {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}。
D、则公式变为 =SUM(IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{329;0;528;638;762;892;982})),再把两个数组对应元素相乘(相乘时,True 被转为 1,FALSe 被转为 0),则公式变为 =SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;982}),最后求和返回 982。
提示:If + SubTotal 组合按条件求和不能这样写公式:=SubTotal(109,If(C2:C8="男装"),C2:C8,))。