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

忽略篩選行求和

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

我說(shuō)：“那好辦，換個(gè)求和函數(shù)就行。別用SUM了，試試SUBTOTAL?！?/p>

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

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

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

“那就聽(tīng)我細(xì)細(xì)給你道來(lái)~首先，來(lái)解決你表格的問(wèn)題?！闭f(shuō)著，我就打開(kāi)了她的表格，如圖所示。

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

=SUBTOTAL(9,A2:A6)

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

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

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

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

忽略隱藏行求和

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

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

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

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

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

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

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

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

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

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

? ? ? ??

（隱藏前）? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??? ? ? ? ? ? ? ?（隱藏后）

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

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

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

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

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

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

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

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

“我們了解了SUBTOTAL函數(shù)的特性之后，就可以用它來(lái)做一些什么，比如給列表編號(hào)。”

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

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

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

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

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

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

現(xiàn)在我們來(lái)逐步解釋一下公式=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。如圖所示。

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

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

相關(guān)學(xué)習(xí)推薦：excel教程

以上是Excel函數(shù)學(xué)習(xí)之以一敵十的SUBTOTAL函數(shù)！的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！