分类：Excel 2016实用技巧大全

包含212个案例，每个案例都分为疑难场景、解题步骤和知识扩展3部分，其中疑难场景用于描述问题；解题步骤用于阐述解决问题的方法，按步骤先后详细地罗列出来，同时配有操作过程与操作结果的截图，并在图中提供箭头指示操作对象，从而帮助读者快速掌握解题方法；知识扩展用于补充解题方案中部分代码的相关语法、思路，或者提供其他解题方案，使读者对每个步骤了解得更透彻，并可举一反三。

Excel 能按颜色和数值双条件排序吗？

图6.14所示的工作表中包含了某班学生的100米短跑成绩和排名情况，其中女生被标示为黄色背景以示区别。现要求将黄色背景的单元格排在一起，然后再按短跑时间值升序排列。

图6.14 短跑成绩表

解题步骤

本例需求是将黄色背景的单元格排在一起，同时按短跑时间值升序排列，只要进入“排序”对话框，设置对应的两个条件即可，其中颜色条件放在上方，具体操作步骤如下。

1.选择A1：D12区域。

2.单击功能区的“开始”→“排序和筛选”→“自定义排序”，弹出“排序”对话框。

3.将第一排序条件的“主要关键字”设置为“姓名”，将“排序依据”设置为“单元格颜色”，然后在“次序”列表中选择“黄色”和“在顶端”，表示排序时将所有黄色单元格排在顶端，其他颜色的单元格按原来的顺序排列。图6.15是设置界面。

图6.15 设置第一排序条件

4.单击“添加条件”，然后将“次要关键字”设置为“时间”，其他选项保持默认值不变，最后单击“确定”按钮执行排序。图6.16是第二排序条件的设置界面，图6.17是排序结果。

图6.16 设置第二排序条件

图6.17 排序结果

知识扩展

1.按颜色排序包含单元格的背景颜色和单元格的字体颜色，可以二选一也可以两者同时使用。

2.Excel内置的按颜色排序只能一次把一种颜色的单元格排在一起，如果有10个颜色，要求每个颜色的单元格都排一起，那么应该添加10个排序条件。

如果要求10种颜色都各自排列在一起，使用Excel内置的功能相当困难，使用外置插件则瞬间完成，如Excel插件“E灵”可以一次性将所有颜色的单元格都按颜色排列在一起。

Excel 是否可以按夺冠次数多少对冠军降序排列？

图6.10中包含19届比赛的冠军榜，其中部分人员多次获得冠军。现要求按夺冠次数对各冠军的姓名降序排列，即获得冠军次数最多的人排在前面、最少的人排在后面，如果两人夺冠次数相同，那么数据源中最先出现的姓名排在前面。

图6.10 冠军榜

解题步骤

Excel本身并不支持按单元格字符的出现次数排序，因此只能借助COUNTIF函数辅助完成工作，具体操作步骤如下。

1.在C1单元格中输入“次数”。

2.在C2单元格中输入公式“=COUNTIF（B$2：B$20，B2）”，然后双击C2单元格的填充柄并将公式向下填充到C20。

3.选择C1：C20区域，然后单击功能区的“开始”→“筛选和排序”→“自定义排序”，此时Excel将弹出图6.11所示的“排序提醒”对话框，不做任何修改，单击“排序”按钮进入下一步界面。

图6.11 “排序提醒”对话框

4.在“排序”对话框中将“主要关键字”修改为“次数”，将“次序”修改为“降序”，然后单击“确定”按钮执行排序。图6.12是设置界面，图6.13则是排序结果。

图6.12 设置排序方式

图6.13 排序结果

5.删除辅助区域的所有单元格。

知识扩展

1.COUNTIF函数在本例中的功能是计算一个单元格的值在一个区域中的出现次数。为了确保公式下拉填充后返回结果总是正确的，第一参数必须使用B$2：B$20或$B$2：$B$20，而第二参数则必须采用相对引用。

2.在排序之前，如果只选择了一个连续区域中的部分单元格，启动“排序”对话框前会弹出对话框询问用户是以当前选定区域排序还是以扩展区域排序。扩展区域其实就是当前选区所在的整个数据区域，本例中的选区是C1：C20，其扩展区域是A1：C20。

Excel 是否可对工作表数据横向排序？

快速访问工具栏的“升序排序”、“降序排序”及“开始”选项卡中二级菜单“排序和筛选”下的子菜单“升序”和“降序”都无法实现横向排序。

