Excel檢查成本分攤設置 - 會計

Excel檢查成本分攤設置

成本分攤三元素：製程、工時、成本，月底把這三塊資料統計好了之後，當
月所有成本先分攤到各個製程，各製程再依工單工時比例，將製程成本分配
到各個工單，由此計算出該工單產出的單位成本，最後所有工單入庫和其他
存貨異動加權平均，便可結算出當月成本。

其中成本分攤到製程比較關鍵，一般ERP系統帳結算成本，例如鼎新Tip-top
，以部門會科組合起來作為一成本項目，先評估這成本項目該由哪些製程分
攤，再設置好分攤權數，明確各製應依多少比例分攤，總分攤權數合計是
100%。舉例而言，A部門5100會科當月共有100塊成本，這100塊以3:2比例分
給a和b兩個製程。一家公司假設有十個部門、十個會科、十個製程好了，這
樣就有10X10X10=1,000筆資料，實在不是個小數目。

更麻煩的是，費一番功夫設置好龐大資料庫，卻並非從此一勞永逸，因為會
科並非一成不變、部門組織有可能調整，而且某月份某製程也有可能無工時
產生（根本未開工），有時候甚至連分攤比例都可能需要修改。凡此種種情
形，原來的設置就必須更新，否則成本結算會跳出錯誤訊息，拋轉成本傳票
時會拉不出會科。

如前所述，分攤設置的資料過於龐雜，沒辦法一筆一筆檢視是否有誤，很需
要有一套完善機制，能把錯誤訊息偵察出來，倘若系統沒有，資訊人員又沒
有客製，那只得靠萬能的會計人自己動手囉。在此分享我所遇到的實例，還
有相對應的Excel檢錯方式：

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

一、首先，如圖所示，已經設置好的部門會科，有些當月沒有交易金額產生
，另外當月有些新增的部門會科，這兩種情況都會使得成本結算出問題，想
要利用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個相符。

五、和MAX函數情況相同，上一步驟公式求的是實際費用有，分攤設置無的部
份，只要依照SUMPRODUCT公式原理，將欄位稍加替換，便可求出分攤設置有
，實際費用無的部份。

六、想全面瞭解SUMPRODUCT函數，在公式欄左邊的「fx」按一下，函數小教
室跳出來，如果小教室看了還不夠，左下角還有個「函數說明(H)」超連結，
點一下即可查閱官方指南。

七、Excel方法很多，同樣目的有許多不同路徑可以達到，如同這篇文章的
MAX和SUMPRODUCT一般，戲法人人會變，巧妙各有不同，多熟悉一個函數，便
多一種戲法，所以平常沒事的時候，只要隨便一個空格按下「fx」，所有函
數都在這裡，任君學習！

--

經典電影，精彩再現：

https://www.facebook.com/b881.movies


--