[Excel函數] 文字:Len、Left、Right、Mid (正規化資料剖析)


資料庫的建置與使用在任何行業都非常的重要,而資料庫中最重要的一點就是編碼,好的編碼讓人可以分析其編碼原則,而不會只是連續一串無意義的數字。

工作中我們常會自公司系統中轉出資料加以分析,但是對於一大串文字或數字還需要去切割、擷取才能拿到我們需要分析的資料,因為是自資料庫獲得的資料,通常是具有正規化的,便可以使用Excel的資料剖析去處理,但若希望維持原始資料的完整性,便可以新增欄位,採用簡單的函數:LEN、LEFT、RIGHT、MID去指定字元數,來擷取特定字元。

LEN


=LEN(要知道字數的儲存格)

非常簡單只有1個引數的函數,這個函數會直接顯示指定儲存格內的字元個數,包含空白及符號,所以有時候我們可以用這個函數來檢查資料裡面是不是有不小心字元長度不對的問題。


LEFT、RIGHT


=LEFT(要從左邊擷取部分文字的儲存格, n個字)
=RIGHT(要從右邊擷取部分文字的儲存格, n個字)

簡單明瞭,LEFT就是從左邊開始擷取文字,RIGHT則是從右邊。引數2若省略,則只會回傳1個字。


MID


=MID(要擷取文字的儲存格, 從第x個字開始, 總共n個字)

前面說明了從左邊跟從右邊擷取文字,當然還有直接從中間找的方法,這個函數比較需要注意的是第3個引數,要輸入「總共幾個字」而不是「到第幾個字」。


範例


這裡先以「衛生福利部中央健康保險署的特材收載品項」為例,「全民健保特殊材料品項分類代碼編訂原則」中有說明特材代碼的編碼原則:「特材代碼共計12碼,包括第1碼為大類碼,第2碼為小類碼,第3至第5碼為品名碼,第6至第10碼為規格碼,第11至第12碼為廠牌代碼。」因此我們下載資料後,依照說明便可以用剛剛教的函數拆分出各大、小類及廠牌等資訊。


總碼數為特材代碼長度 =LEN([@特材代碼])
大類碼在第1碼 =LEFT([@特材代碼],1)
小類碼在第2碼  =MID([@特材代碼],2,1)
品名碼在第3至5碼 =MID([@特材代碼],3,5-3+1)
規格碼在第6至10碼 =MID([@特材代碼],6,10-6+1)
廠牌代碼在最後2碼 =RIGHT([@特材代碼],2)
擷取出來的資料,便可以用VLOOKUP([Excel函數] 檢視與參照:VLookUp、HLookUp)和健保署所提供的編碼對照表連結,知道各代碼的意思囉!


如果要找的字串位置不固定,亦或字串長度會變化,可以使用FIND及SEARCH來結合上述的函數來判斷要擷取的起始字元位置及長度。

具有正規化的資料可以用資料剖析處理


上述的範例是有規則可循的資料,因此除了可以用公式擷取資訊外,也可以用資料剖析來分割文字,得到的結果是相同的。

頁籤「資料」→資料剖析,依資料類型選擇「分隔符號」或「固定寬度」作為裁切的準則,此例中沒有分隔符號,便直接採用固定寬度。


依照需要裁切的碼數,直接點擊便會出現裁切線;若不小心點錯,滑鼠按2下便會清除裁切線。


最後記得將資料類型選擇為適合的格式,此例中若規格碼沒有選擇為文字,則剖析後的資料將顯示為數字型態,前面的0會消失。


剖析完後的資料,與用公式擷取是一樣的,可以依照需求及掌握度選擇適合的方式。



如果你認同我的努力,歡迎贊助本網站,讓我能有更多資源做得更好

歐付寶O'Pay贊助網址:https://payment.opay.tw/Broadcaster/Donate/CE587F7C6D2634F0A490D23375652BDD
支援付款方式:歐付寶帳戶、網路ATM、ATM櫃員機、信用卡及超商代碼

 


沒有留言:

張貼留言