今天是常用函数的第六篇，我们来学习下SUBTOTAL函数，它也是职场人必备的函数之一，最大的特点就是可以不统计的隐藏的数据区域，如果在工作中遇到仅统计可见区域的问题，最简单的解决方法就是利用SUBTOTAL，下面我们来了解下它是的使用方法
5 Z7 Y% x' b3 Y' R一、作用与参数8 L# N5 g7 \: J! p6 c/ p
) E# V! J: H( E5 _
SUBTOTAL：返回一个数据区域的分类汇总的结果，它一共有11种对应的计算规则，分别使用对应的数字来代替. y# ~) Q. Z u) h
语法： =SUBTOTAL(function_num,ref1,[ref2],...)$ H4 v1 g# W% W, C1 |% X7 i1 }& k+ d# \; h
第一参数：数据汇总的规则，当参数值为1-11的时候函数会计算隐藏数据区域，当参数值为101-111的时候函数不会计算隐藏的数据区域，具体的参数对应，详见下图
, {9 |& x2 `- B& V2 O1 Y4 T第二参数：第一个统计区域
9 q6 i: X4 ?) b$ {. Z K9 Z. u# B第三参数：第二个统计区域' \$ ~4 K! O* D% u3 B& M
以此类推，最多可以设置254个引用的数据区域 Z2 `. F, y- v) d2 U* K
需要注意的是这个仅仅对列数据有效，对行数据是无效的。2 l9 I/ W, G& q* d6 H! w
5 W( k$ Z" J& i8 G
8 G$ c7 W i5 L+ j; Y二、使用效果
" h: P. H; `8 V- a8 [& o6 m5 ^# V4 z7 Z) R# f" h
如下图，我们想要计算下【考核得分】的均值，SUBTOTAL想要计算均值，我们就需要第1参数设置为1或者101，它们唯一的区别就是否统计隐藏的数据区域
S2 P( t1 l) t4 s计算隐藏数据：=SUBTOTAL(1,B2:B11)
! Q1 A# C" W7 F( i4 V不计算隐藏数据：=SUBTOTAL(101,B2:B11)- E: m- }+ N9 @( h
如下动图所示，当没有隐藏区域的时候2个结果都是一模一样的，如果对数据进行隐藏，参数值为101的公式就会仅仅计算显示的数据，而不计算隐藏数据。参数值为1的公式结果不会发生变化。
) K- d3 I( m7 d# q& g/ _+ Q/ J筛选后求和、计数，最大值等等都值可以的，我只需要将第一参数设置为100以上的对应数值即可
. p0 o$ P' {' y. s5 V
" j9 j4 ]/ y$ i6 @8 C' C' X4 ~
6 `" L$ `: X5 A% g! W三、筛选后序号自动更新* W( i: K' r: I" P+ `2 h# T
1 |- p" P- U7 N
利用SUBTOTAL我们可以让序号在筛选后保持自动更新，这个技巧在实际工作中还是非常好用的。0 e% w9 M2 k+ k, b, u
公式：=SUBTOTAL(103,$B$2:B2)
) k- a! G8 D4 C- W如下图所示，我们在A2单元格输入公式，然后向下填充会得到一列序号，之后我们筛选下组合，无论怎么筛选，序号这列都是从1开始，并且是连续的. T0 @0 b1 `3 Q& O6 [
- j I5 L( @4 g0 ]8 w# t6 C6 ]1 E0 O! ~) {) d: j7 I1 U
以上就是今天的全部内容，其实对于SUBTOTAL函数，我们只需要记得2点就行了
1 ~" U2 Z1 \! j) Y: ^( h' t1 B1.函数是可以不统计隐藏区域的) G2 F7 a3 z8 `: Z* c* T$ r8 v* E' G
2.第一参数设置为100以下会统计隐藏区域，设置为100以上不会统计隐藏区域' n0 s k" t) v$ y4 M1 d' Q
怎么样，你学会了吗？# y6 e5 S1 T7 e" G& \
我是Excel从零到一，关注我，持续分享更多Excel技巧
# V# p8 Z6 Y2 }# V& a想要从零学习Excel，这里↓↓↓ |
提升卡
置顶卡(30天)
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