Excel如何設計函數公式整理固定資產清冊 - 會計

Excel如何設計函數公式整理固定資產清冊

網誌圖文版：

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

事務所工作的時候，接觸過很多家企業的帳冊和ERP系統，臺灣大型的集團企業蠻多使用
SAP系統，稍具規模的中小型企業很多是被鼎新拿下，例如Workflow系統和Tip-top系統。
像這些成熟完整的ERP系統，會依照功能分成好幾個模組，其中總是會有個固資模組，裡
面很多制式化報表，包括不可缺少的「財產目錄」或者「固定資產清冊」。

固定資產清冊核心欄位有：資產編號、資產名稱、規格、數量、原始成本、取得成本、本
期折舊、累計折舊，這些歷史資料可以滿足事務所種種查核程序。然而，如果公司財會部
門想要做財務分析，例如未來五年十年的預測模型，評估帳上固資未來幾年折舊對於損益
的影響，此項分析除了已發生的歷史，更需要的是未來資訊，也就是折舊到期日。

比較少在ERP系統報表看到這個欄位，事務所不需要，但其實它是公司財務分析模擬未來
狀況的關鍵資訊。以下介紹如何在現有固定資產清冊的基礎上，運用Excel函數公式計算
出折舊到期日：

一、簡單扼要的「固定資產清冊」，如同文章前言所述，欄位有「資產編號」、「名稱」
、「取得日期」等，目的是利用這些資料算出折舊到期的月份。

二、首先是計算出每月折舊的公式：「=ROUND(G5/D5/12,0)」，亦即「取得成本」除以「
耐用年限」、再除「12」(月份)。通常系統報表會有當期折舊，建議還是另外架個公式，
剛好也是驗算。

三、計算出折舊的「最後一年」：「=VALUE(RIGHT(YEAR(C5)+D5,2))」，這裡利用「Year
」函數將「取得日期」轉換成年度，再加「耐用年限」，最後再以「Value」函數強制把
計算結果設定為數值型態， 方便再進一步處理。

四、考慮到月份有一位數和兩位數的差異，為符合一般年月為整齊四位數的表達方式，設
計了「IF」函數作為邏輯判斷：「
=IF(VALUE(MONTH(C5))<10,CONCATENATE("0",MONTH(C5)),MONTH(C5))」，計算結果即為
「最後折舊月份」，這裡是假設取得固資後的次月開始提列折舊。

五、簡單將年月合併：「=I5&J5」，得到一目瞭然的「折舊到期」所屬年月。

六、由於最後一年的折舊通常不會剛好是12個月，必須精心設計計算公式：「=IF(＄
I5<18,0,IF(＄I5<19,＄H5＊＄J5,＄Ｈ5*12))」，如果是17年以前到期，於18年當然折舊
費用為零，再來如果是小於19年，加上前面已經篩選掉17年以前，判斷結果便是當年18年
到期，折舊計算為「每月折舊」乘以「最後折舊月份」，前兩個條件皆不成立的話，表示
在當年折舊不會到期，所以是折12個月。

七、上一步驟將欄位的部份前面都加了個「＄」，作用為固定住公式中的欄，到了這個要
進一步計算「2019」年及「2020」年，公式拖曳複製過來之後，只要修改其中關於年份的
部份即可。如此，完美計算出未來三年折舊費用預測。公式設的延伸

文章結語兩點補充：

第一，ERP系統雖然很多報表可以使用，但畢竟是制式化軟體，不一定符合每個企業狀況
和需求，很多時候原始報表必須運用Excel進行後加工處理。公式設計時應一併考量延伸
性，如同此篇文章範例，想再增加未來預測年度、過後新的結帳期間要再更新資料，都是
很容易的。

第二，後加工的過程愉悅或者痛苦，決定於日積月累的Excel功力。這篇文章有些函數之
前我用過了，有些函數之前從沒用過，只是知道現在處理跟日期還有文字有關的問題，而
我大致瞭解這兩個類型有些什麼函數可以使用，所以解題的過程沒遇到太大困難。在這裡
建議各位讀者，既然工作上會一直用到Excel，那麼就在每一次遇到難題的時候，把它當
作是挑戰和磨練，假以時日，每個人都可以達到贊贊小屋見招拆招、無招剛好練功的境界
。

延伸閱讀：

Excel整理術 - 入門密技+進階實作 台北場淡江大學台北校區

https://goo.gl/y6QKVG

高效率 Excel VBA 工作術 台北場淡江大學台北校區

https://goo.gl/kYqdUc

--


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

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


--