分类：效率篇

图4.151中部分成绩添加了黄色背景，现要求将黄色修改为蓝色，按住Ctrl键逐个选中单元格，然后修改颜色就可以达成目的，但是黄色单元格太多时就会显得效率低下。有没有办法一性次修改完成呢？

图4.151 成绩表

解题步骤

Excel支持按格式替换，因此在“查找和替换”对话框中直接操作即可，具体步骤如下。

1.按组合键＜Ctrl+A＞全选数据区域。

2.按组合键＜Ctrl+H＞，弹出“查找和替换”对话框，然后单击“选项”按钮显示更多选项。

3.单击上方的“格式”按钮，弹出“查找格式”对话框，打开“填充”选项卡，然后选择黄色方块，并按“确定”按钮结束。操作界面如图4.152所示。

图4.152 设置查找格式

4.单击下方的“格式”按钮，弹出“替换格式”对话框，打开“填充”选项卡，选择蓝色方块，然后单击“确定”按钮结束。此时在“查找和替换”对话框中可以看到前面所设置的两种格式预览，效果如图4.153所示。

图4.153 查找和替换格式预览

5.单击“全部替换”按钮，Excel 会弹出图 4.154 所示的提示。关闭对话框后可以看到单元格替换结果，其效果如图4.155所示，所有原本背景为黄色的单元格都已经显示为蓝色。

图4.154 提示替换成功的数量

图4.155 替换结果

知识扩展

1.Excel支持按格式替换，可以设置单个格式也可以设置多个格式，而且允许查找的格式数量与替换的格式数量不一致，如同时满足字号 12、背景色为红色且合并居中 3 个条件，那么就将它替换成字号15。

2.在替换时，如果指定了替换格式，同时“替换为”文本框是空白的，那么 Excel 只替换格式；如果“替换为”文本框是空白的，同时又没有指定替换格式，那么Excel会将目标单元格替换成空白的。

图4.147所示的工作表中包含多个省的人员信息，由于工作中需要不定时定位湖南和湖北的所有单元格，有什么办法提升定位速度呢？

图4.147 成绩表

解题步骤

将湖南和湖北地区的单元格定义为指定的名称，以后需要定位时在名称框中输入湖南、湖北名称即可，具体操作步骤如下。

1.按组合键＜Ctrl+A＞全选数据区域。

2.按组合键＜Ctrl+F＞，弹出“查找和替换”对话框。

3.将查找内容设置为“湖南*”，然后单击“查找全部”，效果如图4.148所示。

图4.148 查找所有“湖南*”的单元格

4.按组合键＜Ctrl+A＞全选所有目标，然后关闭“查找和替换”对话框。

5.在名称框中输入“湖南”，然后单击Enter键，当前选区就会自动被命名为“湖南”，图4.149中左上角就是名称框。

图4.149 对所有湖南区域的单元格命名为“湖南”

6.重复步骤1、2、3、4、5，继续查找“湖北*”，然后将选中的区域命名为“湖北”。

7.在名称框中输入“湖南，湖北”，然后单击 Enter 键，工作表中所有包含湖南、湖北区域的单元格将自动被选中，效果如图4.150所示。

图4.150 最终效果

知识扩展

1.如果某个区域需要反复定位，那么对它命名，并通过名称框定位目标区域，这是最高效的操作方式。如果该区域只需要定位一次，那么命名后再定位则显得复杂了。

2.引用运算符有三种：冒号“：”属于区域运算符，表示引用冒号两边单元格所形成的矩形区域；空格“”属于交叉运算符，表示引用空格两边的两个区域的交集，例如“F：F6：6”代表F6，即F列与第6行的交集；逗号“，”属于联合运算符，表示同时引用逗号两边的两个区域，“F：F，6：6”表示F列和第6行。

在本例中，“湖南，湖北”表示同时引用代表湖南和湖北的两个区域。

3.如果工作表中新添加了数据，那么应该重新查找并命名，名称不具备自动扩展的功能，不会将新的包含湖南或者湖北的单元格纳入名称中。

