SUBSTITUTE函數的幾個妙用

馬震安

1.規避帶單位統計出錯問題

以圖1為例，要計算帶單位數字的合計結果，使用公式“=SUMPRODUCT（SUBSTITUTE （B2：B9，"萬元"，""）*1）&"萬元"”，就會規避帶單位統計出錯的問題，能夠順利完成數據統計（圖1）。

公式解釋：先用SUBS TITUTE函數把B2：B9單元格區域中的“萬元”替換成空，乘以1，將提取出的文本型數字轉換成數字型數字，再利用SUMPRODUCT函數進行求和，最后將求出的各和值用“&”連接符加上單位。

當然，這個公式也可換成“=SUM（--SUBSTITUTE（82：B9，“萬元”，“"萬元"”，按下“Ctrl+Shift+Enter”組合鍵使函數生效（圖2）。

2.隱藏數字

在信息發布時，為了保護隱私，有時需要將電話或身份證中某幾個數字加以隱藏，這時使用SUBSTITUTE函數就非常方便。例如，使用如圖所示的公式“=SUBSTITUTE（B2，MID（B2，12，5），"*****"）”，即可實現隱藏身份證號碼數字的目的（圖3）。

公式解釋：用MID函數從身份證號的第12位開始提取5位，再利用SUBSTITUTE函數將身份證號中的這5位數字替換成“*****”。

3.名單人數統計

在登記參會人員時，有可能將多個人名存放在一個單元格中，統計每個單元格中所包含的人數，同樣也可以使用SUBSTITUTE函數。如圖所示，只需利用公式“=LEN（B2）-LEN（SUBSTITUTE（B2，"、"，""））+1”即可完成統計（圖4）。

公式解釋：先利用LEN函數計算單元格原來值的長度，再用LEN函數計算由SUBSTITUTE函數將單元格中的“、”替換成空格后得到的文本長度，兩個長度相減后再加1，得到的結果就是單元格中所包含的人數了。