操作更高效 增强Excel的批量替换功能

俞木发

用好模糊搜索 指定条件进行批量替换

如果需要替换一组数据，可以借助“*”号进行模糊查找，然后再进行批量替换。比如下表中要将成绩小于60的数据全部替换为“需补考”，打开“查找和替换”后在“查找内容”框中输入“*”，点击“查找全部”，然后在下方的窗格中点击“值”，这样表中学生所有科目的成绩会自动排序，按提示选中分数小于60的数据，接着在“替换为”中输入“需补考”，最后点击“全部替换”即可（图1）。

利用类似的方法，我们可以很方便地批量选中符合条件的不连续数据，比如现在需要将小于60分的数据全部填充为黄色，在选中所有符合条件的数据后，点击“开始一填充颜色一黄色”即可。这样，以后就可以使用“查找一格式一从单元格中选中格式”，快速找到填充色是黄色的单元格，还可以对这些单元格中的数据进行批量替换操作，比如将字体颜色设置为红色，或者添加边框等操作（图2）。

多个数据 轻松替换

上述方法适合于将多个不同的数据批量替换为一類名称。如果有多个数据需要替换为对应的种类，就不适用了。比如某公司由于技术标准改变，需要将原来文档中的旧标准，如GX-ZZ-201801更改为FJGX-201801CBZZ，即将原来标准中的GX替换为FJGX，ZZ-201801替换为201 801 CBZZ，因为每个标准字符都不相同，利用Excel的查找替换效率极低。对于类似格式的字符替换，可以使用智能填充来快速实现。首先在B1中按照A1的替换格式输入FJGX-201801CBZZ，接着按下Ctrl+E，这样Excel会根据A1、B1的格式对比智能地确定内容，并向下进行填充，最后将A列删除即可。这样省去了每个字符都要查找替换的麻烦（图3）。

如果需要替换的多个数据的格式并不相同，比如“FJ-GX01-dzi01”要替换为“FJ-GX01/zj01”（即将代码中的第二个“-”更改为“/”）（图4），因为第二个“-”在原始数据中的位置并不相同，而且代码格式也不同，使用智能填充无法完成快速替换（当然内置替换组件也无法选择指定“-”进行替换），对于类似的替换可以借助于Replace函数来实现。在C1输入公式“=REPLACE（A1，FIND（"-"，A1，6），1，"/"）”，接着向下填充即可完成替换操作（图5）。

公式解释：

先使用Find函数查找第二个“-”的位置，这里要注意观察原始数据中第二个“-”的位置特性。比如在数据中第一个“-”的最小位置是在第3个字符（即FJ-），最大位置则为第5个字符（即AGER-），因此需要使用FIND（"-"A1，6），表示从A1的第6个字符开始搜索“-”的位置，这样才能找到第二个“-”的正确位置，同时返回整数值作为Replace开始替换的位置。比如“=FIND（"-"，A1，6）”返回的数字是“8”，REPLACE（A1，8，1，"/"），表示从A1的第8个字符（即第二个“-”）开始替换，替换1个字符，替换符号是“/”。

简单高效 使用VBA脚本快速替换

上述方法适合于单个文档的替换，如果需要替换的文档很多，比如在某个工作簿中原来有很多中文名称，如“一分部”、“二分部”等，由于录入管理系统无法识别中文，现在需要将所有中文使用相应的拼音替代。这时，使用上述函数替换就不方便，可以借助VBA脚本进行快速替换。

首先整理好需要替换的所有字符，在当前工作表的名称上右击并选择“查看代码”，打开VB编辑窗口，在第一个下拉框中选择Worksheet，第二个下拉框中选择BeforeDoubleClick，在其中输入图中显示的代码（实际使用时请自行替换字符，每个需替换字符一行，代码下载：http：∥dwz.date/bU E5，提取码：kubg），点击“运行”就可以完成所有替换（图6）。

由于VB支持正则表达式，我们可以灵活地使用它来进行各种替换。比如为了便于及时记账，公司食堂采购人员经常使用手机便签，使用诸如“2020.06.03购买冬瓜123元”的格式记账，这些记录导入电脑后，需要使用时间和金额格式进行替换（图7）。为此，先到http：∥suo.im/510iHg（提取码：pxyf）下载所需的代码，打开VB编辑窗口，输入下载到的代码，点击“运行”即可。也可以将其保存为宏工作簿使用，这样以后需要替换类似的数据时，只要点击“开发工具→宏→数据转换”即可（图8）。