Excel樞紐分析表(透視表)如何計算欄位

Excel之於公司小會計,如同計算機之於查帳員是氧氣般存在。會計工作每天與Excel為伍,打開財務部公盤資料夾,裡面滿滿的一張張Excel,內容物可能是明細分類帳、工單發料明細表、可能是銷貨毛利表。Excel將一格一格將文字數值很清楚依序列示,Excel2007版本總共可以裝得下1048576行16384列的資料(Excel 2003版本是65535行256列),關鍵是對於這些資料,微軟同步提供許多分析歸納統計小工具,例如篩選、查找、排序等等,因此使得資料處理變得簡單容易上手,微軟Excel風行辦公室打遍天下無敵手,厲害的地方就在這裡。

在這些小工具中,要我選出一個最最厲害的代表性招式,樞紐分析表當之無愧,它能快速將資料歸納統計,是實務上編製管理報表、提供悠關資訊的大刀級殿堂工具,以下分享基礎用法和延伸應用:

一、首先,ERP系統轉出來,略為整理成一個標準資料庫格式,第一行是欄位清單,第二行開始是資料內容,在這裡我的範例是銷貨毛利表。

銷貨毛利表

二、在工具版塊的「插入」中,找到「樞紐分析表」,鼠標滑過時會跳出半浮動的說明視窗。

樞紐分析表

三、第一步決定分析表的資料來源和產生位置。通常滑鼠如果落在資料表格裡面,Excel會自己抓取整面表格範圍。除非是在數一數二的大公司集團總部,不然大概是用不到「使用外部資料來源」的資料庫檔案。所產生的樞紐分析表,可以在原本資料來源的工作表上(「已經存在的工作表」),也可以在「新工作表」。

建立樞紐分析表

 

四、千篇一律的樞紐分析表,一個是統計表格,一個是「樞紐分析表欄位清單」。可以將欄位拖曳到表格裡,也可以直接在欄位清單下面框框裡拉來拖去。

標準的樞紐分析表

五、舉例而言,將月份、客戶拉到「列標籤」,將銷貨數量、銷貨金額、銷貨成本、銷貨毛利拉到「值」。

樞紐分析表欄位清單

六、結果如圖所示,一看就是上得了廳堂的財務統計報表。

兩個月客戶別銷貨毛利表

七、學習Excel不難,凡是有的工具都試看看怎麼用就對了。在這裡將客戶拉到「欄標籤」,然後為了簡潔起見,在「值」裡面只保留一個銷貨金額。

重新選擇樞紐分析表欄位清單

八、結果立刻產生兩個月客戶別的營收統計!

兩個月客戶別營收統計表

九、光在欄位清單裡拉來拖去,只是初級應用,接下來再分享個進階應用。在工具版塊裡找到「樞紐分析工具」中的「選項」,於「公式」下「拉出計算欄位」。

樞紐分析表計算欄位

十、在「名稱」輸入毛利率,將銷貨毛利「插入欄位」,打個「/」表示除以,再將銷貨金額「插入欄位」,大功告成按下「新增」。

插入計算欄位

十一、回到樞紐分析表一看,兩個月客戶別銷貨毛利率比較編製完成。

兩個月客戶別銷貨毛利率比較

十二、除了各欄位資料運算,也可以輸入固定數字,例如弄個營收佔比:「=銷貨金額/3736535」。

插入計欄位(營收佔比)

十三、客戶營收佔比彙總,ok。

客戶別營收佔比彙總

Excel整理術 - 入門密技+進階實作 台北場淡江大學台北校區
2017-12-23(星期六) 09:00~16:00

http://smart.businessweekly.com.tw/ecourse/product.aspx?PROD_TYPE_NO=PROD000008934

《會計人的Excel小教室》:每位會計人都應該具備的武功秘笈!

http://www.books.com.tw/products/0010734874

《會計人的Excel VBA小教室》:會計人Excel強化並且VBA入門!

http://www.books.com.tw/products/0010753074