第一部分：substitute函數的用法介紹

　　Substitute這個單詞就是替換的意思。substitute函數是屬于什么函數，如何使用呢?substitute函數有點類似于excel中的查找替換命令，但卻更靈活好用。

　　substitute函數屬于文本查找類函數，就是查找某個字符，然后替換成別的字符。

　　substitute函數的語法是：SUBSTITUTE(text,old_text,new_text,instance_num)

　　其中的參數意義如下：
　　Text：為需要替換其中字符的文本，或對含有文本的單元格的引用。
　　Old_text：為需要替換的舊文本。
　　New_text ：用于替換 old_text 的文本。
　　Instance_num ：為一數值，用來指定以 new_text 替換第幾次出現的 old_text。如果指定了 instance_num，則只有滿足要求的 old_text 被替換;否則將用 new_text 替換 Text 中出現的所有 old_text。

　　第二部分：substitute函數的應用實例

　　第一題：substitute函數基礎應用

　　實例如下圖所示。源數據為A5單元格。本題實現的效果就是變換不同的參數，將源數據中的“笑”字替換為“看今朝”三個字。

　　C5單元格的公式，實現的就是B5單元格的效果。其中，第四個參數，省略，就表示源數據中所有“笑”字都替換掉。如果只是替換源數據中第一個“笑”，即B6單元格的效果，只需把第4個參數寫1就可以。同樣，如果要替換第2個笑，即B7單元格的效果，就把第4參數寫2就可以。

　　如果源數據中有多處數據需要替換。比如源數據有三個笑，需要替換其中的2和3個笑字，那么就需要嵌套函數。關于嵌套層數，在Excel 2003及以前的版本，最多允許7層嵌套，在Excel 2007中允許使用64層嵌套。

　　B8、B9單元格的效果，前面包含空格，可以使用LEN函數測試出空格數，比如：=LEN(B8)-4，得到1，前面有1個空格。這里的4，代表笑看今朝4個字符。

　　C9單元格的公式，用到了REPT函數。REPT函數就是按照給定的次數重復顯示文本�？梢酝ㄟ^此函數來不斷地重復顯示某一文本字符串，對單元格進行填充。比如，要重復顯示10個空格，可以輸入=REPT(" ",10)。

　　其實空格是沒有實際意義，為什么要舉這個列子呢，是因為有時候輸入不規范。比如一次性把A1單元格的所有空格取消，可以輸入公式：=SUBSTITUTE(A1," ","")。

　　第二題：substitute函數進階應用實例

　　下圖所示的是一個單位的12月份出差費用明細表，方便演示，只截取了部分圖表。

　　第一，根據上圖，統計D5單元格“楊”出現的次數。

　　公式分析： substitute函數如果第4參數省略不寫，就是把所有需要替換掉的內容替換掉。這里，使用len函數測試有幾個字符被替換掉，減少的字符數就是有幾個“楊”。

　　公式為：=LEN(D5)-LEN(SUBSTITUTE(D5,"楊",""))

　　第二，12月份“陳錫盧”共出現幾次。

　　此題需要使用到sumproduct函數，這個函數功能很強大，求和、計數都可以使用。這個函數與SUM函數很相似。sumproduct函數的用法是在給定的幾組數組中，將數組間對應的元素相乘，并返回乘積之和。在以后的講座還會專門講解此函數。

　　公式為：=SUMPRODUCT(--((LEN(D5:D31)-LEN(SUBSTITUTE(D5:D31,"陳錫盧","")))>0))

　　公式分析：本題我們就是使用sumproduct函數來計數。總字符—替換的字符>0，統計大于0的個數。因為公式判斷出來的結果是邏輯值，不能直接求和，因此得先變為數值才行。- - 的作用是把文本轉換為數字，讓邏輯值參加運算。另外*1，/1，+0，等都可以讓文本參與運算。另外，用LEN(D5:D31)給出12月份這個區域。

　　第三，在人數C列統計出對應的人數有幾個。

　　公式為：=IF(D5="","",LEN(D5)-LEN(SUBSTITUTE(D5,"、",""))+1)

　　公式分析：從上面的工作表，可以發現D列人員名單中的“、”符號比人名少一個，利用substitute函數把它替換成空,然后計算。前面再加上IF來判斷沒有人名的情況就顯示為空。

　　第四，計算12月份的總金額。

　　公式為：=SUMPRODUCT(--SUBSTITUTE(E5:E31,"元",""))&"元"

　　公式分析：從上面的工作表中的數據可以發現金額后面都有個“元”字，這時，利用sum是不能正確求和的。其實，用SUBSTITUTE(E5:E31,"元","")將數字提取提取出來，然后就可以求和了。