比如老板让HR查查，哪些人经常连续三天迟到。这个任务可不轻呀，怎么样在人员繁多，日期天数又多的数据中找出连续三天迟到的日期呢？其实写个条件格式的公式就将问题解决了，所有迟到天数大于3天的全部标上了黄色。
下面我们一起看下是怎么做到的。
Step 01
先用鼠标选择计算范围，也就是考勤数据的B2:N19单元格。点击【新建规则】，【使用公式确定要使用规则的单元格】。
Step 02
条件格式中输入函数函数是
=MATCH(3,COUNTIF(OFFSET(B2,,ROW($A$1:$A$3)-3,1,3),"迟到"),0)
Step 03
点击【格式】，【填充】，【黄色】，【确定】。
下面我们主要来讲解下第2步的函数思路。
内层的OFFSET(B2,,ROW($A$1:$A$3)-3,1,3)是以B2为起点，向下偏移0行，向右偏移{-2；-1；0}列，这样就生成了三块不同的单元格，在以这三个不同的单元格为起点生成往右的三个连续单元格区域。
OFFSET(E2,,ROW($A$1:$A$3)-3,1,3)
以上面的公式来说，offset的基点是E2，那么它就会返回以下3块区域。
C2:E2
D2:F2
E2:G2
然后使用countif函数统计这三个区域中是“迟到”的个数
COUNTIF(OFFSET(E2,,ROW($A$1:$A$3)-3,1,3),"迟到")
最后返回一个一维数组{1;0;0}。最后使用match函数查询这个数组中是否存在3，如果不存在，会返回错误值，如果存在会返回3在序列值中的位置。在条件格式中数值就代表true，错误值就是false。所以如果能找到3，那么就会触发条件格式在对应的单元格区域中标记上黄色。
评论 (0)