Excel技巧：SUBSTITUTE函数的几个妙用

在Excel表格中，如果数值带了单位，直接用SUM函数进行统计的话就会出现错误，因此通常的做法是去掉单位再计算。其实，如果用SUBSTITUTE结合SUMPRODUCT函数，这个问题就可以轻松解决。此外，SUBSTITUTE函数还有其他方面的妙用。

1. 规避带单位统计出错问题

以图1为例，要计算带单位数字的合计结果，使用公式“=SUMPRODUCT(SUBSTITUTE(B2:B9,"万元","")*1)&"万元"”，就会规避带单位统计出错的问题，能够顺利完成数据统计（图1）。

公式解释：先用SUBSTITUTE函数把B2:B9单元格区域中的“万元”替换成空，乘以1，将提取出的文本型数字转换成数字型数字，再利用SUMPRODUCT函数进行求和，最后将求出的各和值用“&”连接符加上单位。

当然，这个公式也可换成“=SUM(--SUBSTITUTE(B2: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，得到的结果就是单元格中所包含的人数了。