图6.6中每个年级的成绩都是横向排列的，如何才能对它们降序排列呢？

图6.6 待排序的成绩表

解题步骤

Excel所有版本的排序工具都是默认纵向排序的，但可以进入“排序”对话框的“选项”窗口调整排序方向，具体步骤如下。

1.选中A1：J5区域。

2.单击功能区的“开始”→“排序和筛选”→“自定义排序”，弹出“排序”对话框。

3.单击“选项”按钮，弹出“排序选项”对话框，选择“按行排序”后单击“确定”按钮，返回“排序”对话框。图6.7是设置界面。

图6.7 选择按行排序

4.单击“主要关键字”右方的倒三角按钮，从弹出的列表中选择“行5”，然后将“次序”调整为“降序”，设置界面如图6.8所示。

图6.8 设置排序条件

5.单击“确定”按钮执行排序，结果如图6.9所示。

图6.9 排序结果

知识扩展

1.按列排序时，在“排列”对话框中的关键字会显示为列标题名称，而当“选项”中调整为“按行排序”后，关键字却不会显示为行标题，而是“行1”、“行2”……

2.如果使用多条件排序，每个条件的顺序相当重要。在“选项”按钮之前有“上移”和“下移”按钮，可以通过这两个按钮调整条件的顺序。

Excel 是否可按数值降序/生产线升序双条件排列数据？

图6.1中包含两条生产线的19名员工的捐款数据，现要求将这些数据按捐款额的大小降序排列，同时要确保A线的员工排在一起，B线的员工排在一起。

例如，A线职员B的捐款大于A线职员A的捐款，那么职员B应该排在职员A的前面；如果B线职员C的捐款大于A线职员A的捐款，那么它不能排在A线职员A和B的前面，而是要排在所有A线职工的后面。

图6.1 需要排序的数据

解题步骤

快速访问工具栏的“升序排序”、“降序排序”及“开始”选项卡中“排序和筛选”下的子菜单“升序”和“降序”都只能单条件排序。要执行多条件排序，需要调用“自定义排序”菜单，具体操作步骤如下。

1.选择A1：E20区域。

2.单击功能区的“开始”→“排序和筛选”，弹出“排序”对话框。

3.单击“主要关键字”右方的倒三角按钮，然后从列表中选择“生产线”，表示第一个排序条件是A列的生产线，“次序”保持默认的“升序”不变，设置界面如图6.2所示。

图6.2 设置第一排序条件

4.单击“添加条件”，然后将“次要关键字”设置为“捐款”，将“次序”调整为“降序”，设置界面如图6.3所示。

图6.3 设置第二排序条件

5.单击“确定”按钮执行排序，双条件排序效果如图6.4所示。

图6.4 排序结果

从图6.4中可以看出，排序时采用了双条件，优先条件是生产线名称，次要条件是捐款数额，所有数据都依照这两个条件调整顺序。

知识扩展

1.在“排序”对话框中，“数据包含标题”复选框默认是处于选中状态的，表示选区中的首行不参与排序。假设排序前只选择了B线的A11：E20区域，那么不能勾选此选项。

2.尽管看起来排序后的图6.4和数据源图6.1的A列未产生变化，但是在设置排序条件时仍然需要使用两个条件，其中一个条件用于限制 A 列升序排列。如果只用“捐款”单条件降序排列，生产线名称就会被打乱，形成图6.5所示的效果。

图6.5 单条件排序结果

Excel 如何让公式可以引用合并单元格的值？

由于 Excel 内置的合并工具对一个区域执行合并后会丢失部分数据，因此使用 SUMIF 或COUNTIF等函数统计数据时会遗漏部分数据。

图 5.40 是合并前的数据，图 5.41 是合并后的数据，合并后使用公式“=SUMIF（B2：B20，G2，E2）”统计湖北省的职工捐款时会遗漏大部分数据，原本合计结果应该为2000，实际合计结果却是535。

如何才能实现合并单元格的状态下执行数据运算而不遗漏部分数据呢？

图5.40 合并前

图5.41 合并后计算出错

解题步骤

直接对区域执行合并会导致区域中左上角单元格以外的所有单元格都丢失数据。

在辅助区域中执行合并，然后将辅助区域的格式粘贴到需要合并的区域，最后删除辅助区域的值，此方法既可合并目标单元格，又能保留每个单元格的值，从而不影响公式运算。

