SUBSTITUTE函数可添加式批量替换的非VBA解决方案

z391634362

前一段时间给部门的人设计表格模版，经常碰到这么个情况。

源数据中某一列文本，需要根据对应的条件批量替换字符，格局类似于VLOOKUP，左数据右条件。

按正常情况下，用SUBSTITUTE函数逐层套嵌就可以了，问题是条件区域动辄成百上千行，并且还要能让不会EXCEL的人傻瓜式添加条件。

因SUBSTITUTE函数不支持数组替换，又SUBSTITUTE套嵌工作量太大，由于我不会用VBA，就卡在了如何用函数公式解决这个问题。苦思良久，发现了用evaluate宏表函数来实现以上功能的方法。

问题说明：

制作如下模版

1、  将A列内容根据D2:E8区域的条件替换成B列的文本

2、  D2:E8条件区域的内容可添加，并添加完内容后B列的内容自动更新

由于本人非IT专业，自学excel时日尚不足一年，略懂皮毛，此次班门弄斧，第一次发帖，望抛砖引玉，前辈们不吝赐教。

解决方案如下：

核心思路：

1、  通过普通公式批量转换为SUBSTITUTE多层套嵌的文本公式（B列）

2、  用EVALUATE宏表函数转化文本公式（C列）

B列公式为：

=REPT("SUBSTITUTE(",COUNTA($F$2:$F$300))&CHAR(65)&ROW()&PHONETIC($E$2:$I$300)

通过COUNTA计算条件列的个数决定需要套嵌多少层SUBSTITUTE函数，重复次数由REPT实现。

转化后的文本公式如下：

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"A","1"),"B","2"),"C","3"),"D","4"),"E","5"),"F","6"),"G","7")

引号、逗号、右括号都通过辅助列添加。

然后通过EVALUATE函数将文本公式转化为实际公式。

1、  自定义名称——引用位置=EVALUATE(答案!B2)

2、  在C2单元格输入=EVALUATE，向下填充即可。

到此模版已经完成。不懂EXCEL者只要将E8:I8的内容往下拖动，然后将F列和H列中的条件部分改成自己需要的内容即可。

由于PHONETIC函数不支持公式，因此符号的辅助列部分需要手工添加。

首次发帖，希望大家支持。我所有的EXCEL知识都来自于EXCELHOME论坛及EXCELHOME系列图书。本人才疏学浅，此贴意在回报EXCELHOME对我极大的帮助。如有不妥，烦请指正。

（附件表格中含有宏表函数，打开前请启用宏）

substitute批量替换.zip (10.35 KB, 下载次数: 310)

2014-8-19 09:57 上传

点击文件名下载附件

安然祥和

请继续努力，取得更大的成绩。

z391634362

谢谢老师鼓励，必再接再厉，在EXCEL这条不归路上一条道走到黑^ ^

mgblue

先表示很佩服楼主，研究了以后学到不少东西。
发现一个使用中可能会碰到的问题，就是old_text和new_text左右的符号在录入替换条件时需要手动录入，否则会出错；不能提前录入，公式会出错；也无法用公式来自动录入，因为PHONETIC函数的参数不接受公式得到的文本。

z391634362

mgblue 发表于 2014-8-25 15:58
先表示很佩服楼主，研究了以后学到不少东西。
发现一个使用中可能会碰到的问题，就是old_text和new_text左 ...

感谢支持。
你说的问题我也是意识到的，苦于无法解决。如果PHONETIC函数能支持公式就完美了。
其实我只是提供一个大批量使用SUBSTITUTE函数的小技巧。毕竟做模板的情况很少。
按这个思路，日常使用中遇到大批量套嵌substitute函数时不需要挨个手动输入，哪怕要套嵌几百次，先用rept函数重复个多少次SUBSTITUTE(，粘贴成数值，再把替换条件写下来，批量录入","、")"等符号，PHONETIC连起来，粘贴成数值，然后两边文本复制到进一个单元格，加个=号，就能快速生成一个SUBSTITUTE函数大批量套嵌的公式。

星语心愿

感谢楼主的帖子，解决了我的问题：http://club.excelhome.net/thread-1189218-1-1.html
只是公式太复杂了，还不太彻底了解。而且楼主的说明和四楼的说明感觉也不太理解，E、G、I列的符号有什么用啊？是指定的吗？

星语心愿

这个如果用VBA操作，方便吗？

you5693601

那个c1=sw是什么意思啊

luanjinling

不错!有创意!

linky11

为什么在E8行再添加后就显示错误了呢？