贊贊小屋

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數

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

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第1張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第2張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第3張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第4張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第5張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第6張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第7張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第8張

九、將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」,往下拉就好了。如此想法沒有錯,只是當會科數量有變化,例如從四個增加成六個,公式便必須重新調整。而如果在一開始將公式設計好,日後參數倘若有變動,只要在分攤設置表更新,所有資料便會同步更新,一步到位。以成本結算的分攤設置而言,保留參數變動的彈性,比較合乎實際。

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第9張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第10張

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

Excel間接部門成本分攤表,ROW、VLOOKUP、INT、MOD函數 成本會計 第11張

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

本文內容取自《會計人的Excel小教室(增訂版)》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

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

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1