※ 本文為 layzer 轉寄自 ptt.cc 更新時間: 2015-07-12 18:21:14
看板 Office
作者 標題 [算表] Excel：成本分攤設置（直接部門）
時間 Mon Jun 22 17:10:51 2015
Excel：成本分攤設置(直接部門)
先前介紹過間接部門的成本分攤設置，由於間接部門的成本，會分攤到所有
製程工段，在設置上相對單純，直接部門則是各部門有相對應的製程工段，
比較麻煩，現在分享直接部門的成本分攤設置：
製程工段，在設置上相對單純，直接部門則是各部門有相對應的製程工段，
比較麻煩，現在分享直接部門的成本分攤設置：
網誌圖文版：
http://www.b88104069.com/archives/1403
Excel：成本分攤設置(直接部門) | 贊贊小屋
![[圖]]()
先前介紹過間接部門的成本分攤設置，由於間接部門的成本，會分攤到所有製程工段，在設置上相對單純，直接部門則是各部門有相對應的製程工段，比較麻煩，現在分享直接部門的成本分攤設置： 一、如圖所示，為了方便說明，簡單假設有三個直接部門，分別有2、1、3個製程工段，兩個會科。 二、3個直接部門相對應的工段如表 ...
![[圖]](http://www.b88104069.com/wp-content/uploads/2015/03/E23a00.jpg)
一、如圖所示，為了方便說明，簡單假設有三個直接部門，分別有2、1、3個
製程工段，兩個會科。
二、3個直接部門相對應的工段如表格列示。
三、實際上有可能某個部門沒有某個會科，但為了在設置上力求完美，將每
個部門會科都設置進去，第一個部門「製一課」為例，有兩個會科兩個工段
，總共應該有四筆資料(2X2=4)，至於各個工段的分攤權數，看公司具體怎麼
決定，我這裡是簡便處理，各工段平均分攤部門成本。暫不考暫函數怎麼弄
，手工一筆一筆輸入的話，結果應該如此。
個部門會科都設置進去，第一個部門「製一課」為例，有兩個會科兩個工段
，總共應該有四筆資料(2X2=4)，至於各個工段的分攤權數，看公司具體怎麼
決定，我這裡是簡便處理，各工段平均分攤部門成本。暫不考暫函數怎麼弄
，手工一筆一筆輸入的話，結果應該如此。
四、實務上一間工廠的部門工段會科絶對沒有這麼少，真正的分攤設置可能
有成千上萬筆資料，所以最好藉助EXCEL函數。要下函數之前，先要找出資料
裡的規律。以部門而言，因為分別有2、1、3個工段，每個部門兩個會科，所
以分別有4、2、6筆資料，以項次而言，1-4是「製一課」、5-6是「製二課」
、7-12是「製三課」，簡單設個函數「=C4*2+F4」把項次帶出來。第一列因
為要設為0，所以最好是第一列和第二列都手動輸入，沒辦法直接帶相同的公
式。
有成千上萬筆資料，所以最好藉助EXCEL函數。要下函數之前，先要找出資料
裡的規律。以部門而言，因為分別有2、1、3個工段，每個部門兩個會科，所
以分別有4、2、6筆資料，以項次而言，1-4是「製一課」、5-6是「製二課」
、7-12是「製三課」，簡單設個函數「=C4*2+F4」把項次帶出來。第一列因
為要設為0，所以最好是第一列和第二列都手動輸入，沒辦法直接帶相同的公
式。
五、規律找到了，並且有整理出來，下一步便可以把資料帶過來。我們想要
的是序列1-4是1，序列5-6是2，序列7-12是3，這樣就可以把部門資料
VLOOKUP過來。使用LOOKUP函數「=LOOKUP(H2,部門!$F$2:$F$5,部門
!$A$2:$A$5)」，意思是在「部門」這張工作表的F2-F5的範圍內，找出H2(值
為1)的相對位置，並根據這個位階，傳回A2-A5相對應的值。LOOKUP函數的特
性是查找範圍(F2-F5)必須是遞增順序挑列，因為如果找不到相同的值，函數
會去抓小於或等於查找值中的最大值。以「I2」儲存格為例，查找值是
為1)的相對位置，並根據這個位階，傳回A2-A5相對應的值。LOOKUP函數的特
性是查找範圍(F2-F5)必須是遞增順序挑列，因為如果找不到相同的值，函數
會去抓小於或等於查找值中的最大值。以「I2」儲存格為例，查找值是
H2(1)，查找範圍中(F2-F5)沒有1，這個範圍內{0,5,7,13}小於等於1的最大
值是0，傳回範圍中(A2-A5)和0(F2)相同位階的是A2，傳回的值是1。
六、把部門順序排出來之後，便可以輕鬆VLOOKUP部門名稱或是代碼。
七、會科的部份，因為只有兩個要重覆循環，簡便處理，以利用函數「
六、把部門順序排出來之後，便可以輕鬆VLOOKUP部門名稱或是代碼。
七、會科的部份，因為只有兩個要重覆循環，簡便處理，以利用函數「
ISODD」判斷是否為奇數：「=IF(ISODD(H2),部門!$D$2,部門!$D$3)」，如果
為序列為奇數，帶部門工作表的「D2」，否則的話帶「D3」。
八、部門工段的部份，依照之前方式整理出規律。
九、再用LOOKUP把工段的順序表排出來。
十、把工段順序排出來之後，便可以輕鬆VLOOKUP工段名稱或是代碼。
十一、權數的部份，設置為平均分攤：「=1/VLOOKUP(I2,部門!B:C,2,0)」就
好了，看起來會有尾差，但其實EXCEl計算的位數很夠，加總合計是1，實際
要看ERP系統小數位的設置情形，再看看是否要修正。
好了，看起來會有尾差，但其實EXCEl計算的位數很夠，加總合計是1，實際
要看ERP系統小數位的設置情形，再看看是否要修正。
--
會計人的Excel小教室：
https://www.facebook.com/acctexcel
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 223.64.63.55
※ 文章代碼(AID): #1LXz4W1b (Office)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1434964256.A.065.html
→ : 步驟五原po已設定F欄的項次，為什麼還要先參照序列，再由1F 06/22 17:54
→ : 序列vlookup參到傳回部門，不懂，這方面就可以直接傳回部
→ : 門了，https://goo.gl/NOQXPo
→ : 序列vlookup參到傳回部門，不懂，這方面就可以直接傳回部
→ : 門了，https://goo.gl/NOQXPo
Dropbox - 20150622_lookup.xls
![[圖]]()
Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again! ...
![[圖]](https://cf.dropboxstatic.com/static/images/icons128/page_white_excel.png)
→ : 只有兩個的循環可用=$D$2+(ISEVEN(H2))，不用在if判斷了4F 06/22 17:58
→ : 步驟九同上步驟五，為什麼都設定E欄的項次，還要先參序列
→ : ，這方面可直接傳回工段及部門
→ : https://goo.gl/gUvqmr
→ : 步驟九同上步驟五，為什麼都設定E欄的項次，還要先參序列
→ : ，這方面可直接傳回工段及部門
→ : https://goo.gl/gUvqmr
Dropbox - 20150622_lookup_v2.xls
![[圖]]()
Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again! ...
→ : 補充=$D$2+(ISEVEN(H2))，用於數字類型8F 06/22 18:25
→ : 謝謝指教，高手！9F 06/25 21:18
--
※ 看板: layzer 文章推薦值: 0 目前人氣: 0 累積人氣: 453
回列表(←)
分享