由於 Joomla 的資料是存在 MySQL 中，本來想說直接在 MySQL 中以 SQL 處理字串格式就好，但是要一口氣把「93.3.26~94.3.25」的資料拆成「2004-03-26」和「2005-03-25」是頗麻煩的，而且我對 MySQL 又不熟，所以最後還是改用 Excel 來完成啦！
用 Excel 的好處是可以先編輯資料，等到所有資料都確認無誤以後再匯入到 MySQL 中。以這次的需求而言，需要先新增「起始年」、「起始月」、「起始日」、「截止年」、「截止月」、「截止日」等，如下：
接下來只要把這些欄位的內容處理好 (例如補零、年份要 +1911、確認格式是否正確等)，之後，再組合成最後所需的 YYYY-MM-DD 格式，然後把新增的 temp 欄位刪除就好啦！
但是一打開 Excel，想要自己動手用內建 function 硬幹的壞毛病又發作了，最後發現雖然可以做的出來，但由於缺少了「split」這個重要的 function，實作起來相當麻煩，公式也會搞的很複雜，以開始年為例，我的公式如下：
( =IF(OR(ISBLANK(A2), EXACT(A2, "N/A")), "", SUBSTITUTE(MID(A2, 1, 3), ".", "")+1911) )
首先要判斷 A2 欄位的值若為空或者 N/A 就不處理，確定有資料以後再用 MID 取出所需的年、月、日等字串。為了要正確取出民國 100 年，還必需要先多取一位 (可能會多取到一個「.」，例如「98.」)，而多取了一位以後又要用 SUBSTITUTE 這個名字落落長的 function 來把多取的「.」給刪除掉，非常的麻煩！
雖然很麻煩，但是最後我還是把各欄位的公式都實作出來了，如下：
- Start Year: =IF(OR(ISBLANK(A2), EXACT(A2, "N/A")), "", SUBSTITUTE(MID(A2, 1, 3), ".", "")+1911)
- Start Month (TMP): =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",SUBSTITUTE(MID(A2,4,2),".",""))
- Start Day (TMP): =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",SUBSTITUTE(MID(A2, FIND(".", A2, 4)+1, 2), "~", ""))
- Start Month: =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",REPT(0, 2-LEN(N2))&N2)
- Start Day: =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",REPT(0, 2-LEN(O2))&O2)
- End Year: =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"", SUBSTITUTE(MID(A2, FIND("~", A2)+1, 3), ".", "")+1911)
- End Month (TMP): =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",SUBSTITUTE(MID(A2, FIND("~", A2)+4, 2), ".", ""))
- End Day (TMP): =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",SUBSTITUTE(RIGHT(A2, 2), ".", ""))
- End Month: =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",REPT(0, 2-LEN(S2))&S2)
- End Day: =IF(OR(ISBLANK(A2),EXACT(A2,"N/A")),"",REPT(0, 2-LEN(T2))&T2)
這次學到的技巧主要是「如何補零」，例如「3 -> 03」，作法請參考 Padding out a cell with leading zeros (leading zero 是關鍵字，中文或許可翻為「前導零」？)。
把公式實作出來，並且做完資料格式調整後，總是覺得不太對，Excel 沒有內建 split function 未免也太麻煩了，於是又找了一些資料 (例如這篇：How to Split a Column of Data in MS Excel )，這才發現 Excel 是把「分割字串」這個強大的功能放在「資料」-->「資料剖析」裡面阿！(其實以前好像用過的，但因為少用就忘記它的存在了 Orz)
試用了以後很快的發現，利用這功能只要不到 10 分鐘就可以把上面那一大堆公式要做的事情做完了，而且過程相當的直覺，可以節省非常多的時間阿！
實作的步驟如下：
步驟一：首先選取要分析的資料(A欄)，再點選「資料」-->「資料剖析」
步驟二：在「原始資料類型」中採用預設值：分隔符號(D)，之後按下一步
步驟三：此時先進行第一次分割，把「分隔符號」設定為「~」，以區隔開始日期和截止日期。此時可在下面「預覽分欄結果」視窗中先檢視是否將字串如預期的拆開，在第三行可看出的確有正確將「99.1.2」和「100.1.1」拆開 (免去了用公式要多取一位，之後再用 SUBSTITUTE 把多取的一位刪除的大麻煩)
步驟四：接下來可設定「欄位的資料格式」(此時用預設值「一般」即可)，最重要的是「目標儲存格」，在此例中，以「~」作為分隔符號會將 1 欄拆成 2 欄，因此要選一個可容納兩欄的空間，假如 Excel 中的原始資料長這樣：
那麼就應該要選擇「K欄」($K:$K) 作為目標儲存格：
步驟五：確定目標儲存格已設定為 K 欄後，按一下「完成」按鈕即可
此時資料會正確被分為兩欄：
步驟六：接下來只要按照上述步驟，分別把 K 欄和 L 欄以「.」作為分隔符號再拆出 3 欄 (起始/截止年、月、日)，再用前面提過的小技巧來補零，最後把年、月、日組合成 YYYY-MM-DD 的格式就可以了！這個作法比起直接用公式硬幹真的是太直覺，而且方便太多啦！
希望這篇簡短的紀錄對有須要的人會有幫助，也希望以後自己不要再忘記 Excel 這個簡單好用的工具啦！
2 則留言:
雖然我來做會用 VBA 去處理
不過用 function 還是比較適合一般大眾 :D
VBA 當然是無敵的，但是用資料剖析比較輕鬆阿哈哈哈 XD
張貼留言