分类：Excel 2016实用技巧大全

将等于某个值的所有单元格修改为 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一致。

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

图4.98中C列中的值是比赛项目，由于存在多人参加相同项目的问题，因此有些项目重复出现。现要求在E列罗列出所有比赛项目，不允许有重复值。

解题步骤

Excel从2007版开始提供了一个“删除重复项”工具，用于提取行中的唯一值，如果是Excel 2003，则只能通过公式或筛选来完成。本例展示“删除重复项”的应用，具体操作步骤如下。

1.选择C2：C13区域，按组合键＜Ctrl+C＞复制。

2.选择E2单元格，然后按Enter键粘贴数据。

3.单击功能区的“数据”→“删除重复项”，弹出如图4.99所示的“删除重复项”对话框。

4.单击“确定”按钮保存设置，同时执行删除重复项，Excel 会弹出图 4.100 所示的提示信息，通知用户删除了多少个重复值和保留了多少个唯一值，图4.101则是删除重复值后的效果。

知识扩展

1.删除重复项功能不是从数据源中提取唯一值并存放在其他地方，而是直接在原始区域中删除重复出现的值，高级筛选工具才可以提取选区唯一值到其他的区域。

为了不破坏数据源，本例在操作时先将数据复制到其他区域，然后再删除重复项。如果不想复制数据，要直接提取唯一值，那么应该采用高级筛选工具，操作方式为：单击数据区域以外的空白单元格（如 K1），然后单击功能区的“数据”→“高级”，弹出如图 4.102 所示的“高级筛选”对话框，在对话框中按图4.103所示的方式设置筛选参数，当单击“确定”按钮后能得到图4.104所示的筛选结果。

2.删除重复项工具支持单列重复和多列重复的判断，本例处理的是单列重复项，多列重复指的是多列数据都同时相同才算重复，如果部分数据相同则不会删除。

在图4.105中，“姓名”、“地区”和“参赛项目”复选框都选择表示3列同时重复才删除，因此尽管有3个长沙，有3个田径，有2个铅球，但是真正重复的只有第5行和第13行，姓名、地区和参赛项目同时重复才删除，因此执行结果是删除第13行，其他数据保留。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

公司有一台测试机，办公人员每天都需要将测试机中产生的压力和温度抄写到Excel中，压力和温度都包含两位小数。

由于每天要输入的数值较多，是否有办法让小数点自动产生呢？例如，要输入 123.45，只需要输入12345即可。

解题步骤

在“Excel选项”对话框中提供了自动产生小数的选项，具体操作步骤如下。

1.按组合键＜Alt+T+O＞，弹出“Excel选项”对话框。

2.打开“高级”选项卡，然后选择“自动插入小数点”复选框，下方的“位数”保持默认值2即可，表示自动在整数的第2位小数前面插入小数点。设置界面如图4.97所示。

图4.97 设置自动插入小数点2位

3.单击“确定”按钮保存设置，同时返回工作表。

4.在单元格中输入数值 12345，当单击 Enter 键后可以发现单元格的值已经自动变成123.45。继续在单元格中输入 645789，单击 Enter 键后可以发现单元格的值已经自动变成6457.89。

知识扩展

1.“自动插入小数点”选项默认是未选中的，当选中后可以自动在指定的位置插入小数点，但是它只对整数有效，如果在单元格中输入的值本身就是小数，那么Excel不会在该值的指定位置插入新的小数点。

2.自动插入小数点的默认位数是2位，可以根据需求随意调整，但是只能在-300～300范围之内。

3.如果将位数调整为-3，那么在单元格中输入3后将自动变成3000。

在图4.95中有4个标题，要求每个标题的格式必须一致。现在已经对A组的标题设置了格式，包含字体名称、字号、字体颜色和单元格背景颜色，是否有办法将A组的格式快速复制到其他标题区域中去？

解题步骤

格式刷可以将一个单元格或一个区域的值快速复制到其他单元格或区域，具体操作方法如下。

1.选择第一个标题区域A1：C2。

