|
送人玫瑰，手有余香，請(qǐng)將文章分享給更多朋友
動(dòng)手操作是熟練掌握EXCEL的最快捷途徑！ 【置頂公眾號(hào)】或者【設(shè)為星標(biāo)】及時(shí)接收更新不迷路 小伙伴們好，今天來和大家分享一道文本處理題目。這道題目有一定的難度，朋友們一起來做做看吧！ 原題是這樣子的： 要求用公式將左側(cè)的結(jié)構(gòu)轉(zhuǎn)變?yōu)橛覀?cè)的結(jié)構(gòu)。今天準(zhǔn)備用兩種方法來解決這個(gè)問題。第一種方法我們需要寫三條公式；第二種方法則是一步到位，一條公式完成。 觀察一下這道題目的源數(shù)據(jù)，我們發(fā)現(xiàn)，結(jié)構(gòu)轉(zhuǎn)換后，部門名稱需要重復(fù)顯示，重復(fù)的次數(shù)等于名單中人名的數(shù)量。而人數(shù)又比分隔符多一個(gè)。根據(jù)這些特點(diǎn)，我們開始書寫公式吧！ 拆分部門。 在單元格D2中輸入公式“=INDEX($A$2:$A$5,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN(A:D),100,ROW($A$2:$A$5)),ROW(A1))-1)”，三鍵回車并向下拖曳即可。 思路： LEN(SUBSTITUTE($B$2:$B$5,"、","")部分，用空值替代源數(shù)據(jù)中的分隔符“、”，再用LEN函數(shù)求得替換后的字符串長度 (LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)，再利用LEN函數(shù)求得替換前的字符串長度，兩項(xiàng)相減，再加上1，就得到了人數(shù) 用(LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)這部分和COLUMN(A:D)相比，得到一個(gè)4行4列的一個(gè)內(nèi)存數(shù)組。再利用IF函數(shù)根據(jù)邏輯判斷的結(jié)果，返回100或者對(duì)應(yīng)的行號(hào)。這部分的結(jié)果是：{2,2,2,100;3,100,100,100;4,4,100,100;5,5,100,100}。用一個(gè)矩陣的形式看得更直觀
提取名單。
接下來就要在D列的基礎(chǔ)上來提取名單。這時(shí)候就要用到SUBSTITUTE函數(shù)的經(jīng)典應(yīng)用了。 在單元格E2中輸入公式“=MID(TRIM(MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)),1, LEN(TRIM(MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)))-3)”，并向下拖曳即可。 思路： VLOOKUP($D2,$A$2:$B$5,2,)部分，確保在D列的部門相同時(shí)（例如“財(cái)務(wù)部”）都能夠提取到單元格B2的內(nèi)容。下面的也是一樣的 SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99))部分，用重復(fù)了99次的空格“ ”來替代源數(shù)據(jù)中的分隔符“、” MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)部分，利用MID函數(shù)來從第1、197、296..個(gè)位置上提取長度為99的字符串 COUNTIF($D$2:$D2,$D2)*99-98,99部分，是一個(gè)小亮點(diǎn)。通常使用這個(gè)公式時(shí)，COUNTIF($D$2:$D2,$D2)是會(huì)寫成類似于COLUMN(A:D)這種形式的，但今天在這里不行。因?yàn)镈列中有多個(gè)部門，且每個(gè)部門出現(xiàn)的次數(shù)也是不一樣的。而部門出現(xiàn)次數(shù)又代表這個(gè)部門有多少人，需要用SUBSTITUTE函數(shù)做多少次分割。因此這里就寫成了COUNTIF($D$2:$D2,$D2)這種形式。隨著公式向下拖曳，COUTIF函數(shù)會(huì)字符返回不同部門出現(xiàn)的次數(shù) 最后利用TRIM函數(shù)去除多余的空格。
到這里為止，名單提取僅僅完成了一半，因?yàn)楣椒祷氐氖穷愃朴?quot;張二-18"這樣的結(jié)果。我們還需要把姓名單獨(dú)提取出來。方法也很簡單，定位“-”的位置后用字符串總長度再減去3，就是名字的長度。利用MID函數(shù)提取就好了。 提取年齡 在單元格F2中輸入公式“=RIGHT(TRIM(MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)),2)”，并向下拖曳即可。 這個(gè)很簡單，只要用RIGHT函數(shù)提取就行，不再過多介紹了。 一道題寫3條公式在效率上不好。如果你不能使用高版本函數(shù)，就可以考慮用海鮮大法寫一條公式。 在單元格D2中輸入公式“=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PHONETIC($A$2:$C$5),"部","部部門、"),"-","名單、年齡"),D$1,"<c/>"),"、","</b><b>")&"</b></a>",IF(COLUMN()=4,"a/b[c][.='"&INDEX($A$2:$A$5,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN($A:$D),100,ROW($A$2:$A$5)),ROW(A1))-1)&"']","a/b[c][position()="&ROW(A1)&"]"))”，三鍵回車后向右向下拖曳即可。 思路： 多次利用SUBSTITUTE函數(shù)整理源數(shù)據(jù)結(jié)構(gòu)，使其符合FILTERXML函數(shù)的要求 重點(diǎn)講一下它的第二參數(shù)。第二參使用了IF函數(shù)，根據(jù)列標(biāo)的不同返回不同的內(nèi)容。大家注意看，INDEX($A$2:$A$5,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN($A:$D),100,ROW($A$2:$A$5)),ROW(A1))-1)這部分其實(shí)就是我們上面介紹的如何重復(fù)顯示部門的那一段公式。由于返回的結(jié)果是文本，因此需要把它放在’“&&”’的中間，整體上符合[.='"&公式&"']的要求，和[c]同樣作為篩選條件。其含義是，源數(shù)據(jù)中等于INDEX函數(shù)返回結(jié)果的那個(gè)數(shù)據(jù) 再來看看a/b[c][position()="&ROW(A1)&"]這部分，當(dāng)公式拖曳到E列和F列時(shí)，F(xiàn)ILTERXML函數(shù)就分別構(gòu)成了姓名的內(nèi)存數(shù)組和年齡的內(nèi)存數(shù)組，如下 {"張二";"李四";"唐一心";"曾賢";"歐陽小虎";"帥小火";"林小木";"阿七"} {18;29;24;36;28;24;24;34} 而position()表示數(shù)據(jù)的位置信息，position()="&ROW(A1)&"表示取第一個(gè)位置上的數(shù)據(jù)，隨著公式向下拖曳，position()="&ROW(A1)&"會(huì)依次提取后面的數(shù)據(jù)信息
|
