贊贊小屋

Excel樞紐分析表計算欄位:平均毛利率及營收占比

Excel樞紐分析表通常是原始資料的直接彙總,因應需求有提供新增計算欄位的指令,彙總後再計算產生新的欄位。本文以銷貨報表的毛利率及營收占比介紹。

先前章節介紹建立和更新樞紐分析表,報表的金額來自於原始資料,沒有作任何變動。不過某些情況下,有可能必須就資料做計算,然後在報表上呈現計算結果。如果只是A加減B之類的簡單計算,只要在原始明細表中增加欄位,先做計算再更新到樞紐分析表即可。但如果是A除以B之類的乘除計算,例如周轉率毛利率營收占比等,明細每一項計算比率加起來不會等於總額的乘除,所以先前方法行不通,比較適當作法是直接以樞紐彙總後的結果再執行計算,以下具體示範操作:

一、銷貨毛利明細表

ERP系統轉出來的銷貨毛利明細表,已經整理成標準的資料庫格式:第一行是欄位標題,第二行開始是資料內容。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第1張

二、插入樞紐分析表

上方功能區「插入>表格>樞紐分析表」。這裡補充兩項:首先,游標滑過命令工具時,會跳出半浮動的說明視窗,有需要都可以按「F1」取得進一步說明;再者,如果是已經整理好的標準資料庫格式,滑鼠落在資料表格裡,Excel會自己抓取整個報表。但是上個步驟第10列是加總列,不適合納入樞紐分析表來源,最好手工調整範圍。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第2張

三、樞紐分析表欄位

在「樞紐分析表欄位」視窗中,將「月份」及「客戶」拖曳拉到「列」區域,將「銷貨數量」、「銷貨金額」、「銷貨成本」、「銷貨毛利」拖曳拉到「值」區域。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第3張

四、樞紐分析表

結果如圖所示,稍微修改樞紐分析表格式,原來標題欄位上的「加總-」都刪掉。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第4張

五、樞紐分析表切換

樞紐分析表妙用之一是快速切換資料的彙總方式。例如將客戶拉到「欄」區域,「值」區域只保留一個「銷貨金額」。

順便補充說明,樞紐分析表欄位視窗中有個齒輪圖標,下拉有蠻多欄位區域區段的布局選項,預設是第三步驟看到的「堆疊欄位區域和區域區段」,這裡則是選擇「僅區域區段(2X2)」,兩相比較很容易理解差異在哪裡。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第5張

六、客戶別營收統計

原來的分析表馬上變成兩個月客戶別的營收統計。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第6張

七、計算欄位

樞紐分析表的妙用之二:游標停留在分析表中,上方功能區「樞紐分析表工具>分析>計算」將「欄位、項目和集」下拉,點選「計算欄位」。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第7張

八、毛利率計算

在「名稱」輸入毛利率,於下面的「欄位」中先選取「銷貨毛利」後按「插入欄位」,「公式」裡打個「/」表示除法,同樣方法再選取「銷貨金額」後按「插入欄位」,公式好了按右上角的「新增」,最後是「確定」。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第8張

九、新增毛利率欄位

回到「樞紐分析表欄位清單」視窗,可以發現多了一個「毛利率」,再調整一下配置。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第9張

十、毛利率分析報表

原來的分析報表多了一個毛利率欄位,這裡要把該欄位的數值格式設置為百分比,不然的話看起來都是零,因為都小於1。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第10張

十一、營收占比計算

除了以欄位作運算,還可以輸入固定數字,例如新增營收占比欄位:「=銷貨金額/200000」。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第11張

十二、客戶別營收占比報表

變成是客戶營收占比報表了。

Excel樞紐分析表計算欄位:平均毛利率及營收占比 樞紐分析表 第12張

活用樞紐分析表計算欄位

這一節主要介紹如何在「樞紐分析表欄位清單」新增「計算欄位」,在同一個樞紐分析表的基礎上,只要巧妙運用欄位配置,雖然資料內容一樣,報表的呈現會比較靈活多變,配合不同管理需求能迅速產生相對應的報表。每個人工作狀況不一樣,讀者可以就自己實務上所編製的樞紐分析表嘗試看看。

本文內容取自《會計人的Excel小教室(增訂版)》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010817518



本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1