今天給大家集中火力聊一個(gè)函數(shù)：SUBSTITUTE。該函數(shù)是Excel最常用的文本函數(shù)之一，在數(shù)據(jù)分析過(guò)程中，常用于字符串的整理和清洗。
SUBSTITUTE函數(shù)的基礎(chǔ)語(yǔ)法是：
SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個(gè)])
最后一個(gè)參數(shù)，[替換第幾個(gè)]， 是可以省略的。
舉幾個(gè)小栗子，和大家分享下SUBSTITUTE函數(shù)的常用技巧和套路。
1，將數(shù)據(jù)中的某個(gè)值替換為另一個(gè)值
比如將B列數(shù)據(jù)里的二班，替換為一班。
C2輸入公式，并向下復(fù)制填充：
=SUBSTITUTE(B2,"二班","一班")
這里沒(méi)啥好解釋的了，就是將B列中單元格中的“二班”全部替換為“一班”。
2，隱藏手機(jī)號(hào)的中間5位
C2輸入公式，并向下復(fù)制填充：
=SUBSTITUTE(B2,MID(B2,4,5),"*****")
先使用MID函數(shù)取得B列號(hào)碼中的中間五位，再用字符串“*****”替換掉這部分內(nèi)容。
3，對(duì)含單位的數(shù)據(jù)求和
B7輸入公式：
=SUMPRODUCT(SUBSTITUTE(B2:B6,"人",)*1)
先用SUBSTITUTE替換掉B列單元格中的“人”，得到文本型數(shù)字，乘以1后轉(zhuǎn)換成可以計(jì)算的數(shù)值，再用SUMPRODUCT函數(shù)進(jìn)行求和。
4，數(shù)據(jù)分列，將B列數(shù)據(jù)按頓號(hào)進(jìn)行分列
C2單元格輸入公式橫向拖動(dòng)，并向下復(fù)制填充。
=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))
REPT(” “,100)
先使用REPT函數(shù)，將空格重復(fù)100次，得到100個(gè)空格。
SUBSTITUTE($B2,”、”,REPT(” “,100))
使用SUBSTITUTE函數(shù)將姓名中的的間隔符號(hào)頓號(hào)替換為100個(gè)空格。
MID(SUBSTITUTE($B2,”、”,REPT(” “,100)),COLUMN(A1)*100-99,100)
再使用MID函數(shù)，依次從帶有空格的新字符串中的第1、第101、第201位……截取長(zhǎng)度為100的字符。
這樣得到的字符串是帶有多余空格的，因此再使用TRIM函數(shù)將多余空格刪除掉。
5，混合文本中，計(jì)算人數(shù)個(gè)數(shù)
B2公式：
=LEN(A2)-LEN(SUBSTITUTE(A2,"、",))+1
LEN(A2)取得A2字符串的長(zhǎng)度。
LEN(SUBSTITUTE(A2,”、”,))+1，替換掉人名之間的間隔符，也就是頓號(hào)，再用LEN計(jì)算該值的長(zhǎng)度，最后加1，是因?yàn)樽詈笠粋(gè)人名沒(méi)有頓號(hào)。
用A2數(shù)值原有的長(zhǎng)度減去被替換掉人名之間間隔符的長(zhǎng)度，也就是人名的個(gè)數(shù)。
6，混合文本中，計(jì)算數(shù)值最大值。
B2數(shù)組公式：
=MAX((SUBSTITUTE(A2,ROW($1:$98),)<>A2)*ROW(1:98))
SUBSTITUTE(A2,ROW($1:$98),)<>A2
依次將數(shù)值1到98從A2替換為空，然后把替換后的結(jié)果和被替換值（1-98）進(jìn)行比較 ，如果不相等，則證明A2中存在該數(shù)值。
最后將上述部分公式的運(yùn)算結(jié)果，也就是邏輯值TRUE和FALSE，乘以被替換的值(1-98)，用MAX函數(shù)從中取得最大值。
7，計(jì)算某個(gè)值在某個(gè)范圍的最大連續(xù)次數(shù)
B2數(shù)組公式：
=MAX((SUBSTITUTE(PHONETIC(A2:A9),REPT("A",ROW(1:9)),)<>PHONETIC(A2:A9))*ROW(1:9))
PHONETIC(A2:A9)
將A2:A9的文本值黏合成一個(gè)值，以便SUBSTITUTE函數(shù)進(jìn)行操作。
REPT(“A”,ROW(1:9))
把“A”重復(fù)1到9次。
SUBSTITUTE(PHONETIC(A2:A9),REPT(“A”,ROW(1:9)),)<>PHONETIC(A2:A9)
思路回到示例6，SUBSTITUTE函數(shù)將REPT函數(shù)的運(yùn)算結(jié)果，在PHONETIC函數(shù)的運(yùn)算結(jié)果里替換掉，然后和PHONETIC函數(shù)的原值進(jìn)行比較。如果后者存在替換值，則被替換掉，此時(shí)和原值不相等，返回FALSE，否則返回TRUE。
最后依然把上述公式返回的邏輯值TRUE和FALSE，分別乘以ROW(1:9)，用MAX函數(shù)從中取得最大值。
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任，
本站有權(quán)保留或刪除有爭(zhēng)議評(píng)論。
參與本評(píng)論即表明您已經(jīng)閱讀并接受
上述條款。