分类：数据分析篇

公司每月都会为过生日的员工发放小蛋糕，现要求标示出图 6.61 所示的表中下个月将要过生日的职工。

解题步骤

Excel的条件格式提供了诸多内置的日期条件，单击即可标示下月日期。不过使用内置条件只能标示日期所在单元格，若要将整行数据都进行标示，则需要使用公式自定义条件。本例同时讲解两种操作过程。

方法一的步骤如下。

1.选择D2：D16区域.

2.单击功能区的“开始”→“条件格式”→“突出显示单元格规则”→“发生日期”，弹出“发生日期”对话框。

3.在对话框中将日期设置为“下个月”，格式则保持内置格式不变，此时可以从工作表中预览条件格式的效果，设置界面和预览效果如图6.62所示。

4.单击“确定”按钮应用设置好的条件格式。

备注：笔者编写此案例时是 2015 年 11 月，因此工作表中标示的 3 个下月日期都是 2015年12月。

方法二的步骤如下。

1.选择D2：D16区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.将规则类型设置为“使用公式确定要设置格式的单元格”，然后在下方在的文本框中输入以下公式：

设置界面如图6.63所示。

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置。图6.64是应用条件格式后的效果。

知识扩展

1.方法一仅标示选区中符合条件的日期所在单元格，方法二是可以标示选区中符合条件的日期所在行，两者效果差异较大，操作复杂度也差异较大。

2.公式“=AND（$D2＞EOMONTH（TODAY（），0），$D2＜=EOMONTH（TODAY（），1））”的含义是：如果D列的日期大于本月最后一天，而且小于等于下月最后一天，那么返回True。

其中TODAY函数用于产生今日的日期，“EOMONTH（TODAY（），0）”则用于生成本月最后一天的日期，“EOMONTH（TODAY（），1）”用于生成下月最后一天的日期。参数0和1分别代表今天之后的0个月或1月，此参数允许使用负数。例如，赋值为-2表示返回2个月前的当月最后一天。

3.在设置公式时，引用对象一定要用“$D2”，不能使用“D2”或“$D$2”。

图6.57中包含10人的各季度销量数据，要求将每一行的最小值标示出来，如何才能一次性标示完成？

解题步骤

与标示两列数据的相同项一样，标示区域中的最小值要使用公式来确定目标，具体操作步骤如下。

1.选择B2：E11区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.选择“使用公式确定要设置格式的单元格”，然后在下方的文件框中输入公式“=B2=MIN（$B2：$E2）”，操作界面如图6.58所示。

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置。图6.59为格式设置界面，图6.60则是应用条件格式后的效果。

知识扩展

1.公式“=B2=MIN（$B2：$E2）”表示当任意单元格的值等于它所在行的最小值时就符合条件，立即应用后面设置的格式。由于 MIN（$B2：$E2）总是计算单行的最小值，因此标示单元格时会每一行标示一次。

2.如果要标示B2：E11区域中的最小值，那么应该将公式修改为“=B2=MIN（$B$2：$E$11）”。

3.通过条件格式标示目标值会一直保留下来，重新打开工作簿后仍然能看到条件格式的显示结果，而采用数据验证的圆圈标示目标则只能用于临时查看，重启Excel后会自动消失。

图6.53是包含去年与今年成绩前10名的名单，现要求将两列名单中的相同值用红色标示出来。

解题步骤

将目标单元格标示颜色的最佳工具是条件格式，具体操作步骤如下。

1.选择A2：C11区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.选择“使用公式确定要设置格式的单元格”，然后在下方的文本框中输入公式“=AND（ISTEXT（A2），COUNTIF（$A$2：$C$11，A2）＞1）”，设置界面如图6.54所示。

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置，图6.55为设置界面。

5.单击“确定”按钮返回工作表界面，此时工作表中A列与C列的相同值所在单元格都会显示为红色背景，而成绩相同时却不会标示出来，效果如图6.56所示。

知识扩展

1.本例中选择A2：C11后再设置条件格式是因为去年与今年的学生姓名都刚好容纳于此区域中，尽管选择A2：D11区域再设置条件格式也能实现同样的功能，但是设置条件格式时应该遵循一个规则：由于条件格式本身比较耗内存，因此应该对尽可能小的区域设置条件格式，非必要的区域应该忽略。

2.公式“=AND（ISTEXT（A2），COUNTIF（$A$2：$C$11，A2）＞1）”表示如果A2：C11区域中某个单元格是文本，而且在这个区域中出现的次数大于1次，那么它就满足条件。

表达式“ISTEXT（A2）”用于判断单元格中的值是否为文本，“COUNTIF（$A$2：$C$11，A2）＞1”用于判断单元格的值出现的次数，最后用AND函数来判断是否两个条件同时满足，只有同时满足两个条件才添加背景色，从而忽略重复的成绩。

3.本例还有另一种操作方法：分两次设置条件格式，一次对 A2：A11 区域设置条件格式，一 次 对 C2：C11 区 域 设 置 条 件 格 式。对 A2：C11 区 域 设 置 条 件 格 式 时，用“=COUNTIF（$C$2：$C$11，A2）＞0” 作 为 条 件，对 C2：C11 区 域 设 置 条 件 时，用“=COUNTIF（$A$2：$A$11，C2）＞0”作为条件。

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

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

解题步骤

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个词语中的一个，而不能是“衡阳高中”或“武汉大学”这种包含多余文字的词语，否则无法排序。