→ soyoso: 看網頁第3點可以，a2=row()-2 03/10 20:39
→ soyoso: 第6點可I2=int(row(a3)/3) 03/10 20:40
→ soyoso: 第8點不用輔助欄，也不用if 03/10 20:40
→ soyoso: =MOD(INT(ROW(A3)/3)-1,4)+1 03/10 20:40
→ moodyblue: 遇到高手了 學習了 謝謝 03/10 22:08
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
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 220.248.175.107
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1425773558.A.D32.html