贊贊小屋

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

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

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

一、銷貨毛利明細表

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

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

二、建立樞紐分析表

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

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

三、樞紐分析表欄位配置

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

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

四、毛利彙總報表

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

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

五、僅開啟欄

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

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

六、樞紐分析表欄位標題

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

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

七、兩個月毛利彙總

調整好的報表如圖所示。

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

八、成管會價量分析

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

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

九、報表格式整理及分析

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

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

十、SUMIFS函數彙總

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

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

樞紐分析表與函數彙總的比較

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

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

博客來網路書店網址:

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