使⽤实例_ExcelSubTotal函数的使⽤⽅法及实例，函数扩展使
⽤
在 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 表⽰忽略隐藏值，具体如下：
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所⽰：
图1
2、公式 =Subtatol(9,e2:e9) 中 9 表⽰求和，e2:e9 为求和区域。
(⼆)忽略嵌套分类汇总
1、已按“分类”进⾏了分类汇总，现在再⽤SubTotal函数分类汇总。双击 D12 单元格，把公式 =SUBTOTAL(3,D2:D11) 复制到
D12，按回车，返回统计结果 7；操作过程步骤，如图2所⽰：
图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所⽰：
图3
(四)包含隐藏⾏与忽略隐藏⾏
1、选中 E9 单元格，把公式 =SUBTOTAL(2,E2:E8) 复制到 E9，按回车，返回统计有数值单元格个数为 7；选中第⼆⾄第四⾏，右键第⼆⾏⾏号 2，在弹出的菜单中选择“隐藏”，则所选中的三⾏被隐藏，E9 中的数值仍是 7；双击 E9，把公式中的 2 改为 102，按回车，返回统计结果 4；操作过程步骤，如图4所⽰：
图4
2、公式 =SUBTOTAL(2,E2:E8) 中的 2 表⽰ Count函数，当隐藏三⾏后，统计结果仍为 7，说明参数 Function_Num 为 1-11 时包含隐藏⾏；当把公式中的 2 改为 102 后，统计结果变为 4，说明参数 Function_Num 为 101-111 时忽略隐藏⾏。
(五)隐藏⾏或列对分类汇总结果的影响
excel数组函数的实例1、选中 E10 单元格，把公式 =SUBTOTAL(109,C2:E9) 复制到 E10，按回车，返回求和结果 14091；选中第⼆⾏和第三⾏，右键第⼆⾏⾏号 2，在弹出的菜单中选择“隐藏”，则第⼆三⾏被隐藏；E10 中的值变为 12014；按 Ctrl + Z 取消隐藏⾏，右键第四列列号D，在弹出的菜单中选择“隐藏”，则第四列被隐藏，E10 中的值仍为 14091；操作过程步骤，如图5所⽰：
图5
2、当隐藏⾏时，公式 =SUBTOTAL(109,C2:E9) 忽略了被隐藏的⾏；⽽隐藏列时，公式 =SUBTOTAL(109,C2:E9) 把隐藏的列也计⼊求和。
(六)同时引⽤多个区域
1、假如要求服装表中任意两种服装(如“衬衫”和“裤⼦”)的平均销量。选中 E10 单元格，把公式 =SUBTOTAL(1,C2:C9,E2:E9) 复制到 E10，按回车，返回 560.5；操作过程步骤，如图6所⽰：
图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所⽰：
图7
2、公式 =SUBTOTAL(9,F$2:F2) 中的 F$2:F2 表⽰对单元格 F2 的引⽤，$2 表⽰对⾏的绝对引⽤，当往下拖时，F$2 不变，F2 会变为 F
3、F
4、……。
(⼆)在筛选状态下求乘积之和⽤ 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所⽰：
图8
2、公式说明：
A、公式 =SUMPRODUCT((D2:D8)*(E2:E8)*SUBTOTAL(3,OFFSET(E1,ROW(1:7),))) 执⾏过程如图9所⽰：
图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所⽰：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。