Excel SUBSTITUTE函數的用法

在前面的文章中，有多次提到SUBSTITUTE的使用，那么今天我們專(zhuān)門(mén)以這篇文章來(lái)討論一些SUBSTITUTE的使用方法和技巧。

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

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

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

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

簡(jiǎn)單的講解一下公式，仔細的看，公式中SUBSTITUTE需要替換的文本是“,0”（這是逗號和零），這樣的話(huà)，除了第一個(gè)數09外，其它以0開(kāi)頭的都被替換沒(méi)有了。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

再使用TRIM去掉空白后，再用“--”轉換成數值。

最后用SUMPRODUCT對數組求和。

SUBSTITUTE復雜應用

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

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