相册

工作表中存在重复值，是否可以删除重复值只保留唯一值呢？

例如，同一项数据出现了多次，只保留第一次出现的数据。

解题步骤

Excel 从 2007 版开始提供了删除重复项工具，它可以将单列重复或多列重复的数据删除，Excel 2003及以前的版本不带此功能。

以图 6.46 为例，要求姓名和学号同时重复时删除重复项，其中一项重复时不删除，操作步骤如下。

1.选择A1：C11区域。

2.单击功能区的“数据”→“删除重复项”，弹出“删除重复项”对话框。

3.在图6.47所示的对话框中，取消选择“成绩”复选框，表示只判断姓名和学号，成绩允许重复。

4.单击“确定”按钮后，Excel会弹出如图6.48所示的对话框，通知用户删除了多少个重复值。关闭对话框后可以看到图6.49所示的结果。

知识扩展

1.删除重复项和通过高级筛选工具提取唯一值有所不同。删除重复项的缺点是无法把结果显示在其他区域，优点是允许自由选择判断重复的条件，可以以单列的值为判断条件，也可以以多列的值为判断条件。

2.如果要求将重复值隐藏起来，便于以后在必要时恢复数据，那么应该采用辅助区域搭配筛选工具来完成。例如，在D1输入“辅助区域”，再在D2输入公式“=A2&B2”并向下填充，效果如图6.50所示。接着选择D1：A11区域，并单击功能区的“数据”→“高级”，弹出“高级筛选”对话框，在对话框中按图6.51所示的方式设置选项，当单击“确定”按钮后将得到图6.52所示的筛选结果。

图6.52中重复值已经处于隐藏状态，如果此时清除筛选可以还原所有数据。

图6.43中包含两条生产线的产量与不良品数量，现要求将大于1000的产量用红线标注出来，如何才能快捷完成？

解题步骤

条件格式无法将符合条件的单元格加圈，数据验证工具则可以实现，操作步骤如下。

1.选择C2：C13区域。

2.单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

3.将验证条件设置为整数，且小于或等于1000，设置界面如图6.44所示。

4.单击“确定”按钮返回工作表界面，然后单击功能区的“数据”→“数据验证”→“圈释无效数据”，此时大于1000的所有单元格都会自动被红圈圈住，效果如图6.45所示。

知识扩展

1.数据验证用于限制区域中只能输入符合条件的数据，对于已经在区域中输入的数据是无法限制的，但是可以通过“圈释无效数据”找出不符合条件的单元格。

在设置数据验证时，一定要设置为相反的条件。例如，本例的目标是标示大于1000的单元格，那么设置数据验证时就将“小于等于1000”作为条件。

2.“圈释无效数据”产生的红圈仅用于临时查看，保存文件后红圈会自动消失。

图6.38中包含70项成绩，要求找出所有高于90分的成绩，并将其加粗显示，是否可以一步完成？

解题步骤

启用条件格式可以让符合条件的单元格加粗显示，具体操作步骤如下。

1.选择B2：H11区域。

2.单击功能区的“开始”→“条件格式”→“突出显示单元格规则”→“大于”。

3.在图6.39所示的“大于”对话框中将位于左方的参考标准值修改为“90”，然后从右方的格式列表中选择“自定义格式”，弹出“设置单元格格式”对话框。

4.打开“字体”选项卡，在“字形”列表中选择“加粗”，然后单击“确定”按钮返回“大于”对话框。最后在“大于”对话框中单击“确定”按钮应用条件格式。图 6.40 是设置条件的界面，而图6.41则是应用条件格式后的效果。

知识扩展

1.将数字加粗其实并不会让目标很明显地区分开来，因为加粗与不加粗的差异并不会太大，反而修改字体能突显两者的反差，如将字体修改为Arial Black。在图6.42中，A1的字符是宋体，B1的字符是Arial Black体，两者差异很明显，不过条件格式工具不允许以字体或字号作为格式。

