Excel如何自動化整理報表




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

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

系統應收報表

二、比較理想的資料報表,最好不要有空白,方便篩選、排序、樞紐。因此新建一張工作表,引用原始報表數據,A2儲存格因為是第一項,比較特別,直接連結:「=應收!B2」,A3以下是想要填滿空白,設計公式為:「=IF(應收!B3″”,B2,應收!B3)」,然而卻沒有成功帶出來。

=IF(應收!B3"",B2,應收!B3)

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

其實儲存格裡包含了一個空格

四、系統有政策,Excel有對策。更改公式為:「=IF(TRIM(應收!B3)=””,B2,應收!B3)」,利用Trim函數將空格殺掉,成功帶出原始資料。

=IF(TRIM(應收!B3)="",B2,應收!B3)

五、希望報表顯示帳款單據的性質,依照單別設定輸入判斷公式:「=IF(LEFT(B9,2)=”SA”,”銷貨”,”銷退”)」

=IF(LEFT(B9,2)="SA","銷貨","銷退")

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

例如「業務」,因為沒需要,不必帶過來

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

=IF(TRIM(應收!E3)="",七!I2,應收!E3/(應收!F3-應收!E3))

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

原始資料只要第12列,依照本篇文章設的公式,自動化報表第13列開始,還是會一直複製第12列,雖然金額為零,不影響數據正確性,但如果為了美觀或者是想檢查公式列是否足夠,也是可以進一步完善公式設置,關於這部份,以後有適當機會再作補充說明。






當前文章延伸閱讀:
經典函數