Excel樞紐分析表應用:傳票總分類帳建立費用報表

Excel原始資料可能有所不足,本文以傳票格式產生的系統總分類帳或明細分類帳為例,介紹MONTH或MID函數得到月份,再計算金額,建立費用管控的樞紐分析表。

會計人每個結完帳,除了必定會有的資產負債表和損益表,通常還會做個部門費用彙總表。有了這張表,可以進一步瞭解詳細費用差異,內部管控各部門支出,並且可以參考編列預算,分析實際與預算的達成狀況。在此想分享如何以樞紐分析表,將一般格式的明細分類帳,彙總成部門費用報表:

一、總分類帳Excel

很常見的明細分類帳,ERP總帳系統都會有這麼一隻報表,就算沒有ERP,手工做也會把它做出來,因為會計法規上一定要有的報表。這裡是把所有科目合在一起,如果依照科目篩選,便是各科目的明細分類帳。

明細分類帳

二、月份及金額欄位

上述的報表如果要直接樞紐彙總,有些欄位資料仍然欠缺。例如月份、例如以正負表達的分錄金額(借方為正、貸方為負)。簡單的Excel公式可以做出來:「=MONTH(A2)」(得到月份1)、「=MID(B2,3,4)」(得到月份2)、「=G2-H2」(得到金額)。

以正負表達的分錄金額(借方為正、貸方為負)

三、建立樞紐分析表

整理好了原始資料後,上方功能區前往「插入>表格>樞紐分析表」,它會自動依照目前儲存格選取適當範圍,因此直接按「確定」即可。

針對Excel建立樞紐分析表較詳細的介紹,可參考贊贊小屋相關文章

Excel樞紐分析表應用:傳票總分類帳建立費用報表 1

四、樞紐欄位配置

接下來進行樞紐分析表欄位配置,將「科目」拉到列標籤、將「月份」拉到欄標籤、將「金額」拉到值。

針對Excel樞紐分析表欄位配置較詳細的介紹,可參考贊贊小屋相關文章

將「科目」拉到列標籤、將「月份」拉到欄標籤

五、費用彙總報表

如圖所示,這是一張依照科目列示的月份別費用彙總表。

依照科目列示的月份別費用彙總表

六、樞紐欄位配置

將鼠標移到樞紐分析表,會出現欄位清單。將「科目」拉回去上面的清單,或者直接在清單上將「科目」的打勾取消,然後將「部門」勾選,或者是將「部門」拉到列標籤。

將「部門」勾選,或者是將「部門」拉到列標籤

七、費用分析報表

好了,依照部門列示的月份別費用彙總表!

依照部門列示的月份別費用彙總表

Excel新增報表欄位

這一節有介紹如何帶Excel公式,將日期或流水號轉化為月份。實務上,系統跑出來的報表,大多不會特別有個月份欄位,而會計都是以一個月一個月作為期間劃分,所以雖然轉化月份的公式相當簡單,但卻是非常實用。

加強學習:贊贊小屋樞紐分析表文章合輯