excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)

在实际的数据统计分析中,经常会遇到很多复杂的因素,例如,对隐藏的行或计算结果返回错误类型的值不予统计等等……如果此时还用常规的Sum系列、Count系列、Average系列等函数去做数据统计分析,将会是难上加难或者根本无法完成。此时,如果要一个只对“可见”单元格或区域、忽略错误等类型进行统计分析的函数,将会是“雪中送炭”……今天,小编带大家了解一下万能的分类统计汇总函数Subtotal和Aggregate。


一、分类汇总函数:Subtotal,返回指定区域的分类汇总结果。

功能:返回列表或数据库中的分类汇总。

语法结构:=Subtotal(汇总方式,数据区域1,[数据区域2]……[数据区域254])。

其中【汇总方式】分为1~11(包含隐藏值)和101~111(忽略隐藏值)两大类。具体功能请参阅下表。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(1)

注意事项:

1、参数【汇总方式】必须为数值类型或可转换为数值的数据,且必须为1~11或101~111以内的数字,否则返回错误值“#VALUE!”。

2、如果计算的区域总存在隐藏行,使用代码1~11时,隐藏的行仍然在统计的范围内,如果使用101~111时,只对“可见”区域有效,暨忽略隐藏的行。

3、Subtotal函数对隐藏列区域无效,即如果统计的数据范围内包含隐藏的列,不管使用代码1~11还是101~111,这些隐藏的列数据仍然在统计的范围内。

4、【数据区域】只支持二维引用,不支持三维引用,否则返回错误值“#VALUE!”。


(一)、分类汇总函数Subtotal:隐藏汇总。

目的:对“可见”数据区域进行汇总。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(2)

方法:

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

解读:

1、从示例中可以看出,未“隐藏”之前,3种形式的计算结果是相同的,但“隐藏”之后,代码“109”的计算结果和其它2种的不同,原因在于代码“109”忽略隐藏行的数据,只对“可见”数据区域有效。

2、其它代码所对应的功能和求和的用法相同。


(二)分类汇总函数Subtotal:筛选汇总。

目的:按部门统计“年薪”。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(3)

方法:

在目标单元格中输入公式:=SUBTOTAL(9,E3:E9)、=SUBTOTAL(109,E3:E9)。

解读:

1、在筛选数据后,代码“9”和代码“109”的返回结果是相同的,而且为“可见”单元格的汇总数据。

2、其它代码所对应的功能和求和的用法相同。


(三)分类汇总函数Subtotal:经典用法之保持序号的连续性。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(4)

方法:

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

解读:

代码“3”或“103”代表的函数为Counta,即非空单元格的个数。而对于筛选,代码“3”或“103”都是对可见单元格有效。所以用公式=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)都统计的是从当前单元格的上一单元格开始的非空单元格的个数。


二、分类汇总函数:Aggregate,返回指定区域的分类汇总结果。

功能:返回列表或数据库中的分类汇总。

语法结构:=Aggregate(汇总方式,忽略方式,数据区域1,[数据区域2]……[数据区域254])。

其中【汇总方式】为1~19之间的数字。具体功能请参阅下表。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(5)

【忽略方式】表示要在函数的计算区域中忽略那些值,该参数为0~7之间的数字。具体功能请参阅下表。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(6)

注意事项:

1、当【汇总方式】参数为14~19时,必须制定【数据区域2】的值,否则Aggregate函数将返回错误值“#VALUE!” 。

2、如果Aggregate函数的引用中包含嵌套的Aggregate和Subtotal函数,则将忽略这两个函数。

3、Aggregate函数适用于数据列或垂直区域,不适用于数据行或水平区域。

4、Aggregate函数必须在10及以上版本中使用。


(一)、分类汇总函数Aggregate:隐藏汇总。

目的:对隐藏后的“可见”数据区域进行汇总。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(7)

方法:

在目标单元格中输入公式:=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读:

1、如果只是对隐藏后的“可见”区域进行求和,可以使用公式=SUBTOTAL(109,E3:E9)完成。

2、上述公式中的忽略代码“1”、“3”、“5”、“7”的一个共同功能是“忽略隐藏行”。

3、其它代码所对应的功能和求和的用法相同。


(二)、分类汇总函数Aggregate:忽略错误值隐藏汇总。

目的:忽略错误值并对“可见”区域汇总。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(8)

方法:

在目标单元格中输入公式:=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读:

1、数据区域中的值包含一个错误值“#N/A”, 此时用Sum或Subtotal函数无法完成汇总任务。所以必须对错误值#N/A 忽略,所以用Aggregate函数替代Subtotal或Sum函数。

2、上述公式中的忽略代码“3”、“7”的一个共同特点“忽略隐藏行、错误值”。

3、其它代码所对应的功能和求和的用法相同。


(三)、分类汇总函数Aggregate:筛选汇总。

目的:对筛选后的“可见”数据区域进行汇总。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(9)

方法:

在目标单元格中输入公式:=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读:

1、如果只是对筛选后的“可见”区域进行求和,可以使用公式=SUBTOTAL(9,E3:E9)或=SUBTOTAL(109,E3:E9)完成。

2、上述公式中的忽略代码“1”、“3”、“5”、“7”的一个共同功能是“忽略隐藏行”。

3、其它代码所对应的功能和求和的用法相同。


(四)、分类汇总函数Aggregate:忽略错误值筛选汇总。

目的:忽略错误值并对“可见”区域汇总。

excel中隐藏或筛选数据后不显示(Excel中隐藏或筛选数据后)(10)

方法:

在目标单元格中输入公式:=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读:

1、数据区域中的值包含一个错误值“#N/A”, 此时用Sum或Subtotal函数无法完成汇总任务。所以必须对错误值#N/A 忽略,所以用Aggregate函数替代Subtotal或Sum函数。

2、上述公式中的忽略代码“3”、“7”的一个共同特点“忽略隐藏行、错误值”。

3、其它代码所对应的功能和求和的用法相同。


结束语:

从上述的示例中可以看出,Subtotal函数和Aggregate函数都是对指定的区域或数据库进行分类汇总,其中Subtotal函数在汇总的时候不能进行嵌套,同时数据源中不能有错误类型值,而Aggregate函数可以嵌套,如果数据源中有错误类型值,可以忽略不计。Subtotal函数“隐藏”和“筛选”是针对不同的代码而言的,而Aggregate函数只对“可见”区域有效,即“隐藏”和“筛选”都是同一个代码,例如求和的代码都为“9”。

Subtotal函数和Aggregate函数在学习时,数字代码较多,容易混淆,建议大家根据系统的“联想”功能有针对性的选择实用代码,不建议死记硬背哦!如果亲有更好的学习或使用技巧,欢迎在留言区留言讨论哦!


#我要上# #Excel函数公式#

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页