Excel如何篩選及函數,彙總營業成本表




比較成熟的ERP系統,大多可以跑出營業成本表,不過由於存貨分類群和會科的不同,有可能系統跑出來的,是分類群成本表,而我們要的是會科成本表,這時候如果資訊不能協助客製化,就必須自己Excel整理,以下分享如何藉助函數處理:

一、如圖所示,系統跑出來的成本表分成五金耗材、庶務用品、物料,這些存貨的會計科目都是原料,所以會計上的成本表,特別是給查帳會計師或稅局的報表,都必須依照會科彙總。

系統跑出來的成本表

二、像這種依照某特定內容彙總的場合,第一個想到的是「篩選」,依照微軟講法:「很輕鬆快速地在儲存格範圍或表格欄中,找出資料子集合並加以運用。」

Excel篩選

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

「篩選」、「文字篩選」、「包含」

四、跑出「自訂自動篩選」視窗,項目是「包含」,輸入「期初存貨」。

自訂自動篩選

五、篩選之後,雖然看到的都是期初存貨,但仔細再看,列數是1、2、10、18,這樣跳躍很不利於Excel資料的統計,所以選取篩選出來的範圍,大膽按「F5」(「到」的快捷鍵)。

大膽按「F5」(「到」的快捷鍵)

六、選擇「特殊」之後,出現「特殊目標」視窗,在這裡點「可見儲存格」,表示那些隱藏跳過的列,例如第3列到第9列,我們都不要。

出現「特殊目標」視窗,在這裡點「可見儲存格」

七、選好篩選後的可見儲存格之後,複製貼上,這樣就有了三行期初存貨的資料,連續完整的表格資料,列數不跳躍,我在下面加了一個「期初存貨小計」。

選好篩選後的可見儲存格之後,複製貼上

八、寫了這麼多,終於來到這篇文章的重點:以函數方式實現期初存貨小計。標黃色部份是公式:「=SEARCH($D$1,A2)」,表示在A2儲存格裡,尋找D1(期初存貨)的起始位置,「D1」掛成「$D$1」,這樣將公式往下拉的時候,A2會跳A3A4等等,D1不會跟著跳,這個掛$的動作可按快速鍵「F4」達成。「=ISNUMBER(D2)」表示判斷D2是否為數值,依照判斷結果返回「TRUE」或「FALSE」「=IF(E2,$D$1,””)」代表如果E2為真(TRUE),返回「D1」(固定不變),否則(E2為假(FALSE),呈現空白(“”)。最後,終於可以弄一個「=SUMIF(F2:F25,D1,B2:B25)」代表在F2到F25之間,如果有等於D1的行列,加總B2到B25位於同一列上的數值,結果,就是期初存貨小計!

標黃色部份是公式:「=SEARCH($D$1,A2)」

九、利用同樣方式,可以再做出本期進貨、結存調整等等的小計,也可以將三段公式合併:「=IF(ISNUMBER(SEARCH($D$1,A2)),$D$1,””)」,只要把中間的過程D到F欄組合隱藏,留下結果的H到I欄,這就是很完美的分類項目彙總。利用函數計算的好處是,只要把這個公式表架好,每月系統跑出新的成本表時,只要把資料值貼上AB欄,HI欄自動計算出會科小計,一勞永逸,這是Excel函數設定的最高境界。

=IF(ISNUMBER(SEARCH($D$1,A2)),$D$1,"")






當前文章延伸閱讀:
成本會計