条件格式 – 第2页

Excel 2019利用条件格式快速比较不同区域的数值

在实际工作中，用户需要对比的数据可能不是位置一一相对应的。如果工作表中两列编号的排列顺序并不相同，要把两列不匹配的编号一一标记出来，条件格式的设置要相对复杂一些。

通过以下操作步骤，可以快速比较不同区域的数值。

STEP01：打开“匹配数据.xlsx”工作簿，选择A2:B20单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项打开如图5-72所示的“新建格式规则”对话框。在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”文本框中输入公式，这里输入“=OR(EXACT(A2,$B$2:$B$20))=FALSE”，然后单击“格式”按钮打开“设置单元格格式”对话框，如图5-72所示。

STEP02：切换至“填充”选项卡，单击“图案颜色”选择框右侧的下拉按钮，在展开的下拉列表中选择“蓝色”；单击“图案样式”选择框右侧的下拉按钮，在展开的下拉列表中选择“75%灰色”选项，然后单击“确定”按钮完成单元格格式的设置，如图5-73所示。

图5-72　输入公式

图5-73　设置填充格式

STEP03：在“新建格式规则”对话框中单击“确定”按钮返回工作表页面，此时的工作表如图5-74所示，编号一中所有与编号二不匹配的内容都会被标记出来。

图5-74　查询不匹配的内容

在上述公式中，EXACT函数用于比较两个文本字符串是否完全相同，如完全相同则返回TRUE，否则返回FALSE。另外，如果在公式中输入“=NOT(OR(A2=$B$2:$B$20))”，也可以实现相同的操作。

Excel 2019利用条件格式准确查数据

例如，某高校学生有12000多人，在学生电子档案表中如果要查找某一个学生的具体情况，多数用户会选择拖动鼠标进行查找或使用查找命令，但这种方法既浪费时间，工作效率也不高。其实使用条件格式也可以准确查找到数据。接下来通过实例来说明具体的操作步骤。

STEP01：选择A5:F18单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项，打开如图5-67所示的“新建格式规则”对话框。在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”文本框中输入公式，例如这里输入“=$A5=$B$3”，然后单击“格式”按钮打开“设置单元格格式”对话框。

STEP02：切换至“填充”选项卡，单击“图案颜色”选择框右侧的下拉按钮，在展开的下拉列表中选择“红色”；单击“图案样式”选择框右侧的下拉按钮，在展开的下拉列表中选择“75%灰色”选项，然后单击“确定”按钮完成单元格格式的设置，如图5-68所示。

STEP03：在“新建格式规则”对话框中单击“确定”按钮返回工作表页面，此时在精确查找所对应的B3单元格中输入“王义双”，按“Enter”键返回，效果如图5-69所示。

如果想实现模糊查找，可以在如图5-67所示的“新建格式规则”对话框中输入模糊查找的公式“=LEFT($A5,1)=$C$3”，设置的单元格格式保持不变，单击“确定”按钮返回工作表即可，如图5-70所示。此时，在C3单元格中输入“王”字时，工作表查找效果如图5-71所示。

图5-67　选择规则类型

图5-68　设置填充格式

图5-69　精确查找效果图

图5-70　模糊查找公式

图5-71　模糊查找效果

Excel 2019利用条件格式突出显示重复数据

例如，在高校招生时，会有同名的学生。现在工作人员需要把同名学生的成绩突出显示出来，如图5-63所示。此时可以使用“条件格式”功能来实现突出显示重复数据的操作。

其具体操作步骤如下。

STEP01：选择A2:G17单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项，打开“新建格式规则”对话框。在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”文本框中输入公式，例如这里输入“=COUNTIF($A$2:$A$17,$A2)>1”，然后单击“格式”按钮，如图5-64所示。

图5-63　数据工作表

图5-64　选择规则类型

STEP02：打开“设置单元格格式”对话框，切换至“填充”选项卡，单击“图案颜色”选择框右侧的下拉按钮，在展开的下拉列表中选择“绿色”；单击“图案样式”选择框右侧的下拉按钮，在展开的下拉列表中选择“6.25%灰色”选项，然后单击“确定”按钮完成单元格格式的设置，如图5-65所示。

STEP03：在“新建格式规则”对话框中单击“确定”按钮返回工作表页面，此时的工作表如图5-66所示。

