贊贊小屋

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數

Excel資料處理及分析報告時常需要邏輯及資訊函數判斷,本文以會計工作為例,設計SUM、VLOOKUP、IF、OR、ISBLANK、ISERROR函數公式,協助編製存貨成本前十大排行分析。

事務所查帳員或公司會計在統計資料時,基於重大性風險比例原則,都會先作個排行,抓出前幾大項目,接著再針對其中差異較大者分析說明。有時候排行的依據不同,為了避免前後不一致或工作重複,最好有個機制,可以自動篩選出已分析過的項目,以下以前十大毛利分析作為範例,分享實務上作法和Excel相關應用:

一、生產數量排行成本分析

如圖所示,「本月生產彙總-生產數量排行」,依照完工數量排行的前十大料號,後面有兩個月的成本單價,針對單價差異超過10元者分析說明。

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第1張

二、數量及金額排行榜

除了生產數量,還有依照生產金額的排行榜。一樣先抓出前十大料號,針對兩個月的成本單價,差異10元以上者分析說明。這邊有個問題,「A80R8DB」這個料號在前面數量排行已經上榜,分析過了,其實可以直接引用,毋須再作分析之外,也要注意兩個資料金額要一致,不然就穿幫了,是很明顯的疏誤,開會或報告時有可能被K!

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第2張

三、SUM與VLOOKUP函數

檢查方式其中之一,是把除了差異以外的數量金額單價都加起來,也就是「SUM(C17:F17)」,然後再和另外一種排行的相同料號相減:「VLOOKUP(B17,$B$3:$I$12,8,0)」,如果不是零,表示有不一致,例如圖中的「A80R0GB」。

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第3張

四、一致性與是否已分析檢查

除了一致性檢查,還應該把先前已分析過的項目找出來:「=VLOOKUP(B18,$B$3:$H$12,7,0)」,計算結果為「0」,表示這個料號先前的排行榜也有,但是毋須分析,顯示文字,表示先前分析過了,直接引用先前說明,顯示「#N/A」,表示先前排行榜沒有這個料號,所以一定是沒有分析過,這時候視情況看是否需要分析。

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第4張

五、IF、OR、ISBLANK、ISERROR函數

除了簡單VLOOKUP公式,還可以加一些判斷條件,直接在說明欄位上呈現想要的結果:「=IF(OR(ISBLANK(VLOOKUP(B18,$B$3:$H$12,7,0)),ISERROR(VLOOKUP(B18,$B$3:$H$12,7,0))),””,VLOOKUP(B18,$B$3:$H$12,7,0))」,公式看起來稍微複雜,其實在邏維架構簡單直觀,如果先前排行榜沒有此料號、或者先前有料號但不用分析,符合這兩種情形都顯示空白,否則引用先前的說明,如此計算結果就不會有「0」或「#N/A」。

這一步驟有兩個作用,一是能帶出先前已分析過的料號說明,二是沒有分析過的料號可以保持空白。不過仍要搭配上一步驟顯示「#N/A」的未分析料號,針對差異超過10元者進行分析,有了具體的分析文字,直接輸入在說明欄位即可,例如圖片所示的綠色部份。

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第5張

六、函數公式複製套用

倘若是兩種排行十個料號,不一定要函數,細心看便可完成任務。實務工作中,有可能還要以其它方式排行,例如單價,況且也許不只昰十個料號。這樣的狀況如果沒有函數幫助,一個一個細心看,眼睛必定花掉,並且容易出錯,最好是藉助電腦Excel輔助,把其它排行榜都VLOOKUP計算出來,一目瞭然。函數可以再全面一點,核對範圍從B3一直拉到B26:「=IF(OR(ISBLANK(VLOOKUP(B31,$B$3:$H$26,7,0)),ISERROR(VLOOKUP(B31,$B$3:$H$26,7,0))),””,VLOOKUP(B31,$B$3:$H$26,7,0))」像這樣善用Excel避免錯誤及提高效率,便是贊贊小屋此一系列著作的宗旨。

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第6張

七、前十大存貨成本分析報告

最後整理好的分析報告如圖所示,看起來很乾淨,背後其實是經過Excel巧妙的設計。

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第7張

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第8張

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第9張

善用Excel避免錯誤及提高效率

這一節所介紹的前十大毛利分析,如同會計實務上各項管理報表,假設公司有像這樣的管理分析需求,每個月都會進行。因此如果能花些時間把公式架好,往後的日子會輕鬆許多。從最後所呈現的報告來看,確實沒有Excel函數輔助的話,人工核對將是事倍功半、要花很多時間又容易出錯。

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

Excel存貨成本前十大分析,善用ISBLANK及ISERROR等資訊函數 成本會計 第10張