編按：

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

今天給大家介紹一個可以進(jìn)行查找替換的函數(shù)---SUBSTITUTE函數(shù)，SUBSTITUTE函數(shù)的基礎(chǔ)語法是：SUBSTITUTE (要替換的文本,舊文本,新文本,[替換第幾個])。最后一個參數(shù)，[替換第幾個]，是可以省略的，如果要替換的文本存在多個的話，省略這個參數(shù)表示替換全部。

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

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

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

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

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

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

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

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

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

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

示例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使用案例是非常典型的常見用法，希望大家能夠好好利用，簡化自己的工作提高工作效率。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

７個Excel小技巧，提高表格查看效率

Excel運用規(guī)范1：一個單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個公式

9條最實用的計算excel中關(guān)于日期的公式！（建議收藏）

版權(quán)申明：

本文作者郅龍；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。