具体操作步骤如下。

1.将B2：B20区域复制到G2：G20。

2.对G2：G20的值按内容合并，相邻且相同的单元格合并一次，合并效果如图5.42所示。

图5.42 合并相同且相邻的单元格

3.选择G2：G20，并按下组合键＜Ctrl+C＞执行复制。

4.右键单击B2单元格，从右键菜单中选择“选择性粘贴”，然后在弹出的对话框中选择“格式”，最后单击“确定”按钮返回工作表界面。图5.43为“选择性粘贴”的设置界面。

图5.43 设置选择性粘贴选项

5.删除G列的所有单元格。

6.在G1、H1和G2单元格分别输入“省”、“捐款数量”和“湖北省”，然后在H2单元格输入公式“=SUMIF（B2：B20，G2，E2）”，此时可以发现公式的计算结果不再产生任何遗漏，运算结果如图5.44所示。

图5.44 重新合并后的公式计算结果正常

知识扩展

1.直接合并单元格一定会丢失数据。假设A1：A3单元格的值都是“湖南省”，对A1：A3区域执行合并后，A2：A3的值会丢失，图5.45可以明确地说明此问题。

图5.45 用公式证明丢失数据

如果采用本例的方法，通过辅助区域来实现合并，A2 和 A3 的值会保留下来，通过公式可以证明，效果如图5.46所示。

图5.46 用公式证明未丢失数据

2.通过本例的步骤执行合并，区域中会保留所有值，因此选中该区域并单击“开始”选项卡中的“合并后居中”菜单，当单元格取消合并之后可以看到合并之前的所有数据。

Excel 所有单元格中的空格可否一次性删除？

工作表中数据较多，偶然在其中一个单元格中有空格，空格影响了后续的公式运算。由于不知道还有哪些单元格中存在空格，因此打算删除所有单元格中的空格。

有什么方法可以一次性删除工作表中的所有空格呢？

解题步骤

Excel并没有提供删除某字符的工具，但是在替换字符时将目标字符设置为空文本即可实现删除字符的功能，因此本例中的问题可以通过替换来实现，具体操作步骤如下。

1.按组合键＜Ctrl+A＞全选工作表中的所有数据区域。

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

3.将“查找内容”设置为空格，然后观察“单元格匹配”复选框是否已选择，如果已选择则需要手动消掉，然后单击“全部替换”按钮，Excel会提示替换成功的数量。图5.38是替换选项的设置界面，而图5.39则是替换数量提示框。

图5.38 设置替换选项

图5.39 提示替换成功的数量

知识扩展

1.单元格中意外产生了空格时对于表格的运算、查找都有较大的影响。例如，将数值123.45输入成123.45，单元格会由于空格的干扰变成文本，而文本是不能参与SUM函数求和的。此外，如果文本的前面或后面意外产生了一个空格，那么VLOOKUP、COUNTIF等函数引用单元格时都会判断失误。

2.每一次执行替换前都有必要观察“单元格匹配”复选框是否选择，选择时表示等于关系，不选择时则表示包含关系。本例如果在选择状态下执行替换，那么单元格中只有一个空格没有其他字符才能替换成功，如果不选择则单元格中不管有多少字符，都会将其中的空格删除。

Excel 能否将文本型数字转换成数值？

图5.35中C12的公式计算结果不正确，因为C2：C11区域中部分单元格的值是文本，而不是数值。如何才能快捷地将C2：C11区域中每个单元格的值都转换成数值呢？

图5.35 文本型数字导致求和出错

解题步骤

将文本型数字转换成数值有很多方法，最快捷地批量转换方法是分列，按以下步骤调用分列工具。

1.选择C2：C12区域。

2.单击功能区的“数据”→“分列”，弹出“文本分列向导”对话框。

3.不做任何设置，单击“完成”按钮，选区中的所有文本型数字都会转换成数值，C12 的公式也会相应地调整运算结果。图5.36是调整后的效果。

图5.36 转换效果

知识扩展

1.数值在单元格中显示为文本形式，其成因有两个：一是将单元格的数字格式设置为“文本”，然后再输入数值，二是先输入半角状态的撇号，然后再输入数值。不管哪种方式产生的文本型数字都可以通过分列转换成数值。

