贊贊小屋

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援

Excel常常看不懂ERP系統報表的日期,這是文字數值和日期時間兩者的格式溝通不良。本文以銀行日記帳為例,應用TEXT、CONCATENATE、VALUE函數幫忙翻譯原始資料的日期。

會計工作是跟日期分不開的,一般公認會計原則中有四大基本環境假設,其中一項便是會計期間假設,通常以一年作為一會計期間,實務上為了時效性是每個月結算,因此會計部門每個月都要結帳出具財務報表,然後會有隨之而來的當月管理報表。基於這個特性,所有會計原始資料勢必要有個日期欄位,方便進一步執行種種分析統計。

一、銀行存款日記帳

簡化的ERP銀行出納系統報表︳有「日期」、「銀行別」、「銀行代號」、「摘要」、「金額」等欄位。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第1張

二、CONCATENATE函數

原始報表的「日期」中的西元年份僅有兩位數,為了完整起見,先以文字函數將其補齊到四位:「=CONCATENATE(“20”,A2)」。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第2張

三、TEXT函數

設計公式:「=TEXT(F2,”0000-00-00″)」,TEXT為一個強大的文字函數,它可以將數值轉換成文字、可以用函數方式設定數值的顯示格式,還可以搭配CONCATENAT函數突破儲存格格式的限制,於單一儲存格中同時引用兩種以上不同的儲存格格式。

在這個步驟的TEXT函數公式較簡單,以G2儲存格為例,把「20180101」轉換成「2018-01-01」,以便進一步處理。另外萬位以上由於西元都是以1或2開頭,不會以0開頭,所以「”0000-00-00″」和「”0-00-00″」會是相同效果。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第3張

四、VALUE函數

接下來的步驟有點巧妙:「=VALUE(G2)」,作用是將TEXT函數所計算出來的文字轉換成數值,除了VALUE函數之外,如同下面標黃色的H3到H6公式所示,Excel還可以簡單加減乘除的方式,將文字轉換成數值。

Excel為了方便計算起見,將日期以數字形式儲存,如同圖片下面標紅色所示,「19000101」轉換為「1900-01-01」,以數值呈現便是「1」,表示Excel日期中的1900年1月1日,即為數字1,往後只要再加一天,數字也是加1。正確掌握的Excel日期序列值,才能有效應用,這一節範例也是以此觀念為基礎展開。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第4張

五、日期格式設定

上個步驟已經得到日期序列值,其實已經是Excel認定的日期內容了,所以這個直接把儲存格內容帶過來:「=H2」,原本H2為「43101」,調整為日期格式,I2即為「2018/1/1」。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第5張

六、日期篩選

以I欄「日期格式」篩選,可以看到所有資料皆以日期順序分組編排,選擇「日期篩選」還有更多關於日期的篩選方式。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第6張

七、文字篩選

如果是以「TEXT函數」這一欄篩選,和上個步驟兩相比較,只能逐筆選擇是否勾選或者各種「文字篩選」,如此應該就可以理解日期格式於Excel資料分析的差別之處。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第7張

八、Excel日期格式

最後作個補充,有些情況需要設計函數公式將文字數字轉換成日期,但更多情況是,Excel可以自動識別使用者正在輸入日期。如圖所示舉了七個例子,「輸入」列是鍵盤所輸入的文字數值,「顯示」列是Excel儲存格所顯示的內容。注意到Excel可能在不改變對應日期的前提下,會自動調整日期格式,沒標黃色部份是輸入與顯示相同,標黃色部份Excel自動依照輸入日期調整成適合的顯示內容。

例如輸入「19年1月1日」,顯示「2019年1月1日」,輸入「1/1」,顯示「1月1日」,仔細看G3儲存格的資料編輯列內容為「2019/1/1」,表示此Excel已識別此儲存格為日期內容,當以圖片所示的表格資料篩選時,每一欄都會像第六步驟一樣,可以進行「日期篩選」。

【Excel TEXT函數】銀行日記帳的日期格式不對?你需要TEXT、CONCATENATE、VALUE神救援 日期函數 第8張

Excel日期資料的處理

如同這節範例所示,Excel主要是處理數值的應用軟體,所以在日期資料上作了特殊的處理,不過往往原始資料並沒有被Excel識別為日期,在需要使用到日期相關的處理、呈現、或者計算的情況下,勢必要做文字、數值、日期之間的轉換。這一節範例是以簡便使用連續數字表示日期,各位讀者在自己的工作個案中有可能遇到其他相類似情形,可參考本節範例加以解決。

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

博客來網路書店網址:

https://www.books.com.tw/products/0010817518

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1