在图4.144中，C列、F列和I列都使用了公式对上方的数值进行求和，但是前两列的合计结果都是错误的，怀疑是某些单元格设置成了文本格式，导致单元格中的值不参与运算，因此需要验证工作表是否存在文本格式的单元格。

假设工作表中有文本格式的单元格，是否有办法一次性选中它们呢？

图4.144 求和出错的销量表

解题步骤

“定位条件”工具可以选择所有值为文本的单元格，但是不能选中所有格式为文本的单元格，因此唯一的选择是通过“查找和替换”对话框实现需求，具体操作步骤如下。

1.按组合键＜Ctrl+A＞全选数据区域。

2.按＜Ctrl+F＞，弹出“查找和替换”对话框。

3.单击“选项”按钮，然后单击“格式”按钮，弹出“查找格式”对话框。

4.打开“数字”选项卡，选择左方的“文本”，然后单击“确定”按钮返回“查找和替换”对话框，图4.145是“查找格式”设置界面。

图4.145 设置查找格式

5.单击“查找全部”，然后按＜Ctrl+A＞全选目标单元格，关闭对话框后可以看到所有文本格式的单元格都处于选中状态，效果如图4.146所示。

图4.146 被选中的文本格式单元格

在“开始”选项卡的格式栏中可以看到选区单元格的格式代码为“文本”。

知识扩展

1.将单元格设置为文本格式，然后输入数值，此数值不会参与求和运算。但是在单元格中已经有数值的情况下将单元格修改为文本格式，那么此时的数值可以参与求和运算。

2.在文本格式的单元格中输入数值，数值会变成文本，从而致使单元格的值不参与求和运算，但是此时将单元格的格式修改为“常规”，单元格中的值仍然不能参与求和运算，必须修改格式后再重新输入数值才行。

最简单的方法是双击单元格，然后按下Enter键，此操作相当于重新输入一次数值，不过只能一次性修改一个单元格的值。

将文本格式的数字批量还原为数值，可以参考本书的案例117。

图4.141所示的工作表中包含几十个产品名称及其对应的销量，现要求找到所有包含“扳手”二字的单元格，有没有办法一次性查找完成？

图4.141 销量表

解题步骤

包含XX通常有4种情况：其一是单元格的值等于XX；其二是单元格的值以XX开头；其三是单元格的值以 XX 结尾；其四是在单元格的中间包含 XX。使用“*XX*”为条件批量查找可以同时满足4种情况，具体操作步骤如下。

1.按组合键＜Ctrl+A＞全选数据区域。

2.按组合键＜Ctrl+F＞，弹出“查找和替换”对话框。

3.在对话框中将查找内容设置为“*扳手*”，然后单击“查找全部”按钮，在对话框下端会列出所有找到的目标，效果如图4.142所示。

图4.142 查找“*扳手*”

4.按组合键＜Ctrl+A＞全选目标单元格，然后关闭“查找和替换”对话框，工作中所有包含“扳手”的单元格都会瞬间被选中。

知识扩展

1.“*扳手*”代表“扳手”前面和后面存在任意长度的任意字符，“任意”也包含 0，因此“扳手”前面或后面没有任意字符也符合条件。

2.查找包含XX的单元格还有一种方法，直接查找XX，不选择“单元格匹配”复选框，然后单击“查找全部”按钮即可（见图 4.143）。不完全匹配其实就是包含的意思，因此不再需要输入通配符*。

图4.143 查找包含XX的目标

3.Excel支持在活动工作簿的所有工作表中查找，但是无法通过＜Ctrl+A＞组合键全选所有目标，因为选择操作只能作用于活动工作表。

工作表中有很多合并单元格，它们分散在各处，没有规则，是否有办法一次性选中这些合并单元格呢？

解题步骤

当合并单元格较多且分散在多处时，按住Ctrl键后逐一选择合并单元格的方式效率偏低，通过内置的查找工具可以一次性选中所有合并单元格，具体操作步骤如下。

1.以图4.136所示的数据为例，首先按组合键＜Ctrl+A＞全选数据区域。

图4.136 挂科科目表

2.按组合键＜Ctrl+F＞，弹出“查找和替换”对话框，然后单击“选项”按钮让对话框显示为图4.137所示的状态。

