比闹钟还管用!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 函数每一个条件只返回成立时的结果,所以不成立的情况我们就不列出来了。

(0)

相关推荐