2.分列仅对列有效，一次转换一列数据。如果一个多行多列的区域都需要转换，如B2：G200，那么可以在旁边的一个空白单元格中输入数值0，并复制该单元格，然后选择B2：G200区域，调用“选择性粘贴”对话框，选中“数值”和“加”单选按钮，最后单击“确定”按钮。粘贴选项的设置界面如图5.37所示。

图5.37 设置粘贴选项

Excel 如何解除循环引用警告？

打开文件时出现图5.31所示的警告信息，是什么原因？如何才能取消此对话框？

图5.31 循环引用警告

解题步骤

图5.31属于循环引用警告，当工作簿中有任意单元格的公式引用了公式所在单元格的值时，打开该工作簿时就会产生循环引用警告。消除此警告的方法是修改产生循环引用的公式或删除该公式。

打开工作簿后，在Excel的状态栏可以看到活动工作表中产生循环引用的单元格地址，因此消除此警告的方法是根据状态栏的显示内容判断活动工作表是否存在循环引用，如果有循环引用则修改公式，具体步骤如下。

1.打开工作簿，当产生图5.31所示的警告时单击“确定”按钮关闭对话框。

2.查看状态栏，在图5.32中，Sheet1的状态栏只显示了“循环引用”却未标注单元格地址，说明Sheet1中不存在循环引用。

图5.32 Sheet1的状态栏

3.切换到 Sheet2 工作表，在状态栏中可以看到循环引用的单元格地址为 C12，因此选中C12后从编辑栏查看其公式，从图5.33中可以看到其公式“=SUM（C2：C12）”明显有问题，公式应该引用C2：C11而非C2：C12，原来的公式已将公式所在单元格C12也一并引用进去，从而导致公式运算出错。。

4.将公式改为“=SUM（C2：C11）”，C12的公式马上返回正确结果，但是在状态栏又显示了新的循环引用地址E12，效果如图5.34所示，因此还需要继续修改E12单元格的公式。

图5.33 Sheet2的状态栏与循环引用公式

图5.34 修改公式后

5.选中E12单元格，可以看到其公式是“=AVERAGE（E2：E12）”，将其中的E12修改为E11，修改后公式会马上生成正确的计算结果，同时状态栏不再显示“循环引用”字样。

知识扩展

1.循环引用是公式引用了公式所在单元格的值去参与运算，导致公式无法得到正确结果。如果只是引用公式所在单元格的行号、列号，那么是不会造成循环引用的，只有引用公式所在单元格的值才会造成循环引用。

例如，在A2单元格输入公式“=Row（a2）”不会造成循环引用，在A2单元格输入公式“=a2”会造成循环引用。

2.除了状态栏，依次单击功能区的“公式”→“错误检查”→“循环引用”也能看到工作表中的循环引用单元格地址。

3.循环引用也不只是缺点，在特殊情况下还会故意让公式循环引用，从而实现特殊需求。不过这超出了本例的知识范畴，不再详述。

Excel 是否能跨列合并且保留所有数据？

Excel内置了合并居中功能，但是合并后会丢失部分数据。

图5.24中A1：C1区域合并后只能保留A1的值。

图5.24 Excel内置的合并居中效果

使用内置的合并工具不仅会丢失数据，还无法实现批量合并。例如，要求实现图 5.25 中每一行单独执行一次的合并，Excel是无法完成的。

图5.25 特殊需求的批量合并效果

有没有办法突破以上两个限制，既合并后保留所有值，又可以实现批量合并，每行单独合并一次？

解题步骤

Excel内置的任何工具都无法实现此需求，因此笔者用VBA开发了一个新的插件来完成，使用插件就和使用内置功能一样，只要单击菜单即可合并完成，而且支持撤销。

插件的名称为“疑难114 增强版跨越合并.xlam”，读者可以从案例文件中找到此文件。假设只是临时使用插件来执行一次合并，那么使用步骤如下。

1.打开需要合并数据的工作簿，假设该工作簿中有 A1：C6 区域需要合并，内容如图 5.26所示。

图5.26 待合并数据

2.双击打开“疑难114 增强版跨越合并.xlam”，此时在“开始”选项卡中会产生一个新按钮，名为“跨越合并”，效果如图5.27所示。

3.选择A1：C6区域，然后单击功能区的“开始”→“跨越合并”，执行结果如图5.28所示。

图5.27 新菜单

图5.28 合并结果

如果此时按下组合键＜Ctrl+Z＞，则可以返回到如图5.27所示的状态。