图4.137 显示更多选项

3.单击右方的“格式”按钮，弹出“查找格式”对话框，然后打开“对齐”选项卡，选择“合并单元格”复选框，最后单击“确定”按钮。图4.138为“查找格式”的操作界面。

图4.138 设置查找格式为合并单元格

4.返回“查找和替换”对话框后单击“查找全部”按钮，此时 Excel 会罗列出所有找到的目标单元格地址，按组合键＜Ctrl+A＞全选目标，最后关闭对话框即可。图4.139是选中所有合并单元格后的效果。

图4.139 已选中所有合并单元格

知识扩展

1.“查找和替换”对话框在默认状态下不支持按格式查找，需要单击“选项”按钮后才能看到设置格式的选项。

2.Excel允许查找任意格式的目标单元格，包括按字体名称查找、按字体颜色查找、按单元格颜色查找、按文本方向查找、按边框样式查找……也可以同时按多个格式查找，只要在“查找格式”对话框中指定需要查找的格式即可。本例需要查找的是合并单元格，因此选择“合并单元格”复选框，忽略其他格式。

3.Excel还提供一种更快捷的格式设置方式——从单元格选择格式。当查找需求不只是合并单元格，还需要指定字体、颜色等格式信息时，直接指定一个样本单元格比手工设置查找格式更快捷。图4.140用于说明“从单元格选择格式”菜单的调出方式。

图4.140 调出“从单元格选择格式”

4.在不关闭 Excel 的情况下，在“查找和替换”对话框中设置的查找格式信息会自动保留下来，在特殊情况下它会带来隐患。例如，刚才按格式查找，现在想按数值查找，在查找时有可能发现明明查找的值是存在的，但总是找不到，这是因为第一次查找时设置的格式信息会干扰第二次查找，应该单击“清除查找格式”后再执行第二次查找。

将等于某个值的所有单元格修改为 Arial Black 字体，要完成此需求比较简单，按条件查找后全选目标，然后设置字体即可。但是Excel不支持按范围查找，如果要求“将大于0的所有数值修改为Arial Black字体”，Excel就无法直接实现需求了，Excel的“查找和替换”对话框不支持比较运算符。

是否有变通的方法突破限制，将图4.129中大于等于80、小于等于90的单元格修改为Arial Black字体呢？

图4.129 成绩表

解题步骤

Excel本身不支持按范围查找，但是调整一下查找方式可以找到符合指定范围的所有数值，然后对它们设置字体格式，具体步骤如下。

1.在H1和H2两个单元格分别输入80和90。

2.按下组合键＜Ctrl+F＞，弹出“查找和替换”对话框。

3.将“查找内容”设置为“*”，表示查找任意值的目标单元格，然后单击“查找全部”，此时在下方会罗列出所有找到的目标单元格地址和目标数值，同时在状态栏标注找到的目标数量，效果如图4.130所示。

图4.130 查找所有“*”

4.左键按住对话框下端并下拉，从而将对话框拉高，然后单击标题栏“值”，表示按值的大小排序，排序结果如图4.131所示。

图4.131 按值对查找目标升序排列

5.按住滚动条并下拉，直到出现第一个80时单击选中80，效果如图4.132所示。

图4.132 选中第一个80

6.再次下拉滚动条，直到出现90时停止，此时按住Shift键单击最后一个90（假设有多个90），然后关闭对话框。图4.133是选中80～90范围的值的状态下的“查找和替换”对话框，图4.134则是选中80～90范围的值的状态下的成绩表。

图4.133 按住Shift键选中最后一个90

图4.134 已选中值为80～90的成绩表

7.从字体框中选择Arial Black，成绩表中大于等于80和小于等于90的单元格都会显示为Arial Black字体，效果如图4.135所示。

图4.135 对选区修改字体

8.删除H列的两个辅助单元格。

知识扩展

1.本例在H列添加两个辅助单元格的意义在于方便定位，当成绩表中没有80和90时，人工添加80和90，在排序查找结果后方便定位上限和下限。如果成绩表中本身就有80和90，那么在“查找和替换”对话框中选择数值时要选择第一个80和最后一个90，避免遗漏。

