自定义条件格式规则

使用公式设置条件格式规则，能够使条件格式的应用更加多样化。

示例24-1 输入内容自动添加边框

图24-17展示了某公司客户信息表的部分内容，只要在A列输入内容，Excel会自动对这一行的A~E列区域添加边框。当A列数据清除后，边框自动消失。

图 24-17　输入内容自动添加边框

操作步骤如下。

步骤1选中需要输入数据的单元格区域，如A2:E10单元格区域，依次单击【开始】→【条件格式】→【新建规则】命令，打开【新建格式规则】对话框。

步骤2在【选择规则类型】列表中选中【使用公式确定要设置条件的单元格】选项，然后在【为符合此公式的值设置格式】编辑框中输入以下公式：=$A2<>''

步骤3单击【格式】按钮，在弹出的【设置单元格格式】对话框中切换到【边框】选项卡，选择一种边框颜色，如“蓝色，个性1”,单击【外边框】按钮，最后依次单击【确定】按钮关闭对话框，如图24-18所示。

图 24-18　设置边框效果

在条件格式中使用函数公式时，如果选中的是一个单元格区域，必须根据活动单元格作为参照来编写公式，设置完成后，该规则会应用到所选中范围的全部单元格。如果选中的是多行多列的区域，则需要同时考虑行方向和列方向的引用方式。

本例中，活动单元格为A2,条件格式的公式为“=$A2◇''”,A2:E10区域中每一个单元格都根据当前行的A列单元格是否为空白来判断是否应用带边框的格式。

如果需要在条件格式的公式中固定引用某一行或某一列时，可以理解为在所选区域的活动单元格中输入公式，然后将公式复制到所选区域。

示例24-2 突出显示销量最高的产品记录

图24-19展示了某公司近期服装销售汇总的部分内容，使用条件格式能够自动突出显示销量最高的产品记录。操作步骤如下。

图 24-19　突出显示销量最高的产品记录

步骤1选中A2:M13单元格区域，依次单击【开始】→【条件格式】→【新建规则】命令，打开【新建格式规则】对话框。

步骤2在【选择规则类型】列表中选中【使用公式确定要设置格式的单元格】选项，然后在【为符合此公式的值设置格式】编辑框中输入以下公式：=SM2=MAX($M$2:$M$13)

步骤3单击【格式】按钮，在弹出的【设置单元格格式】对话框中切换到【填充】选项卡，选择一种填充颜色，最后依次单击【确定】按钮关闭对话框。

因为每条记录都用当前行的M列数据与M列固定区域的最大值比较，所以$M$2:$M$13使用了绝对引用，而$M2则是列方向使用绝对引用，行方向使用相对引用。

示例24-3 劳动合同到期提醒

图24-20展示了某公司劳动合同表的部分内容，通过设置条件格式，使距今30天内的合同到期日以浅蓝色填充突出显示，距今7天内的合同到期以橙色填充突出显示。

图 24-20　劳动合同到期提醒

步 骤 1 选中N2:N12 单元格区域，依次单击【开始】→【条件格式】→【新建规则】命令，打开【新建格式规则】对话框。

步 骤 2 在【选择规则类型】列表中选中【使用公式确定要设置格式的单元格】选项，然后在【为符合此公式的值设置格式】编辑框中输入以下公式：

=AND($N2>=TODAY(),$N2-TODAY()<30)

步 骤 3单击【格式】按钮，在【设置单元格格式】对话框的【填充】选项卡下选择【浅蓝色】,最后依次单击【确定】按钮关闭对话框。

步骤4重复步骤1、步骤2,在【为符合此公式的值设置格式】编辑框中输入以下公式：

=AND($N2>=TODAY(),SN2-TODAY()<7)

步骤5重复步骤3,在【填充】选项卡下选择【橙色】。

本例第一个规则的公式中，分别使用两个条件对N列当前行单元格中的日期进行判断。

第一个条件$N2>=TODAY0,用于判断目标单元格中的合同到期日期是否大于等于当前系统日期。

第二个条件$N2-TODAY(<30,用于判断当前系统日期是否比目标单元格中的合同到期日期早30天之内。

公式中用AND函数判断两个条件是否同时成立，也可以将两个条件相乘来表示。

=(§N2>=TODAY())*(SN2-TODAY()<30)

即条件1乘以条件2,如果两个条件同时符合，则相当于TRUE*TRUE,结果为1,否则结果为0。

第二个条件格式规则的公式原理与之相同，不再赘述。这两个条件格式必须按上述顺序添加，否则无法达到所需效果。

示例24-4 突出显示指定名次的销售业绩

图24-21展示了某营销公司销售汇总表的部分内容，使用条件格式，能够根据指定的名次在符合条件的单元格内添加图标集。

操作步骤如下。

步①选中E2:E14单元格区域，依次单击【开始】→【条件格式】→【新建规则】命令，打开【新建格式规则】对话框。

步②在【选择规则类型】列表中选中【基于各自值设置所有单元格的格式】选项，在【编辑规则说明】区域中单击【格式样式】下拉按钮，在下拉列表中选择【图标集】。

步③在【根据以下规则显示各个图标】区域中进行如下设置。

(1)将第一个图标样式设置为“红旗”,【类型】设置为【公式】,设置【当值是】为【>=】,在【值】编辑框中输入以下公式：

=LARGE($E$2:$E$14,$H$2)

(2)依次单击第二个和第三个图标右侧的下拉按钮，在样式列表中选择【无单元格图标】选项，最后单击【确定】按钮，如图24-22所示。

图 24-22　新建格式规则

LARGE函数根据H2单元格中的数值，计算出E2:E14单元格区域中的第k个最大值。然后用所选区域单元格中的数值进行比较，如果大于或等于公式结果，就在单元格中显示出“红旗”图标。调整H2单元格中的数值时，公式结果会随之变化，条件格式的效果也会实时更新。

在色阶、数据条和图标集的条件中使用函数公式时，仅支持单元格的绝对引用方式，而不允许使用相对引用。也就是所选区域的每一个单元格只能使用公式返回的同一结果作为判断条件，否则会弹出错误提示，如图24-23所示。

图 24-23 Excel提示对话框

示例24-5 使用条件格式标记数据增减

将条件格式与自定义数字格式相结合，能够完成一些更加个性化的显示效果。图 24-24 展示了某公司各下属单位不同月份的销售数据，需要将这些数据与第 13 行中的上年同期平均值进行对比。

图 24-24　使用条件格式标记数据增减

操作步骤如下。

步 骤 1 选中B2:M12 单元格区域，依次单击【开始】→【条件格式】→【管理规则】命令，打开【条件格式规则管理器】对话框。

步 骤 2 单击【新建规则】按钮，在弹出的【新建格式规则】对话框中选中【使用公式确定要设置格式的单元格】选项，然后在【为符合此公式的值设置格式】编辑框中输入以下公式：=B2>B$13

步 骤 3 单击【格式】按钮，在弹出的【设置单元格格式】对话框中切换到【数字】选项卡下，单击【分类】列表中的【自定义】，然后在右侧的类型文本框中输入以下格式代码，依次单击【确定】按钮返回【条件格式规则管理器】对话框，如图 24-25 所示。

[ 红色 ] ↑ 0.0

图 24-25　自定义数字格式

步 骤 4 再次单击【新建规则】按钮，参照步骤②，在【为符合此公式的值设置格式】编辑框中

输入以下公式：=B<b$13 <='' span=''>

步 骤 5 单击【格式】按钮打开【设置单元格格式】对话框，参照步骤③，输入以下自定义数

字格式代码，依次单击【确定】按钮关闭对话框。