|
★ 編按 ★ 今天給大家介紹一個可以進行查找替換的函數(shù)---SUBSTITUTE函數(shù),，SUBSTITUTE函數(shù)的基礎(chǔ)語法是：SUBSTITUTE (要替換的文本,舊文本,新文本,[替換第幾個])。 最后一個參數(shù),，[替換第幾個],，是可以省略的，如果要替換的文本存在多個的話,，省略這個參數(shù)表示替換全部,。 先通過一個示例來掌握SUBSTITUTE函數(shù)的基本用法。 掃碼入群,，下載Excel練習(xí)文件,，同步操作 示例1：將單元格里的“付款”替換成“賬期” 公式為=SUBSTITUTE(B2,"付款","賬期")。  這個公式省略了最后一個參數(shù),，所以將單元格里的所有“付款”都換成“賬期”,。如果只想替換第一個“付款”，公式需要修改為：=SUBSTITUTE(B2,"付款","賬期",1)。  如果是“預(yù)付款”不進行替換,，公式可以修改為：=SUBSTITUTE(B2,"天付款","天賬期",1),。  通過這個例子相信大家對于SUBSTITUTE函數(shù)的基本用法應(yīng)該明白了。不過在實際應(yīng)用中,，單獨使用SUBSTITUTE函數(shù)的機會很少,，基本上都是和其他函數(shù)組合使用的，下面的幾個例子都是組合套路,，非常實用,。 示例2：SUBSTITUTE組合MID加密手機號 這里所說的加密就是將手機號的中間四位顯示成*，公式為： =SUBSTITUTE(A2,MID(A2,4,4),"*****"),。  公式的原理很簡單,，MID(A2,4,4)是從手機號的第4位開始提取4個數(shù)字，用SUBSTITUTE函數(shù)把這部分內(nèi)容換成"*****",，從而實現(xiàn)了手機號加密,。 示例3：SUMPRODUCT組合SUBSTITUTE實現(xiàn)帶單位的數(shù)字求和 公式為：=SUMPRODUCT(--SUBSTITUTE(A2:A13,"元",""))。  首先用SUBSTITUTE(A2:A13,"元","")將區(qū)域中數(shù)據(jù)的單位“元”替換為空,，因為SUBSTITUTE函數(shù)得到的結(jié)果是文本格式,，所以前面用兩個負(fù)號將替換后的數(shù)據(jù)變成數(shù)值。 最后用SUMPRODUCT函數(shù)對這一組數(shù)字求和,。 （注意：數(shù)字加單位是一種不規(guī)范的用表習(xí)慣,，如果確實需要加單位可以用自定義格式實現(xiàn)。） 示例4：LEN組合SUBSTITUTE統(tǒng)計一個單元格內(nèi)的人數(shù) 公式為：=LEN(B2)-LEN(SUBSTITUTE(B2,",、",))+1,。  在這個公式中，LEN(B2)取得B2單元格中字符串的長度,。LEN(SUBSTITUTE(B2,",、",))+1的意思是用LEN計算不含頓號的字符串長度。在這個例子中,，人名之間的間隔符是頓號,，最后加1，是因為最后一個人名沒有頓號,。 用B2原有的長度減去被替換掉人名之間間隔符的長度,，也就是人數(shù)。使用這個公式要注意,，每個名字之間的分隔符必須是一樣的,，否則統(tǒng)計結(jié)果就會出錯。 示例5：五個函數(shù)聯(lián)手實現(xiàn)數(shù)據(jù)分列 將示例4中存在于一個單元格的多個人物分開,，每個單元格只存放一個人物,，公式需要用到五個函數(shù),。 公式為：=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100)),。  這個公式的原理比較復(fù)雜,，篇幅所限僅做簡要解釋。 REPT(" ",100)：先使用REPT函數(shù),，將空格重復(fù)100次,，得到100個空格； SUBSTITUTE($B2,",、",REPT(" ",100))：使用SUBSTITUTE函數(shù)將姓名中的的間隔符號頓號替換為100個空格,； MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100)：再使用MID函數(shù),，依次從帶有空格的新字符串中的第1,、第101、第201位……截取長度為100的字符,。 這樣得到的字符串是帶有多余空格的,，因此再使用TRIM函數(shù)將多余空格刪除掉。如果實在不好理解會套用即可,。 今天分享的五個SUBSTITUTE使用案例是非常典型的常見用法,，希望大家能夠好好利用，簡化自己的工作提高工作效率,。  |
|
|
