2014年9月27日 星期六

Excel-將日期分隔為年月日(INDEX,MID,TEXT)

在 Excel 中能接受的日期格式,例如:「西元年/月/日」或是「西元年-月-月」,輸入後都會轉換為:西元年/月/日。常見國人會輸入日期格式:西元年‧月‧日,無法被 Excel 接受。如果要將日期分開年、月、日來顯示該如何處理呢?

參考下圖(上)的「日期格式一」為例,你可能最常使用 MID 函數來處理:

儲存格B2:=MID(A2,1,4),儲存格C2:=MID(A2,6,2),儲存格D2:=MID(A2,9,2)

在網路上看到有人用以下的公式,覺得可以用在陣列觀念的練習:

儲存格B2:=INDEX(MID($A2,{1,6,9},{4,2,2}),COLUMN(A2))

藉助查表公式 INDEX 函數,將上述的三個 MID 函數,併入一個公式中。其中 COLUMN(A2)=1,當往右欄位複製/貼上時,會產生COLUMN(B2)=2、COLUMN(C2)=3。

複製儲存格B2,貼至儲存格B2:D2。複製儲存格B2:D2,貼至儲存格B2:D8。

參考上圖(下),如果以「日期格式二」為例,每個日期的長度並不一致,如果要使用 MID 函數,也會很辛苦,因為每個公式都要修改。

所以使用 INDEX 函數是一個好的選擇,但日期參數要先稍加變化:

儲存格B11:==INDEX(MID(TEXT($A11,"yyyy/mm/dd"),{1,6,9},{4,2,2}),COLUMN(A11))

TEXT($A11,"yyyy/mm/dd"):使用 TEXT 函數配合參數:yyyy/mm/dd,將日期格式轉換為年四碼、月二碼、日二碼的格式。

複製儲存格B11,貼至儲存格B11:D11。複製儲存格B11:D11,貼至儲存格B11:D18。

沒有留言:

張貼留言

好康東東