哈喽,大家好。 Excel里的条件格式,大家都会用吧? 通常用来标记一些特殊的数据;设置颜色;自动添加边框;自动提醒等等。 今天再来给大家讲解两个有一丁点儿复杂的应用。
1.标记特定时间后出现的特殊数据 如图所示,这是一张质量检测表。 已知2023/1/4召开了质量会议,现在需要将1月4日后第一次出现不合格的员工标注出来。 操作步骤: 选中C2:I6数据区域,点击条件格式-新建规则,使用公式确定要设置格式的单元格。 输入公式 =AND(COUNTIFS(C$2:C2,C2,C$2:C2,"不合格")=1,C$1>$A$9) 再设置一个橘色的填充色即可。 公式解释: 使用AND函数判断:在C2:I6中使用COUNTIFS统计不合格次数为第1次出现,且满足C$1>$A$9。比如第二行的I2单元格,其日期大于A9,并且是1.4之后出现的。这类单元格会显示为TRUE,即符合条件格式所设定的条件。 2.标记第二次重复出现的数据 如下图所示,我们需要重点标记第二次出现【不合格】的单元格,如何操作呢? 操作步骤: 选中C2:I6单元格,点击“开始→条件格式→新建格式规则”,选择“使用公式确定要设置格式的单元格”。 输入公式“=COUNTIFS($C2:C2,C2,$C2:C2,"不合格")=2”,点击“格式”,设置一个黄色的填充色。 公式解释: 使用COUNTIFS 函数进行多条件的查找计数,其中: 第一个计数区域是$C2:C2,下拉会依次变为$C3:C4、$C3:C5……动态区域,右拉后会变为$C3:D2、$C3:E2……,计数条件是C3,依次统计该行字符。 第二个计数区域也是$C3:C3,计数条件则是“不合格”,统计每行出现“不合格”的次数。 这样通过公式可以对每行单元格中“不合格”的进行重复次数标记,比如第三行的F3出现的“不重复”,在公式中就会被标记为“2”,因为其满足条件格式中设置的条件,所以会被填充上黄色(其他单元格的判断同理)。 如果你的Excel(2016以下版本)没有COUNTIFS函数,那么可以使用“=AND(C2="不合格",COUNTIF($C2:C2,C2)=2)”替代。 |
|