Excel MOD函數用法:如何建立1套成本分攤表

Excel有時候需要建立特殊規則的數列,本文以成本會計工作實務為範例,介紹ROW、INT、MOD、VLOOKUP、IF函數綜合應用,設置工段、權數、會科、部門成本分攤表。

成本分攤將當月份的人工製費,結算到各個工單工段上,會計實務上,通常以部門會科為一組單位,分攤到預設的製程工段上。例如,間接製造部門的各項會科費用,須分攤到所有工段,第一個是生管的薪資費用,第二個是品保的薪資費用,第三個是生管的攤銷費用,接下來依此類推,所有部門會科必須設置好,才能將當月份所發生的間接部門人工制費,全部分攤到當月份的製造工單上。剛開始導入系統結算成本時,先要討論決定出分攤方案,在系統裡依照方案做好相關設置。以下用Excel的方式,介紹分攤表:

一、成本基本參數表

首先如圖所示,三個工段,兩個間接部門,四個會科,因此將有3X2X4=24項分攤設置。

E22a21

二、部門會科分攤表

依照成本結算原則所設置的分攤表。四個會科依序分攤給三個工段,依照既定的分攤權數,第一個部門好了,接下來第二個部門。每個部門有4X3=12項,兩個部門總共有24項,擷圖資項次看起來很多,但這只是完整表格的一部份。

E22a22

三、簡單好用ROW函數

先介紹簡單卻妙用無窮的Row函數。微軟的官方說明:傳回參照位址中的列號,簡單講就是找出儲存格所在的列號。依照這個函數定義,輸入公式「=ROW(C2)-1」,滑鼠移到儲存格右下角,游標變成小黑十字架,連按滑鼠左鍵兩次,便可將公式往下拉,完成序列。

E22a23

四、直接快速自動填滿

再來設置工段。有三個工段,所以是ABC一直循環,先手工在D2到D4輸入前三個ABC,然後在D5儲存格輸入公式:「=D2」,同樣將公式往下拉。

E22a24

五、萬用VLOOKUP函數

工段設好了,利用Vlookup函數引用分攤權數:「=VLOOKUP(D2,分攤!B:C,2,0)」,往下拉,輕輕鬆鬆。在這裡,實際引用來源只有B2到C4範圍,所以用「分攤!B:C」和「分攤!$B$2:$C$4」效果相同,前者在輸入公式較為簡便,後者因為使用「$」將引用範圍固定住,不會每個公式都跑一整個B欄C欄,較為節省Excel計算資源。

E22a25

六、去掉小數的INT函數

會科的公式稍微複雜。因為有三個工段,每個會科要先重覆三次,再跳到下個會科,並且之後將用Vlookup函數把會科帶過來,所以要想辦法做出像111222333的排序內容。這個使用的公式是:「=INT((ROW(C2)-2)/3)+1」。INT函數是將小數點去掉,保留整數,例如C2儲存格的「1」,便是(2-2)/3的整數值(0)再加1,計算出來的值是「1」,往下儲存格每個列號會加1,整體計算結果如圖所示。

E22a26

七、公式傳回錯誤訊息

和第五步驟相同架構的公式,以Vlookup引用分攤設置的會科代碼。第13列開始是「#N/A」,這是因為只有四個會科,依照INT函數計算結果,在第13列開始是5以上,Vlookup公式搜尋不到,所以會返回錯誤訊息。

E22a27

八、除法餘數MOD函數

規律是四的倍數以上,回到1再重新跑,因此利用除法餘數的函數「MOD」。如果是四的倍數,餘數為0,利用IF判斷函數將0變成4,其餘情況直接取除以四的餘數即可,順著這思惟所設計的公式為:「=IF(MOD(F14,4)=0,4,MOD(F14,4))」。

E22a28

九、落落長的巢狀公式

將Row、Int、Mod、Vlookup函數組合起來,完整公式是一長串:「=VLOOKUP(IF(MOD(INT((ROW(C14)-2)/3)+1,4)=0,4,MOD(INT((ROW(C14)-2)/3)+1,4)),分攤!$A$2:$E$5,5,0)」,姑且不論其閱讀困難度,計算結果是我們要的,這個最重要。不過心裡難免會有個想法,這麼麻煩,不如直接像工段那樣,先輸入一個完整循環(12筆資料),然後第13筆開始套公式:「=C2」,往下拉就好了。如此想法沒有錯,只是當會科數量有變化,例如從四個增加成六個,公式便必須重新調整。而如果在一開始將公式設計好,日後參數倘若有變動,只要在分攤設置表更新,所有資料便會同步更新,一步到位。以成本結算的分攤設置而言,保留參數變動的彈性,比較合乎實際。

E22a29

十、複雜報表輔助欄位

設計函數公式時,一方面是讓公式易於閱讀理解,另方面為了將思惟邏輯更清楚呈現、方便偵錯,實務上常常將各個函數區塊拆分,例如F欄是Int函數取值、G欄是MOD函數取值、C欄是最後Vlookup結果的呈現。如此思維邏輯將公式設計好了,看是將中間過程的欄位隱藏,或者是貼上值之後刪除皆可。不過要提醒的是,如果要刪除,原始公式最好留存備查。

E22a30

十一、依序循環部門欄位

部門部份,以相同思維設計公式即可,端視其幾列循環一次,更改參數值,也可以於分攤設置表填上循環次數,直接引用,這樣如果部門數量有變動,直接更新分攤設置表。

E22a31

Excel函數與ERP系統結合

在所有會計參數中,成本分攤的資料量應該是最多的。假設有5個部門10個會科3個工段,這樣將有(5X10X3=150)項分攤項目,任何參數只要多一個,資料就會多一倍,不好處理。一筆一筆在ERP系統輸入,不但耗時,並且容易出錯。較為可行方法,是請資訊人員提供系統可接受的資料格式,借助Excel函數,將分攤設置依照格式建置好,批次導入系統。本章節所介紹的,便是構建成本分攤表的實例,其中所渉及到函數,雖然不常用,但是在處理大量資料的情況下,有時候是相當便利,而且合乎直觀思維。

學習清單:贊贊小屋成本會計Excel文章合輯

YouTube video

最新文章: