Excel SUBSTITUTE函數(shù)的用法

在前面的文章中，有多次提到SUBSTITUTE的使用，那么今天我們專門以這篇文章來討論一些SUBSTITUTE的使用方法和技巧。

SUBSTITUTE函數(shù)可以對(duì)指定的字符串進(jìn)行替換。

上一篇的文章中提到了用TEXT中“0”做為占位使用。比如123456要變成0000123456，可以使用TEXT(123456,”0000000000”)來得到。那反過來，要去掉0000123456中占位的“0”，，怎么操作呢，其實(shí)很簡單，只要做一次數(shù)值的計(jì)算就 可以了，假設(shè)A3=0000123456,即--A3=123456,A3*1=123456，A3+0=123456；

但往往實(shí)際使用中問題就沒有那么簡單了。假設(shè)A4=09,03,11,40,06,02,00,00，想要 去掉這些多余的“0”，要如何操作？其實(shí)也不復(fù)雜：

MID(SUBSTITUTE(A4,",0",","),1+(LEFT(A4)="0"),99)= 9,3,11,40,6,2,0,0；

簡單的講解一下公式，仔細(xì)的看，公式中SUBSTITUTE需要替換的文本是“,0”（這是逗號(hào)和零），這樣的話，除了第一個(gè)數(shù)09外，其它以0開頭的都被替換沒有了。

(LEFT(A4)="0")是一個(gè)技巧，其實(shí)你可以把它看作是一個(gè)判斷IF函數(shù)的簡寫，它的作用就是判斷第一個(gè)字符是否為"0"，在Excel的計(jì)算中，TRUE是被當(dāng)做1來處理的，同 樣FALSE是被當(dāng)做0來 處理，所(LEFT(A4)="0")返回的結(jié)果是TRUE，它和1相加，則為2；

然后用MID從第2位開始取值，最后取99個(gè)值，你也可以寫199，都不 影響，如果你想把這個(gè)取值長度精確化，那么你可以使用LEN(SUBSTITUTE(A4,",0",","))來計(jì)算它。

上面這段文字需要慢慢的領(lǐng)會(huì)。至少有幾個(gè)非常實(shí)用的技巧。

SUBSTITUTE包含的內(nèi)容非常的 多，可以收藏這篇文章后慢慢的琢磨。

再說說SUBSTITUTE的自動(dòng)換行。假設(shè)A5=”我愛你，我愛她，我愛大家“，通過以下公式進(jìn)行換行

=SUBSTITUTE(A5,",",""&CHAR(10))

輸完公式，記得單元格設(shè)置自動(dòng)換行。其中CHAR(10)是換行符。

用SUBSTITUTE可以來統(tǒng)計(jì)單元格中某個(gè)字符出現(xiàn)的次數(shù)。比如A6=”abcdabcdaa”，先用SUBSTITUTE把要統(tǒng)計(jì)的字符替換成空 ，然后用原有的單元格長度減去剩下的長度，即為字符出現(xiàn)的次數(shù)。

LEN(A6)-LEN(SUBSTITUTE(A6,"a",))=4

下面這個(gè)應(yīng)用會(huì)比較復(fù)雜一些。我會(huì)具體的解釋一下。

假設(shè)單元格A7的值為99分,98分,97分,89，我們要對(duì)其中的數(shù)字求和。公式如下 ：

=SUMPRODUCT(--(0&TRIM(MID(SUBSTITUTE(A7,"分,",REPT(" ",100)),ROW($1:$9)*100-99,99))))=383

這里的難點(diǎn)就是要把單元格里的文字去掉，然后轉(zhuǎn)換成數(shù)組，再對(duì)數(shù)組求和。

首先用SUBSTITUTE把“分,”（分和逗號(hào)），替換掉空白，這邊是用了100個(gè)空（REPT(" ",100)）;

然后用MID分別取進(jìn)行取值，這邊有一個(gè)技巧，ROW($1:$9)*100-99的意思是從{1;101;201;301;401;501;601;701;801}開始對(duì)前面替換的數(shù)組開始取值，取99個(gè)值。這實(shí)際上就是運(yùn)用了數(shù)組。

這里再補(bǔ)充一下，為什么用100個(gè)空?主要的目的是把A7單元格里包含的數(shù)值想象成位數(shù)比較多。當(dāng)然也可只替換成10個(gè)空，公式就要再改一改。

所以MID取值范圍是與前面替換的空白相關(guān)聯(lián)的。

再使用TRIM去掉空白后，再用“--”轉(zhuǎn)換成數(shù)值。

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

SUBSTITUTE復(fù)雜應(yīng)用

這個(gè)公式中應(yīng)用到數(shù)組，可能會(huì)比較難明白。后續(xù)再詳細(xì)的講解。

特別要說明的是SUMPRODUCT也是一個(gè)非常非常強(qiáng)大的函數(shù)，它的計(jì)算速度非常的快。在VBA寫程序的時(shí)候，為了提高程序運(yùn)算的速度，我們 也會(huì)用到它。