剛入門的小伙伴總喜歡抱怨“excel的函數太多了，老是記不住，有沒有一個能匯總很多函數功能的函數呢？”不瞞你說，還真有！今天咱們要說的這個函數，其最大的功能就是可以替代11種函數使用。除此之外，它還可以根據不同的篩選結果，變更最后的計算結果！怎么樣？是不是光聽起來就很厲害呢？廢話不多說，趕緊和小編一起來看看吧！
忽略篩選行求和
“苗老師，我碰到了個問題”，小白一上班就來找我，她說：“我有一張合計的表，打印的時候不想打印某些人的內容，就把它們用篩選隱藏了，但是每次求和都要更改求和區域，老麻煩了。”
我說：“那好辦，換個求和函數就行。別用SUM了，試試SUBTOTAL。”
小白：“這是什么函數，沒用過。”
我說：“這函數可比SUM函數厲害多了，能應對好幾種求和場景呢！”
小白：“這么厲害，那你可得教教我。”
“那就聽我細細給你道來~首先，來解決你表格的問題。”說著，我就打開了她的表格，如圖所示。
“現在，你的表格使用的是SUM函數求和，我們把它換成SUBTOTAL函數，你再看看”。說完，我在單元格A7中輸入了公式。
=SUBTOTAL(9,A2:A6)
“真的變了！”接著小白又篩選了一些別的行，發現都可以得到她想要的結果，十分高興。不過隨后她又發現了新大陸，“那這個9是什么意思呢？”
我：“這個9呀，表示忽略未篩選出的數據，僅對篩選后的結果進行求和”。
小白：“聽你這說法，還有其他數字代表其他的含義咯？”
我：“當然，那我就再跟你說說其他數字的含義吧！”
忽略隱藏行求和
我們有時候會碰到這種情況，有一列數字，需要隱藏幾個不進行運算的數據。如果是直接使用SUM，是無法得到正確結果的，如圖所示。
即使用上剛學的SUBTOTAL函數的參數“9”，也是無法實現的，如圖所示。
這時候我們就要考慮換一個參數了。
下面有請參數“109”，登場！
公式：=SUBTOTAL(109,A1:A5)
如下圖所示，將SUBTOTAL函數第一參數變為“109”后，就能輕松得到忽略隱藏行后的求和結果！如圖所示。
參數“109”的作用是對可見數值進行求和，它既可以對隱藏后的數據求和，也可以對篩選后的數據求和。而參數“9”只能使用在篩選行，對隱藏行則無效。
SUBTOTAL其他參數的應用
SUBTOTAL不僅僅局限在求和領域，平均值、最大值、標準差、方差，都能求，只需改變它的第一參數即可。例如，現在我們要統計忽略隱藏行的最大值，如圖6所示。
公式：=SUBTOTAL(104,A1:A5)
? ? ? ??
（隱藏前）? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??? ? ? ? ? ? ? ?（隱藏后）
隱藏了最大值“8”后，直接在單元格A6中得到了當前可見的最大值“7”。
那為什么是104呢？其實SUBTOTAL函數里面有一套數字代表規則，今天咱們就把其他的參數都說一說，包括求平均值、最大值、最小值、標準差、方差等11種功能。有的常用，有的不常用，大家結合自己的需求來選擇。下面是11種參數的對照表。
|
計算時忽略被篩選值 |
計算時忽略隱藏行和被篩選值 |
作用 |
對應函數 |
|
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 |
拓展部分1：只統計分類匯總
我們在制表的時候，經常會碰到這樣一種匯總情況，在同表內進行分項匯總，如圖所示。
如果使用SUM進行匯總，則會統計出所有的數據，如圖所示。
可是我們只想合計各個小計的內容呀！別慌，只需把SUM換成SUBTOTAL就可以得到我們想要的答案。如圖所示。
這是為什么呢？其實SUBTOTAL除了能忽略掉被隱藏、篩選的行外，還會忽略掉包含SUBTOTAL，以及AGGREGATE函數的單元格。單元格B3、B6、B10都是用SUBTOTAL函數計算的小計，自然在最后用SUBTOTAL函數求和時，會被忽略掉。
拓展部分2：不間斷序號
“我們了解了SUBTOTAL函數的特性之后，就可以用它來做一些什么，比如給列表編號。”
“什么，列表編號不是用鼠標拉一下就好了嗎？”
“不一樣~我的編號，可是自動的哦！無論是刪除行還是隱藏行，編號都能自動重新排列!”
“這么神奇，那我可要好好學學。”
其實它非常簡單，假設我有一張列表，目前序號列是空的，如圖所示。
在A2單元格輸入公式：=SUBTOTAL(103,B$2:B2)，然后下拉填充，就能得到我們想要的序號。如圖所示。
我們試著來隱藏一行，就會發現，序號仍然是按照順序排列的，并沒有中斷，如圖所示。
現在我們來逐步解釋一下公式=SUBTOTAL(103,B$2:B2)
-
103：查看上述參數對照表可以得知，103的作用是忽略隱藏行和被篩選值，統計非空單元格數。
-
B$2:B2：A2單元格內的區域是B$2:B2，目的是，統計出B2:B2區域中非空單元格數，結果為1。在公式下拉后，A3單元格內的區域變成了B$2:B3，那么統計的非空單元格數就變成了兩個，得到的結果為2。如圖所示。
以此類推，隨著公式的下拉，我們就可以得到一組連續的序號。再結合SUBTOTAL函數第一參數只計算可見數值的特性，就可以得到一組不間斷的序號！
你還知道哪些關于SUBTOTAL函數的妙用呢？歡迎留言分享給我們哦~喜歡文章的小伙伴不妨點下“在看”，支持我們哦！
相關學習推薦：excel教程
