Excel銷貨收入樞紐分析表,MID、RIGHT、IF函數應用

目錄

Excel建立樞紐分析表前,通常要先整理原始資料。本文以出貨單為主的銷貨明細表為例,介紹如何利用MID、RIGHT、IF等函數,先新增所需欄位再編製管理報表。

如果系統報表完整,我們要的資料都在裡面,可以直接跑樞紐分析表,那是很完美。可是有些時候,例如編製收入報表,系統跑出來的「銷貨明細表」,只有依照系統流程操作、正常的銷貨單明細,非系統正常操作的收入,不會在裡面。例如應收系統立的雜項收入,又例如傳票系統直接立的總帳收入,都不會在「銷貨明細表」中,造成報表編製上的困難。這種情況下,想把所有收入彙總在一起,然後跑樞紐分析表,必須多花點心思,以下分紹:

一、系統銷貨明細表

系統跑出來的銷貨明細表。有客戶、有出貨單、有銷貨收入,還有一欄是傳票編號,空白表示尚未開票立帳(暫估),有編號表示是已立帳的傳票號。

二、各種出貨單狀況

除了正常出貨帶來的收入,還有尚未建存貨料號,沒辦法打出貨單,直接在總帳系統輸入的總帳樣品收入,另外還有已經出貨,客戶卻要求降價,訂單單價已鎖住,無法修改,直接在應收系統打雜項調整收入。

三、MID與RIGHT函數

想把總帳收入和雜項收入,人工套進系統「銷貨明細表」中,欄位部份必須匹配,最終跑出來的樞紐才有意義。以總帳收入為例,先將「銷貨明細表」四個欄位複製過來,後面加上兩個欄位:「備註」、「性質」。利用公式將欄位內容自動帶出:「=MID(C2,3,1)」、「=A2」、「=E2」、「=RIGHT(C2,4)」。至於「性質」欄位的「總帳收入」,直接輸入文字,和公式一樣,把游標下拉即可填滿。

四、簡單建立雜項收入

雜項收入是相同作法,公式更簡單:「=B8」、「=C8」、「=D8」、「=E8」。

五、IF函數判斷性質

欄位弄好,便能直接複製值到「銷貨明細表」下方,於報表標題欄位加上「備註」及「性質」。原銷貨明細表的「性質」欄位,輸入公式:「=IF(C2=””,”暫估收入”,”立帳收入”)」,如此能把「暫估收入」和「立帳收入」標註清楚。

六、客戶地區欄位

因應管理報表需要,可再加一欄「客戶地區」判斷欄位:「=IF(B2=”B”,”美國”,IF(B2=”E”,”歐洲”,”亞洲”))
」,這是兩層的若P則Q公式。

七、建立樞紐分析表

報表整理好,可以跑樞紐了,選取所有範圍,設置好「樞紐分析表欄位清單」。

八、銷貨收入彙總表

跑出來的樞紐分析表,稍加修飾一下,便是可以印列出來的銷貨收入彙總表。

九、客戶類別順序

如果想調整客戶類別的順序,將游標移到有個減號的「亞洲」地方,按滑鼠右鍵:「移動」、「移動”亞洲”到開頭」。

十、管理分析報表

順序調整好的銷貨明細表。

手工方式補全系統報表

這一節利用新增調整欄位的方式,硬是把總帳收入和雜項收入,加到了系統的銷貨明細表中,以便跑出樞紐分析表。這麼做似乎多此一舉,但還是有它的好處。最主要是兩點:一、從樞紐分析表上每一個彙總金額,在數字上點兩下,馬上會在新工作表上展開明細,非常方便;二、每個月報表格式都統一且單一,可以直接將不同月份的報表加在一起,便成了一個季度或是一個年度的明細資料,在統計分析時,也是非常方便。

相關文章