2.查找时输入的“*”是通配符，代表任意长度的任意值，换言之，是查找所有非空单元格。

3.使用条件格式可以将指定范围的值突出显示，包含添加字体颜色、单元格背景色、对单元格添加下画线、添加删除线，但是条件格式不能修改单元格的字体。

图4.114中罗列了公司在17个地区的产品销售数据，包含华东地区、华南地区、华中地区、华北地区、西北地区、西南地区，假设某人属于华东地区总代表，因此经常需要定位表中华东地区的数据，而表中的数据并没有按地区名称排序，是否能在不改变数据排列方式的情况下随时、快速地定位华东地区的数据呢？

图4.114 销量表

解题步骤

将不规则的区域命名，然后通过定位工具定位到名称即可，具体操作步骤如下。

1.由于华东地区包括山东、江苏、安徽、浙江、福建、上海，因此选择工作表中的A3：C3、A7：C7、A10：C10、A13：C13、A16：C16和A18：C18区域。

2.按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框。

3.单击“新建”按钮，弹出图4.115所示的“新建名称”对话框，将默认的名称“上海”修改为“华东地区”，然后单击“确定按钮”返回“名称管理器”对话框，在该对话框中可以看到前面定义的“华东地区”名称，如图4.116所示。

图4.115 “新建名称”对话框

图4.116 名称管理器中的名称列表

4.重复步骤1到3，将A2：C2、A6：C6、A17：C17区域命名为“华南地区”，将A8：C8、A14：C14、A5：C5区域命名为“西南地区”。

5.按组合键＜Ctrl+G＞，弹出“定位”对话框，然后按图 4.117 所示的方式选择“华东地区”，单击“确定”按钮后Excel会自动选中属于华东地区的区域，定位结果如图4.118所示。

图4.117 定位华东地区

图4.118 定位结果

6.再次按下组合键＜Ctrl+G＞，弹出“定位”对话框，选中“西南地区”，然后单击“确定”按钮，Excel会瞬间定位属于西南地区的A8：C8、A14：C14、A5：C5区域。

知识扩展

1.将不规则的区域命名，然后按名称定位可以快速选中不规则的区域。不过此办法主要适用于需要多次选择目标单元格的情况，如果只需要选择一次，直接按住Ctrl键后进行多选即可。

2.定义名称时名称要符合几个规则：不能以数字或标点符号开头，不能用C和R作为名称、不能用单元格地址作为名称，如A1、V15等，不能使用空格，名称不能超过255个字符。

3.定义名称后可以直接在 A1 单元格上方的名称框中调用，不需要调用“定位”对话框。图4.119表示通过名称框定位“华南地区”所代表的区域：在名称框中输入“华南地区”后单击Enter键即可自动选择A8：C8、A14：C14、A5：C5区域。

图4.119 名称框

当工作表中数据较多时，要查看最下方最后一个非空单元格的值或最右方的非空单元格的值，有何方法瞬间定位成功，不需要使用鼠标滚轮或拖动Excel的滚动条？

解题步骤

Excel提供了多个快速定位区域尾端单元格的组合键，为了方便读者理解，特以图4.109所示的数据为例讲解定位过程。

图4.109 模拟数据

1.选择单元格D6，按下组合键＜Ctrl+↓＞，此时Excel会选中值为“22”的单元格D9，因为相对于D6单元格，D9属于B3：F10区域中的本列最后一个非空单元格，效果如图4.110所示。

图4.110 定位下方最后一个非空单元格

2.按下组合键＜Ctrl+←＞，此时Excel会选中值为“行标题6”的单元格B9，因为B9是B3：F10区域中D6所在行的第一个非空单元格，效果如图4.111所示。

图4.111 定位左边第一个非空单元格

3.按下组合键＜Ctrl+↑＞，此时Excel会选中值为“列标题1”的单元格B3，因为B3是B3：F10区域中B9单元格所在列的第一个非空单元格，效果如图4.112所示。

图4.112 定位上方第一个非空单元格