如果需要经常使用此功能，那么应该将文件“疑难114 增强版跨越合并.xlam”安装在Excel中，随时都可以调用，而非每次使用前都双击打开“疑难114 增强版跨越合并.xlam”。

安装“疑难114 增强版跨越合并.xlam”的步骤如下。

1.按组合键＜Alt+T+I＞，弹出“加载宏”对话框，对话框界面如图5.29所示。

2.单击“浏览”按钮，在“浏览”对话框中找到文件“疑难114 增强版跨越合并.xlam”，然后双击文件返回“加载宏”对话框，此时插件已经安装完成，在“加载项”对话框中可以看到新装的插件名称和功能介绍，界面如图5.30所示。

图5.29 “加载宏”对话框

图5.30 安装插件后的“加载宏”对话框

3.单击“确定”按钮返回工作表界面，在“开始”选项卡中将会出现名为“跨越合并”的新菜单，而且菜单会永远出现在此处，随时可以调用。

知识扩展

1.Execl插件相当于游戏软件的外挂，它依附在Excel中，不能脱离Excel使用。

2.Excel 插件有多种开发方式，其中最简单的是使用 VBA 开发，作者编著的另一本 Excel图书《Excel VBA程序开发自学宝典（第3版）》讲解了大量的插件开发过程和基础理论，有兴趣者可以翻阅此书。

3.使用插件后只能撤销插件所执行的功能，之前所有操作都不能撤销，如对 A1：C1A 区域添加边框、修改字体颜色，然后调用插件执行合并，此时只能撤销合并这个步骤，前面的两个步骤是不能撤销的。

4.按组合键＜Alt+T+I＞的正确方式是先按Alt键，松开后再按T键，松开T键后再按I键，不能三键同时按，否则不能弹出“加载宏”对话框。

Excel 可否隐藏所有的错误值？

图 5.18 中由于部分职工的产量数据还没有输入，计算产量排名时就只能得到错误值。现要求在不修改公式的前提下隐藏所有错误值，要如何实现？

图5.18 显示错误值的公式

解题步骤

隐藏错误值最简单的手段是让字符与单元格的背景色一致，主要有三种方法：第一种方法是采用条件格式实现，步骤如下。

1.选择C2：C11区域，然后单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

2.单击“使用公式确定要设置格式的单元格”，然后在下方的公式栏输入“=iserror（C2）”，最后单击“格式”按钮，弹出“设置单元格格式”对话框，操作界面如图5.19所示。

3.打开“字体”选项卡，将字体颜色修改为白色，然后打开“填充”选项卡，将单元格的填充颜色也修改为白色，最后单击“确定”按钮保存设置，并返回工作表界面，图 5.20 为设置字体颜色界面。

图5.19 指定公式条件

图5.20 设置颜色

设置好条件与格式后，返回工作表可以看到图 5.21 所示的效果，其中原本为错误值的单元格已经显示为空白。

图5.21 添加条件格式后的效果

第二种方法是采用定位工具直接选中包含错误值的单元格，然后修改单元格的字体颜色与背景色，让二者统一。这种方法操作比较简单，但是通用性不强，当区域中有新的单元格产生错误值时必须再次定位、设置颜色，因此不再详细阐述操作过程。

第三种方法是设置单元格的数字格式达成需求，步骤如下。

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

2.单击分类项目表中的“自定义”，然后在右方的格式代码框中输入“[黑色]”，操作界面如图5.22所示。

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

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

4.在“开始”选项卡中为选区指定字体颜色为白色，此时选区中的错误值将自动隐藏起来，效果如图5.23所示。

图5.23 将字体颜色改为白色后的效果

知识扩展

1.本例所讲的3种方法都是通过修改字符的颜色来实现隐藏的，因此选中单元格后，在编辑栏仍然可以看到单元格的原值。如果要求编辑栏中也无法看到，那么需要选择单元格的“锁定”复选框，然后保护工作表。

2.本例用到的格式代码“[黑色]”表示让单元格中的文字显示为黑色，但是此代码对错误值无效。然后将单元格的字体颜色设置为白色，它对所有值都生效。由于格式代码的权限高于单元格的字体色，因此单元格的执行顺序是先应用字体颜色，将所有单元格的值都显示为白色，然后应用格式代码，让非错误值显示为黑色，而错误值则仍然显示为白色。

3.格式代码“[黑色]”仅对中文版Office有效，如果使用英文版Office，只能用英文的格式代码。