excel条件格式中使用函数与公式的方法

使用Excel的条件格式功能，可以根据单元格中的内容应用指定的格式，改变某些具指定特征数据的显示效果，使用户能够直观地查看和分析数据、发现关键问题。使用函数与公式作为条件格式的规则，能够实现更加个性化的数据展示需求。

条件格式中使用函数与公式的方法

在条件格式中，可设置的格式包括数字、字体、边框和填充颜色等。Excel内置的条件格式规则包括“突出显示单元格规则”，“最前/最后规则”，“数据条”，“色阶”和“图标集”能够满足大多数用户的应用需求。

在条件格式中使用函数与公式时，如果公式返回的结果为TRUE或不等于0的任意数值，则应用预先设置的格式效果，如果公式返回的结果为FALSE或数值0，则不会应用预先设置的格式效果。

例 突出显示低于计划完成额的数据

下图为某公司上半年销售记录表的部分内容。需要根据实际完成额和计划完成额进行判断，突出显示低于计划完成额的数据。

具体操作步骤如下

1、要选中C2:C7单元格区域，在开始]选项卡中单击[条件格式]下拉按钮，在弹出的下拉菜单中选择[新建规则]命令。

2、在弹出的[新建格式规则]对话框中，选择[选择规则类型]列表中的[使用公式确定要设置格式的单元格]选项然后在[为符合此公式的值设置格式] 编辑框中输入以下公式。

=C2<B2

3、单击[格式]按钮，弹出[设置单元格格式] 对话。切换到[填充]选项卡选择一种背景色，如橙色，单击[确定]按钮，返回[新建格式规则]对话框再次单击[确定]按钮关闭对话框。

使用公式“=C2<B2”判断C2单元格的数值是否小于B2单元格的数值，返回逻辑值TRUE或 FALSE，Excel再以此作为条件格式的执行规则。

设置完成后，所选区域中小于计划完成额的数据全部以指定的背景色突出显示。

选择正确的引用方式

在条件格式中使用函数与公式时，如果选中的是一个单元格区域，可以以活动单元格作为参照编写公式，设置完成后，该规则会应用到所选中范围的全部单元格。

如果需要在公式中固定引用某一行或某一列，或者固定引用某个单元格的数据，需要别注意选择不同的引用方式。在条件格式的公式中选择不同引用方式时，可以理解为在所选区域的活动单元格中输入公式，然后将公式复制到所选范围内。

如果选中的是一列多行的单元格区域，需要注意活动单元格中的公式在向下复制时引用范围的变化，也就是行方向的引用方式的变化。

如果选中的是一行多列的单元格区域，需要注意活动单元格中的公式在向右复制时引用范围的变化，也就是列方向的引用方式的变化。

如果选中的是多行多列的单元格区域，需要注意活动单元格中的公式在向下、向右复制时引用范围的变化，也就是要同时考虑行方向和列方向的引用方式的变化。

例 自动标记收益率最高的债券

如下图所示，需要根据D列的债券到期收益率，整行突出显示收益率最高的债券记录。

具体操作步骤如下

1、选中A2:E11单元格区域，依次选择[开始]→[条件格式]→[新建规则]命令，打开[新建格式规则]对话框。

2、在弹出的[新建格式规则] 对话中，选择[选择规则类型]列表中的[使用公式确定要设置格式的单元格]选项，然后在[为符合此公式的值设置格式]编辑框中输入以下公式。

=$D2=MAX($D$2:$D$11)

3、单击[格式]按钮，打开[设置单元格格式]对话框。切换到[字体] 选项卡在[字形]列表框中选择[加粗]选项，单击[颜色]下拉按钮，在弹出的主题颜色面板中选择字体颜色为深红色。然后切换到[填充]选项卡，选择一种背景色，如橙色，单击[确定]按钮，返回[新建格式规则] 对话框，再次单击[确定]按钮，关闭对话框完成设置，如图30-6所示。

本例中条件格式设置的公式如下。

=$D2=MAX($D$2:$D$11)

公式先使用“MAX($D$2:$D$11)”部分计算出D列的收益率最大值，然后与D2单元格中的数值进行比较，判断该单元格中的数值是否等于该列的最大值。

因为事先选中的是一个多行多列的单元格区域，并且每一行中都要以该行D列的到期收益率作为比对的基础，所以$D2使用列绝对引用。而每一行每一列都要以$D$2:$D$11单元格区域的最大值作为判断标准，所以行、列都使用了绝对引用方式。

提示：使用条件格式时，如果工作表中有多个符合条件的记录，这些记录都将应用预先设置的格式效果。