編按：
SUBSTITUTE替換函數(shù)，在文本單元格數(shù)據(jù)的判斷、比較、計算中常常起到至關(guān)重要的作用。下面，將用兩個工作實例，全面分析，怎么用SUBSTITUTE函數(shù)和其它函數(shù)寫出正確的嵌套函數(shù)，其中的公式邏輯又是怎樣的……

近年來，斷舍離、極簡主義等文化在國內(nèi)相當(dāng)流行。筆者特意去百度了一下“斷舍離”，顧名思義其意思是：“斷”指的是斷絕不需要的東西，“舍”指的是舍棄多余之物，“離”指的是脫離對物質(zhì)的迷戀。

說的簡單粗暴點，就是把多余的東西“咔嚓”掉。下面要講的這個函數(shù)組合，就充滿了斷舍離的精神，它可以通過舍棄一些內(nèi)容來達到最終效果，是不是很神奇呢？

今天，筆者將通過一個小伙伴提出的問題，來和大家講講這個函數(shù)組合的精髓所在，然后再通過一個廣泛應(yīng)用的案例，領(lǐng)略這個斷舍離函數(shù)組合的妙用。

一、如何構(gòu)建函數(shù)組合

小伙伴的數(shù)據(jù)如下圖，A列保存的是文本格式的二位數(shù)，B列保存的是文本格式的三位數(shù)。

問題：判斷A列每個單元格內(nèi)二位數(shù)的每一位數(shù)字，是否全部被包含在B列對應(yīng)單元格內(nèi)的三位數(shù)中（與數(shù)據(jù)出現(xiàn)的先后順序無關(guān)）。即如果A列中每一位數(shù)字均出現(xiàn)在B列中，則判斷結(jié)果為“包含”；否則，判斷結(jié)果為“不包含”。

大家先來理順一下思路：

①以A1和B1為例，A1中的數(shù)據(jù)為“66”，它的第一位和第2位數(shù)字均為“6”。需要在B1中，根據(jù)A1中的兩位數(shù)字來進行“斷舍離”。

②第一次斷舍離是舍去A1中第一位數(shù)“6”，于是，B1中的數(shù)據(jù)將變成“05”；第二次再對“05“進行一次斷舍離，這次要舍去的是A1中第二位數(shù)“6”，但是，經(jīng)過第一次斷舍離后的數(shù)據(jù)是“05”，它里面不包含“6”，所以想舍也沒的舍了，這時數(shù)據(jù)保持不變，依然為“05”。大家可以發(fā)現(xiàn)，在經(jīng)過兩次斷舍離之后，B1中剩余數(shù)據(jù)的長度為2。

③如果大家逐一判斷A列中數(shù)據(jù)經(jīng)過兩次斷舍離后剩余的數(shù)據(jù)長度，就可以找出一個規(guī)律：若B列中剩余數(shù)據(jù)的長度為2或者為3，則A列數(shù)據(jù)不包含在B列中；若B列中剩余數(shù)據(jù)的長度為1，則A列數(shù)據(jù)包含在B列中。

搞清楚了思路，現(xiàn)在來用函數(shù)分步實現(xiàn)：

Step.1 制作輔助列。首先通過LEFT函數(shù)獲取一下A列中數(shù)據(jù)的第一位數(shù)字，在C1中輸入“=LEFT(A1,1)”，可以得到A列中數(shù)據(jù)的第一位數(shù)字（注意：第二參數(shù)可省略）。

Step.2 第一次斷舍離，從A列單元格中舍去輔助列C列的內(nèi)容。在D1中輸入“=SUBSTITUTE(B1,C1,,1)”，即得到結(jié)果。

函數(shù)講解：

SUBSTITUTE函數(shù)的語法為：SUBSTITUTE(Text,Old_text,New_text,[Instance_num])。

①Text參數(shù)為需要替換其中字符的文本，即為B1單元格。

②Old_text參數(shù)為需要被替換掉的老文本，即為C1單元格（亦是A1中第一位數(shù)字）。