2.通过条件格式将部分单元格加粗，尽管看起来部分单元格显示为加粗效果而其他部分单元格未加粗，事实上它们的单元格格式是一致的，在按格式查找时无法找到格式为“加粗”的单元格。换言之，通过条件格式创建的格式和选择单元格后手工添加的格式有着本质的区别。

3.由于任意文本都大于数值，因此在对数值添加条件格式前，一定不能选择包含文本的区域。例如，本例中选择A1：H11区域后再创建条件格式，那么行标题和列标题都会被加粗显示，它们都符合“大于90”这个条件。

在图 6.32 中，部分人员获得多次冠军，要求通过背景色将多次夺冠者所在行标示出来，如何才能实现？

解题步骤

用背景颜色标示目标值只能使用条件格式，不过由于要求目标所在行都要标示，而非只标示目标单元格，因此需要对整个区域添加条件格式，而且要通过公式来确认单元格是否重复，具体操作步骤如下。

1.选择A2：C13区域，然后单击功能区的“开始”→“条件格式”→“新建规则”。

2.从规则类型列表中选择“使用公式确定要设置格式的单元格”，然后在下方的文本框中输入公式“=COUNTIF（$B$2：$B$13，$B2）＞1”，操作界面如图6.33所示。

3.单击“格式”按钮，弹出“设置单元格格式”对话框，打开“填充”选项卡，然后选择红色并单击“确定”按钮，图6.34即为格式设置界面。

4.设置好条件与格式后，关闭对话框，返回工作表界面，此时选区会自动应用条件格式，最终效果如图6.35所示。

知识扩展

1.公式“=COUNTIF（$B$2：$B$13，$B2）＞1”的含义是B2：B13区域中任意值的出现次数大于1，就符合条件格式的条件，然后自动应用指定的格式。其中“$B$2：$B$13”必须用绝对引用，而“$B2”则只能锁定列标、不能锁定行号。

2.条件格式中自带一个“重复值”子菜单，单击即可标示完成，不需要使用公式。它的缺点是只能标示一列，不能达成本例的效果——B2符合条件时，将A2：C2都标示颜色。

3.条件格式自带的标示重复值使用步骤为：选择B2：B13，然后单击功能区的“开始”→“条件格式”→“突出显示单元格规则”→“重复值”，在弹出的“重复值”对话框中不做任何修改，直接单击“确定”按钮应用条件格式即可操作完成。图6.36是条件与格式设置界面，图6.37则是条件与格式的应用效果。

图6.29中包含姓名、性别、成绩和奖学金，要求将平均成绩高于80分的男生相关的数据提取出来并放到F列去，如何才能快捷完成呢？

解题步骤

有很多方法提取符合多个条件的数据，其中最快捷的方法是高级筛选，具体操作步骤如下。

1.在F1、G1、F2和G2四个单元格分别输入“性别”、“成绩”、“男”及“＞80”。

2.选择A1：D15区域，然后单击功能区的“数据”→“高级”，弹出“高级筛选”对话框。

3.选中“将筛选结果复制到其他位置”单选按钮，然后将“条件区域”设置为 F1：G2，将“复制到”设置为F5，最后单击“确定”按钮执行筛选。图6.30是设置界面，而图6.31是筛选结果。

知识扩展

1.与筛选相比，高级筛选的优势在于两点：其一是筛选条件放在数据源之外，便于了解当前筛选结果基于何种条件，本例中F1：G2即为筛选条件，而普通筛选要了解其条件需要花费数倍的时间。其二是高级筛选的结果既可以显示在原区域也可以显示在其他区域，而普通筛选只能将结果保存在原区域。

2.如果数据源中存在公式，利用高级筛选将筛选结果存放在其他区域时，筛选结果将只显示筛选出来的值，不显示公式。

图 6.24 中部分人员姓名有黄色背景，部分单元格有灰色背景，部分单元格没有背景颜色。现要求只显示有黄色背景的人员资料，如何操作才能快捷地实现需求呢？

解题步骤

从Excel 2007开始，所有高版本的Excel都支持按单元格背景色执行筛选，而Excel 2003并不支持此功能。具体操作步骤如下。

