Excel实现自动提醒,再也不怕忘事了!

临近年底,在学习群中收到了不少小伙伴关于Excel如何设置自动提醒的提问,这类场景非常多,例如:

  1. 清算有多少合同未到期不需要着急续签;
  2. 有多少快到期需要尽快续签;
  3. 有多少已到期的需要补签的。

这里小北模拟填写了一份数据,如下:

图片

数据结构上已经非常清晰了,但是数据一多的情况下,【状态】和【处理】还需要手动生成就非常麻烦,而且数据从颜色上没有什么区分,看多了容易遗漏。

图片

但是做成这种自动生成+区分颜色,就再也不怕看不到了。

图片

那么这个效果是如何实现的呢?接下来快和小芒一起学习下吧~

公式设置自动计算

首先我们来整理下需求,【状态】和【处理】共有 3 种场景,如下:

  1. 当前时间距离到期时间大于30天,分别为“未到期”和“/”,无填充色;
  2. 当前时间距离到期时间小于等于30天,分别为“快过期”和“尽快续签”,填充黄色;
  3. 如果已到期,分别为“已过期”和“补签”,填充棕红色效果;

了解了需求,接下来就可以开写函数了,首先当前时间可以使用 now 函数计算,使用 ifs 函数来判断多种场景,【状态】列公式如下:

=IFS(D2-NOW()>30,"未到期",D2-NOW()>0,"快到期",TRUE,"已过期")

【处理】列的判断和【状态】列一模一样,公式如下:

 =IFS(D2-NOW()>30,"/",D2-NOW()>0,"尽快续签",TRUE,"补签")

现在我们就拥有了一份能自动计算结果的表格了,效果如下:

图片

接下来我们为表格穿上漂亮的衣服,让阅读起来更舒服。

条件格式填充表格

虽然公式计算的结果已经可以通过筛选快速找出不同的合同,但是还是略显麻烦,这个时候只需要为表格自动填充不同的配色。

让不同的状态区分出来就行,使用条件格式即可,操作也很简单。

选中【数据区域】,点击【开始】选项卡下的【条件格式】-【管理规则】,并新建一条规则,如下。

图片

接下来输入公式:=$E2="已过期",设置格式为填充棕红色,效果如下:

图片

然后继续添加一条规则,设置公式:=$E2="快到期",并填充黄色,如下:

图片

现在条件格式里的两条规则就已经设置完毕,未到期的场合,因为没有填充色,所以可以不用设置。

现在【自动计算】+【动态配色】的合同提醒报表就制作完成了。

图片

而且这个表格用到了 now 函数,每天打开表格的时候,now 函数都会自动计算当天的日期并更新数据,再也不用一个一个手动核对了,Nice!

文章摘自公众号:芒种学园

芒种学园

© 版权声明
THE END
喜欢就支持一下吧
点赞8 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容