2015年6月25日 星期四
辦公室秘技 - 如何用Excel處理日期
相信在辦公室工作的朋友都有用Excel,而日期是其中一個經常要處理的資料,其實Excel對日期的支援比大家想像中多,在此就跟大家分享一下,最後部份會教大家把看似日期的字串還原成Excel可讀的格式吧。
在Excel裡日期是一組數字,就如42069代表2015年3月3日,亦因如此兩個有效的日期是可以互相加減的。
Year(日期)可以得到該日期中的年份,如Year(42069)= 2015
Month(日期)可以得到該日期中的月份,如Month(42069)= 3
Day(日期)可以得到該日期中的月份,如Day(42069)= 3
接下來的比較實用,因為你Excel是可以自動計算出該日期是在那個星期以及是星期幾
如果想知到該日是在一年中的那個禮拜(1-52)的話可以用weeknum這個公式
Weeknum(日期, n)
如想知道任何一天是星期幾則用weekday這公式
Weekday(日期, n)
公式中的參數n是指你希望星期中日子的順序,例如如果n是1的話公式結果1就代表星期日,2代表是星期一等等,如果n是2的話,公式結果的1就代表星期一,如此類推。
上面的都是Excel裡內置的公式,操作簡單,沒甚麼了不起,接下來的才是今篇的精華所在,如何把不同格式的日期變換成Excel可讀的格式。最重要是抓住重點,那就可以靈活運用。
例子1:Excel能接受YYYY-MM-DD/DD-MMM-YYYY的日期格式但就不能識別YYYYMMDD的格式,所以20150615在Excel眼中只是一個數字。要令其可以被Excel當成日期運算的話就需要把它轉換成日期的格式,而秘密就是TEXT這個公式。
在B1儲存格中輸入上面的公式就可以把20150615變成Excel能識別的日期格式
=TEXT(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2),"YYYY-MM-DD")
*大家可以自行把A1換成其他儲存格
TEXT這個公式分開兩個部份,前半部是輸入的字串,正如之前所述,Excel能接受YYYY-MM-DD的日期格式,所以第一步就要把20150615變成2015-06-15,竅門就在於如何靈活運動字串處理公式LEFT、MID和RIGHT。
簡單介紹一下這三條公式,LEFT和RIGHT的用法很簡單,分別是從左或右讀取多少個字元,例如LEFT(A1,4),就是在A1儲存格從左面開始讀取4個字元,如上面的例子結果就會是2015。而MID就略為複雜一點,簡單一點說就是從字串中間由第幾個字元從左至右讀多少個字元,例如MID(A1,5,2),就是在A1儲存格由第五個字元(0)讀取兩個字元,因此公式的結果是06。
所以LEFT(A1,4)、MID(A1,5,2)、RIGHT(A1,2)的結果其實就分別是2015、06、和15,接下來就要用"-"把三者串起來變成2015-06-15。
而TEXT的另一個部份就是要告訴EXCEL如何讀取這段文字,"YYYY-MM-DD"正正是代表4個數字的年份,兩個數字的月和日。這樣就大功告成了。
可以用上述的Year、Month、Day公式來驗證一下是否轉換成功
A2和B2都是用Month這公式但只有B2能成功傳回月份=6,證明轉換成功了。
例子2︰如果日期的格式是有英文月份的簡寫,轉換時次序就要用DD-MMM-YYYY。
如果你看得明例子1中的LEFT、MID、RIGHT的用法的話這個轉換就應該毫無難度。
再用Month公式驗證一下,同樣轉換成功。
除了"-"之外,"/"同樣可以作為年月日的分隔符號。只要掌握到箇中方法,無論多刁鑽的日期(例如2015.06.15、JUN152015等等)格式都可以轉換成Excel能識別日期格式以作運算,希望大家能在日常工作上用得著吧。
我的Facebook專頁︰https://www.facebook.com/dennisckkblog
訂閱:
張貼留言 (Atom)
沒有留言:
張貼留言