Excel SUBSTITUTE函數(shù)的用法

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

SUBSTITUTE函數(shù)可以對指定的字符串進(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”（這是逗號和零）,，這樣的話,，除了第一個數(shù)09外，其它以0開頭的都被替換沒有了。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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