比闹钟还管用!Excel自动提醒,再也不怕忘记事儿
嗨,各位同学们好呀!我是小E~
小E为同学们准备了100+Excel模板,获取直接在公种号【秋叶Excel】回复【头条】即可~
快过年了,负责采购的同事小圆又开始盘点合同签约情况,清算有多少合约未到期不着急续签,有多少快到期需要尽快续签,多少已到期需要补签合同的。
其实小圆已经整理得很清晰了,但是他想「状态」列和「处理」列可以自动生成,不用手动填写,毕竟再怎么细心的人,看着密密麻麻的数据也有看错眼的时候。
而且最好通过标记上颜色,可以更直观地区分。
那这些要怎样自动实现呢?我们一起来看~
需求梳理
小圆想实现如下需求:
❶ 如果当前时间离到期时间大于等于 30 天,则状态显示「未到期」,处理显示「签约」;
❷ 如果当前时间离到期时间不足 30 天,则状态显示「未到期」,处理显示「尽快签约」,该行显示为浅黄色;
❸ 如果已到期,则状态显示「已过期」,处理显示「补签」,该行显示为红色。
解决方法
我们可以将上面的需求分解成两步,第一步判断是否逾期,并用文字标注;第二步根据逾期情况,用颜色标记。
▋第一步:文字标记——函数法
❶ 状态列:单条件判断的 IF 函数
白色单元格的条件中,到期时间和当前时间的差值都大于 0 时,也都属于「未到期」
所以可以根据差值是否大于 0 这个条件是否成立,分为「未到期」和「已过期」。
接着,我们将人话版条件翻译成 Excel 能懂的语言,下图以到期时间列 D2 单元格为例:
标准的日期格式直接进行加减运算,然后判断是否大于 0 即可,使用 Today()函数可以动态获取当前系统日期,所以条件是(D2-TODAY())>0;
不同结果返回的内容分别是未到期或已到期的文本,需要加上英文字符的双引号。
最后,将条件和结果套进「用来进行条件判断的 IF 函数」。
=IF((D2-TODAY())>0, '未到期', '已过期')
解析:= IF (条件, 成立时返回的结果, 不成立时返回的结果)
❷ 处理列:多条件判断的 IFS 函数
处理列有三种条件和对应的结果,是一个多条件判断。
此时如果用 IF 函数,就要进行多层重叠,新手分分钟被绕晕。
不过,在 Excel2016 以后的版本有一个函数 IFS,可以直接实现多条件判断(WPS 也有)。
同样,我们先将人话翻译一下。IFS 函数每一个条件只返回成立时的结果,所以不成立的情况我们就不列出来了。