贊贊小屋

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數

Excel函數中的陣列公式較為複雜,因為它多維組合的特性,很適合多條件查找或是加總。本文以成本會計中部門費用分攤為範例,介紹使用MAX陣列和SUMPRODUCT函數。

成本分攤有三個主要元素:製程、工時、成本。每月結算成本,把這三塊資料統計好了,將當月份所有人工製費,先依照權數分攤到各個製程,各製程再依照工單工時比例,將製程成本分攤到各個工單,由此統計出該工單應當歸屬的成本,再將此成本計算出工單產出的單位成本,最後所有工單入庫成本,和期初成本、其他存貨異動一起加權平均,結算出當月成本。

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

更麻煩的是,費一番功夫設置好的分攤表,並不是從此一勞永逸。會計科目並非一成不變、部門組織有可能調整,某項製程也有可能當月無工時產生(表示未開工生產),有時候,甚至連分攤比例都有可能修改。凡此種種,原來的設置必須更新,否則成本結算系統會跳出錯誤訊息,拋轉的成本傳票拉不出會科。

綜上所述,分攤設置表有可能出問題,但是它資料量太大,沒辦法一筆一筆檢視是否有誤,很需要有一套完善機制,能把錯誤情況偵察出來。倘若原始系統沒有,資訊人員又沒有客製,那只能會計人員自己處理。在此介紹Excel具體流程:

一、部門費用成本分攤

如圖所示,分攤設置好的部門會科。仔細看標黃色部份:「5300-D」和「5400-A」有實際費用,但是沒有設置分攤;「5200-C」和「5300-A」有設置分攤,但是當月沒有費用金額。這兩種情況,都會使得成本結算出問題。在資料量大的情況下,很難用人工方式一筆一筆檢查。想要設計Excel函數公式偵錯,因為涉及到部門會科一組兩個變數,必須使用二維數列的概念。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第1張

二、MAX陣列函數公式

使用Max函數:「{=MAX(($E$3:$E$11=B10)*($F$3:$F$11=C10)*$E$3:$E$11)}」。公式意思是E3到E11中等於B10、而且F3到F11中,相同列數的儲存格也要等於C10,同時滿足這兩個條件,在E3到E11範圍內,取其中最大值。文字說明較為難懂,以實例來說,G10儲存格公式的取值條件,是B10、C10(會科5300、部門D),E3到E11、F3到F11裡,並沒有符合的儲存格,所以E3到E11取值為零。G9儲存格公式的取值條件,是B9、C9(會科5300部門C),E3到E11、F3到F11裡,只有E11、F11這一組陣列,同時符合這兩個條件,因此在E3到E11中,取最大值即為E11的5300。

特別再說明陣列符號「{}」。直接在資料編輯列輸入左右大括號,Excel會理解為文字符號,而非公式計算元素。必須先輸入公式:「=MAX(($E$3:$E$11=B10)*($F$3:$F$11=C10)*$E$3:$E$11)」之後,游標停留在資料編輯列,同時按住Ctrl和Shift不放,再按Enter鍵,如此會自動跑出「{}」,表示已將公式陣列化。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第2張

三、反向公式核對

上一個步驟,可以追查出有部門費用、沒有分攤設置的部份,依照公式原理架構,條件欄位稍加替換,便可以追查出有分攤設置、沒有部門費用的部份,公式:「{=MAX(($B$3:$B$11=E8)*($C$3:$C$11=F8)*$B$3:$B$11)}」。同樣順著上一步驟的說明架構,可以拆解出公式計算原理。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第3張

四、SUMPRODUCT函數

一般遇到多條件求值,Excel高手信手捻來便是陣列。本節將Max函數陣列化,恰巧合乎需求,也算是神來之筆了。不過既然多條件求值,在此介紹名門正宗的陣列函數,輸入公式:「=SUMPRODUCT(($E$3:$E$11=B10)*($F$3:$F$11=C10))」,意思是E3到E11中等於B10、並且F3到F11同一列數也等於C10,這兩個條件都滿足的儲存格個數。在G10儲存格裡的公式,滿足條件是B10、C10(會科5300、部門D),這組陣列並沒有設置分攤,所以計算結果是0個。上一格G9的公式裡,滿足條件是B9、C9(會科5300、部門C),設置裡剛好有個會科部門(E11、F11)都相同的分攤組合,所以計算結果有1個相符。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第4張

五、反向成本分攤核對

和Max函數情況相同,上一個步驟是追查出有部門費用、沒有分攤設置的部份,依照公式原理架構,稍加修改公式:「=SUMPRODUCT(($B$3:$B$11=E8)*($C$3:$C$11=F8))」便可以追查出有分攤設置、沒有部門費用的部份。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第5張

六、插入函數

想深入瞭解SUMPRODUCT函數,將游標移到上面函數公式左邊的「fx」,點選「插入函數」。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第6張

七、函數引數

「函數引數」視窗,這等於是函數小教室,很值得好好看一下,有函數說明,有各引數的說明、輸入值、輸入結果,也可以整個函數公式計算結果。如果小教室看了還不夠,很有學習熱忱,左下角還有個「函數說明(H)」超連結,可以體驗一下微軟在這方面是多麼認真。

Excel檢查成本會計分攤設置,MAX陣列公式和SUMPRODUCT函數 成本會計 第7張

學習Excel函數的內建最佳方法

Excel方法很多,同樣目的有許多不同路徑可以達到,如同這篇文章的MAX和SUMPRODUCT一般,戲法人人會變,巧妙各有不同,多熟悉一個函數,便多一種戲法。實務工作上遇到需要Excel的時候,都可以先想想,是否函數公式或命令工具適合運用,才能收事半功倍之效。平常沒事的時候,資料編輯列裡的函數公式隨便一個位置按下「fx」,Excel很樂意認真跟你講解喔。

本文內容取自《會計人的Excel小教室(增訂版)》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010817518

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1