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

excluded

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

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

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

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

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

Excel 如何計算「篩選後」的數值?(加總、平均皆可)

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

SUBTOTAL 函數說明如下:

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

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

計算方式(計算手動隱藏的列)計算方式(不計算手動隱藏的列)函數
1101AVERAGE(平均值)
2102COUNT(資料個數)
3103COUNTA(非空白的資料個數)
4104MAX(最大值)
5105MIN(最小值)
6106PRODUCT(乘積)
7107STDEV(依樣本求標準差)
8108STDEVP(依整個母體求標準差)
9109SUM(加總)
10110VAR(依樣本求變異數)
11111VARP(依整個母體求變異數)

看不懂嗎?不要緊,以下用範例來解釋。

用 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(計算方法,資料範圍)

計算方法為數值,參考表格如下:

計算方式(計算手動隱藏的列)計算方式(不計算手動隱藏的列)函數
1101AVERAGE(平均值)
2102COUNT(資料個數)
3103COUNTA(非空白的資料個數)
4104MAX(最大值)
5105MIN(最小值)
6106PRODUCT(乘積)
7107STDEV(依樣本求標準差)
8108STDEVP(依整個母體求標準差)
9109SUM(加總)
10110VAR(依樣本求變異數)
11111VARP(依整個母體求變異數)

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

更多 Excel 小技巧,請見延伸閱讀:

Excel 一次選取/刪除所有空格:「特殊目標」搜尋所有空白儲存格

https://applealmond.com/posts/76587