編按：剛?cè)腴T的小伙伴總喜歡抱怨“Excel的函數(shù)太多了，老是記不住，有沒有一個能匯總很多函數(shù)功能的函數(shù)呢？”不瞞你說，還真有！今天咱們要說的這個函數(shù)，其最大的功能就是可以替代11種函數(shù)使用。除此之外，它還可以根據(jù)不同的篩選結(jié)果，變更最后的計算結(jié)果！怎么樣？是不是光聽起來就很厲害呢？廢話不多說，趕緊和小編一起來看看吧！

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

一、忽略篩選行求和

“苗老師，我碰到了個問題”，小白一上班就來找我，她說：“我有一張合計的表，打印的時候不想打印某些人的內(nèi)容，就把它們用篩選隱藏了，但是每次求和都要更改求和區(qū)域，老麻煩了?！?br/>

我說：“那好辦，換個求和函數(shù)就行。別用SUM了，試試SUBTOTAL。”

?小白：“這是什么函數(shù)，沒用過?！?/p>

?我說：“這函數(shù)可比SUM函數(shù)厲害多了，能應(yīng)對好幾種求和場景呢！”

?小白：“這么厲害，那你可得教教我?！?/p>

?“那就聽我細細給你道來~首先，來解決你表格的問題?！闭f著，我就打開了她的表格，如下圖所示。

“現(xiàn)在，你的表格使用的是SUM函數(shù)求和，我們把它換成SUBTOTAL函數(shù)，你再看看”。說完，我在單元格A7中輸入了公式。

=SUBTOTAL(9,A2:A6)

“真的變了！”接著小白又篩選了一些別的行，發(fā)現(xiàn)都可以得到她想要的結(jié)果，十分高興。不過隨后她又發(fā)現(xiàn)了新大陸，“那這個9是什么意思呢？”

我：“這個9呀，表示忽略未篩選出的數(shù)據(jù)，僅對篩選后的結(jié)果進行求和”。

小白：“聽你這說法，還有其他數(shù)字代表其他的含義咯？”

我：“當(dāng)然，那我就再跟你說說其他數(shù)字的含義吧！”

二、忽略隱藏行求和

我們有時候會碰到這種情況，有一列數(shù)字，需要隱藏幾個不進行運算的數(shù)據(jù)。如果是直接使用SUM，是無法得到正確結(jié)果的，如下圖所示。

即使用上剛學(xué)的SUBTOTAL函數(shù)的參數(shù)“9”，也是無法實現(xiàn)的，如下圖所示。?

這時候我們就要考慮換一個參數(shù)了。?

下面有請參數(shù)“109”，登場！

公式：=SUBTOTAL(109,A1:A5)

如下圖所示，將SUBTOTAL函數(shù)第一參數(shù)變?yōu)椤?09”后，就能輕松得到忽略隱藏行后的求和結(jié)果！如下圖所示。?

參數(shù)“109”的作用是對可見數(shù)值進行求和，它既可以對隱藏后的數(shù)據(jù)求和，也可以對篩選后的數(shù)據(jù)求和。而參數(shù)“9”只能使用在篩選行，對隱藏行則無效。

三、SUBTOTAL其他參數(shù)的應(yīng)用

SUBTOTAL不僅僅局限在求和領(lǐng)域，平均值、最大值、標(biāo)準(zhǔn)差、方差，都能求，只需改變它的第一參數(shù)即可。例如，現(xiàn)在我們要統(tǒng)計忽略隱藏行的最大值，如下圖所示。

公式：=SUBTOTAL(104,A1:A5)

隱藏了最大值“8”后，直接在單元格A6中得到了當(dāng)前可見的最大值“7”。

那為什么是104呢？其實SUBTOTAL函數(shù)里面有一套數(shù)字代表規(guī)則，今天咱們就把其他的參數(shù)都說一說，包括求平均值、最大值、最小值、標(biāo)準(zhǔn)差、方差等11種功能。有的常用，有的不常用，大家結(jié)合自己的需求來選擇。下面是11種參數(shù)的對照表。

四、拓展部分1：只統(tǒng)計分類匯總

我們在制表的時候，經(jīng)常會碰到這樣一種匯總情況，在同表內(nèi)進行分項匯總，如下圖所示。

如果使用SUM進行匯總，則會統(tǒng)計出所有的數(shù)據(jù)，如下圖所示。?

可是我們只想合計各個小計的內(nèi)容呀！別慌，只需把SUM換成SUBTOTAL就可以得到我們想要的答案。如下圖所示。

這是為什么呢？其實SUBTOTAL除了能忽略掉被隱藏、篩選的行外，還會忽略掉包含SUBTOTAL，以及AGGREGATE函數(shù)的單元格。單元格B3、B6、B10都是用SUBTOTAL函數(shù)計算的小計，自然在最后用SUBTOTAL函數(shù)求和時，會被忽略掉。如下圖所示。

五、拓展部分2：不間斷序號

“我們了解了SUBTOTAL函數(shù)的特性之后，就可以用它來做一些什么，比如給列表編號?！?br/>

?“什么，列表編號不是用鼠標(biāo)拉一下就好了嗎？”

?“不一樣~我的編號，可是自動的哦！無論是刪除行還是隱藏行，編號都能自動重新排列!”

?“這么神奇，那我可要好好學(xué)學(xué)。”

其實它非常簡單，假設(shè)我有一張列表，目前序號列是空的，如下圖所示。?

在A2單元格輸入公式：=SUBTOTAL(103,B$2:B2)，然后下拉填充，就能得到我們想要的序號。如下圖所示。?

我們試著來隱藏一行，就會發(fā)現(xiàn)，序號仍然是按照順序排列的，并沒有中斷，如下圖所示。?

現(xiàn)在我們來逐步解釋一下公式=SUBTOTAL(103,B$2:B2)

103：查看上述參數(shù)對照表可以得知，103的作用是忽略隱藏行和被篩選值，統(tǒng)計非空單元格數(shù)。

?B$2:B2：A2單元格內(nèi)的區(qū)域是B$2:B2，目的是，統(tǒng)計出B2:B2區(qū)域中非空單元格數(shù)，結(jié)果為1。在公式下拉后，A3單元格內(nèi)的區(qū)域變成了B$2:B3，那么統(tǒng)計的非空單元格數(shù)就變成了兩個，得到的結(jié)果為?2。如下圖所示。?

以此類推，隨著公式的下拉，我們就可以得到一組連續(xù)的序號。再結(jié)合SUBTOTAL函數(shù)第一參數(shù)只計算可見數(shù)值的特性，就可以得到一組不間斷的序號！

你還知道哪些關(guān)于SUBTOTAL函數(shù)的妙用呢？一起來交流學(xué)習(xí)吧！

＊＊＊＊部落窩教育－excel不一樣的求和技巧＊＊＊＊

原創(chuàng)：苗旭/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號：exceljiaocheng