贊贊小屋

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位

Excel原始資料總是需要後加工整理。本文以會計應收帳款明細表為例,介紹IF、TRIM、MID等函數綜合應用,自動清理掉ERP系統報表空白,依需求增加分析欄位。

ERP系統很多預設報表,這些報表都是符合一般情況、最大公約數設計出來的。實際工作中為了符合個別特殊目的,往往需要再修改原始系統報表,例如填滿空白、調整格式、判斷單別、計算數值等。像這樣的公司管理報表,大多是例行性,每周或是每月必須編製,雖然每次整理不是太複雜,但一再重複的作業流程,如果能設計稍微自動化編製,豈不更好。這一節以範例具體介紹:

一、有點麻煩的ERP系統應收帳款報表

系統應收帳款報表,一張單頭應收帳款,有好幾項單身出貨料號,原始報表為了美觀,沒有資料的保持儲存格空白。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第1張

二、會思考的IF函數填補空白

比較理想的資料報表,最好不要有空白,方便Excel進行篩選、排序、樞紐等種種統計分析。為了保留ERP資料不作更動,新建一張工作表,引用原始報表數據,A2儲存格因為是第一項,比較特別,直接連結:「=’1′!B2」(這裡的「’1′!」代表第一個步驟的工作表),A3以下是想要填滿空白,設計公式為:「=IF(‘1′!B3=””,A2,’1’!B3)」,然而,沒有成功帶過來資料。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第2張

三、系統報表怎麼夾帶了空格

公式本身沒有問題,仔細研究之後,把游標移到原始報表的A3儲存格資料編輯列,左右移動,發現雖然儲存格顯示沒有任何內容,但其實內容包含了空格,這應該是系統報表自己預設跑出來的,沒辦法事先改變。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第3張

四、不討喜的東西交給TRIM函數

系統有狀況,Excel有對策。更改公式為:「=IF(TRIM(‘1′!B3)=””,A2,’1’!B3)」,利用Trim函數將空格去掉,如此成功帶出原始資料。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第4張

五、LEFT和IF合作自動帶入單別

希望報表顯示帳款單據的性質,新增一個原始報表沒有的欄位,依照單別設定輸入判斷公式:「=IF(LEFT(A2,2)=”SA”,”銷貨”,”銷退”)」。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第5張

六、我喜歡乾淨的資料表

利用類似公式,可以將原始報表的資料,一一帶到另外新建的工作表上,有些原始報表的欄位,例如「業務」,因為沒需要,不必帶過來。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第6張

七、要什麼欄位自己組函數

除了直接引用資料,配合管理需要,有些欄位資料必須自己設計,例如E3儲存格的「淨額」公式:「=IF(B3=”銷貨”,’1′!H3,-‘1’!H3)」,意思是銷貨取正數,否則(銷退)取負數;F3儲存格的「月份」公式:「=MID(A3,4,4)」,意思是「帳款編號」A3第四字元位置起,取四個字元,配合單據編碼原則剛好為年月;G3儲存格的「匯率」公式:「=IF(TRIM(‘1′!D3)=””,G2,’1′!F3/’1’!D3)」,意思是「本幣應收」除以「原幣應收」,如此得到匯率,Trim函數部份先前提過,不再贅述。H3儲存格的「稅率」公式:「=IF(TRIM(‘1′!E3)=””,H2,’1’!E3/(‘1’!F3-‘1’!E3))」,意思是如果有稅額,將稅額除以未稅金額(應收減去稅額),如此得到稅率。最後I3儲存格的「本幣未稅」公式:「=E3/(1+H3)*G3」,意思是含稅應收先換算成未稅收入,再換算成本幣金額,最後得到的,便是每項帳款的收入金額。

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第7張

自動化後加工整理系統報表

這篇文章介紹Excel自動化整理報表的方法。欄位資料填好,第一列、第二列公式設好,第三列開始可以一直往下拉,複製公式即可。原始報表有但是不需要的資料,不用帶過來,原始報表沒有但是有需要的資料,設計公式計算出來。只要將自動化工作表第一次弄好,往後在做下一期報表時,只要將跑出來的系統報表,取代貼上活頁簿中的原始報表工作表,自動化工作表便會更新成新一期的資料,真正一勞永逸。

原始資料只到第12列,依照本篇文章設計的公式,自動化報表第13列開始,還是會一直複製第12列,雖然金額為零,不影響資料正確性,如果是為了美觀或者想檢查公式列是否足夠,也可以進一步完善公式設置。這部份主要會用到COUNT函數和格式化條件的技巧,下一節會有關於COUNT(COUNTA)函數用法的介紹,至於格式化條件,讀者有興趣可先自行嘗試,筆者往後有適當會再作說明。

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

博客來網路書店網址:

Excel報表整理:IF、TRIM、MID函數刪除空白新增欄位 經典函數 第8張