EXCEL中灵活多变的替换利器

SUBSTITUTE函数和其他函数组合的进阶用法

在表格的处理过程中，经常需要把特定的内容替换成其他的内容，大部分人都是用【查找】-【替换】来实现这一功能，如果要实现不规则替换，或涉及到单元格的类型转换，用函数来处理这些情况，效率就会提高很多，本文介绍几个SUBSTITUTE函数和其他函数的组合用法。

1． 如下图所示，需要对B列中的销售额进行汇总计算。

由于B列中单元格内容带有单位，单元格是文本格式，不能用SUM来直接求和（直接用SUM结果为0，如下图B9所示），这时可以使用SUBSTITUTE和SUMPRODUCT组合起来实现这一功能，相关公式为：

= SUMPRODUCT(SUBSTITUTE(B2:B8,'元','')*1)

SUBSTITUTE(B2:B8,'元','')*1是将文本格式转换为数值格式，也可以使用VALUE来将文本转换为数值格式，公式可以修改为：

= SUMPRODUCT(VALUE(SUBSTITUTE(B2:B8,'元','')))

2． 对单元格中不规则内容的替换

有时单元格中需要替换的内容没有特定的规律，不方便使用【查找】-【替换】进行一次性操作，如下图所示，需将旧编码进行更改，更改规则为：将第1个'C'替换为'F'，将第2个'C'替换为'G'。由于字母'C'没有特定规律，这时可以将SUBSTITUTE进行嵌套使用。

= SUBSTITUTE(SUBSTITUTE(A6,'C','F',1),'C','G')

这样就可以比较方便实现不规则内容的替换。如果使用'查找'-'替换'，需要进行数次操作才能达到目的。

3． 和MID组合批量替换数字

如下图所示，批量替换括号号码中间4位为'*'，公式为：

= SUBSTITUTE(A2,MID(A2,4,4),'****')

当然本例也可以使用公式= REPLACE(A2,4,4,'****')来实现目的。

4． 快速统计个数

如下图所示，要快速统计部门的人数，由于原始数据比较粗糙，未将人员分开，如果手工查人数明显效率太低，这时可以使用LEN函数和SUBSTITUTE函数组合实现目的。

公式为：

= LEN(B2)-LEN(SUBSTITUTE(B2,'、',''))+1