图5-65　设置单元格格式

图5-66　突出显示同名的学生成绩

Excel 2019利用条件格式限制输入数据

前面介绍过，在对Excel进行数据输入时，为了确保单元格数据输入正确，可以使用数据有效性来限制输入数据的范围。其实也可以用条件格式来达到该目的。例如为超过一定数值的单元格自动加上颜色，比如一个单元格的数值上限设置为100，如果输入101，这个单元格就变成红色。

使用条件格式限制输入数据的具体操作步骤如下。

STEP01：选择B2:G14单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“突出显示单元格规则”选项，然后在展开的级联列表中选择“大于”选项，如图5-60所示。

图5-60　选择条件格式

STEP02：在打开的“大于”对话框中设置大于数值为“100”，设置显示为“绿填充色深绿色文本”，最后单击“确定”按钮返回工作表，如图5-61所示。

图5-61　设置数值条件

STEP03：如果在选定区域内输入了上限大于100的数值，则单元格就会被绿填充色深绿色文本填充，如图5-62所示。

图5-62　条件格式设置效果

Excel 2019设置唯一值条件格式的操作方法

仅对唯一值或重复值设置格式可以在单元格中查找唯一值或重复的值。打开“期末成绩统计表.xlsx”工作簿，以查找大学英语课程中的唯一值为例。设置唯一值条件格式的具体操作步骤如下。

STEP01：选择C2:C14单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项，打开如图5-57所示的“新建格式规则”对话框。在“选择规则类型”列表框中选择“仅对唯一值或重复值设置格式”选项，并在“编辑规则说明”列表框中为选定范围中“唯一”的数值设置格式，然后单击“格式”按钮，打开“设置单元格格式”对话框，如图5-57所示。

图5-57　选择规则类型

STEP02：切换至“填充”选项卡，在“背景色”列表框中选择“浅蓝色”选项，然后单击“确定”按钮返回“新建格式规则”对话框，如图5-58所示。再次单击“确定”按钮返回工作表，为选定范围中的唯一值设置的格式效果如图5-59所示。

图5-58　设置填充颜色

图5-59　仅对唯一值设置格式效果

Excel 2019设置标准偏差条件格式

设置标准偏差条件格式可以在单元格区域中查找高于或低于平均值或标准偏差的值。

打开“期末成绩统计表.xlsx”工作簿，以查找高数课程中高于平均分的学生为例。设置高于平均值的数据设置格式的具体操作步骤如下。

STEP01：选择B2:B14单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项，如图5-53所示。打开“新建格式规则”对话框。

STEP02：在“选择规则类型”列表框中选择“仅对高于或低于平均值的数值设置格式”选项，并在“编辑规则说明”列表框中为“高于”选定范围的平均值设置格式，然后单击“格式”按钮打开“设置单元格格式”对话框，如图5-54所示。

图5-53　选择“新建规则”选项

图5-54　选择规则类型

STEP03：切换至“字体”选项卡，在“字形”列表框中选择“加粗倾斜”选项，设置字体颜色为“黄色”，然后单击“确定”按钮返回“新建格式规则”对话框，如图5-55所示。最后单击“确定”按钮返回工作表，为高于选定范围的平均值设置的格式效果如图5-56所示。

图5-55　设置字体格式

图5-56　为高于选定范围的平均值设置的格式效果

Excel 2019添加和更改条件格式操作图解

如果在工作表中应用了条件格式，则可以一目了然地识别工作表一系列数值中存在的差异。因此条件格式可以让用户更清晰地看到数据变化的幅度。如果用户想添加、更改或清除条件格式，则可以执行以下操作。

STEP01：打开“期中学生成绩统计表.xlsx”工作簿，选择A3:A23单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项，如图5-34所示。

STEP02：打开如图5-35所示的“新建格式规则”对话框。在对话框中可以根据实际需求对新建格式规则的各个选项进行设置。这里在“选择规则类型”列表框中选择“基于各自值设置所有单元格的格式”选项，然后在“编辑规则说明”列表框中选择“格式样式”为“双色刻度”，右侧的颜色选择框设置为“水绿色”，右侧的颜色选择框设置为“浅黄色”，最后单击“确定”按钮即可应用到工作表中，如图5-35所示。最终效果如图5-36所示。