錦子老師

在用Excel做資料統計時，常見有關於求和、平均值、計數和最大值、最小值等函數，但是這些函數解決不了篩選和隱藏的問題。這次介紹的SUBTOTAL函數在計算時是可以忽略不包括在篩選結果中的列，也就是說函數結果會隨著篩選結果的變化而變化，是不是很棒?而且這一個函數就能實現SUM、COUNT、AVERAGE、MAX和MIN的功能。

SUBTOTAL函數是怎麼來實現這麼多功能的呢?

下面我們一起來看一下。

語法：SUBTOTAL(Function_num,Ref1,…)

TIPS：

1、儲存格輸入=SUBTOTAL(，就會提示上述語法

2、此函數並不是「一個函數」，而是「一群函數」

3、此函數是Excel中唯一一個能統計使用者可見儲存格的函數

參數用法對照表

1、參數Function_num對應表

1-11和101-111代表的11個函數是一樣的，不過1-11在計算時會把手動隱藏列或欄的值也計算進去，而101-111則不會計入，忽略手動隱藏值。

2、範例說明

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

在D12儲存格輸入公式：「=SUBTOTAL(9,D2:D11)」後，按Enter鍵。9代表使用SUM函數，D2:D11即為求和範圍，計算結果等同於「=SUM(D2:D11)」，並且在輸入「=SUBTOTAL(」後，按「Alt + È」鍵，會出現如下圖所示的提示，所以就算記不住第一個參數的數值所代表的函數也沒關係。

下面我們點取「資料 > 排序與篩選 > 篩選」圖示，如下圖所示。

點取「A1」儲存格右方的「È」符號，將「1月」核取方塊打勾，如下圖所示。

點取「確定」鈕，篩選出1月的明細，D12儲存格值變為1月銷售量之和20,634；

如果篩選為2月的明細，D12儲存格變為2月銷售量之和22,314；

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

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

再來看看109代表的SUM函數與9代表的有什麼不同；

我們將D12儲存格公式改為：「=SUBTOTAL(109,D2:D11)」，並將第6、7行手動隱藏，此時計算結果為46,853，即第6、7行由於隱藏而在計算時被忽略了，即為忽略隱藏值。

而如果是9代表SUM函數，就算第6、7行隱藏了，在求和時也會被計入，計算結果為55,499。

最後，介紹一個SUBTOTAL函數的另一個亮點功能--「優生」。

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

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