2个实例让你学会带函数公式的条件格式
作者：龚春光来源：部落窝教育发布时间：2018-06-01 18:23:25点击：6813
小编有话说：条件格式应该大家都用过，你们知道加上公式后是什么样子吗？简直太好用了！可以同时标注出100行数据每一行的最大值，还可以设置合同到期提醒等等，下面来看一下吧！
条件格式大家都会玩，但如何同时找出100行数据中每一行的最大值并标注出来，相信你应该不知道。今天我们通过2个实例跟大家一起学习一下条件格式配合函数公式后的用法。
实例一【多行数据的最高分标色】
说起用条件格式标注出最大值，相信大家都知道，使用如下图所示的“项目选取规则”里的各项就可以完成，但是这个操作比较受限制。如果我们有100行数据，要同时把每一行的最高值标注出来呢？下面给大家讲解用公式解决这个问题。
近期部落窝教育进行excel、word、ppt、综合四个科目阶段考核。表中记录了学员考试成绩，现在需要同时将每一位学员的最高分所在单元格用绿色填充。
操作步骤：
（1）选中D2:G13数据区域后单击【开始】选项卡中【条件格式】，选择【新建规则】。
（2）打开【新建格式规则】对话框后选择【使用公式确定要设置格式的单元格】规则类型。在下方的编辑规则说明中输入公式=AND(D2=MAX($D2:$G2),D2<>"")。单击【格式】将单元格填充颜色设置为绿色。
函数公式解析
（1）D2=MAX($D2:$G2)判断D2是否等于$D2:$G2区域中的最大值，如果相同则返回TRUE，否则返回FALSE。 其中列坐标加了$符号为绝对引用，这样数据便都是在D-G列进行判断。行坐标为相对引用，每向下移一行，公式就自动变为对应的行坐标。
（2）D2<>“”表示D2大于小于也就是不等于空白单元格，则返回TRUE，否则返回FALSE。
（3）AND函数判断以上2个条件是否都为真，如果都为真则返回TRUE，单元格将填充绿色。
编辑完成格式规则后单击【确定】进入【条件格式设置管理器】（备注：条件格式设置管理器可以理解为存储设置好的条件格式）
在管理器中看到了刚刚设置的条件格式，当前条件格式应用的区域固定为$D$2:$G$13单元格区域。如果我们的数据区域发生了变化，只需修改应用单元格区域即可。
单击上图中的【应用】-【确定】就完成了最高分标色。最终结果如下。
大家可以试着改一下公式并将最低分标色哦！
实例二【合同到期提醒】
如何在表格中设置合同到期提醒？相信做合同管理的伙伴都有这样的需求。最简单的方式是使用“条件格式”里的“发生日期”，可以将即将到期的数据所在单元格用特殊颜色显示。
但是这种方式只有下面几个选项，若我想要找出5天内将要到期的合同呢？就需要使用函数公式了。
下表是联通集团公司营销部员工劳动合同记录表，表中记录员工入职时间以及合同终止时间。现在需要通过条件格式把合同即将期满的员工自动标色提醒。
操作步骤：
（1）选中H2:H13数据区域后单击【开始】选项卡中的【条件格式】。选择【新建格式规则】，打开【新建格式规则】对话框后选择【使用公式确定要设置格式的单元格】
（2）在编辑规则说明中我们输入设置条件为=DATEDIF(TODAY(),H2,"d")<5，单元格格式设置为红色。
可能很多小伙伴还不是很熟悉DATEDIF(TODAY(),H2,"d")<5含义。DATEDIF函数是Excel中隐藏函数，在单元格中输入函数首字母是不会自动出现这个函数，必须输入完整的函数。DATEDIF函数主要的功能是计算两个日期之间的差值，其语法为“DATEDIF（日期1，日期2，“单位（年、月、日）”）”。
下面我们用一张表简单介绍一下这个函数：
大家可以看到，我们通过入职日期与当前日期（today()返回当前日期）进行对比，分别以“y”、“m”、“d”为计算单位，依次返回两个日期相差的年数、月数、天数。注意：函数公式第1个参数为小的日期，第2个参数为大的日期。
理解了DATEDIF函数后，我们不难理解前面设置合同到期提醒的公式DATEDIF(TODAY(),H2,"d")<5，其含义为统计2个日期相差的天数，然后判断该天数是否小于5，如果小于5则标注红色。这里要注意的是，合同到期日期是大于当前日期的，所以TODAY()为第1个参数，H列为第2个参数。
今天我们通过2个实例跟大家分享了在条件格式中结合公式来判断数据并标注。现实工作中相关的实例非常多，比如：
1、 通过=COUNTIF($H$3:$H$13,H2)>1函数，可以标注重复数据。
2、 通过=VLOOKUP($H2,$M:$M,1,0)<>"#N/A"函数，将匹配到的单元格直接标色。大家可以试着操作一下哦！
本文配套的练习课件请加入QQ群：316492581下载。 如果您因工作所需使用到Excel，不妨关注部落窝教育的《一周Excel直通车》视频课或《Excel极速贯通班》直播课系统学习。 相关推荐：