經手大量資料的人有時會遇到的問題:從其他地方下載 / 匯入 / 複製到 Excel 的一批資料,格式和需要的不一樣,得自己整理。可能是一坨資料都擠在同一格裡面,需要把同一個欄位裡的不同資訊分拆到不同儲存格,或是反過來,需要把一批資料的多個欄位值,全都一起合併到同一個欄位。
這時候,不用一一筆資料複製貼上、合併儲存格。很多人知道可以利用現成的功能:資料剖析(一拆多)和公式(多併一),來快速處理多筆資料,但有時沒辦法快速判斷「怎麼用」,特別是遇到換行等特殊狀況時,會需要用點小技巧。
這裡特別整理出各種情境的對應解法,可以參考下方表格,點擊對應的超連結。
(註:如果你是 Ragic 的用戶,要將 Excel 匯入 Ragic 前有時也需要這樣的資料整理技巧。如果是已經存放在 Ragic 的資料,想知道怎麼在 Ragic 做到這些事的話,可以查看這個部分的說明。)
需求 | 圖示 | 方法 | 相關說明 |
快速拆欄位(一拆多) | ![]() | 資料剖析 | 1.固定寬度怎麼拆 2.空格或特定符號區隔怎麼拆 3.不太規則的情況怎麼拆 4.拆成多列 5.儲存格內多行(換行)怎麼拆 |
快速合併欄位(多併一) | ![]() | 主要是各種公式 | 1.單純多併一 2.擷取部分內容多併一(去字尾) 3.多併一且在儲存格內換行 |
例如,原本一批資料的某個欄位值是「姓名」像「王大明」,現在需要把它分拆成一欄「姓」一欄「名」變成「王」和「大明」:
又例如拿到一組全都擠在一欄的「地址」資訊,希望依照郵遞區號、縣市鄉鎮等分成一欄一欄的:
這在 Excel 最簡便的方法就是利用資料頁籤下的資料剖析來處理。
只要你的一批資料可以整理出一定的「分欄規則」,就可以用資料剖析把一個儲存格一刀切成好幾塊。
最簡單可以直接適用的規則是:① 要切成固定寬度(不同資料每一段要切的都一樣長,字元數一樣),或者 ② 欄位值有以空格或特定符號來分隔。所以先來說這部分。
如果你想要「切」的資料,切分點右方每一段的字數都是相同的(例如把地址切成郵遞區號、縣市鄉鎮、其餘地址,其中郵遞區號固定是 3 碼、縣市鄉鎮固定是三個國字),那它就是固定寬度/固定字元的情況,可以直接使用「資料剖析」來切資料。
方法為:框選要拆分的欄位後,到資料 > 資料剖析 > 選「固定寬度」 > 依系統指示在畫面上切出分割線,完成步驟即可。如果希望指定把切好的欄位放在其他地方,就點「目的地」來另外框選位置。
「106 台北市 大安區 南昌路 二段 222號 」、「106,台北市,大安區,南昌路,二段,222號 」「106台北市大安區南昌路二段_222號 」「106/台北市/大安區/南昌路/二段/222號 」......這些都可以切成「106」「台北市」「大安區」「南昌路」「二段」「222號 」沒問題。
不過不限於這些符號,其實只要是一個特定的字元都可以,例如「106✦台北市✦大安區✦南昌路✦二段 ✦222號」只要有指定用✦來區隔,或是「106隔台北市隔大安區隔南昌路隔二段隔222號」指定用「隔」來區分,就都可以得出「106」「台北市」「大安區」「南昌路」「二段」「222號 」。
方法為:框選要拆分的欄位後,到資料 > 資料剖析 > 選「分隔符號」 > 選擇你的分隔符號並完成步驟即可。(可參考上圖。如果希望指定把切好的欄位放在其他地方,就點「目的地」來另外框選位置。)
選分隔符號時,如果是上面提到的特殊分隔符號,就勾「其他」並填入該字元即可:
如果同時有兩種分隔符號(例如空格與逗號),只要兩個都一起勾選就可以了喔!
這裡還有兩個「特例」可以講一下,以下例子雖然分隔符號不是那麼「規則」或直觀,但還是可以用的:
首先,假如我手上有一批地址資料要切得很細,每段寬度不一(例如前面例子的郵遞區號有的三碼有的五碼;或更常見的路名長度不一、「區」有的一個字有的兩個字),不能用固定寬度來切,但也沒有空格、逗號等區隔符號,該怎麼辦?
用「尋找與取代」製造區隔字元
此時仔細研究一下,會發現我想要分段的東西還是有規則可循的,例如郵遞區號跟地址之間一定一邊是數字一邊是文字、其他的區隔多半可以從「縣」「市」「鄉」「鎮」「區」關鍵字看出。
利用這些規則,搭配 Excel 的「尋找與取代」功能,把我們想要切割的地方製造出「,」之類的區隔字元,之後就可以快速利用前面的資料剖析功能了。例如下圖:你可以用「縣,」取代「縣」,「市,」取代「市」,「鄉,」取代「鄉」,「鎮,」取代「鎮」... 如果郵遞區號跟地址中間沒空格,一樣可以用「0,」取代「0」,以此類推。
連續分隔符號視為單一處理
另外一個比較單純的情形:例如假設我的資料每一段都是用雙引號" "括起來,要做資料剖析時,會發現兩段之間因為有兩個分隔符號,會被額外切出一個空欄。此時只要在設定畫面勾選「連續分隔符號視為單一處理」即可:
例如你手上有的是一串名字,它們有用空格區分,但都擠在同一個欄位,你最終希望一個名字放在一個儲存格,且每一個名字是垂直往下一列一列排的像這樣:
那首先還是可以先用資料剖析把它拆成多欄位。接著,利用 Excel 的轉置功能從欄轉置(旋轉)到列就可以了,方法為:先選取這些儲存格之後右鍵複製,接著到要貼上的地方按「選擇性貼上」並選「轉置」。
其實在 Excel,「換行」這個動作本身是可以被辨識或轉換成某個區隔符號的,把它辨識出來、或轉換出來,就可以當成一個「分隔符號」來做資料剖析了。(解決這點,接下來有需要的話可以用跟上面一樣的方法,用「轉置」來將欄換成列)
在資料剖析精靈裡直接填入快速鍵「Ctrl+j」
很常見的方法是利用代表「換行符號」的快速鍵 Ctrl+j ,在資料剖析精靈裡直接打 Ctrl+j:
很多情況下畫面上雖然仍然是空白(肉眼看不到你打的 Ctrl+j ),但你可以看到已經把不同行的資料區隔開了。
利用「尋找與取代」將「Ctrl+j」取代成其他分隔符號例如「,」
一樣就是在「尋找與取代」時,尋找「Ctrl+j」,用「,」取代。取代完之後就可以用這個肉眼可見的分隔符號來做操作了,這是很多人用的方法。
其他方式:利用 CHAR(10) 公式
但如果你跟小編一樣,不確定是因為 Excel 版本還是 Mac 電腦的關係,用了「Ctrl+j」或是其他任何江湖傳言的換行符號,都沒有任何改變,可以來試試另一個方法: 用 CHAR(10) 公式來找。
CHAR 這個公式的意思是「取得電腦字元編碼代表的值」,而 10 是在 Mac 跟 Windows 電腦裡都可以代表「換行」的編碼,所以 CHAR(10) 就是換行符號的意思。
可以利用一個效果跟「尋找與取代」類似的公式: SUBSTITUTE 公式,來做到把換行符號代換成其他肉眼可見符號的方法。
SUBSTITUTE 在 Excel 的用法是這樣:
=SUBSTITUTE(參照欄位值,參照欄位中要被替代的值,要把被替代的值換成什麼值),例如「一百三十元」要換成「一百三十圓」,那就是SUBSTITUTE(參照欄位值,元,圓)。
因此如果要以「,」取代換行符號,公式就是這樣寫:
用公式取得有區隔符號的值後再做一次資料剖析就可以了。
電腦、自動化處理批次資料這種事情就是需要有個規則,所以真的理不出一個規則的話就真的就沒辦法了,可能還是得從源頭資料著手,讓資料從源頭就用比較好處理的方式呈現、匯出。
有時候你可能有跟「分拆欄位」相反的需求:要把多個欄位合併成一個,例如手上有「姓」和「名」要合成「姓名」,手上有郵遞區號、縣市鄉鎮等,要組合成一個完整的地址欄位,或是要把姓名、地址等欄位集結成單一個出貨備註欄位。
這些在 Excel 大致上可以用字串公式搭配一些變化版達成,如下。
如果我手上拿到的資料是這樣:「106」「台北市」「大安區」「南昌路二段222號 」,想要整合成單一儲存格的完整地址「106 台北市大安區南昌路 二段222號 」,這是最單純的需求,可以用 Excel 的字串公式 & 或是合併多個字串的公式 CONCAT 或 CONCATENATE 來做。
舉例來說,「106」「"台北市"」「"大安區"」「"南昌路二段222號" 」,想要整合成沒有雙引號區隔的單一儲存格完整地址「106 台北市大安區南昌路二段222號 」,那麼可以利用「擷取部分字串」的公式 MID 擷取去掉頭尾的字串,再利用前面字串相加的公式串起來即可。
*要擷取的字串有固定字元長度的話,MID 公式裡填入所需的字元長度即可;沒有固定字元長度的話可以找出規律,例如下圖「其餘地址」這個欄位要取的字元數會隨著地址長短而有所不同,但確定的是都要取"之後到「號」這個字之間的字元數,因此搭配 SEARCH 公式 「SEARCH("號",D2)-1」取得所要的數字填入。
跟「單純多併一」的情況相比,只需要在字串公式中插入換行符號。
前面有講到,在 Excel 公式裡要系統性的使用換行符號可以用 CHAR(10) 來做,因此只要在 =(儲存格1&儲存格2&儲存格3)這樣的公式中,插入 CHAR(10) 像這樣: =(儲存格1&CHAR(10)&儲存格2&CHAR(10)&儲存格3),另外記得在該儲存格上的「常用」頁籤勾選「自動換行」圖示,就可以了。
如果你是 Ragic 用戶,偶爾會拿到一些不太符合原本 Ragic 表單格式的資料,你可以考慮利用上面講的 Excel 功能拆分或合併儲存格,清整資料之後再匯入 Ragic,就不用在 Ragic 為了整頓資料建立多餘欄位,可以省很多力。
不過如果你是固定會需要這樣拆欄位/合併欄位之後匯入 Ragic ,評估有需要的話,以上需求也可以在 Ragic 用公式來做。 Ragic 的公式好處是:邏輯類似 Excel 、好上手,但一批資料只要套用一次公式在欄位標頭即可,省力且不易在編輯資料/複製欄位值時被意外錯改。
Ragic 目前沒有 Excel 的「資料剖析」功能,因此一次性的欄位拆分其實還是比較建議在 Excel 整理。不過如果需要系統性在 Ragic 做這種事的話,Ragic 也支援各種字串擷取的公式: MID, LEFT, RIGHT 等取出部分字串的公式,以及「找出某個字元在字串中位置」的公式:FIND(無論該字元是中文或英文,在 Ragic 都可以使用 FIND,不需要特別使用 SEARCH,例如FIND('吳',A1) 就是找出 A1 欄位中「吳」是第幾個字)。
因此,再以前面的地址為例,假如手上有的是完整地址,要拆分成不同欄位的話可以像這樣:
Ragic 也支援「字串相加」的公式,但邏輯比 Excel 更簡單一些,不用 & 而是用加號「+」。只要是非數字欄位使用「+」,例如「A1+A2」,就等同於 Excel 「=A1&A2」的意思。郵遞區號、縣市等組成完整地址欄位的例子,公式就會像這樣:
需要擷取部分字串相加時,由於如同上面所說,Ragic 也支援各種擷取字串的公式,直接搭配擷取字串與 + 一起使用即可。
至於「搭配換行」的部分,Ragic 採用的是換行符號 "\r\n",可以直接參考這篇文章的說明來輕鬆達成訂單內備註換行等功能。
標籤: Excel