Excel如何以Cell函數自動更新會計期間

會計每個月結帳,每個月都有新的「當月份」和「上月份」,如果是系統產生報表,期間資料自然不會有問題。不過會計實務工作上,很多管理報表皆為手工維護,系統只是方便撈原始資料。於此情形,每次結完帳編製管理報表,第一步便是更新會計期間相關欄位,舉凡有月份的地方都需要更新,雖然操作簡單,但也許工作一忙,這顯而易見的小細節被疏漏掉了。我自己就有幾次報表忘了更新月份,信件一發送出去,對方顯而見看到當然是一臉大問號,同事的話倒也還好,可是極大機率信件會抄送老闆,一問下來,面子掛不住了。為了徹底杜絶這個「低級錯誤」,既然報表是用Excel做,當然希望它聰明一點,自動幫我們更新會計期間。以下介紹具體方法:

一、「應收帳款周轉轉天數」 報表,表頭有一個當期會計期間「Jul-17」,儲存格內容為「2017/7/1」,報表標題列有三個月,分別當期會計期間和前兩個月,所以總共有四個月份資料。

二、像這樣有四個期間相關聯,首先將其中之一當作基準、另外三個參照基準,例如前兩個月的「May-17」公式設置為「A3-60」,其餘標題月份類似作法。

三、承上個步驟,四個月份的設置關鍵剩下一個基準月份。想法之一,既然都是月初結上個月的帳,那麼輸入公式:「=TODAY()-30」,每次使用這個檔案,期間會自動呈現這次結帳的會計期間。

四、利用「TODAY」函數雖然會自動更新日期,但有利有弊,過一陣子,例如兩三個月過,如果開啓以前月份的結帳檔案,「TODAY」變了,工作表上的會計期間也會往後跳,失去正確性。因此參考先前分享文章,以絶對的「DATE」取代相對的「TODAY」,輸入公式:「=DATE(2017,8,31)」。

五、自動化再往前推一步,基準日期用「DATE」雖然絶對,但變成每次都要更新一次,所以再耍點小聰明,通常會計每月結帳就那些檔案,便於區分起見會在相同檔名後面加個期間,例如這裡的「應收帳款周轉天數_2017.08.xlsx」,利用此特性如果將基準會計期間參照到檔案名稱的期間區位,不就省一事了?輸入公式:「=CELL(“filename”)」。

六、「=CELL(“filename”)」能帶出檔案的資料夾路徑及名稱,參考以前分享文章,藉用「FIND」、「MID」、「DATE」成功將「CELL」的會計期間轉換出來:「2017/8/1」。

七、終極公式:「=DATE(LEFT(MID(CELL(“filename”),FIND(“.xls”,CELL(“filename”))-7,7),4),RIGHT(MID(CELL(“filename”),FIND(“.xls”,CELL(“filename”))-7,7),2),1)」從今爾後,每次結帳只要複製好檔案,更新檔案名稱,檔案裡的工作表會計期間同步更新!

這一節介紹如何自動更新會計期間,文章看下來其實很明顯,就算老實點手工維護這個東西,也不花太多時間,這裡費盡心思設定自動更新,追求的不是效率,而是正確性。因為魔鬼藏在細節裡,只要有出錯的可能性,值得事先在這裡設置自動控管防呆的機制。在如履薄冰的實務工作上,講究效率的同時不容許鷄蛋裡出骨頭,所以Excel的學習和應用,高效率和不容易出錯值得不斷地被追求,這一節就是最好範例。

Excel整理術 - 入門密技+進階實作 台北場淡江大學台北校區
2017-12-23(星期六) 09:00~16:00

http://smart.businessweekly.com.tw/ecourse/product.aspx?PROD_TYPE_NO=PROD000008934

《會計人的Excel小教室》:每位會計人都應該具備的武功秘笈!

http://www.books.com.tw/products/0010734874

《會計人的Excel VBA小教室》:會計人Excel強化並且VBA入門!

http://www.books.com.tw/products/0010753074