雲端工作術
各類應用示範
案例故事
逃離惡夢
關於 Ragic
經手大量資料的人有時會遇到的問題：從其他地方下載 / 匯入 / 複製到 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
