close

480

SUBTOTAL函數-篩選後資料統計的必備函數

示範檔

範例檔

480.XLSX

結果檔

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

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

下面我們一起來看一下。

語法:SUBTOTAL(Function_num,Ref1,…)

TIPS

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

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

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

參數用法對照表

1、參數Function_num對應表

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

Function_num
(
包含隱藏值)

Function_num
(
忽略隱藏值)

函數

函數定義

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

計算基于整個樣本總體的方差

2、範例說明

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

image

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

image

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

image

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

image

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

image

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

image

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

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

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

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

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

image

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

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

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

image

image

 

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()