③New_text參數(shù)為用于替換Old_text的文本，即為替換成的新文本。此參數(shù)若省略不寫，則默認(rèn)為替換成空值，如上圖。

④Instance_num參數(shù)用來指定以新文本替換第幾次出現(xiàn)的老文本，在D1單元格所寫的函數(shù)中，由于替換的是第一次出現(xiàn)的“6“，所以將這個參數(shù)的值設(shè)為“1”。（注：如果缺省，則意味著用新文本替換Text中出現(xiàn)的所有老文本。）

Step.3 制作輔助列E列。通過RIGHT函數(shù)獲取一下A1單元格中的第二位數(shù)，E1中輸入“=RIGHT(A1,1)”，得到的結(jié)果如下（注意：第二參數(shù)可省略）。

Step.4 第二次斷舍離，從A列單元格中舍去輔助列D列的內(nèi)容。在F1中輸入“=SUBSTITUTE(D1,E1,,1) ”即得到結(jié)果。

敲黑板：在這一次的斷舍離中,SUBSTITUTE函數(shù)的第一參數(shù)是D1單元格中的數(shù)據(jù)，即經(jīng)過第一次斷舍離之后的數(shù)據(jù)。

這時，大家可以看到——只有當(dāng)A列數(shù)據(jù)中的兩個數(shù)字都出現(xiàn)在B列對應(yīng)的單元格中時，F列中的數(shù)據(jù)才是一位數(shù)。

Step.5 判斷A列單元格內(nèi)容是否全部被包含于B列。

通過LEN計算一下F1中數(shù)據(jù)的位數(shù)，在G1中輸入“=LEN(F1)”，即判斷A列單元格內(nèi)容有多少個數(shù)字被包含于B列。

再對G列中的數(shù)據(jù)進行一個IF判斷，在H1中輸入“=IF(G1=1,"包含","不包含") ”，就OK了。

Step.6 將上述函數(shù)嵌套一下，在I1中輸入“=IF(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,1),,1),RIGHT(A1,1),,1)=1,"不包含","不包含") ”，就可以得到最終的結(jié)果了。

二、應(yīng)用實例

如下圖所示，B2至B6單元格中的數(shù)據(jù)是參會人員的名單，在每個人名之間，用中文輸入法下的頓號（、）分隔，現(xiàn)在需要統(tǒng)計每一天的參會人數(shù)。

這個問題的斷舍離思路：

先批量去掉所有的頓號，再計算去除頓號后的數(shù)據(jù)的長度，然后計算數(shù)據(jù)原始長度和去除頓號后的數(shù)據(jù)長度的差值，這樣算出來的剛好是頓號的數(shù)量，最后，用頓號的數(shù)量再加1，就行了。

為什么要加1呢？因為如果用1個頓號，可以分隔2個姓名；用2個頓號，則可以分隔3個人，以此類推，人數(shù)始終比頓號的數(shù)量多1。

下面，開始分步寫函數(shù)。

Step.1 在C2中輸入“=SUBSTITUTE(B2,"、",)”。敲黑板：在此處的函數(shù)中，第三參數(shù)省略，意味著用空格來替代頓號；第四參數(shù)省略，意味著替換掉所有的頓號。

在D2、E2中分別輸入“=LEN(B2)”、“=LEN(C2)”，得到如下結(jié)果。

在F2中輸入“=D2-E2+1”，即得到每天參會的人員總數(shù)。

最后，大家將函數(shù)嵌套一下，就得到一個終極的公式“=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1”，如下圖。

好了，親愛的小伙伴們，今天的內(nèi)容，你們學(xué)會了嗎？函數(shù)可以斷舍離，但是對于Excel的追求，千萬不能斷舍離哦！

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

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

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

相關(guān)推薦：

Excel函數(shù)經(jīng)典案例：substitute函數(shù)應(yīng)用

Excel怎么匯總貨品清單中的文本數(shù)據(jù)?

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

如何對單元格內(nèi)的x箱x條x包批量求和

版權(quán)申明：

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