贊贊小屋

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表

Excel配合ERP系統固定格式資料,架好公式可以自動彙總報表。本文以營業成本表為例,介紹篩選及定位特殊目標的到指令,再綜合應用SEARCH、ISNUMBER、IF、SUMIF函數。

成熟的ERP系統,在成本結算的模塊,必定會有一個營業成本表。因為系統的存貨分類和會計科目分類不同,有可能系統跑出來的,是比較細項的存貨子分類成本表,但對於會計而言,需要的是大類別的會科營業成本表。像這種情況,可以藉助Excel函數公式處理,自動擷取會計上所需要的數據,以下介紹具體操作實例:

一、ERP系統成本表

如圖所示,系統跑出來的成本表,光是原料部份,分成A類、B類、C類,這些存貨類別,會計科目都是原料。然而會計上的成本表,例如給查帳會計師或稅局的報表,都必須依照會科彙總,所以要做適當的轉化。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第1張

二、篩選指令

像這種情況,依照某特定內容彙總的場合,第一個想到的是「篩選」命令,依照Excel線上說明:「輕鬆快速地在儲存格範圍或表格欄中,找出資料子集合並加以運用。」

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第2張

三、文字篩選

先選取第一列的範圍(欄位名稱所在列),依序點選「篩選」、「文字篩選」、「包含」。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第3張

四、自訂篩選

跳出來「自訂自動篩選」視窗,在預設的「包含」項目是中,輸入「期初存貨」。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第4張

五、到(快速鍵「F5」)

篩選之後,雖然看到的都是期初存貨,但仔細再看,列數是1、2、7、12,表示有隱藏資料,如此不利於Excel資料的統計,所以再選取篩選出來的範圍,執行「到」命令(快速鍵「F5」),按下左下角的「特殊」。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第5張

六、特殊目標

「特殊目標」視窗中,圈點「可見儲存格」,將那些隱藏不見的列資料,例如第3列到第6列,忽略不計,所有執行命令只針對可見資料。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第6張

七、期初存貨小計

設定好了,將那些篩選後的可見儲存格複製貼上,這樣就有了期初存貨加總表,列數連續完整的表格資料,下面加了一個「期初存貨小計」。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第7張

八、SEARCH、ISNUMBER、IF、SUMIF函數

最後介紹以函數方式,實現期初存貨小計。D2儲存格公式:「=SEARCH($D$1,A2)」,作用為在A2儲存格裡,尋找D1字串(期初存貨)的起始位置,計算結果是6,因為在「A類原料-期初存貨」中,「期初存貨」出現在第6個字元位置。公式中「D1」掛成「$D$1」,這樣將公式往下拉的時候,A2會跟著往下跳A3、A4、……,D1則會固定住,這個掛「$」的動作,可以在資料編輯列按快速鍵「F4」達成。E2儲存格公式:「=ISNUMBER(D2)」,作用為判斷D2到D16是否為數值,依判斷結果顯示「TRUE」或「FALSE」。F2儲存格公式「=IF(E2,$D$1,””)」代表如果E2為真(TRUE),返回「D1」(固定不變),否則的話,E2為假(FALSE),呈現空白(””)。最後,於F1儲存格設定公式:「=SUMIF(F2:F16,D1,B2:B16)」作用為在F2到F16之間,如果有等於D1的儲存格(F2、F7、F12),加總B2到B16位於同一列號上的數值(B2、B7、B12),計算結果便是期初存貨小計(90,000)。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第8張

九、函數公式組合

上一步驟的基礎上,運用同樣方式,很快能照樣造句出本期進貨、本期出售、本期領用、期末存貨,結存調整等的小計。在公式設計上,也可以將三段合併:「=IF(ISNUMBER(SEARCH($J$6,$A2)),$J$6,””)」。只要把中間過程的D到H欄組合隱藏,留下結果的J到K欄,這就是很完美的分類項目彙總。

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第9張

架好一勞永逸的Excel函數公式

第一步,往往最辛苦,只要順利跨出,接下來會走得很快。在這裡費盡心思將公式架好,圖的不是一時,而是長久的以後。如果是為了這個月彙總成本表需要,直接自己拿計算機按按即可,可是,如果想到日後的工作上,每個月都必須彙總一次成本表,如果現在把Excel公式架好,從今爾後每個月,只要把當月的系統報表貼上A欄B欄,J欄K欄便會自動彙總,如此一勞永逸,這絶對是Excel函數設定的最高境界,也是本書想要講的最最重點。

況且,計算機可能手抽筋按錯,萬能的Excel大神是不會出錯的。

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

博客來網路書店網址:

Excel成本會計:SEARCH、ISNUMBER、IF、SUMIF函數彙總成本表 成本會計 第10張