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

動(dòng)手操作是熟練掌握EXCEL的最快捷途徑！

【置頂公眾號(hào)】或者【設(shè)為星標(biāo)】及時(shí)接收更新不迷路

小伙伴們好，今天來(lái)和大家分享一道非常重要的題目，從一組順序混亂的文本字符串中按照對(duì)應(yīng)的位置提取數(shù)值。

文本字符串的提取我們多常用SUBSTITUTE函數(shù)，但今天這道題它卻不適用了。而我們今天介紹的這個(gè)方法，在這道題目行秒殺了SUBSTITUTE函數(shù)。

原題目是這樣子的：

根據(jù)左側(cè)的源數(shù)據(jù)，轉(zhuǎn)換為右側(cè)的格式。這道題目我們不能使用SUBSTITUTE函數(shù)，原因是源數(shù)據(jù)中三門(mén)功課的順序和右側(cè)表格中的順序是不一致的，導(dǎo)致SUBSTITUTE函數(shù)不能正確提取對(duì)應(yīng)的數(shù)據(jù)。

這時(shí)，我們就可以考慮使用FILTERXML函數(shù)了。它可以同個(gè)第二參數(shù)的條件設(shè)置，達(dá)到按條件提取數(shù)據(jù)的目的。

在單元格C2中輸入公式“=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," ","姓名 ",1)," ","",2)," ","",3)," ","",4),C$1,"<c/>")," ","</b><b>")&"</b></a>","a/b[c]")”，并向右向下拖曳即可。

思路：

• 目標(biāo)格式的標(biāo)題欄中有“姓名”這一數(shù)據(jù)，而源數(shù)據(jù)中沒(méi)有。因此需要在源數(shù)據(jù)中添加“姓名”這一數(shù)據(jù)，原因后面講。因此SUBSTITUTE($A2," ","姓名 ",1)部分完成此目的。公式中的第四個(gè)參數(shù)1表示替換第一個(gè)空格

• 要解決這道題目，首先要將科目和成績(jī)放在一起，中間不能有空格。因此多次利用SUBSTITUTE函數(shù)對(duì)科目和成績(jī)之間的空格替換。此步驟完成后，生成下面的結(jié)果"張三姓名 語(yǔ)文88 數(shù)學(xué)96 英語(yǔ)89"

• 再次利用SUBSTITUTE函數(shù)對(duì)這個(gè)內(nèi)存數(shù)組進(jìn)行替換。這次將單元格C1的內(nèi)容替換為“<c/>”。單元格C$1中的內(nèi)容是“姓名”，這也就解釋了為什么前面的步驟要添加“姓名”這個(gè)數(shù)據(jù)了。隨著公式向右拖曳，C$1變更為D$1、E$1和F$1。替換的內(nèi)容分別是“姓名、語(yǔ)文、數(shù)學(xué)、英語(yǔ)”

• 利用SUBSITUTE函數(shù)將空格替換為“</b><b>”，并在左右兩端添加“<a><b>”和“</b></a>”，以滿足FILTERXML函數(shù)的格式要求

• 這里FILTERXML函數(shù)的第二個(gè)參數(shù)"a/b[c]"的含義是，在路徑a下的路徑b中，符合條件c的數(shù)據(jù)。因此整個(gè)公式就按條件對(duì)應(yīng)地提取數(shù)據(jù)了

本期內(nèi)容練習(xí)文件提取方式：

鏈接：https://pan.baidu.com/s/1TW8KZWRQi3_UZjKfXLlF0Q?pwd=twun

提取碼：twun

我就知道你“在看”