Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數

Excel樞紐分析表很多妙用。本文以成本與管理會計中的價量分析為例,介紹從原始的銷貨毛利明細表資料,簡單設置編製為管理報表,最後補充SUMIFS函數應用。

會計工作涉及到大量的資料處理,在成本會計和毛利分析這一塊更是會著於資料的統計分析,Excel在這方面最強大的工具為樞紐分析表。因此作者在第一本書《會計人的Excel小教室》第四章「樞紐分析表應用」,有一整篇五個小節介紹樞紐分析表於會計工作上的應用。從如何建立樞紐分析表、資料更新、欄位清單、計算欄位,相當完整地介紹樞紐分析表。這一章為毛利分析,當然也會用到樞紐分析表,所以這一節仍然會快速帶過樞紐分析表,重點是如何藉助樞紐分析表更快速地執行價量分析。

一、銷貨毛利明細表

簡化的銷貨毛利明細表,僅有12筆資料,很容易看出兩期差異,實務工作也許是120筆資料、也許是1,200筆資料、通常會是更多,因此很難目視法一眼看穿,必須藉助Excel統計分析。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 1

二、建立樞紐分析表

於上方功能區移到「插入」頁籤,在「表格」群組中點選「樞紐分析表」,Excel即會自動選取適當範圍,「建立樞紐分析表」,直接按「確定」,表示在「新工作表」生成樞紐分析表。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 3

三、樞紐欄位配置

適當配置「樞紐分析表欄位」,在此僅簡略帶過,不多作說明,有需要讀者可參考先前著作《會計人的Excel小教室》第四章「樞紐分析表應用」,會有一整章五個小節完整的介紹。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 5

四、毛利彙總報表

彙總好的樞紐分析表,接下來即以此為基礎,依照實務範例需要進一步整理。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 7

五、報表僅開啟欄

樞紐分析表強大之處,除了快速統計報表,還可以快速調整報表格式,滑鼠游標移到樞紐分析表任何一個儲存格,上方功能區即會多出一項「樞紐分析表工具」,共有「分析」和「設計」兩個頁籤,於「設計」中的「版面配置」群組,將「總計」指令下拉,設定為「僅開啟欄」,可以看到樞紐分析表的格式立即變更,不再有最右邊的總計欄位了。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 9

六、樞紐欄位標題

樞紐分析表會將每個統計欄位加上計算方式,例如「加總-數量」,雖然有其作用,但顯然在最終的報表並沒有需要。於「樞紐分析表工具」中的「分析」頁籤,「作用中的欄位」點選「欄位設定」,會跳出「值欄位設定」視窗,直接在「自訂名稱」中把「加總-」刪掉,必須注意「銷貨收入」這個欄位名稱已經存在,Excel會跳出卡關訊息,所以退而求其次,游標移到最後面「Space空格鍵」按一下,多一個隱形的空格即可設定名稱。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 11

七、兩個月毛利彙總

調整好的報表如圖所示。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 13

八、成管會價量分析

表架好之後,便可以進行差異比較,輸入公式:「=F4/E4*B4-C4」、「=F4/E4*(E4-B4)」、「=D4-G4/E4*B4」、「=G4/E4*(B4-E4)」這雖然是簡單的加減乘除,卻足以是大學會計系「成本與管理會計」價量分析一章的內容重點了。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 15

九、報表格式整理

整理報表格式:群組隱藏、取消格線、下劃線和加總線、合併儲存格、空行分隔,下面加些附註說明,即為一份簡單完整的分析報告。會計工作的價值在於讓數字說話,先整理好報表出來,透過報表數字提出對於公司內部管理的說明和建議,就是一份好的分析。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 17

十、SUMIFS函數彙總

最後補充介紹如何以函數公式執行樞紐分析表:

「=SUMIFS(D$2:D$13,$B$2:$B$13,$G5,$A$2:$A$13,I$1)」,這樣做的好處是如果資料有更新、例如到了下個月新的資料,只要將資料貼進來,函數計算的結會自動更新,在預期此價量分析不致於有太大變動的前提下,使用SUMIFS也是蠻不錯的作法。

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 19

樞紐分析表與函數彙總

《會計人的Excel小教室》第四章「樞紐分析表應用」有介紹到計算欄位,作用是就彙總後數字作進一步加減乘除的計算,這一節的價量分析雖然也是如此,但一方面價量分析較為全面,幾乎每個項目都要參與計算,像這樣就不適合用計算欄位,另一方面這裡算好的價量分析表,是直接作為報告的用途,樞紐分析表雖然提供了許多設計和版面方面的指令,畢竟不是很方便,另外建立分析表格,將資料彙總整理,直接調整儲存格格式,絶對會比調整樞紐分析表更為靈活自由。

每天學習,每天充電:Excel函數文章合集

Excel價量分析表:樞紐分析表欄位設定及SUMIFS函數 21
加入Line社群,口袋裡的Excel小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.