Excel如何自動化整理報表 - 會計

Excel如何自動化整理報表

網誌圖文版：

http://www.b88104069.com/archives/4099

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

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

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

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

四、系統有政策，Excel有對策。更改公式為：「＝IF（TRIM（應收！B3）＝
"",B2,應收！B3）」，利用Trim函數將空格殺掉，成功帶出原始資料。

五、希望報表顯示帳款單據的性質，依照單別設定輸入判斷公式：「＝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」，意思是含稅應收先
換算成未稅收入，再換算成本幣金額，最後得到的，便是每項帳款的收入金
額。

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

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


延伸閱讀（Excel自動化）：

Excel如何自動填滿空白儲存格

http://www.b88104069.com/archives/4057

Excel如何格式化條件自動標示逾期應收帳款

http://www.b88104069.com/archives/4064

Excel如何用index函數自動排序資料

http://www.b88104069.com/archives/4070

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--