Excel技巧应用篇：在条件格式中使用公式

在文章（Excel技巧应用篇：格式工具的使用技巧）介绍了条件格式中系统预设的规则，今天介绍如何在条件格式中使用公式创建新的规则，从而更加充分有效地利用条件格式工具。

首先，要了解以下两点在条件格式中使用公式的重要规则：

第一，公式的返回值必须是逻辑值TRUE或者FALSE，如果返回的是数字，数字0看作FALSE，非0的数字看作TRUE。当返回值为TRUE时，执行条件格式；为FALSE时，不执行条件格式。

第二，当公式中包含单元格引用时，有绝对引用和相对引用的区别，其规则与在工作表单元格中使用公式相同。

我们举一个例子说明。

首先选择单元格A4至D15，点击条件格式菜单中的“新建规则”。

在弹出的“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”，并在“编辑规则说明”处键入公式“=$A4=$A$1”，该公式表示当月份与单元格A1显示的月份一致时返回TRUE，则运用设置的格式。

因为活动单元格即光标在A4，所以公式中引用了A4；单元格A1是固定不变的，所以$A$1用了绝对引用；每行有四个数据占据四个不同列，但都根据A列与单元格A1比较的结果判断是否运用条件格式，而不同月份的行号会变化，所以使用混合引用$A4，锁列不锁行。

然后点击“格式”按钮，在“设置单元格格式中”将填充色涂成黄色。

我们来看看效果，在A1单元格选择月份后，相应月份的数据涂成了黄色。

接下来，再看几个在条件格式中使用公式的例子。

1、标记最大值和最小值

选择单元格区域C2至C13，然后打开“新建格式规则”对话框，键入公式“=C2=MAX(C$2:C$13)”，用黄色标记最大值。

选择单元格区域C2至C13，重新打开“新建格式规则”对话框，键入公式“=C2=MIN($C$2:$C$13)”，用蓝色标记最小值。

2、隔行设置填充色

选择单元格A2至D14，然后打开“新建格式规则”对话框，键入公式“=MOD(ROW(),2)”。

ROW函数返回当前单元格的行号，行号除以2，整除时，MOD函数返回0，相当于逻辑值FALSE；不能整除返回1，相当于逻辑值TRUE。所以本公式是给奇数行填充黄色。

3、生日提醒

选择单元格B3至B9，打开“新建格式规则”对话框，键入公式“=DATE(YEAR(TODAY()),MONTH(B3),DAY(B3))=TODAY()”。

DATE函数生成一个新日期，年份等于今天所在年份，月份和日等于员工生日的月和日，如果新日期等于今天，则涂成红色提醒今天是该员工的生日。

4、制作甘特图

工作中经常会制作甘特图跟进活动进展，用条件格式也可以制作甘特图。

首先制作数据表格，表头的数字1-30实际是日期6/1-6/30，在“设置单元格格式”对话框中，选择“自定义”，然后在右侧键入格式代码“d”，这样单元格中就只显示日期中的“日”，但只是改变了显示效果，单元格中存储的仍然是日期。（关于数字的显示格式，请参阅关于Excel的数字自定义选项）

选择区域E3至AH8，打开“新建格式规则”对话框，键入公式“=(E$2>=$B3)*(E$2<=$D3)”，点击“格式”按钮，设置填充色为淡紫色。

(E$2>=$B3)表示表头日期大于等于开始日期，(E$2<=$D3)表示表头日期小于等于结束日期，中间用“*”表示一个“与”的关系，即必须同时满足两个条件，结果才为TRUE。因为，只要一个条件为FALSE，乘法结果为0。

刚才的淡紫色表示活动覆盖的日期，我们还要加上一个进度条，表示已经完成的日期。选择区域E3至AH8，打开“新建格式规则”对话框，键入公式“=(E$2>=$B3)*(E$2<=$D3)*(E$2<=TODAY())”，然后点击“格式”按钮，设置填充色为紫色。

公式在上一个公式的基础上增加了一个必须同时满足的条件(E$2<=TODAY())，即小于等于今天。

我们可以在条件格式菜单中点击“管理规则”查看，单元格区域显示有两个规则，系统会从下往上运行规则，上面规则的格式覆盖下面的。

再做些美化工作，譬如增加图例等，一个简易的甘特图就完成了。

在条件格式中使用公式，你学会了吗？