SUBSTITUTE函數(shù)常用的套路集合
發(fā)布時間:2022-11-02 10:24 [ 我要自學(xué)網(wǎng)原創(chuàng) ] 發(fā)布人: 小劉2175

今天給大家集中火力聊一個函數(shù):SUBSTITUTE。該函數(shù)是Excel最常用的文本函數(shù)之一,在數(shù)據(jù)分析過程中,常用于字符串的整理和清洗。

SUBSTITUTE函數(shù)的基礎(chǔ)語法是:
SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個])
最后一個參數(shù),[替換第幾個], 是可以省略的。
舉幾個小栗子,和大家分享下SUBSTITUTE函數(shù)的常用技巧和套路。

1,將數(shù)據(jù)中的某個值替換為另一個值

比如將B列數(shù)據(jù)里的二班,替換為一班。
C2輸入公式,并向下復(fù)制填充:
=SUBSTITUTE(B2,"二班","一班")



這里沒啥好解釋的了,就是將B列中單元格中的“二班”全部替換為“一班”。

 

2,隱藏手機(jī)號的中間5位

C2輸入公式,并向下復(fù)制填充:
=SUBSTITUTE(B2,MID(B2,4,5),"*****")



先使用MID函數(shù)取得B列號碼中的中間五位,再用字符串“*****”替換掉這部分內(nèi)容。

 

3,對含單位的數(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ù)按頓號進(jìn)行分列

C2單元格輸入公式橫向拖動,并向下復(fù)制填充。
=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))



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ù)將多余空格刪除掉。

 

5,混合文本中,計(jì)算人數(shù)個數(shù)



B2公式:
=LEN(A2)-LEN(SUBSTITUTE(A2,"、",))+1

LEN(A2)取得A2字符串的長度。
LEN(SUBSTITUTE(A2,”、”,))+1,替換掉人名之間的間隔符,也就是頓號,再用LEN計(jì)算該值的長度,最后加1,是因?yàn)樽詈笠粋人名沒有頓號。
用A2數(shù)值原有的長度減去被替換掉人名之間間隔符的長度,也就是人名的個數(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ì)算某個值在某個范圍的最大連續(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的文本值黏合成一個值,以便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)行比較。如果后者存在替換值,則被替換掉,此時和原值不相等,返回FALSE,否則返回TRUE。
最后依然把上述公式返回的邏輯值TRUE和FALSE,分別乘以ROW(1:9),用MAX函數(shù)從中取得最大值。

Excel2019視頻教程
我要自學(xué)網(wǎng)商城 ¥80 元
進(jìn)入購買
文章評論
0 條評論 按熱度排序 按時間排序 /350
添加表情
遵守中華人民共和國的各項(xiàng)道德法規(guī),
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭議評論。
參與本評論即表明您已經(jīng)閱讀并接受
上述條款。
V
特惠充值
聯(lián)系客服
APP下載
官方微信
返回頂部
分類選擇:
電腦辦公 平面設(shè)計(jì) 室內(nèi)設(shè)計(jì) 室外設(shè)計(jì) 機(jī)械設(shè)計(jì) 工業(yè)自動化 影視動畫 程序開發(fā) 網(wǎng)頁設(shè)計(jì) 會計(jì)課程 興趣成長 AIGC