點擊上方

藍色

文字  關(guān)注我們吧！

送人玫瑰，手有余香，請將文章分享給更多朋友

動手操作是熟練掌握EXCEL的最快捷途徑！

小伙伴們，在日常工作中我們經(jīng)常會遇到類似下面的數(shù)據(jù)吧？同一個省份下面的市都擠在同一個單元格中了。現(xiàn)在要把這樣的數(shù)據(jù)轉(zhuǎn)換為數(shù)據(jù)列表，我們該怎樣操作呢?

方法一：POWER QUERY

單擊【數(shù)據(jù)】-【從表格】，對“市”這一列進行拆分。

單擊“市”這一列，然后依次點擊【轉(zhuǎn)換】-【拆分列】-【按分隔符】。單擊“高級選項”，拆分為“行”。

點擊確定后完成。

在有一些低版本的POWER QUERY中如果沒有“高級選項”時，可以先按分隔符拆分列，然后在逆透視列，同樣可以達到相同的目的。

方法二：公式法

在單元格D2中輸入“=INDEX(A:A,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN($A:$Z),100,ROW($1:$4)),ROW(A1))+1)&""”并三鍵回車，向下拖曳即可。

思路：

• (LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1部分用來計算單元格中文本數(shù)據(jù)的數(shù)量，采用的是數(shù)組的形式

• 利用IF函數(shù)創(chuàng)造一個序號的內(nèi)存數(shù)組
  

• 利用SMALL函數(shù)依次返回最小的序號

• 利用INDEX返回省份的名稱
  

在單元格E2中輸入“=TRIM(MID(SUBSTITUTE(VLOOKUP(D2,$A$1:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:D2,D2)*99-98,99))”并三鍵回車，向下拖曳即可。

思路：

• 這其實是一個經(jīng)典的SUBSTITUTE函數(shù)的應用，具體的解釋可以參看帖子總結(jié)篇--SUBSTITU函數(shù)實用終極帖

如果小伙們有任何疑問可以私信我哦！

推薦閱讀：

IF函數(shù)七兄弟，個個本領(lǐng)大！

一對多查詢經(jīng)典函數(shù)組合拓展應用--多對多查詢

來，平均一下！

總結(jié)篇--反向查找函數(shù)使用終極帖

遇到不規(guī)范的數(shù)據(jù)錄入，你該怎么辦？