【原】有一個(gè)Excel函數(shù)叫：省時(shí)一整天，你卻連我的名字都不知道…

Excel教程平臺(tái) 2021-10-08

展開全文

★

編按

★

Hello大家好，今天給大家介紹一下SUBSTITUTE函數(shù)，它可以將數(shù)據(jù)中的舊值替換為新值，與我們熟悉的“查找替換”功能相似。很多小伙伴可能會(huì)說，簡(jiǎn)單的查找替換就能解決的問題，我為什么還要學(xué)習(xí)用復(fù)雜的函數(shù)？相比Excel的基礎(chǔ)操作，函數(shù)可以構(gòu)建和數(shù)據(jù)源之間的動(dòng)態(tài)關(guān)聯(lián)，當(dāng)我們的數(shù)據(jù)源發(fā)生變化和，函數(shù)會(huì)自動(dòng)更新結(jié)果，但基礎(chǔ)操作卻不會(huì)。所以我們今天就通過5個(gè)案例一起來學(xué)習(xí)一下SUBSTITUTE函數(shù)吧~

今天給大家介紹一個(gè)可以進(jìn)行查找替換的函數(shù)---SUBSTITUTE函數(shù)，SUBSTITUTE函數(shù)的基礎(chǔ)語(yǔ)法是：SUBSTITUTE (要替換的文本,舊文本,新文本,[替換第幾個(gè)])。

最后一個(gè)參數(shù)，[替換第幾個(gè)]，是可以省略的，如果要替換的文本存在多個(gè)的話，省略這個(gè)參數(shù)表示替換全部。

先通過一個(gè)示例來掌握SUBSTITUTE函數(shù)的基本用法。

掃碼入群，下載Excel練習(xí)文件，同步操作

示例1：將單元格里的“付款”替換成“賬期”

公式為=SUBSTITUTE(B2,"付款","賬期")。

這個(gè)公式省略了最后一個(gè)參數(shù)，所以將單元格里的所有“付款”都換成“賬期”。如果只想替換第一個(gè)“付款”，公式需要修改為：=SUBSTITUTE(B2,"付款","賬期",1)。

如果是“預(yù)付款”不進(jìn)行替換，公式可以修改為：=SUBSTITUTE(B2,"天付款","天賬期",1)。

通過這個(gè)例子相信大家對(duì)于SUBSTITUTE函數(shù)的基本用法應(yīng)該明白了。不過在實(shí)際應(yīng)用中，單獨(dú)使用SUBSTITUTE函數(shù)的機(jī)會(huì)很少，基本上都是和其他函數(shù)組合使用的，下面的幾個(gè)例子都是組合套路，非常實(shí)用。

示例2：SUBSTITUTE組合MID加密手機(jī)號(hào)

這里所說的加密就是將手機(jī)號(hào)的中間四位顯示成*，公式為：

=SUBSTITUTE(A2,MID(A2,4,4),"*****")。

公式的原理很簡(jiǎn)單，MID(A2,4,4)是從手機(jī)號(hào)的第4位開始提取4個(gè)數(shù)字，用SUBSTITUTE函數(shù)把這部分內(nèi)容換成"*****"，從而實(shí)現(xiàn)了手機(jī)號(hào)加密。

示例3：SUMPRODUCT組合SUBSTITUTE實(shí)現(xiàn)帶單位的數(shù)字求和

公式為：=SUMPRODUCT(--SUBSTITUTE(A2:A13,"元",""))。

首先用SUBSTITUTE(A2:A13,"元","")將區(qū)域中數(shù)據(jù)的單位“元”替換為空，因?yàn)镾UBSTITUTE函數(shù)得到的結(jié)果是文本格式，所以前面用兩個(gè)負(fù)號(hào)將替換后的數(shù)據(jù)變成數(shù)值。

最后用SUMPRODUCT函數(shù)對(duì)這一組數(shù)字求和。

（注意：數(shù)字加單位是一種不規(guī)范的用表習(xí)慣，如果確實(shí)需要加單位可以用自定義格式實(shí)現(xiàn)。）

示例4：LEN組合SUBSTITUTE統(tǒng)計(jì)一個(gè)單元格內(nèi)的人數(shù)

公式為：=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1。

在這個(gè)公式中，LEN(B2)取得B2單元格中字符串的長(zhǎng)度。LEN(SUBSTITUTE(B2,"、",))+1的意思是用LEN計(jì)算不含頓號(hào)的字符串長(zhǎng)度。在這個(gè)例子中，人名之間的間隔符是頓號(hào)，最后加1，是因?yàn)樽詈笠粋€(gè)人名沒有頓號(hào)。

用B2原有的長(zhǎng)度減去被替換掉人名之間間隔符的長(zhǎng)度，也就是人數(shù)。使用這個(gè)公式要注意，每個(gè)名字之間的分隔符必須是一樣的，否則統(tǒng)計(jì)結(jié)果就會(huì)出錯(cuò)。

示例5：五個(gè)函數(shù)聯(lián)手實(shí)現(xiàn)數(shù)據(jù)分列

將示例4中存在于一個(gè)單元格的多個(gè)人物分開，每個(gè)單元格只存放一個(gè)人物，公式需要用到五個(gè)函數(shù)。

公式為：=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))。