【SUBTOTAL】一個“以一敵十”的函式（二）

在用Excel做資料統計時，常見有關於求和、平均值、計數和最大值、最小值等函式，但是這些函式解決不了篩選和隱藏的問題。

今天介紹的Subtotal函式在計算時是可以忽略不包括在篩選結果中的行，也就是說函式結果會隨著篩選結果的變化而變化，而且這一個函式就能實現Sum 、Count、Average、Max和Min的功能。

Subtotal定義：

計算各種總計值

Subtotal語法：

=SUBTOTAL(function_num,ref1,…)

引數定義：

function_num：以編號的形式指定總計時採用的功能

ref：指定要計算的值,或值所在的單元格引用.也可指定區域

函式特點：

(1) 如果在 ref1, ref2,… 中有其他的分類彙總(巢狀分類彙總),將忽略這些巢狀分類彙總,以避免重複計算.也就是在資料區域中有SUBTOTAL獲得的結果將被忽略！

(2) 當function_num為從1到11的常數時,SUBTOTAL函式將包括通過"格式"選單的"行"子選單下面的"隱藏"命令所隱藏的行中的值.當您要分類彙總列表中的隱藏和非隱藏值時，請使用這些常數。

(3) 當 function_num 為從 101 到 111 的常數時，SUBTOTAL 函式將忽略通過“格式”選單的“行”子選單下面的"隱藏"命令所隱藏的行中的值。

(4) 當您只分類彙總列表中的非隱藏數字時,使用這些常數.但不論使用什麼function_num值,SUBTOTAL函式都會忽略任何不包括在篩選結果中的行，而SUBTOTAL函式不適用於資料行或水平區域，隱藏某一列不影響分類彙總，但是隱藏分類彙總的垂直區域中的某一行就會對其產生影響。

(5) 可以代替上面說的11種函式,當有上面說的兩種特點情況時,就可以使用SUBTOTAL來完成。

所以在需要處理隱藏資料相關的應用時,SUBTOTAL是其它函式無法代替的,也是SUBTOTAL最大最重要的特點.

函式示例：

為便於大家理解，我們就用最常見9或109代表的SUM函式來舉例好了。

在D12單元格輸入

=SUBTOTAL(9,D2:D11)

9代表使用SUM函式，D2:D11即為求和區域，計算結果等同於=SUM(D2:D11)，並且在輸入SUBTOTAL函式的第一個引數時，會出現像下面這樣的提示，所以就算記不住數字所代表的函式也沒關係。

下面我們篩選出華北地區的明細，D12單元格值變為華北銷售量之和1265；

如果篩選為華南地區的明細，D12單元格變為華南地區的銷售量之和1807；

現在，有木有理解SUBTOTAL的神奇之處呢?

這樣以來，我們再也不用篩選後還點來點去求和了，一個函式搞定，多省心省力!

再來看看109代表的SUM函式與9代表的有什麼不同？
我們將函式改為=SUBTOTAL(109,D2:D11)，並將第6、7、8行手動隱藏，此時計算結果為2373，即第6、7、8行由於隱藏而在計算時被忽略了，即為忽略隱藏值，如下：

而如果是9代表SUM函式，就算第6、7、8行隱藏了，在求和時也會被計入，如下：

最後，介紹一個SUBTOTAL函式的另一個亮點功能–“優生”。

SUBTOTAL函式遇到同類就避開了，就是不會統計由SUBTOTAL計算出來的數值。

下面的表格經常用到，如果組數比較多，用SUM求合計要選擇很多區域，但是用SUBTOTAL可以放心大膽地把D2：D12都選上，因為遇到D7和D13同類就不計算了。

謝謝大家的觀看，我們下一期再見，如果有什麼問題歡迎評論區留言或私信我們

如果你對excel函式公式大全感興趣，可以關注我們，在課程沒更新之前，可以先看看我們前面的課程。

你學會了嗎？

想學習更多的函式知識，歡迎關注轉發