Excel如何編製價量分析表




有些讀者朋友和我交流成本分析相關的問題,我自己在工作上開展Excel最多的地方,也是在成本這一塊,因為成本的資料,無論是手工帳還是系統帳,沒有意外都會達到「非用函數或樞紐不可」的境界,所以這次我想分享如何運用Excel,進行成本管理裡面提到的,算是成本分析基本功的價量分析:

一、首先,又是精心設計的簡單小範例啦!12筆資料,很容易看出兩期差異,實務上呢,也許是120筆資料、也許是1,200筆資料、通常是更多,到了那個時候,肉眼是絶對沒辦法一眼看穿的,所以要藉助Excel統計歸納。

毛利明細表

二、手上拿到這種資料,不囉嗦不解釋,「建立樞紐分析表」。

「建立樞紐分析表」

三、「樞紐分析表欄位清單」,請大家跟我這樣排,有時間有興趣有心,也請自行多多排列組合,包準玩出比我更跩的版面!

「樞紐分析表欄位清單」

四、贊贊小屋版的價量分析樞紐,還算水準之上吧。

贊贊小屋版的價量分析樞紐

五、我天生是Excel外貿協會,絶對不滿足於只是跑樞紐,實用之餘更要美觀:「樞紐分析表工具」、「設計」、「總計」、「僅開啟欄」,把沒用的東西砍掉,輕鬆達到簡約風格。

「僅開啟欄」

六、再來一記:「樞紐分析表工具」、「工具」、「欄位設定」,把那個礙眼的「加總-」刪掉,但必須注意這個欄位名稱已經存在,Excel會跳出卡關訊息,所以退而求其次,就在後面「Space空格鍵」按一下,多一個隱形的空格即可。

值欄位設定

這個欄位名稱已存在

七、噹噹噹,好看否?我自己覺得是無敵啦!

整理後的樞紐分析表

八、表架好之後,便可以進行差異比較,輸入公式:「=F4/E4*B4-C4」、「=F4/E4*(E4-B4)」、「=D4-G4/E4*B4」、「=G4/E4*(B4-E4)」這是會計人最厲害的加減乘除,一樣,不解釋,可回去找找課本「成本與管理會計」。

「=F4/E4*B4-C4」

九、實用之餘更要美觀:群組隱藏、取消格線、下劃線和加總線、合併儲存格、空行分隔,嗯,改天考慮再分享個「Excel化粧術」。下面說明是我胡謅的,如果覺得還不錯,可以參考自己公司的情況,照樣照句,但不能照抄喔!會計人厲害的地方,在於讓數字說話,先整理好數字出來,透過這個數字,一針見血說出公司內部管理的小秘密,就是一個好樣的分析。

「Excel化粧術」

十、老習慣,補充介紹如何以函數替代樞紐:「=SUMIFS(D$2:D$13,$B$2:$B$13,$G5,$A$2:$A$13,I$1)」,以後有適當機會,再來好好分享這個「SUMIFS」函數吧。

「=SUMIFS(D$2:D$13,$B$2:$B$13,$G5,$A$2:$A$13,I$1)」






當前文章延伸閱讀:
SUMIF