4.按下组合键＜Ctrl+→＞，此时Excel会选中值为“列标题4”的单元格F3，因为F3是B3：F10区域中B3所在行的最后一个非空单元格，效果如图4.113所示。

图4.113 定位右方最后一个非空单元格

知识扩展

1.当数据不超过一页时，不使用快捷键也可以瞬间定位到区域的尾端，数据多时只能使用快捷键才能在定位时既快捷又准确。本例为了方便读者理解各种快捷键的功能，故意使用小区域进行展示，实际工作中只有区域很大时才有必要使用快捷键。

2.要注意“尾端”的完整名称——区域尾端，Ctrl+方向键不是针对整个工作表的，而是针对指定区域的尾端。当按下＜Ctrl+↑＞组合键时可定位当前区域从下向上的最后一个非空单元格，当按下＜Ctrl+↓＞组合键时则可定位当前区域从上向下的最后一个非空单元格……

3.当活动单元格处于数据区域中间时，区域尾端指的是当前区域上、下、左、右方向的最后一个非空单元格，而活动单元格处于区域尾端时，通过快捷键定位区域尾端只能定位下一个区域的尾端。

例如，活动单元格是图4.109中的D9，当按下组合键＜Ctrl+↓＞时，可以定位到下方第二个区域的顶部D12单元格。如果下方没有任何非空单元格，那么可以定位到D列的最后一个空白单元格D1048576。

4.方向定位除了使用Ctrl键加方向键以外，还可以使用End键加方向键，不过两者的用法稍有不同。例如，＜Ctrl+↓＞组合键是按住Ctrl键不放，然后单击下箭头键，而＜End+↓＞组合键则是按下End键后松开，然后再按下箭头键。

图 4.106 是公司的缴库表，由于临时要求不打印其中 5 日和 7 日的数据，但是又不能将 5日和7日的对应单元格数据删除或隐藏，是否有办法快速隐藏图4.106中5日和7日区域的数据呢？

图4.106 缴库表

解题步骤

快速隐藏指定区域的值有两种方法：其一是通过设置单元格格式从而隐藏单元格的值，快捷且操作简单；其二是通过条件格式让单元格的字体与背景都显示为白色，从而达到隐藏的目的，两种方法各有优劣。以设置单元格格式为例，具体步骤如下。

1.选择B2：G5、B9：G11区域，按＜Ctrl+1＞组合键，弹出“设置单元格格式”对话框。

2.单击“自定义”，然后在右方的格式代码文本框中输入代码“；；；”，3 个半角分号表示所有字符都不显示，设置界面如图4.107所示。

图4.107 设置单元格的数字格式

3.单击“确定”按钮保存设置，同时返回工作表界面，此时可以看到5日和7日对应的数据都已经隐藏起来，效果如图4.108所示。

图4.108 隐藏效果

知识扩展

1.格式代码分正数、负数、0和文本4个段，默认状态是“G/通用格式；-G/通用格式；0；＠”。本例中采用“；；；”表示4个段都不显示任意值，相当于隐藏单元格的一切字符。

2.采用设置单元格格式的方式隐藏单元格的值在操作上简单快捷，但是它有一个明显的缺点——当一个区域中包含多种数字格式时不宜采用此方法去隐藏区域的值，否则以后需要显示数据时无法还原到原来的状态。只有所有单元格都是统一的格式时才方便还原格式。例如，区域中每个单元格都采用“yyyy-mm-dd”或都是“G/通用格式”，隐藏后直接将格式“；；；”修改为“yyyy-mm-dd”或者“G/通用格式”就还原所有数据了。

3.使用条件格式隐藏区域的值比较方便还原，只是步骤复杂一点，其操作步骤为：选择B2：G5、B9：G11区域，单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框，然后选中“使用公式确定要设置条件格式的单元格”，并在下方输入公式“=true”，最后单击“格式”按钮，弹出“设置单元格格式”对话框，将字体颜色和填充色都设置为白色，单击“确定”按钮，返回工作表界面后可以看到隐藏结果与图4.108一致。

当需要显示被隐藏区域的值时，选择区域，然后清除条件格式即可，它不会破坏单元格原本的格式。