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函數公式偵錯，因為涉及到部門會科一組兩個變數，必須使用二維數列的概念。

二、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鍵，如此會自動跑出「｛｝」，表示已將公式陣列化。

三、交叉核對報表

上一個步驟，可以追查出有部門費用、沒有分攤設置的部份，依照公式原理架構，條件欄位稍加替換，便可以追查出有分攤設置、沒有部門費用的部份，公式：「｛＝MAX（（＄B＄3：＄B＄11＝E8）＊（＄C＄3：＄C＄11＝F8）＊＄B＄3：＄B＄11）｝」。同樣順著上一步驟的說明架構，可以拆解出公式計算原理。

四、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個相符。

五、反向資料比對

和Max函數情況相同，上一個步驟是追查出有部門費用、沒有分攤設置的部份，依照公式原理架構，稍加修改公式：「＝SUMPRODUCT（（＄B＄3：＄B＄11＝E8）＊（＄C＄3：＄C＄11＝F8））」便可以追查出有分攤設置、沒有部門費用的部份。

六、插入函數工具

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

七、函數引數視窗

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

Excel陣列條件的學習

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

微軟參考資料：陣列公式的規則和範例。

本篇文章的講解影片1：

本篇文章的講解影片2：

想學更多相關技巧嗎？歡迎前往贊贊小屋Excel成本會計教學中心：