今天主要是來給大家分享一篇關(guān)于數(shù)據(jù)處理的技巧。

(相關(guān)資料圖)

即將單元格中的內(nèi)容按照分隔符拆分為多行顯示。

效果如下：

今天主要來教大家3種方法：

PQ拆分法

空格替換提取數(shù)據(jù)法

OFFICE365函數(shù)法

方法一：利用PQ拆分單元格內(nèi)容到多行

首先，將鼠標放在任意有數(shù)據(jù)的單元格中，點擊【數(shù)據(jù)】-【來自表格/區(qū)域】，勾選【表格包含標題】，然后確定。

選中【人員】這一列，然后點擊【拆分列】-【按分隔符】來拆分，然后輸入分隔符【、】，展開下方的【高級選項】，選擇拆分到行，然后確定。

下面，這份名單就已經(jīng)拆分出來了，我們再點擊【關(guān)閉并上載至】，選擇現(xiàn)有工作表，選擇A8單元格即可。

效果如下：

溫馨提示:PQ僅在office2016及以上版本適用，WPA版本也暫不支持。

方法二：用空格替換提取數(shù)據(jù)法來拆分單元格內(nèi)容到多行

如果你的excel版本較低，無法使用PQ的話，建議大家用空格替換提取數(shù)據(jù)法來解決這個問題。該法是一個經(jīng)典套路，用多個（如99個）空格來替換分隔符，然后完成字符數(shù)不等的數(shù)據(jù)提取。

步驟1：計算各部門人數(shù)

首先，在C2單元格輸入公式=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1，然后下拉公式，獲取各個部門的人數(shù)。

步驟2：獲取部門名稱

下面，再在D2單元格中輸入公式

=OFFSET($A$1,INT((ROW(A1)-1)/MAX($C$2:$C$6))+1,0)

然后下拉填充。

此公式的含義是獲取部門名稱，重復(fù)的次數(shù)為C列的最大值。

步驟3：將人員名單拆分到多行

我們再在E2單元格輸入公式獲取人員名單，輸完公式后下拉填充。

=TRIM(MID(SUBSTITUTE(OFFSET($B$1,INT((ROW(A1)-1)/MAX($C$2:$C$5))+1,0),"、",REPT("",99)),MOD(ROW(A1)-1,MAX($C$2:$C$6))*99+1,99))

步驟4：刪除多余的單元格

最后，將多余的單元格刪掉即可得到我們想要的效果。

溫馨提示：上述公式還可以優(yōu)化，只用三步完成操作。

步驟1相同。步驟2在D2中輸入公式并下拉填充：

=TRIM(MID(SUBSTITUTE(CONCAT(REPT($A$2:$A$6&"、",$C$2:$C$6)),"、",REPT("",99)),99*(ROW(A1)-1)+1,99))，

步驟3在E2中輸入公式并下拉填充：

=TRIM(MID(SUBSTITUTE(CONCAT($B$2:$B$6&"、"),"、",REPT("",99)),99*(ROW(A1)-1)+1,99))

方法2適用于低版本的excel表格，但很明顯，這些公式都太長，對于新手來說著實不太友好，不利于掌握，下面再來給大家介紹一種相對討巧的函數(shù)公式。

方法三：利用新函數(shù)TEXTSPLIT和TEXTJOIN來拆分單元格內(nèi)容到多行

步驟1：計算各部門的人數(shù)

如下圖所示，在C2單元格輸入公式=COUNTA(TEXTSPLIT(B2,,"、"))，然后下拉填充。

公式含義：先用TEXTSPLIT函數(shù)將B2單元格按照分隔符“、”拆分到列，然后用COUNTA函數(shù)來計數(shù)，即完成各部門的人數(shù)統(tǒng)計。

步驟2：提取部門名稱

在部門這一列，我們輸入公式=TEXTSPLIT(CONCAT(REPT(A2:A6&"、",C2:C6)),,"、",1)

公式含義：

首先，我們使用REPT函數(shù)，即根據(jù)指定次數(shù)重復(fù)文本，函數(shù)結(jié)構(gòu)=(文本,次數(shù))。

將各個部門按照各自的人數(shù)來重復(fù)，如行政部有3個人，即重復(fù)3次，中間用、連接。

再用CONCAT函數(shù)來將這一組多行多列的數(shù)據(jù)合并在一起，最后再用TEXTSPLIT函數(shù)將它們拆分到同一列中。

下面，在人員這一列，輸入公式=TEXTSPLIT(TEXTJOIN("、",,B2:B6),,"、")

公式含義：先用TEXTJOIN函數(shù)將數(shù)據(jù)用“、”合并在同一個單元格，然后再用TEXTSPLIT來拆分。

關(guān)于TEXTJOIN函數(shù)和TEXTSPLIT函數(shù)，在之前的教程中也會大家講解過，可以戳以下鏈接進行查看。

TEXTJOIN，Excel最強大的文本連接函數(shù)

千萬別用"分列"拆分數(shù)據(jù)了，試試這個新函數(shù)，效率太高了！

關(guān)鍵詞