Excel 2007中查找替换的技巧及批量替换换行符实例

技巧1 模糊匹配查找数据
用户常常需要搜索某一类有规律的数据，比如以A开头的人名，以B结尾的货品编码，或者包含66的电话号码等。

这时就不能以完全匹配目标内容的方式来精确查找了，而需要利用Excel提供的通配符进行模糊查找。

在Excel中，有两个可用的通配符能够用于模糊查找，分别是半角问号“？”和星号“*”。

“？”可以在搜索目标中代替任何单个的字符或数字，而“*”可以代替任意多个连续的字符或数字。

默认情况下，“查找和替换”对话框中的“单元格匹配”复选框是未被勾选的，如图1-1所示。

图1-1 “查找和替换”的单元格匹配属性
此时查找“A*”将匹配所有以“A”开头的单元格，同时所有包含“A”的单元格都会被选中。

勾选“单元格匹配”复选框后，查找“A*”将仅匹配以“A”开头的单元格。

更多的匹配规则请参阅表格1-1所示。

表格1-1 模糊搜索
如果要查找通配符本身，可在“查找内容”文本框中输入通配符时在前面输入~，如表格1-2所示。

表格1-2 查找通配符
模糊查找除了在“查找和替换”中使用到，在“筛选”中也经常用到，规则是相通的，多实践是掌握这个技巧的最好方法。

技巧2 查找单元格格式
图2-1是一个数据区域，其中数值大于15的单元格填充为紫色，数值大于10不大于15的单元格填充为蓝色，其他单元格无填充色。

当数据量巨大时，希望对具有不同填充色的数据进行进一步分析（例如统计其个数、平均值、最大值），那么靠手工操作是无法胜任的。

此时需要通过查找格式，将具有相同格式的单元格定义为名称以作进一步处理。

图2-1 标记了不同格式的数据
步骤1 选中数据区域A1:E16，按<Ctrl+F>组合键调出“查找和替换”对话框，单击“选项”按钮，进入“查找和替换”高级模式。

步骤2 在“查找”选项卡中，单击“格式”按钮右侧的下拉箭头，在下拉菜单中单击“从单元格选择格式”，如图2-2所示。

图2-2 根据单元格格式查找目标单元格
此时，光标变成吸管形状，单击目标单元格（如B13单元格）提取作为“查找内容”的单元格格式。

步骤3 单击“查找全部”按钮，此时在对话框下侧列出所有符合条件的单元格，按<Ctrl+A>组合键选中所有目标单元格，单击“关闭”按钮关闭“查找和替换”对话框。

此时，工作表中所有符合条件的单元格都处于选中状态。

步骤4 在名称框输入名称，例如“紫色单元格”，以指代当前处于选中状态的单元格，如图2-3所示。

图2-3 为选中单元格定义名称
步骤5 重复步骤1-4为蓝色单元格创建名称“蓝色单元格”。

经以上操作后，就可以使用函数公式来处理这些离散数据了，如图2-4所示。

图2-4 对相同格式的数据进行统计处理
其中H2:I5单元格区域中的公式都使用了上面定义的名称，简要介绍如下：
数据个数：=COUNT(蓝色单元格)，=COUNT(紫色单元格)
平均值：=AVERAGE(蓝色单元格)，=AVERAGE(紫色单元格)
最大值：=MAX(蓝色单元格)，=MAX(紫色单元格)
大于平均值的个数：{=SUM(N(LARGE(蓝色单元格,ROW(1:15))>H3))}，{=SUM(N(LARGE(紫色单元格,ROW(1:11))>I3))}
带有“{}”的公式是数组公式，在单元格中输入公式字符后按<Ctrl+Shift+Enter>组合键，完成数组公式的录入。

技巧3 替换单元格格式
从Excel 2002开始，查找替换功能新增了对单元格格式的支持，这意味着用户可以查找应用了特定格式的单元格，并将这些格式进行快速替换。

在图3-1中有些单元格设置了灰色填充色，参照以下步骤可以将灰色填充色替换为蓝色填充色。

图3-1 设置了填充色的单元格
步骤1 选中工作表中任意一个单元格，按<Ctrl+H>组合键调出“查找和替换”对话框，单击“选项”按钮，进入“查找和替换”的高级模式。

步骤2 单击“查找内容”右侧的“格式”按钮，在弹出的“查找格式”对话框中设置查找格式为灰色填充色，如图3-2所示。

图3-2 设置“查找格式”和“替换为格式”
步骤3 类似步骤2，单击“替换为”右侧的“格式”按钮，在弹出的“替换格式”对话框中设置替换格式为蓝色填充色。

步骤4 单击“全部替换”按钮执行格式替换，在弹出的提示替换完成的对话框中单击“确定”按钮关闭对话框。

步骤5 单击“关闭”按钮关闭“查找和替换”对话框，最后格式替换的效果如图3-3所示。

图3-3 将灰色填充色替换为蓝色填充色
注意：在设置“查找内容”的格式及“替换为”的格式时，不仅可以手工设定格式，还可以单击“格式”按钮右侧的下拉按钮，在弹出的扩展菜单中选择“从单元格选择格式”命令，直接从单元格中提取格式。

技巧4 巧用查找替换批量删除换行符
将数据从其他系统导入到Excel时常常会包含大量换行符、空格或者其他不可见的不明字符。

如何快速地删除这些字符是用户迫切想知道的，因为这些字符可能造成公式意外出错等情况。

如图4-1，根据姓名查询员工才艺时，由于基础表中的内容是从其他系统导入的，各单元格
包含有换行符，导致使用姓名进行查找时找不到对应的数据而报错。

参照以下步骤可以快速删除目标区域中的换行符：
图4-1 由换行符引起的公式意外出错情况
步骤1 选定要删除换行符的目标单元格区域，如A列和B列。

注意：如果要在整个工作表范围内删除所有的换行符，确保只选中一个单元格。

当选中的单元格为两个或两个以上时，Excel将自动限定在选定区域内进行查找和替换。

步骤2 在Excel程序窗口中按<Ctrl+H>组合键打开“查找和替换”对话框，如图4-2所示。

图4-2 删除换行符后公式正常运行
步骤3 将光标定位到“查找内容”文本框，按住<Alt>键的同时使用数字小键盘键入“10”，松开<Alt>键，输入换行符；在“替换为”文本框中不输入任何字符，保持为空。

输入的换行符不但不可见，而且不占任何字符位置，但确实是存在的。

因此绝不要重复输入，以至于查找内容变成连续两个换行符，这样反而查找不到换行符。

步骤4 单击“全部替换”按钮执行替换，单击“确定”按钮关闭替换完成提醒对话框。

此时，基础表中的所有换行符被删除，公式返回预想结果。

如果要批量删除空格，可以在“查找和替换”对话框的“查找内容”文本框中输入一个空格；如果要删除不可见的不明字符，可以先选中相应单元格，在编辑栏中复制一个不明字符，并粘贴到“查找内容”文本框中。

本篇文章节选自《Excel 2007 实战技巧精粹》ISBN 9787115218360 人民邮电出版社。