公众号
公众号

返回顶部
返回顶部
关注我们二维码
微信扫码关注我们

巧用SUBTOTAL函数计算隐藏单元格

发布时间:2019-05-14 难度:中级

返回数据清单或数据库中的分类汇总。通常,使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇

总的数据清单。一旦创建了分类汇总,就可以通过编辑 SUBTOTAL 函数对该数据清单进行修改。 

语法 
SUBTOTAL(function_num,ref1,ref2,...) 
■Function_num 为 1 到 11(包含隐藏值)或 101 到 111(忽略隐藏值)之间的数字,指定使用何种函数在数据

单中进行分类汇总计算。 

image.png

■Ref1, ref2, 为要进行分类汇总计算的 1 到 29 个区域或引用。 


说明 
■如果在 ref1, ref2,… 中有其他的分类汇总(嵌套分类汇总),将忽略这些嵌套分类汇总,以避免重复计算。 
■当 function_num 为从 1 到 11 的常数时,SUBTOTAL 函数将包括通过“格式”菜单的“”子菜单下面的
"隐藏"

命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时,请使用这些常数。当 function_num 为从101 

到 111 的常数时,SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的"隐藏"命令所隐藏的行中的值。

当您只分类汇总列表中的非隐藏数字时,使用这些常数。 

■SUBTOTAL 函数忽略任何不包括在筛选结果中的行,不论使用什么 function_num 值。 
■SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。

例如,当 function_num 大于或等于 101 时需要分类汇总某个水平区域时,

例如 SUBTOTAL(109,B2:G2),则隐藏某一列不影响分类汇总。

但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。 

■如果所指定的某一引用为三维引用,函数 SUBTOTAL 将返回错误值 #VALUE!。 

在生活和工作中,我们有时会遇到Excel单元格隐藏求和的情况,那么如何计算隐藏单元格求和数值呢?

此处涉及SUBTOTAL函数,下面以此产品销售表为例。


微信截图_20190513151557.png


分界线.png


■打开WPS 2019 EXCEL表格,将光标放在A5列处,右键点击隐藏。


4.gif


将光标放在(D14)处,点击插入函数-三角函数-SUBTOTAL函数。

此时弹出对话框,共两项参数:

“函数序号”可填1-11数字,分别代表不同的汇总函数,如此函数序号对应表所示

1为AVERAGE函数不忽略隐藏值,101为它的忽略隐藏值。

假如我们要计算单元格的平均值,我们就输入1,表示使用AVERAGE函数计算单元格的平均值。

同理,如需要进行其他函数计算,则按功能填入函数序号。

“引用”指所要引用的区域,可自行添加多个引用区域。


巧用SUBTOTAL函数计算隐藏单元格2.gif

微信截图_20190514110721.png


■因为我们要计算的是隐藏A5 列后的求和数值,所以我们使用“函数序号”-9-SUM函数。

在“函数序号”中输入9,在“引用”中输入(D3:D13),意思是计算隐藏A5列后,D3至D13区域的求和值。

点击确定,就能得出隐藏A5列后所选区域的求和值了。


巧用SUBTOTAL函数计算隐藏单元格3.gif


■那么如何计算忽略隐藏值的区域求和呢?

我们将光标放在(E14)处,点击插入SUBTOTAL函数,在“函数序号”中输入109,在“引用”中输入(E3:E13)。

意思是计算忽略隐藏A5列后,E3至E13区域的求和值。

点击确定,就能得出忽略隐藏A5列后所选区域的求和值了。


巧用SUBTOTAL函数计算隐藏单元格4.gif

复制链接