Excel設置成本分攤表（間接部門） - 會計

Excel設置成本分攤表（間接部門）

鼎新TipTop成本分攤以部門會科為一組單位，分攤到預設的製程工段上。例
如，間接部門的費用須分攤到所有工段，第一個是生管的薪資費用，第二個
是品保的薪資費用，第三個是生管的攤銷費用，接下來依此類推，所有部門
會科都要設置，才能將間接部門的所有人工製費，全部分攤到當月份的工單
上。剛開始導入系統結算成本，先要討論出分攤方案，然後在系統裡面依方
案設置。

通常成本分攤的資料量會很肥大，假設5個部門10個會科3個工段，這樣將有
5X10X3=150項分攤項目，而且任何參數多一個，資料就會多一倍，非常可怕
。因此一筆一筆在系統裡面輸入，會讓有人有種回到原始時代的錯覺。比較
可行的方法，是請資訊提供系統可接受的資料格式，藉助Excel函數，將分攤
設置依照格式建好，再批次導入系統。以下分享實務上的作法：

圖片參考：

http://www.b88104069.com/archives/1342

一、首先如圖所示，三個工段，兩個間接部門，四個會科，因此將有
3X4X2=24項分攤設置。這個為了說明的簡化例子，實務上肯定更多，以我自
己處理的案子，有43個工段、37個部門、62個會科，所以有
43X37X62=98,642項分攤設置，但是一筆一筆在系統設式手工輸入，手指頭會
抽筋，人也會變得阿呆，工作擺在眼前，不處理沒辦法下班回家。只能跟資
訊協調，用Excel整批匯入取代人工輸入。

二、這是我們要的結果，四個會科依序分攤給三個工段，依照既定的分攤權
數，第一個部門好了，接下來第二個部門。每個部門有3X4=12項，兩個部門
共24項，擷圖只是完整表格的一部份。

三、開始介紹怎麼運用Excel設置成本分攤。先介紹簡單卻妙用無窮的ROW函
數，以微軟的官話講：傳回參照位址中的列號，白話講就是找出儲存格所在
的列號，依照這個函數定義，輸入公式「=ROW(B2)-1」，滑鼠移到儲存格右
下角，游標變成小黑十字架，連按滑鼠左鍵兩下，便可以將公式往下拉，完
成序列。

四、再來設置工段。很簡單就是ABC一直循環，先手工輸入前三個ABC，在第
四個輸入「=D2」的公式，一樣將公式往下拉，工段OK。

五、工段設好了，就可以利用Vlookup函數將分攤權數帶出來，輸入公式：「
=VLOOKUP(J2,分攤!B:C,2,0)」，往下拉，輕輕鬆鬆。

六、會科這裡就要動點腦筋。三個工段，因此每個會科要先重覆三次，再跳
到下個會科，並且之後要用VLOOKUP將會科帶出來，所以要想辦法做出
111222333的挑序內容，這個使用的公式是：「=INT((ROW(I2)-2)/3)+1」，
INT函數是將小數點去掉，只保留整數，第一列儲存格的「1」，其實是「
(2-2)/3的整數值+1」，下面儲存格每個列號會加1，函數算出來結果如圖所
示，很漂亮吧！

七、用VLOOKUP將會科代碼帶出來，發現第13列開始是「#N/A」，這是因為只
有四個會科，而我們的INT公式結果在第13列開始是5以上，因此VLOOKUP找不
到。待再想想法子。

八、規律是四的倍數以上要回到1再重新跑，所以利用除法餘數的函數MOD，
如果剛好是四的倍數，餘數是0，要利用IF函數將0變成4，其餘的只要直接取
除以四的餘數即可，公式是「=IF(MOD(L2,4)=0,4,MOD(L2,4))」

九、將ROW、INT、MOD、VLOOKUP函數全都套在一塊，完整公式變得如此噁心
，噁心歸噁心，計算出來結果是我們要的。這時候小會計通常心裡會有個
XXX，這麼麻煩，我直接像工段那樣，先輸入第一個完整循環(12筆資料)，然
後在第13筆弄成「=I2」，公式下拉就好了。這樣也可以，只是在會科數量有
變化的時候，例如從四個增加兩個到六個，便要再重新調整。而我的習慣是
如果是會有參數變動的資料，在一開始會將公式設好，日後參數變動了，只
要在VLOOKUP查找的參數表作更新，所有資料便會同步更新，一步到位。除此
之外也是在考驗自己Excel功力，但凡有脈絡規則可尋的資料，都可以用噁心
函數編出來！

十、實際上在編函數時，一方面不想讓公式看起來噁心，另方面為了將思惟
邏輯更清楚呈現，方便除錯，我常常會將各位函數值拆分，例如INT是一欄、
MOD是一欄、最後VLOOKUP又是一欄，成功了之後看是將中間的欄位隱藏，還
是貼上值之刪除皆可，不過如果要刪除的話，最好將原始公式檔案留備留存
。

十一、部門的部份，依樣畫葫蘆即可，看它是幾列循環一次，更改公式值，
也可以在參數表填上循環次數，直接帶，這樣部門數量有變動，直接更新參
數表就好了。

以上，是間接部門的分攤表，因為是分攤到所有工段，設置上相對簡單，如
果是直接部門，每個部門對應不同的工段，這個就麻煩了，之後有時間再來
寫看看。

--

會計人的Excel小教室：

https://www.facebook.com/acctexcel


--