1 丨

基本語(yǔ)法

SUBSTITUTE函數(shù)的基本語(yǔ)法如下：

SUBSTITUTE(源字符串,舊文本,新文本,[替換第幾個(gè)舊文本])

其中第4個(gè)參數(shù)[替換第幾個(gè)舊文本]是可以省略的，絕大部分情況下也都用不上。

舉個(gè)簡(jiǎn)單的小例子。

如上圖所示的數(shù)據(jù)，需要將B列的二班，替換為一班，C2單元格輸入以下公式，向下復(fù)制填充即可。

公式中B2是源字符串，'二班'是被替換的舊值，'一班'是替換成的新值——是不是很簡(jiǎn)單？

有朋友可能會(huì)說(shuō)，這事我用查找替換就可以完成，為什么要用函數(shù)？

相比于基礎(chǔ)操作，函數(shù)的優(yōu)勢(shì)在于它可以構(gòu)建和數(shù)據(jù)源之間的動(dòng)態(tài)關(guān)聯(lián)，當(dāng)數(shù)據(jù)源發(fā)生了變化，函數(shù)可以自動(dòng)更新獲取結(jié)果；另外，也可以作為一個(gè)子函數(shù)，將計(jì)算結(jié)果嵌套在其它函數(shù)中，完成遠(yuǎn)比基操?gòu)?fù)雜的計(jì)算目標(biāo)。

換而言之，函數(shù)具有小規(guī)模自動(dòng)化處理數(shù)據(jù)的能力，而基礎(chǔ)操作自身并不具備這種能力。當(dāng)基操具備這種能力時(shí)，它就——變成了PowerQuery

打個(gè)響指，給大家循序漸進(jìn)舉幾個(gè)例子，分享下SUBSTITUTE函數(shù)的常用技巧和套路。

2 丨

自古深情留不住

只有套路得人心

1 ▎隱藏手機(jī)號(hào)中間5位

=SUBSTITUTE(B2,MID(B2,4,5),'*****')

公式先用MID(B2,4,5)函數(shù)從B2單元格第4個(gè)字符開始取5個(gè)長(zhǎng)度的結(jié)果，然后再使用SUBSTITUTE函數(shù)把這部分替換為5個(gè)星號(hào)。

如下圖所示的數(shù)據(jù)，需要在B7單元格對(duì)B2:B6區(qū)域的人數(shù)統(tǒng)計(jì)求和。由于區(qū)域內(nèi)的人數(shù)并非數(shù)值，無(wú)法直接使用SUM函數(shù)。

B7單元格輸入公式如下：

=SUMPRODUCT(

SUBSTITUTE(B2:B6,'人','')*1)

公式先運(yùn)行：

SUBSTITUTE(B2:B6,'人','')

將區(qū)域內(nèi)的單位'人'，替換為空，獲取結(jié)果如下：

{'3';'7';'8';'2';'6'}

文本函數(shù)返回的結(jié)果必然是文本，因此這些數(shù)值也都屬于文本型數(shù)值，使用數(shù)學(xué)運(yùn)算*1，強(qiáng)制轉(zhuǎn)換為數(shù)值，最后再使用SUMPRODUCT函數(shù)統(tǒng)計(jì)求和。

3 ▎混合文本中計(jì)算人員個(gè)數(shù)

如下圖所示的數(shù)據(jù)，B列是參會(huì)人員名單，現(xiàn)在需要在C列統(tǒng)計(jì)各班參會(huì)人數(shù)。

C2單元格輸入以下公式：

=LEN(B2)-LEN(SUBSTITUTE(B2,'、','')) 1

LEN(B2)取得B2字符串的長(zhǎng)度。

LEN(SUBSTITUTE(B2,'、',)) 1，替換掉人名之間的分隔符，也就是頓號(hào)，然后再用LEN計(jì)算該字符串的長(zhǎng)度。最后加1，是因?yàn)樽詈笠粋€(gè)人名沒(méi)有頓號(hào)，需要補(bǔ)上。如此一來(lái)，每個(gè)分隔符就代表一個(gè)人名。

用A2數(shù)值原有的長(zhǎng)度減去被替換掉人名分隔符的長(zhǎng)度，也就是人名的個(gè)數(shù)。

4 ▎混合文本中計(jì)算整數(shù)最大值

如下圖所示的數(shù)據(jù)，A2單元格是各班人數(shù)的數(shù)據(jù)，現(xiàn)在需要在B2單元格計(jì)算各班中最多的人數(shù)。

=MAX((SUBSTITUTE(A2,ROW(1:99),'')<>A2)*ROW(1:99))

SUBSTITUTE(A2,ROW(1:99),)<>A2部分，以A2為源字符串，依次將數(shù)值1到99替換為空，再把替換后的結(jié)果和源字符串進(jìn)行比較，如果不相等，則證明A2中存在該數(shù)值，返回邏輯值TRUE，否則返回邏輯值FALSE。

最后將這組邏輯值TRUE和FALSE，分別乘以被替換的數(shù)值（1..99），其中TRUE*數(shù)值，返回?cái)?shù)值自身；FALSE*數(shù)值，返回0；用MAX函數(shù)從中取得最大值，即為結(jié)果。

5 ▎計(jì)算數(shù)據(jù)最大連續(xù)次數(shù)

如下圖所示的數(shù)據(jù)，C列是星光足球隊(duì)的戰(zhàn)績(jī)，需要在F2單元格統(tǒng)計(jì)連勝的最大次數(shù)。

F2單元格公式如下：

=MAX(

(SUBSTITUTE(CONCAT(C2:C15),

REPT('勝利',ROW(1:14)),'')

<>CONCAT(C2:C15))

*ROW(1:14))

REPT('勝利',ROW(1:14))，將勝利重復(fù)1..14次，14是最大可能的連勝次數(shù)。

此時(shí)思路回到了案例4。SUBSTITUTE函數(shù)將REPT函數(shù)的結(jié)果，在源字符串里替換掉，然后和源字符串比較是否相等。如果源字符串存在替換值，則和原值不相等，返回邏輯值TRUE，否則返回邏輯值FALSE。

=TRIM(MID(SUBSTITUTE($B2,'/',REPT(' ',100)),COLUMN(A1)*100-99,100))

SUBSTITUTE($B2,'/',REPT(' ',100))部分，將源字符串B2單元格數(shù)據(jù)中的分隔符'/'替換為100個(gè)空格。這樣就將不同愛好分別劃分到了100個(gè)空格構(gòu)建的小房間里。

然后再使用MID函數(shù)，按順序從小房間里取出愛好，最后使用TRIM函數(shù)消除多余的空格。該套路我們有一個(gè)專門的單章來(lái)講述，可參考函數(shù)系列教程文本處理篇的「如何按指定間隔符拆分?jǐn)?shù)據(jù)」

有朋友可能會(huì)想，函數(shù)這么復(fù)雜，我還是用分列吧……誰(shuí)不想回到小時(shí)候過(guò)無(wú)憂無(wú)慮的日子呢

本文由公眾號(hào)“Excel星球”首發(fā)。