2.双击“开始”选项卡中的格式刷（其图标为）。

3.依次选择E1：G2、A11：C12和E11：G12区域，此时E1：G2、A11：C12和E11：G12区域将自动应用A1：C2区域的格式，效果如图4.96所示。

图4.96 使用格式刷批量复制格式后的效果

4.按Esc键取消格式刷工作模式，此时鼠标指针旁边的格式刷图标会自动消失。

知识扩展

1.复制工具很多，其中复制粘贴会将区域的值和区域的格式一并复制过去，不符合本例需求；选择性粘贴格式尽管可以只复制格式，但其步骤多、效率低，它适合单个区域复制；格式刷只复制格式，效率最高，适合对多区域粘贴格式。

2.单击格式刷只能复制一次格式，而双击格式刷则可以不限制复制次数，直到用户按 Esc键后停止。

3.格式刷不会复制单元格的行高和列宽，假设需要将宽度与高度也一并复制过去，应采用选择性粘贴。

图 4.90 中姓名一栏包含两个字、三个字和四个字，它们占用的宽度是不相同的。现要求将它们调整为占用相同的宽度。

手工输入空格从而统一宽度比较费时、费力，有什么办法一次性调整完成呢？

解题步骤

设置单元格的对齐方式可以让长短不一的单元格统一宽度，具体操作步骤如下。

1.选择A1：A11区域，然后按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框。

2.打开“对齐”选项卡，然后将“水平对齐”由默认的“常规”修改为“分散对齐（缩进）”，操作界面如图4.91所示。

3.单击“确定”按钮保存设置，同时返回工作表界面，此时所有姓名将显示为图4.92所示的效果。

图4.91 设置对齐方式为分散对齐

图4.92 分散对齐的姓名

知识扩展

1.在单元格的字符中间添加若干空格可以让字符长短不一的单元格对齐显示，但是它并不能自动适应单元格的宽度调整，手工调整列宽后必须重新输入空格数量，因此效率极度低下。本例的办法是通过设置格式让字符分散对齐，字符会随列宽相应地变化，例如图4.93中A列加宽后，姓名仍然总是保持对齐状态。

2.在“水平对齐”右方有一个“缩进”选项，如果需要将姓名所在列加宽，可以根据实际宽度决定缩进一定的量，例如，对图4.93缩进两个单位后可以得到图4.94所示的效果。

图4.93 自动适应列宽

图4.94 缩进两个单位

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

部分文档在保存时总会产生图4.87所示的提示，需要单击Enter键后才能保存，每天都会浪费不少时间，是否有办法去除这个提示信息呢？

图4.87 保存文件时的提示信息

解题步骤

出现此提示表示文档中有一个或多个不兼容却又无法删除的个人信息，解决方法有两个：一是人工找到该信息然后手工删除，二是通过设置选项让Excel在保存文件时不删除个人信息。后者比较简单易学，具体步骤如下。

1.按组合键＜Alt+T+O＞，弹出“Excel选项”对话框。

2.按图4.88所示的方式依次单击“信任中心”→“信任中心设置”，弹出“信任中心”对话框。

图4.88 弹出“信任中心”对话框

3.在“信任中心”对话框中按图4.89所示的方式单击“个人信息选项”→“保存时从文件属性中删除个人信息”，从而取消选择“保存时从文件属性中删除个人信息”复选框，表示不删除个人信息。由于不再删除，因此不可能删除失败，也就不存在因删除失败而生成的对话框了。

图4.89 取消选择“保存时从文件属性中删除个人信息”复选框

4.单击“确定”按钮，保存设置，返回工作表界面后再次保存文件，可以发现 Excel 将不再出现图4.87所示的提示信息。

知识扩展

1.在“保存时从文件属性中删除个人信息”下方有一个“文档检查器”，它可以检查当前文档中有哪些个人信息。

2.“保存时从文件属性中删除个人信息”复选框在多数情况下是呈灰色禁用状态的，只有文档有问题时才可以使用，因此当Excel无法选中此选项时，不要认为软件有问题，恰恰表示没有问题。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。