編按：

在Excel里有個神奇的函數(shù)：SUBTOTAL。據(jù)說這個函數(shù)從Excel2003就存在了，可是現(xiàn)在都2022了，還有很多人不了解他的妙用，今天就和大家再來認識一下SUBTOTAL函數(shù)。

妙用1：對篩選數(shù)據(jù)求和

可以看到，公式=SUBTOTAL(9,D2:D22)可以按照篩選的結果實現(xiàn)動態(tài)求和。

但是請注意，這個公式只對篩選有效，如果要排除隱藏數(shù)據(jù)則需要對公式進行修改。

妙用2：忽略隱藏數(shù)據(jù)求和

將公式改成=SUBTOTAL(109,D2:D22)，就能忽略隱藏行的數(shù)據(jù)求和。

這個公式同時也對篩選有效，這就是SUBTOTAL函數(shù)第一參數(shù)的神奇之處。

可以這樣說，這個函數(shù)的秘密全部都在第一參數(shù)，第二參數(shù)只是選擇要進行統(tǒng)計的數(shù)據(jù)區(qū)域，并沒什么特殊的。

至于第一參數(shù)都有些什么秘密，可以參照下面這個圖片。

注意功能參數(shù)代碼的區(qū)別，只是包含或者忽略隱藏行，對于篩選行都是有效的。

計算方式則體現(xiàn)了這個函數(shù)的多功能特性，前面兩個示例中用到了9和109，都是同樣的計算方式求和。

另外幾個比較常用的統(tǒng)計方式例如：平均值、最大值、最小值等等，只需要改成對應的代碼即可。

妙用3：對篩選后的數(shù)據(jù)求最大值。

=SUBTOTAL(4,D2:D22)

通過上述示例，發(fā)現(xiàn)一個問題，A列的序號經(jīng)過篩選以后變得不連續(xù)了，怎么辦呢？

正好可以使用SUBTOTAL來得到一個動態(tài)連續(xù)序號的效果。

妙用4：篩選后的連續(xù)序號

公式為=SUBTOTAL(3,$B$1:B2)-1

在這個例子中，數(shù)據(jù)區(qū)域的用法是有點小技巧的，鎖定了區(qū)域的開始位置，但是結束位置則是會隨著公式下拉發(fā)生變化，這樣就實現(xiàn)了區(qū)域遞增的目的。

細心的同學可能會有個疑問，公式用=SUBTOTAL(3,$B$2:B2)不就行了嗎，為什么非要從第一行開始，然后再減一，是不是多此一舉。

行不行試試便知。

通過測試可以看到，不管篩選條件是什么，最后都有一行是一直出現(xiàn)的，序號雖然連續(xù)了，但是多出來一行數(shù)據(jù)也不行啊。

為什么會出現(xiàn)這種情況？

還是要從SUBTOTAL函數(shù)本身的特殊性來解釋，這個函數(shù)也叫分類匯總函數(shù)，通常情況下總是會將最后一行默認是匯總行，所以不受篩選的影響一直都顯示。

如果不想出現(xiàn)這種默認匯總行的話，則需要在函數(shù)后面添加一個計算，加減乘除都可以。

最后一個動畫的公式改成=SUBTOTAL(3,$B$2:B2)*1實際上也是可以的，有興趣的同學可以自己測試一下。

好啦，以上就是今天的所有內(nèi)容，感謝你的收看。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

如何計算員工累計達標的月份，你需要一個SUBTOTAL函數(shù)！

去掉最低分和最高分算平均分：SUBTOTAL等四個函數(shù)法

別怕，VBA入門級教程來了，條件語句很簡單！

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權申明：

本文作者老菜鳥；同時部落窩教育享有專有使用權。若需轉載請聯(lián)系部落窩教育。