贊贊小屋

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數

Excel設計成本分攤表時,直接生產部門各自有專屬的製程工段,因此部門、工段、會計科目三者都必須考慮在內。本文介紹如何應用VLOOKUP、LOOKUP、ISODD等函數完成。

一、部門、工段、會科

直接部門的成本分攤設置,範例簡化為三個直接生產部門,分別有2、1、3個製程工段項次,只有兩個會科。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第1張

二、部門生產工段

三個部門相對應專屬的工段,如表格所列。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第2張

三、成本分攤表

暫不考暫公式函數怎麼設計,手工一筆一筆輸入的話,最終想要的結果如圖所示。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第3張

四、循環項次計算

生產部門依序有2、1、3個工段,每組部門工段又各有兩個會科,所以分別有4、2、6筆資料,將E欄公式設為C欄乘以2,即可得到該資料。以項次而言,1-4是「製一課」、5-6是「製二課」、7-12是「製三課」。設計一個簡單的公式:「=C4*2+F4」,把項次帶出來。第一列儲存格F2必須設為0,所以最好是第一列和第二列都手動輸入,沒辦法直接帶相同的公式。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第4張

五、LOOKUP函數計算部門

規律找到了,並且有整理出來,下一步是引用資料。希望序號1-4是1、序號5-6是2、序號7-12是3,這樣就可以把部門資料帶過來。使用Lookup函數「=LOOKUP(A8,分攤!$F$2:$F$5,分攤!$A$2:$A$5)」,意思是在「分攤」這張工作表的儲存格F2到F5範圍內,找出A8(值為7)的相對位置,並根據這個位階,傳回A2到A5相對應的值。Lookup函數特性是查找範圍(F2:F5)必須是遞增順序排列,如果找不到相同的值,函數會去抓小於或等於查找值中的最大值。以「B8」儲存格的公式計算為例,查找值是A8(7),查找範圍中(F2:F5)沒有7,這個範圍內{0,5,7,13}小於等於7的最大值是7,傳回範圍中(A2:A5)和7(F4)相同位階的是A4,傳回的值是3。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第5張

六、VLOOKUP函數設置部門

部門順序排出來之後,便可以輕鬆Vlookup部門名稱:「=VLOOKUP(B2,分攤!$A$2:$B$4,2,0)」。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第6張

七、ISODD函數循環

會科部份,只有兩個依序重覆循環,可以簡便處理,利用函數「ISODD」判斷是否為奇數:「=IF(ISODD(B2),分攤!$D$2,分攤!$D$3)」,倘若序號為奇數,引用分攤設置表的「D2」,否則的話引用「D3」。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第7張

八、部門工段項次

部門工段的部份,依照先前方式整理出循環規律:「=D3+1」。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第8張

九、LOOKUP函數計算工段

再用Lookup把工段的順序表排出來:「=LOOKUP(A2,工段!$E$2:$E$7,工段!$A$2:$A$7)」。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第9張

十、VLOOKUP函數設置工段

把工段順序排出來之後,和先前步驟一樣,以Vlookup帶出工段名稱:「=VLOOKUP(E2,工段!$A$2:$C$7,3,0)」。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第10張

十一、VLOOKUP函數分攤權數

權數部份,設置為平均分攤,公式輸入:「=1/VLOOKUP(C2,分攤!$B$2:$C$4,2,0)」。看起來似乎會有尾差,但其實Excel的計算位數很夠,加總合計是1。不過實務上,還是要看ERP系統小數點位數的設置情形,再看看是否要修正。

Excel設置直接部門成本分攤表,VLOOKUP、LOOKUP、ISODD函數 成本會計 第11張

成本分攤設置與每月結算前檢查

這裡的成本分攤設置,將所有部門會科完整編列,可是實務上,某個會科費用或是某個製程工段,不一定每個月都有金額或工時。某項會科沒有金額,還不會造成問題,頂多是各工段分攤到的成本為零。某項工段沒有工時,表示這個工段當月沒有生產,如果仍然設置有分攤權數,如此的成本分攤便不合乎實際。比較成熟的ERP系統,應該會提示異常,所以有必要在結算成本之前,先作檢查,具體操作方法,在下一節介紹。

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

博客來網路書店網址:

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

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