Excel如何加總「篩選過」的數值，不計算隱藏的儲存格？

在使用 Excel 做加總計算時，大家都知道要使用「SUM」這個指令；不過有時候只想要計算「篩選過後」的數值，或者是手動將部份表格隱藏起來，但 SUM 指令下，仍然會把這些表格中看不到但仍存在的數值加起來，已沒有辦法處理呢？

舉例來說，下圖是 8 月份支出表，裡面可以看到油錢、餐費、娛樂費、購物費用等等項目。

假使我只想把「油錢」加總起來，這時首先會用到 Excel 的「篩選」功能：

如下圖，此時雖然把 Excel 中的「油錢」篩選出來了，但是上面的「小計」欄位（下圖使用 SUM 函數），仍然顯示九萬多，明顯與表格不符；原因就是 Excel 把篩選前的數值也都一併加進來了。

以下就教大家如何使用 Excel 計算「篩選過後」的數值！包括加總、平均、最大值最小值等等函數，都可以統一用這個「SUBTOTAL」函數來計算！

Excel 如何計算「篩選後」的數值？（加總、平均皆可）

這時就要捨棄 SUM 函數，改用「SUBTOTAL」函數。

SUBTOTAL 函數說明如下：

=SUBTOTAL(計算方式, 資料範圍)

計算方式就代表加總啊、平均值啊、最大值啊、最小值等等，可參考下列表格：

計算方式（計算手動隱藏的列）	計算方式（不計算手動隱藏的列）	函數
1	101	AVERAGE（平均值）
2	102	COUNT（資料個數）
3	103	COUNTA（非空白的資料個數）
4	104	MAX（最大值）
5	105	MIN（最小值）
6	106	PRODUCT（乘積）
7	107	STDEV（依樣本求標準差）
8	108	STDEVP（依整個母體求標準差）
9	109	SUM（加總）
10	110	VAR（依樣本求變異數）
11	111	VARP（依整個母體求變異數）

看不懂嗎？不要緊，以下用範例來解釋。

用 SUBTOTAL 加總篩選後的值

現在假設我要計算下面這張圖中，篩選過後的值，也就是只計算油錢 或 餐費 或 娛樂費…

根據前面的表格，我要選用的計算方法是「加總」，而加總有兩個數值，分別為「9」和「109」。

這邊先用「9」來試試看，根據 SUBTOTAL 的函數 =SUBTOTAL(計算方法,資料範圍)，我設定的函數會變成：

=SUBTOTAL(9,B4:B20)

這時再把表格用篩選挑出來，假設說只篩選「油錢」，可以看到 SUBTOTAL 就會把油錢「加總(9)」起來，數值是 4500 沒錯。

換篩另一種，購物費用好了，一樣可以適用：

那怕你用顏色來做篩選，假使說篩選出紅色底的表格，SUBTOTAL 一樣可以計算。

由於上方的範例是「總共花了多少」，所以要用到的是加總，所以計算方法裡面填的數值是 9 或 109；假使我想算的是平均花了多少，那就要填 1 或 101 了。

那麼，9 跟 109、1 跟 101，差在哪裡呢？請見下方說明。

SUBTOTAL 如何不計算「手動隱藏」的表格數值

如果你在 Excel 裡面手動隱藏表格，無論是 SUM 或 SUBTOTAL 上面的示範，都不會影響數值，這些函數會把手動隱藏的數值也一併計算。

如果想要「不計算手動隱藏」，那在 SUBTOTAL 的「計算方法」中，就要用 101、102、103…

假使我有一筆花費想要先手動隱藏起來，而且不想被計算進去的話，上方的範例就要從 =SUBTOTAL(9,B4:B20) 改為=SUBTOTAL(109,B4:B20)。

這樣隱藏起來的數值就不會被計算進去了。

 

總結：SUBTOTAL 函數的使用方式

以上就是 Excel 中，如何加總、平均等等，計算「篩選過後」的數值的方法；如果要不計算隱藏表格中的數值，也是用一樣的函數 SUBTOTAL，只是在計算方法中要改用另一種數值。

公式：

=SUBTOTAL(計算方法,資料範圍)

計算方法為數值，參考表格如下：

計算方式（計算手動隱藏的列）	計算方式（不計算手動隱藏的列）	函數
1	101	AVERAGE（平均值）
2	102	COUNT（資料個數）
3	103	COUNTA（非空白的資料個數）
4	104	MAX（最大值）
5	105	MIN（最小值）
6	106	PRODUCT（乘積）
7	107	STDEV（依樣本求標準差）
8	108	STDEVP（依整個母體求標準差）
9	109	SUM（加總）
10	110	VAR（依樣本求變異數）
11	111	VARP（依整個母體求變異數）

而上表的兩種計算方式，差在「是否要計算手動隱藏的表格」，若不想計算，用右邊的 10* 數值。

更多 Excel 小技巧，請見延伸閱讀：