Subtotal和SUM求和函数的区别和实际应用案例

2021-10-14 创建 21

SUBTOTAL和SUM的区别是:

1、sum是全部求和

2、subtotal是将筛选出来的可见部分求和。

用SUM函数进行求和 例如 =SUM(A1:A10) 对A1:A10进行求和

用SUBTOTAL(9,引用区域)函数进行求和 例如 =SUBTOTAL(9,A1:A10) 对A1:A10进行求和,和SUM不同的是,SUBTOTAL会根据的你的筛选情况进行分类汇总,如果用SUM函数进行求和,在筛选模式下,求和是会出错的。

Subtotal,其功能真是“以一敌十”,今天我们讨论其最经典的5种用法。

一、Subtotal函数的作用及语法结构。

作用:返回一个数据列表或数据库的分类汇总。

语法:=Subtotal(功能代码,数值区域)。

一句话解读:在自定的区域中按照功能代码的要求进行分类统计。

常见的功能代码有:

Subtotal和SUM求和函数的区别和实际应用案例

代码分为1-11和101-111,但是对应的函数名称却是相同的,难倒是功能一样?有什么区别呢?

1-11:包含手动隐藏的值。

101-111:不包含手动隐藏的值,暨以可见单元格为统计对象。

示例:

目的:计算总销量。

Subtotal和SUM求和函数的区别和实际应用案例

方法:

在目标单元格中输入公式:=SUBTOTAL(109,D3:D9)。

解读:

1、功能代码9或109对应的都是Sum函数,暨求和。但代码109仅对可见单元格有效。所以当隐藏/取消隐藏时,求和结果也跟着发生变化。

2、如果配合筛选功能更能体现其强大的功能。

二、对筛选或隐藏后的数据进行快速计算平均值。

方法:

在目标单元格中输入公式:=SUBTOTAL(1,D3:D9)、=SUBTOTAL(101,D3:D9)。

解读:

1、从示例中可以明确的得出,1-11和101-111中,对应代码在筛选中的作用是相同的。

2、1-11包含手动隐藏的值;101-111以可见单元格为统计对象。

三、保持序号(No)的连续性。

Subtotal和SUM求和函数的区别和实际应用案例

方法:

在目标单元格中输入公式:=SUBTOTAL(103,B$2:B2)。

解读:

1、对可见单元格可见,只能用代码101-111范围内的。

2、保持序号(No)的连续性,其实就是统计当前单元格之前的非空单元格数。暨当前示例中的参数是从B2开始的。

Subtotal和SUM求和函数的区别和实际应用案例

四、筛选出可见单元格的最大值。

Subtotal和SUM求和函数的区别和实际应用案例

方法:

在目标单元格中输入公式:=SUBTOTAL(4,D3:D9)。

五、计数统计。

Subtotal和SUM求和函数的区别和实际应用案例

方法:

在目标单元格中输入公式:=SUBTOTAL(2,D3:D9)。

扩展资料:

SUM 将您指定为参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。)的所有数字相加。每个参数都可以是区域 (区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。)、单元格引用 (单元格引用:用于表示单元格在工作表上所处位置的坐标集。

SUBTOTAL是分类汇总函数,包含了sum的功能进行汇总,可以根据需要进行及时调整函数,采用1-9等数字进行定义相关函数,你可以在excel帮助里看到更精确的解释。

SUBTOTAL的第一个特点是,如果在 ref1, ref2,… 中有其他的分类汇总(嵌套分类汇总),将忽略这些嵌套分类汇总,以避免重复计算。也就是在数据区域中有SUBTOTAL获得的结果将被忽略!

SUBTOTAL的第二个特点是,当 function_num 为从 1 到 11 的常数时,SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。

当您要分类汇总列表中的隐藏和非隐藏值时,请使用这些常数。当 function_num 为从 101 到 111 的常数时,SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时,使用这些常数。

但不论使用什么 function_num 值,SUBTOTAL 函数都会忽略任何不包括在筛选结果中的行。 而SUBTOTAL 函数不适用于数据行或水平区域。隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。

SUBTOTAL的第三个特点是,可以代替上面说的11种函数,当有上面说的两种特点情况时,就可以使用SUBTOTAL来完成。

所以在需要处理隐藏数据相关的应用时,SUBTOTAL是其它函数无法代替的,也是SUBTOTAL最大最重要的特点。

其他案例:https://baijiahao.baidu.com/s?id=1613768540901625738&wfr=spider&for=pc 如下,具体查看

二、SubTotal函数的使用方法及实例

(一)包含隐藏行与不包含隐藏行的实例

1、右键第三行行号 3,在弹出的菜单中选择“隐藏”,则第三行被隐藏;把公式 =SUBTOTAL(9,D2:D6) 复制到 D7 单元格,按回车,返回结果 2977;双击 D7 单元格,把公式中的 9 改为 109,按回车,返回结果 2085;操作过程步骤,如图2所示:

Subtotal和SUM求和函数的区别和实际应用案例

 

2、公式说明:公式 =SUBTOTAL(9,D2:D6) 中的 9 代表求和函数 Sum,D2:D6 为求和区域;当为 9 时,求和结果为 2977;当把 9 改为 109(109 也代表求和函数 Sum),求和结果为 2085;说明 9 包含了隐藏的第三行,109 没有包含隐藏的第三行,即函数序号为 1 到 11 包含隐藏行、101 到 111 不包含隐藏行。

标题:Subtotal和SUM求和函数的区别和实际应用案例

分类:办公技巧

链接:http://www.wlxiu.cn/132.html

版权:云办公导航版©权所有