編按：

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

忽略篩選行求和

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

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

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

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

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

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

圖1

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

=SUBTOTAL(9,A2:A6)

圖2

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

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

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

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

忽略隱藏行求和

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

圖3

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

圖4

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

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

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

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

圖5

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

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

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

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

（隱藏前）                                       （隱藏后）

圖6

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

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

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

我們?cè)谥票淼臅r(shí)候，經(jīng)常會(huì)碰到這樣一種匯總情況，在同表內(nèi)進(jìn)行分項(xiàng)匯總，如圖7。

圖7

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

圖8

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

圖9

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

圖10

拓展部分2：不間斷序號(hào)

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

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

“不一樣~我的編號(hào)，可是自動(dòng)的哦！無論是刪除行還是隱藏行，編號(hào)都能自動(dòng)重新排列!”

“這么神奇，那我可要好好學(xué)學(xué)?！?/span>

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

圖11

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

圖12

我們?cè)囍鴣黼[藏一行，就會(huì)發(fā)現(xiàn)，序號(hào)仍然是按照順序排列的，并沒有中斷，如圖13所示。

圖13

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

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

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

圖14

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

你還知道哪些關(guān)于SUBTOTAL函數(shù)的妙用呢？歡迎留言分享給我們哦~喜歡文章的小伙伴不妨點(diǎn)下“在看”，支持我們哦！

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

最簡單的條件求和函數(shù)《DSUM，最簡單的條件求和函數(shù)！你知道不？》

細(xì)數(shù)自動(dòng)求和那些坑《小白最愛的自動(dòng)求和，恰是連專家也坑的老虎》

求和函數(shù)大集錦《求和，我是認(rèn)真的(Excel函數(shù)教程）》