Excel SubTotal函数包含忽略值的使用方法及与OffSet/SumProduct/If/Sum组合求筛选状态下乘积和与指定条件和

在 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 表示忽略隐藏值，具体如下：

• Function_num（包含隐藏值）
• Function_num（忽略隐藏值）
• 函数

• 1
• 101
• AVERAGE

• 2
• 102
• COUNT

• 3
• 103
• COUNTA

• 4
• 104
• MAX

• 5
• 105
• MIN

• 6
• 106
• PRODUCT

• 7
• 107
• STDEV

• 8
• 108
• STDEVP

• 9
• 109
• SUM

• 10
• 110
• VAR

• 11
• 111
• VARP

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 时忽略隐藏行。

 

（五）隐藏行或列对分类汇总结果的影响

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 会变为 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所示：

图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所示：

图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所示：

图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,))。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。