贊贊小屋

Excel建立樞紐分析圖表,視覺化分析產品毛利率

Excel建立樞紐分析表時,可以同時建立報表及圖表,視覺化呈現數據分析結果。本文以銷貨毛利明細表為例,介紹彙總產品平均毛利率時,一併產生分析圖。

一張報表密密麻麻的數字,在財會人員的眼中是資料完整,但在老闆的眼中可能是沒有重點!因為老闆總是很忙,要的是結論,也就是會計你到底想表達什麼?而且這個結論必須具有決策力,以會計專業術語而言,就是財務報表的攸關性品質。所以會計人在辛苦整理好「大數據報表」之後,勢必要再弄個簡單明瞭的圖表,把分析重點突顯出來,讓閱讀者三秒鐘進入狀況,節省雙方寶貴的時間。本節即以前面幾個小節的基礎上,分享如何以「樞紐分析圖」的方式,讓數字說話(讓數字飛起來)!

一、原始報表

如圖所示,相當精簡的銷貨毛利明細表。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第1張

二、建立樞紐分析表

管理分析的重點為每個月的毛利率情形,建立樞紐分析表:「在以下區域之間拖曳欄位」,如圖所示直接拖曳配置。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第2張

三、銷貨毛利彙總表

生成的樞紐分析表。彙總每個月銷貨金額和銷貨毛利彙總,這樣很容易可以算出每個月的平均毛利率。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第3張

四、毛利率計算欄位

月份別毛利率彙總表。編製方式有兩種,一種是在前面的樞紐分析表後面欄位手動加上公式,一種是直接在樞紐分析表裡面新增一個公式計算欄位,實際操作可參考先前著作《會計人的Excel小教室》第四章「樞紐分析表應用」。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第4張

五、產品別毛利率分析

依照相同方法,抓取資料整理出ABC三個產品的上半年毛利率明細,雖然內容完整,但還是希望再做個漂亮的圖表出來,能夠一眼看出來哪個產品毛利率高以及各產品的毛利變化趨勢。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第5張

六、插入樞紐分析圖

上方功能區移到「插入」頁籤,在「圖表」群組中將「樞紐分析圖」指令下拉,點選「樞紐分析圖和樞紐分析表」,可以參考浮框的說明文字:「樞紐分析表會以圖形方式摘要資料,並可協助您瀏覽複雜資料。樞紐分析表可協助您以表格輕鬆排列與摘要資料。」相當扼要地介紹圖和表兩者的呈現重點。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第6張

七、建立樞紐分析圖

「建立樞紐分析表(圖)」視窗,和前面章節的結構完全一樣,對此讀者應該非常熟悉、沒有任何障礙。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第7張

八、樞紐分析表欄位

依樣畫葫蘆,如圖所示配置欄位,注意到這裡是「樞紐分析圖欄位」,而非「樞紐分析表欄位」。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第8張

九、樞紐分析圖表

同時完成了樞紐分析表和樞紐分析圖。

Excel建立樞紐分析圖表,視覺化分析產品毛利率 圖表設計 第9張

視覺化分析圖表的呈現

從這一節最後的圖表可以想見,在Excel裡面,樞紐分析表和樞紐分析圖是一體兩面,相同的資料來源,呈現的方式不同。圖形的強項在於整體趨勢的表現很強烈,例如第九步驟的藍色A產品和灰色C產品,都是上下有起伏但變動不會太大,藍色A產品的毛利率總是高於灰色C產品,橘色B產品則是很明顯地持續成長。當財會人員想要以簡報分析ABC三種產品的毛利率,圖形(樞紐分析圖)顯然比表格(樞紐分析表)更加能有效率傳達訊息、更加有說明力。

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

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