1.选择B1单元格。

2.单击功能区的“开始”→“排序和筛选”→“筛选”，此时工作表处于图6.25所示的状态。

3.单击B1单元格右下角的倒三角按钮，从而弹出筛选相关的选项对话框。

4.单击“按颜色筛选”，在二级菜单中会显示黄色、灰色和无填充3个选项，单击其中的黄色，此时工作表中黄色背景的姓名所在行将自动筛选出来，其他数据则自动隐藏，操作界面如图6.26所示，效果如图6.27所示。

知识扩展

1.筛选的本质是隐藏非目标数据，便于查看和统计。筛选后选择单元格、设置格式、删除单元格的值等操作都只对筛选出来的单元格生效，而使用公式汇总时则可以自由选择，既可以只汇总符合筛选条件的单元格的值，也可以汇总所有值。

2.观察工作表数据是否处于筛选状态，最简单的方法是查看行号的颜色。当行号显示为蓝色时表示工作表处于筛选状态。

3.一个工作表只允许对一个区域执行筛选。假设工作表中有两个不连续的区域，在第一个区域处于筛选状态时要对第二个区域执行筛选，那么只能先取消第一个区域的筛选状态。

4.当单元格只设置了背景色时，“按颜色筛选”二级菜单中会出现所有单元格背景颜色；当单元格只设置了字体颜色时，“按颜色筛选”二级菜单中会出现所有单元格字体颜色；如果字体和单元格背景都设置了多种颜色，“按颜色筛选”二级菜单中会分别罗列出单元格颜色和字体颜色，效果如图6.28所示。

图 6.18 是应聘者信息表，其中应聘者的学历包含小学、初中、高中和大学。现要求按学历对它们降序排列，如何才能实现呢？

解题步骤

要让Excel按学历排序，其前提是自定义一个学历序列，然后再按自定义序列排序即可，具体操作步骤如下。

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

2.单击左方的“高级”项，然后在右方将滚动条下拉，直到看到“编辑自定义列表”时停止，单击“编辑自定义列表”按钮即可弹出“自定义序列”对话框。6.19是操作界面。

3.在“输入序列”下方的文本框中分别输入“大学”、“高中”、“初中”、“小学”，然后单击“添加”按钮，设置界面如图6.20所示。

4.选择B1：B11区域，然后单击功能区的“开始”→“排序和筛选”→“自定义排序”，此时Excel会弹出“排序提醒”对话框，不做任何修改，单击“排序”按钮即可。

5.在“排序”对话框中将“主要关键字”修改为“学历”，将“次序”设置为“自定义序列”，此时会弹出“自定义序列”对话框。图6.21是排序条件设置界面，而图6.22则是调用自定义序列的界面。

6.选择“学历”序列，然后单击“确定”按钮，返回“排序”对话框。

7.单击“排序”对话框中的“确定”按钮执行排序，排序结果如图6.23所示。

知识扩展

1.Excel内置的排序条件是数值大小、笔画多少、拼音顺序，以及按颜色排序、按条件格式产生的图标排序，当需要按临时指定的其他条件排序时，必须先定义序列，然后调用序列来排序。在指定序列时要注意顺序，因为按自定义序列排序时不能选择升序或降序。

本例中要求按学历降序排序，因此在定义序列时必须大学在前、小学在后。

2.按自定义的序列排序时，排序对象的值必须等于自定义序列中的值，而不能是包含关系的。例如，本例中自定义序列为大学→高中→初中→小学，那么 B2：B11 区域的值也只能是这 4个词语中的一个，而不能是“衡阳高中”或“武汉大学”这种包含多余文字的词语，否则无法排序。

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

解题步骤

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

1.选择A1：D12区域。

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

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

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

知识扩展

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

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

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

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

解题步骤

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

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

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

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

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

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

知识扩展

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

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

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

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

解题步骤

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

1.选择A1：E20区域。

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

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

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

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

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

知识扩展

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

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