SUBSTITUTE函數(shù)的幾個(gè)妙用

馬震安

1.規(guī)避帶單位統(tǒng)計(jì)出錯(cuò)問題

以圖1為例，要計(jì)算帶單位數(shù)字的合計(jì)結(jié)果，使用公式“=SUMPRODUCT（SUBSTITUTE （B2：B9，"萬元"，""）*1）&"萬元"”，就會(huì)規(guī)避帶單位統(tǒng)計(jì)出錯(cuò)的問題，能夠順利完成數(shù)據(jù)統(tǒng)計(jì)（圖1）。

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

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

2.隱藏?cái)?shù)字

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

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

3.名單人數(shù)統(tǒng)計(jì)

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

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