最近帮同学研究了下excel条件格式的使用,这块知识虽然和编码无关,但是对于操作excel报表还是很实用的,所以这里我将详细的使用流程记录下来,方便后续使用。
目标:
对于距离今天还有一天的数据行填充黄色
对于距离今天超过一天的数据行填充灰色
实现方式:
使用excel的“条件格式”功能,核心是添加两条规则(后面可以根据需要再添加其他规则)。一条规则是定义指定日期列距离特定日期还有一天填充黄色,另一条规则是定义指定日期列大于1天填充灰色。
具体步骤:
1、首先准备一些测试数据,如下:
2、打开条件格式的管理规则功能,如下:
3、点击新建规则
4、在新建规则格式页面,有以下几个小操作
1)选择“使用公式确定要设置格式的单元格”
2)编辑规则公式(该规则用于筛选出距离今天还有1天的日期行):
=IF(DATEDIF($C1,TODAY(),"D")=1,1,0)
注意公式前面的“=”号不能少
$C1 表示要计算的时间列,1表示从第几行开始算
“D” 表示统计时间差的精度,D表示的是天
3)点击格式,弹出设置单元格格式框
4)选择填充项
5)选择颜色
5、选择完颜色后点击确定回到规则管理页面
这里有两个常用的功能介绍下:
1)下拉框“显示其格式规则”,如下图:
它默认是展示我们鼠标光标所在excel单元格上的规则,如果我们鼠标光标在规则覆盖的单元格之外,那么我们再规则管理器里是看不到我们的规则的。这时可以选择“当前工作表”,那么它就会列出所有的规则了。
2)操作“应用于”列,进而选择规则生效的范围
可以看到目前规则只在A1单元格生效,所以接下来我们点击后面的按钮重新选择范围,这里我们将会直接选择三列。如下:
6、选择完范围后就好了,接着点击下应用看看效果
如果出现上面的效果表示规则定义成功了,接下来点击确定就行了。因为我们选中的是ABC三列,所以后面只要在这三列添加数据,规则就会自动生效。
7、添加新规则,让时间大于1天的行都填充灰色
公式如下:
=IF((DATEDIF($C1,TODAY(),"D")>1)*(DATEDIF($C1,TODAY(),"D")<365),1,0)
注意这里有个细节,因为我们统计的是大于1天的填充灰色,如果此时单元格为空,那么到当天为止的时间差就是从0到当前的时间差,这个值肯定大于1,所以空的单元格都会被填充灰色。因此我们这里再加个小于365天的限制进而避免空单元格填充灰色的问题(可以适当进行增大或减少该值)。
同样,选择规则生效的范围
点击应用,查看效果。正常则点击确定进行保存