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

亮术网 2019-08-29 本网原创

在 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所示:

Excel SubTotal函数的使用方法及实例

图1

2、公式 =Subtatol(9,e2:e9) 中 9 表示求和,e2:e9 为求和区域。

 

(二)忽略嵌套分类汇总

1、已按“分类”进行了分类汇总,现在再用SubTotal函数分类汇总。双击 D12 单元格,把公式 =SUBTOTAL(3,D2:D11) 复制到 D12,按回车,返回统计结果 7;操作过程步骤,如图2所示:

Excel SubTotal函数忽略嵌套分类汇总

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

Excel SubTotal函数忽略不包含在筛选结果中的行

图3

 

(四)包含隐藏行与忽略隐藏行

1、选中 E9 单元格,把公式 =SUBTOTAL(2,E2:E8) 复制到 E9,按回车,返回统计有数值单元格个数为 7;选中第二至第四行,右键第二行行号 2,在弹出的菜单中选择“隐藏”,则所选中的三行被隐藏,E9 中的数值仍是 7;双击 E9,把公式中的 2 改为 102,按回车,返回统计结果 4;操作过程步骤,如图4所示:

Excel SubTotal函数包含隐藏行与忽略隐藏行

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

Excel SubTotal函数隐藏行或列对分类汇总结果的影响

图5

2、当隐藏行时,公式 =SUBTOTAL(109,C2:E9) 忽略了被隐藏的行;而隐藏列时,公式 =SUBTOTAL(109,C2:E9) 把隐藏的列也计入求和。

 

(六)同时引用多个区域

1、假如要求服装表中任意两种服装(如“衬衫”和“裤子”)的平均销量。选中 E10 单元格,把公式 =SUBTOTAL(1,C2:C9,E2:E9) 复制到 E10,按回车,返回 560.5;操作过程步骤,如图6所示:

Excel SubTotal函数同时引用多个区域

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

Excel SubTotal函数求和到当前行

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

Excel SubTotal函数在筛选状态下求乘积之和用 SumProduct + SumTotal + OffSet 组合

图8

2、公式说明:

A、公式 =SUMPRODUCT((D2:D8)*(E2:E8)*SUBTOTAL(3,OFFSET(E1,ROW(1:7),))) 执行过程如图9所示:

Excel OffSet/SumProduct/Sum组合求筛选状态下乘积

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

Excel SUM + N + OFFSET + Row函数

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

Excel用 Sum + If + SubTotal + OffSet函数按条件求不包含隐藏值的和

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