Excel成本計算:篩選平均值及Subtotal條件計算平均數

Excel成本分析可能希望依照選擇月份計算平均成本,但並沒有專門性指令可使用,本文介紹如何綜合應用篩選、SUM函數、SUBTOTAL函數、AVERAGEIF函數、AVERAGE陣列公式實現。

會計實務上,除了依照特定條件取合計數,在作長期趨勢分析、或者統計編製預算時,常常會用到一段期間的平均值作為參考,所以可能是依照特定條件取平均值,以下就平均成本作具體介紹:

一、產品成本明細

月份別銷貨成本表,三個月份兩種產品的料工費,左邊有一欄總成本:「=SUM(C2:E2)」,下面有一列類似的小計金額。

二、資料排序與篩選

像這樣標準的會計報表,第一個想到的是「篩選」:上方功能區移到「資料」標籤,「排序與篩選」區塊,然後就是「篩選」。

三、篩選特定月份

篩選一月份,拉下「月份」清單,保留勾選「01」和「合計」。

四、篩選金額不變

即使篩選了,合計金額並沒有變,仍然是把C2加到C7(「=SUM(C2:C7)」)。

五、SUBTOTAL函數

換個聰明一點的函數:「=SUBTOTAL(109,C2:C7)」,它有各種不同的加總方式,這裡用的是「109」,可以看到結果就是我們要的,只有小計被篩選出來的部份。

六、SUBTOTAL功能

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

七、選擇性平均值

將參數改成101:「=SUBTOTAL(101,C2:C7)」,搭配月份篩選,如此便實現了選擇性計算平均值。

八、AVERAGEIF函數

以篩選達到的選擇性計算平均值,從另一個角度而言,就是特定條件取平均值,可以設定公式:「=AVERAGEIF($A$2:$A$7,$A2,C2:C7)」。「AVERAGE」是取平均值的Excel函數,「AVERAGEIF」作用類似於「SUMIF」,公式意思是在A2到A7之間,如果有等同於A2的單元儲存格,那麼取相對應的C2到C7儲存格的平均值,和上一節範例相同,這裡用了「$」固定住特定欄號和列號,以便直接拉公式填滿儲存格。

九、AVERAGE陣列

更高段是陣列函數:「{=AVERAGE(IF(($A$2:$A$7=$A$2),C2:C7))}」。先輸入連同「=」在內的公式,滑鼠停留在資料編輯列,同時按住「Ctrl」和「Shift」,再按下「Enter」,兩邊會出現大括號,表示轉換成陣列函數。

Excel進階函數陣列公式

從上一節到這一節,都有介紹到套用陣列到函數裡的公式,應該能發現在必須條件化的情況下,陣列公式有一定類似的架構,如果能夠熟悉這個陣列架構,操作時比較直接並且很快,以後如有適當案例,再以獨立的章節多多介紹陣列的用法。

加入Line社群,口袋裡的Excel小教室!
相關文章