編按：

在日常辦公中，我們難免會(huì)收到不按規(guī)范錄入的數(shù)據(jù)表，所以拆分單元格重置數(shù)據(jù)源就成了Excel人必會(huì)的辦公技能之一，其中分列法更是最受歡迎的技巧之一?？墒?，今天小E講的這個(gè)數(shù)據(jù)案例，單元格中含有多種分隔符，分列法反而成了拖累，連這個(gè)PQ技巧的25%效率都不如……

有小伙伴問了這樣一個(gè)問題：有一組數(shù)據(jù)，數(shù)據(jù)中含有多種分隔符，想要一次性把所有的數(shù)據(jù)拆分出來，并且縱向顯示，有沒有實(shí)現(xiàn)這種拆分的方法？

數(shù)據(jù)如下圖所示。A列是組別，B列是人物姓名，在B列的人物姓名之間，有各種各樣的分隔符——空格、中文逗號(hào)（“，”）、中文頓號(hào)（“、”）、斜杠（“/”）、中文分號(hào)（“；”）、連接號(hào)（“&”）、星號(hào)（“*”）和下劃線（“_”）。

今天，大家就學(xué)習(xí)一個(gè)用PQ快速拆分含有多種分隔符數(shù)據(jù)的方法。

一、把數(shù)據(jù)導(dǎo)入PQ編輯器。

點(diǎn)擊A2:B7區(qū)域中任意一個(gè)單元格，如B3，然后依次用鼠標(biāo)點(diǎn)擊“數(shù)據(jù)”－“自表格/區(qū)域”，彈出“創(chuàng)建表”對(duì)話框。在彈出的“創(chuàng)建表”對(duì)話框中，“表的數(shù)據(jù)來源”被EXCEL自動(dòng)判斷成“=$A$1:$B$7”，即本例中的數(shù)據(jù)區(qū)域，此處保持不變，再勾選“表包含標(biāo)題”（若“表包含標(biāo)題”已被勾選，則保持勾選即可）。

點(diǎn)擊“確定”，就可以進(jìn)入PQ編輯器的界面，如下圖所示。

二、自定義列，創(chuàng)建list。

依次點(diǎn)擊“添加列”－“自定義列”后，彈出“自定義列”對(duì)話框，如下圖所示。

在“自定義列”對(duì)話框中，將“新列名”保持為“自定義”不變即可?！白远x列公式”下面的函數(shù)框，是需要輸入函數(shù)的區(qū)域，大家在等于號(hào)（“=”）后面輸入“Text.SplitAny([人物]," ，、/；&*_")”，如下圖所示。

【Tips】

1.在這個(gè)公式中，Text.SplitAny函數(shù)的作用是對(duì)字段中滿足任意一個(gè)條件的數(shù)據(jù)進(jìn)行拆分。它一共有兩個(gè)參數(shù)，第一參數(shù)是字段，第二字段是拆分符號(hào)。在本例中，第一參數(shù)是“人物”字段，根據(jù)PQ中M函數(shù)的語法規(guī)則，需要用一對(duì)中括號(hào)將它括起來；第二參數(shù)是本例中實(shí)際涉及到的各種分隔符號(hào)，根據(jù)PQ中M函數(shù)的語法規(guī)則，需要用一對(duì)雙引號(hào)將它括起來；第一參數(shù)和第二參數(shù)之間，用逗號(hào)分隔。

2.特別要注意的是，本例中，B2單元格內(nèi)的數(shù)據(jù)是用空格進(jìn)行分隔的，所以大家在寫第二參數(shù)的時(shí)候，千萬別忘了輸入一個(gè)空格，不然空格就無法被拆分了！

公式輸入完成之后，點(diǎn)擊“確定”，得到的結(jié)果如下圖所示。

可以看到，PQ為我們生成了一個(gè)新的叫做“自定義”的列，其中的數(shù)據(jù)均為List。

【Tips】

List是PQ中的一種數(shù)據(jù)類型，大家可以把它理解成一組數(shù)據(jù)或者一個(gè)數(shù)組。（如果小伙伴們感興趣，可以點(diǎn)擊任意一個(gè)List，則在數(shù)據(jù)下方會(huì)出現(xiàn)一個(gè)預(yù)覽的窗格，可以查看其中的內(nèi)容，此處從略。）

接下來，大家點(diǎn)擊“自定義”旁邊的“展開”按鈕，選擇“擴(kuò)展到新行”，如下圖所示。

點(diǎn)擊“擴(kuò)展到新行”后，即得到的結(jié)果。

三、調(diào)整數(shù)據(jù)表格，并上載至Excel中。

把鼠標(biāo)放在“人物”這一列上，單擊鼠標(biāo)右鍵，選擇“刪除”，將此列刪去； 在“自定義”這個(gè)字段名稱上，雙擊鼠標(biāo)左鍵，將其修改為“人物”，得到的結(jié)果如下圖所示。

這時(shí)，數(shù)據(jù)已經(jīng)達(dá)到了最后的基本要求了。大家依次點(diǎn)擊“主頁”－“關(guān)閉并上載”－“關(guān)閉并上載”，即可將數(shù)據(jù)上載到Excel中，如下圖所示。

上載之后的數(shù)據(jù)如下圖所示。此處，Sheet2就是用PQ加工過之后，上載到Excel中的數(shù)據(jù)結(jié)果。

【擴(kuò)展應(yīng)用】

有的小伙伴可能會(huì)問，如果我不想對(duì)數(shù)據(jù)進(jìn)行縱向顯示，而只想把數(shù)據(jù)中的分隔符統(tǒng)一替換成某種分隔符，該如何操作？這個(gè)也比較簡(jiǎn)單。假設(shè)現(xiàn)在需要把所有的分隔符號(hào)替換成英文狀態(tài)下的逗號(hào)，大家來學(xué)習(xí)一下。

生成“自定義”這一列之前的所有步驟與前文所述是一樣的。

在生成“自定義”這一列數(shù)據(jù)之后，大家點(diǎn)擊其后的“展開”按鈕，選擇“提取值”功能，如下圖所示。

點(diǎn)擊“提取值”之后，在彈出的“從列表提取值”對(duì)話框中，通過下拉菜單，將“選擇串聯(lián)列表值所使用的分隔符”從“無”更改為“逗號(hào)”，如下圖所示。

點(diǎn)擊“確定”，得到的結(jié)果如下圖所示。

可以看到，在“自定義”這一列中，所有的分隔符號(hào)都變成了英文狀態(tài)下的逗號(hào)。接下來，只需對(duì)此表稍做修改：刪除“人物”這一列，將“自定義”改為“人物”，就得到如下結(jié)果。

最后，依然點(diǎn)擊“主頁”－“關(guān)閉并上載”－“關(guān)閉并上載”，將數(shù)據(jù)上載到Excel中。得到的結(jié)果如下圖所示。

怎么樣，是不是很簡(jiǎn)單？你學(xué)會(huì)了嗎？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

懶人的高效數(shù)據(jù)整理術(shù)①：復(fù)雜數(shù)據(jù)拆分

如何將缺少規(guī)律的產(chǎn)品代碼規(guī)格型號(hào)分別拆分提??？

如何精確提取單元格內(nèi)不同屬性的數(shù)據(jù)

Excel運(yùn)用規(guī)范1：一個(gè)單元格只記錄一條信息

版權(quán)申明：

本文作者阿碩；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。