Excel自动感应——隐藏行自动不参与计算，不隐藏则计算

 

在Excel中，如何忽略隐藏的行汇总数据

在使用Excel处理数据时，有时候需要把数据表中的若干行隐藏，然后对可见部分的数据进行求和或统计分析，如果使用SUM或COUNT等函数计算，则会将隐藏行中的数据也计算在内，无法满足用户的需求。如图 85?1所示为一份销售记录表，C3:C16销售额之和为5579，现将其中的二月份销售数据隐藏，如何才能快速求出可见部分的销售额之和3358？

图85?1忽略隐藏行求和

→ 解决方案：

使用SUBTOTAL函数忽略隐藏行对数据求和。

→ 操作方法

在E2单元格输入如下公式，按Enter键结束。

=SUBTOTAL(109,C3:C16)

→ 原理分析

本例使用109作为SUBTOTAL函数的function_num参数，相当于SUM函数功能，并且具有忽略隐藏行进行求和的特性，汇总C3:C16单元格区域可见单元格的销售额数据。

SUBTOTAL函数语法如下：

SUBTOTAL(function_num,ref1,[ref2],...])

其中，function_num参数为必需参数，用数字1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）表示，用于指定使用何种函数在列表中进行分类汇总计算。ref1……ref n参数为要对其进行分类汇总计算的第1至n个命名区域或引用。这些参数必须是对单元格区域的引用。

→ 知识扩展

SUBTOTAL函数对应功能及忽略不可见单元格特性

SUBTOTAL函数第1参数的数字与对应功能函数关系如表格 85?1 所示。

表格 85?1 SUBTOTAL第1参数代码对应功能表

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

在忽略不可见单元格进行计算时，SUBTOTAL函数具有以下特性：

1．当 function_num 为从 1 到 11 的常数时，SUBTOTAL函数将计算包括通过“隐藏行”命令所隐藏的行中的值，其功能与AVERAGE、COUNT等11个函数接近。

2．当 function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略计算通过“隐藏行”命令所隐藏的行中的值。

3．当使用“筛选”命令后，SUBTOTAL 函数忽略计算任何不包括在筛选结果中的行，不论使用什么 function_num 值。即SUBTOTAL 函数不计算因筛选而隐藏的数据。

4．如果在 ref1、ref2…中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。即ref1、ref2…参数引用的单元格是含有SUBTOTAL函数的公式计算结果，则该单元格的值将被忽略。

5．SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。例如，当 function_num 大于或等于 101 时需要分类汇总某个水平区域时，例如 SUBTOTAL(109,B2:G2)，则隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。

版权所有 转载须经Excel技巧网许可