※ 本文為 layzer 轉寄自 ptt.cc 更新時間: 2015-07-12 18:19:16
看板 Office
作者 標題 [算表] 檢查成本分攤設置
時間 Sun Jul 12 08:01:57 2015
Excel檢查成本分攤設置
網誌圖文版：
http://www.b88104069.com/archives/876
Excel：檢查成本分攤設置 | 贊贊小屋
![[圖]]()
成本分攤三元素：製程、工時、成本，月底把這三塊資料統計好了之後，當月所有成本先分攤到各個製程，各製程再依工單工時比例，將製程成本分配到各個工單，由此計算出該工單產出的單位成本，最後所有工單入庫和其他存貨異動加權平均，便可結算出當月成本。 其中成本分攤到製程比較關鍵，一般ERP系統帳結算成本，例如鼎新Tip-top ...
![[圖]](http://www.b88104069.com/wp-content/uploads/2015/01/E08a00.png)
成本分攤三元素：制程、工時、成本，月底把這三塊資料統計好了之後，當
月所有成本先分攤到各個制程，各制程再依工單工時比例，將制程成本分配
到各個工單，由此計算出該工單產出的單位成本，最後所有工單入庫和其他
存貨異動加權平均，便可結算出當月成本。
月所有成本先分攤到各個制程，各制程再依工單工時比例，將制程成本分配
到各個工單，由此計算出該工單產出的單位成本，最後所有工單入庫和其他
存貨異動加權平均，便可結算出當月成本。
其中成本分攤到制程比較關鍵，一般ERP系統帳結算成本，例如鼎新Tip-top
，以部門會科組合起來作為一成本項目，先評估這成本項目該由哪些制程分
攤，再設置好分攤權數，明確各制應依多少比例分攤，總分攤權數合計是
，以部門會科組合起來作為一成本項目，先評估這成本項目該由哪些制程分
攤，再設置好分攤權數，明確各制應依多少比例分攤，總分攤權數合計是
100%。舉例而言，A部門5100會科當月共有100塊成本，這100塊以3:2比例分
給a和b兩個制程。一家公司假設有十個部門、十個會科、十個制程好了，這
樣就有10X10X10=1,000筆資料，實在不是個小數目。
給a和b兩個制程。一家公司假設有十個部門、十個會科、十個制程好了，這
樣就有10X10X10=1,000筆資料，實在不是個小數目。
更麻煩的是，費一番功夫設置好龐大資料庫，卻並非從此一勞永逸，因為會
科並非一成不變、部門組織有可能調整，而且某月份某制程也有可能無工時
產生（根本未開工），有時候甚至連分攤比例都可能需要修改。凡此種種情
形，原來的設置就必須更新，否則成本結算會跳出錯誤訊息，拋轉成本傳票
時會拉不出會科。
科並非一成不變、部門組織有可能調整，而且某月份某制程也有可能無工時
產生（根本未開工），有時候甚至連分攤比例都可能需要修改。凡此種種情
形，原來的設置就必須更新，否則成本結算會跳出錯誤訊息，拋轉成本傳票
時會拉不出會科。
如前所述，分攤設置的資料過於龐雜，沒辦法一筆一筆檢視是否有誤，很需
要有一套完善機制，能把錯誤訊息偵察出來，倘若系統沒有，資訊人員又沒
有客制，那只得靠萬能的會計人自己動手囉。在此分享我所遇到的實例，還
有相對應的Excel檢錯方式：
要有一套完善機制，能把錯誤訊息偵察出來，倘若系統沒有，資訊人員又沒
有客制，那只得靠萬能的會計人自己動手囉。在此分享我所遇到的實例，還
有相對應的Excel檢錯方式：
一、首先，如圖所示，已經設置好的部門會科，有些當月沒有交易金額產生
，另外當月有些新增的部門會科，這兩種情況都會使得成本結算出問題，想
要利用Excel偵錯，因為涉及到部門會科一組兩個變數，必須引用二維數列的
概念。
，另外當月有些新增的部門會科，這兩種情況都會使得成本結算出問題，想
要利用Excel偵錯，因為涉及到部門會科一組兩個變數，必須引用二維數列的
概念。
二、第一直覺我是在常用函數MAX上動手腳：
{=MAX(($D$3:$D$11=A10)*($E$3:$E$11=B10)*$D$3:$D$11)}。這個公式表示
在D3到D11範圍里，同時滿足D3到D11中等於A10、而且E3到E11中等於B10的儲
存格，選擇其中最大值。在第十列公式取的是A10B10(會科5300部門D)，D欄
里沒有符合的儲存格，所以取值是零。在第九列公式取的是A9B9(會科5300部
門C)，D欄里只有一個同時符合這兩個條件，就是公式計算結果的5300。需特
別注意陣列符號{}，如果是直接輸入，會讓儲存格變成文字而非公式計算，
要在輸入「=MAX(($D$3:$D$11=A10)*($E$3:$E$11=B10)*$D$3:$D$11)」之後
，滑鼠停留在公式欄，同時按住Ctrl和Shift不放，再按Enter鍵，這樣會自
動跑出{}，將公式陣列化。
三、上一個步驟求的是實際費用有、分攤設置無的部份，只要依照公式原理
，前後欄位稍加替換，便可求出分攤設置有、實際費用無的部份。
四、一般遇到多條件求值的情況，真正的Excel高手信手捻來就是個陣列函數
，我半路出家，陣列觀念不及格，只有簡單函數MAX常用，所以第一時間將
MAX陣列化，想出前面那個長相奇怪的公式，勉強還堪用。不過既然是多條件
求值，在此當然要介紹名門正宗的陣列函數了。在公式欄輸入「
=SUMPRODUCT(($D$3:$D$11=A10)*($E$3:$E$11=B10))」，意思是滿足D3到
D11中等於A10、並且E3到E11同一列數也等於B10，這兩個條件都滿足的儲存
格個數。在F10儲存格里的公式，實際費用是會科5300部門D，沒有設置分攤
，所以計算結果是0個，在上一格F9的公式里，實際費用是會科5300部門C，
設置里剛好有個會科部門都相同的分攤組合，所以計算結果有1個相符。
格個數。在F10儲存格里的公式，實際費用是會科5300部門D，沒有設置分攤
，所以計算結果是0個，在上一格F9的公式里，實際費用是會科5300部門C，
設置里剛好有個會科部門都相同的分攤組合，所以計算結果有1個相符。
五、和MAX函數情況相同，上一步驟公式求的是實際費用有，分攤設置無的部
份，只要依照SUMPRODUCT公式原理，將欄位稍加替換，便可求出分攤設置有
，實際費用無的部份。
份，只要依照SUMPRODUCT公式原理，將欄位稍加替換，便可求出分攤設置有
，實際費用無的部份。
六、想全面瞭解SUMPRODUCT函數，在公式欄左邊的「fx」按一下，函數小教
室跳出來，如果小教室看了還不夠，左下角還有個「函數說明(H)」超連結，
點一下即可查閱官方指南。
室跳出來，如果小教室看了還不夠，左下角還有個「函數說明(H)」超連結，
點一下即可查閱官方指南。
七、Excel方法很多，同樣目的有許多不同路徑可以達到，如同這篇文章的
MAX和SUMPRODUCT一般，戲法人人會變，巧妙各有不同，多熟悉一個函數，便
多一種戲法，所以平常沒事的時候，只要隨便一個空格按下「fx」，所有函
數都在這裡，任君學習！
多一種戲法，所以平常沒事的時候，只要隨便一個空格按下「fx」，所有函
數都在這裡，任君學習！
延伸閱讀：
Excel：設置成本分攤表（間接部門）
http://www.b88104069.com/archives/1342
Excel：設置成本分攤表（間接部門） | 贊贊小屋
![[圖]]()
鼎新TipTop成本分攤以部門會科為一組單位，分攤到預設的製程工段上。例如，間接部門的費用須分攤到所有工段，第一個是生管的薪資費用，第二個是品保的薪資費用，第三個是生管的攤銷費用，接下來依此類推，所有部門會科都要設置，才能將間接部門的所有人工製費，全部分攤到當月份的工單上。剛開始導入系統結算成本，先要討論出分攤方案，然 ...
![[圖]](http://www.b88104069.com/wp-content/uploads/2015/03/E22a00.png)
Excel：成本分攤設置（直接部門）
http://www.b88104069.com/archives/1403
Excel：成本分攤設置(直接部門) | 贊贊小屋
![[圖]]()
先前介紹過間接部門的成本分攤設置，由於間接部門的成本，會分攤到所有製程工段，在設置上相對單純，直接部門則是各部門有相對應的製程工段，比較麻煩，現在分享直接部門的成本分攤設置： 一、如圖所示，為了方便說明，簡單假設有三個直接部門，分別有2、1、3個製程工段，兩個會科。 二、3個直接部門相對應的工段如表 ...
![[圖]](http://www.b88104069.com/wp-content/uploads/2015/03/E23a00.jpg)
Excel：邊際貢獻分析表
http://www.b88104069.com/archives/2461
Excel：邊際貢獻分析表 | 贊贊小屋
![[圖]]()
很樂意有讀者來信，因為教學相長，自己的工作範圍難免有限，別人提出問題，我也從中得到啓發。一般我希望每周能寫一篇，最多就一篇，因為我還有其它非工作的文章想寫呀。這個星期比較特別，多弄了一篇Excel，原因就是有所啓發。在這裡想分享的，是如何用Excel編製邊際貢獻分析表： 一、標準配備的銷貨毛利表：品號、數量、收入 ...
![[圖]](http://www.b88104069.com/wp-content/uploads/2015/06/E38a00.jpg)
--
會計人的Excel小教室：
https://www.facebook.com/acctexcel
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 183.206.180.51
※ 文章代碼(AID): #1LeQvx6g (Office)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1436659323.A.1AA.html
--
※ 看板: layzer 文章推薦值: 0 目前人氣: 0 累積人氣: 377
回列表(←)
分享