Excel如何以篩選或Subtotal,選擇性取成本平均值

很高興有讀者加我好友,並且詢問我如何選擇性地平均成本,這是讀者對我的信任,也是刺激我探索Excel種種可能的動力,在此分享:

一、如圖所示,照舊是很陽春的月份別銷貨成本表,兩種產品三個月份的料工費,下面掛了一欄合計金額:「=SUM(D2:D7)」。

=SUM(D2:D7)

二、讀者來信,希望能一月、一到二月、二到三月等,隨心所欲,想抓哪幾月的平均成本就哪幾個月,我沒有這麼神通廣大,腦海中第一個浮現的是「篩選」。

篩選

三、例如,篩選一月份。

篩選一月份

四、結果,Excel不從人願,即使篩選了,「=SUM(D2:D7)」不為所動,仍然是把D2加到D7。

「=SUM(D2:D7)」不為所動,仍然是把D2加到D7

五、換個聰明一點的函數:「=SUBTOTAL(109,D2:D7)」,以各種不同方式傳回清單小計,這個用的是「109」,可以看到結果就是我們要的,只有小計篩選部份的月份。

=SUBTOTAL(109,D2:D7)

六、按一下函數說明,瞭解到除了「109」,「SUBTOTAL」函數靈活度高,同時還搭配很多各種不同的函數計算,並同可以選擇「包括隱藏的值」和「忽略隱藏的值」。我實際測試過,篩選一月份,「SUBTOTAL」參數用「109」和「9」,結果是一樣的,如果是用隱藏二三月份的方式,的確就會出現不同的結果,這是「SUBTOTAL」的特性,使用時必須注意。

「SUBTOTAL」參數用「109」和「9」

七、說了這麼多,終於要正式回答來信讀者的問題,如何選擇性地平均成本,我給的方案是:「=SUBTOTAL(101,D2:D7)」,各位看倌如果有更好的方案,歡迎不吝來信指教!

=SUBTOTAL(101,D2:D7)

八、最後想順便介紹相關的函數及用法:「=AVERAGEIF($B$2:$B$7,$J$1,D2:D7)」。「AVERAGE」是取平均值的Excel函數,「AVERAGEIF」作用類似於「SUMIF」,是有條件地取平均值。公式意思是在B2到B7之間,如果有等同於J1的單元儲存格,那麼取相對應的D2到D7儲存格的平均值。

=AVERAGEIF($B$2:$B$7,$J$1,D2:D7)

九、更高段是陣列函數:「{=AVERAGE(IF(($B$2:$B$7=$J$1),D2:D7))}」。先輸入連同「=」在內的公式,滑鼠停留在資料編輯列,同時按住「Ctrl」和「Shift」,再按下「Enter」,兩邊就會出現大括號,表示轉換成陣列函數。建議熟悉這裡的公式,因為它結構單純,但是能套用上各種不同的計算函數,而且能視情況再加上符合條件,多學一招總是好的,不知道什麼派上用場。

{=AVERAGE(IF(($B$2:$B$7=$J$1),D2:D7))}