作者：小芒童鞋
来源：芒种学园(ID：lazy_info)
临近年底，近期在学习群中收到了不少小伙伴关于Excel如何设置自动提醒的提问，这类场景非常多，例如：
清算有多少合同未到期不需要着急续签；
有多少快到期需要尽快续签；
有多少已到期的需要补签的。
这里小北模拟填写了一份数据，如下：
数据结构上已经非常清晰了，但是数据一多的情况下，【状态】和【处理】还需要手动生成就非常麻烦，而且数据从颜色上没有什么区分，看多了容易遗漏。
但是做成这种自动生成+区分颜色，就再也不怕看不到了。
那么这个效果是如何实现的呢？接下来快和小芒一起学习下吧~
公式设置自动计算
首先我们来整理下需求，【状态】和【处理】共有 3 种场景，如下：
当前时间距离到期时间大于30天，分别为“未到期”和“/”，无填充色；
当前时间距离到期时间小于等于30天，分别为“快过期”和“尽快续签”，填充黄色；
如果已到期，分别为“已过期”和“补签”，填充棕红色效果；
了解了需求，接下来就可以开写函数了，首先当前时间可以使用 now 函数计算，使用 ifs 函数来判断多种场景，【状态】列公式如下：
=IFS(D2-NOW()>30,"未到期",D2-NOW()>0,"快到期",TRUE,"已过期")△左右滑动查看完整公式
【处理】列的判断和【状态】列一模一样，公式如下：
=IFS(D2-NOW()>30,"/",D2-NOW()>0,"尽快续签",TRUE,"补签")△左右滑动查看完整公式
现在我们就拥有了一份能自动计算结果的表格了，效果如下：
接下来我们为表格穿上漂亮的衣服，让阅读起来更舒服。
条件格式填充表格
虽然公式计算的结果已经可以通过筛选快速找出不同的合同，但是还是略显麻烦，这个时候只需要为表格自动填充不同的配色。
让不同的状态区分出来就行，使用条件格式即可，操作也很简单。
选中【数据区域】，点击【开始】选项卡下的【条件格式】-【管理规则】，并新建一条规则，如下。
接下来输入公式：=$E2="已过期"，设置格式为填充棕红色，效果如下：
然后继续添加一条规则，设置公式：=$E2="快到期"，并填充黄色，如下：
现在条件格式里的两条规则就已经设置完毕，未到期的场合，因为没有填充色，所以可以不用设置。
现在【自动计算】+【动态配色】的合同提醒报表就制作完成了。
而且这个表格用到了 now 函数，每天打开表格的时候，now 函数都会自动计算当天的日期并更新数据，再也不用一个一个手动核对了，Nice！
VBA强制处理方案
如果想操作得更极限一些，比如说检测到已经超期了，没有处理完整不能保存，这个时候也可以使用 VBA 来操作。
例如下方，当时间超期后，如果对应【状态】列并没有处理方案，在保存的时候，会弹窗提示，并禁止保存：
操作起来也很简单，将下面的 VBA 代码复制到 Excel 中：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Dim ws As WorksheetDim lastRow As LongDim currentDate As DateDim i As Long' 设置工作表Set ws = ThisWorkbook.Sheets("数据源")' 获取当前日期currentDate = Date' 获取D列最后一行lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row' 遍历D列For i = 2 To lastRow ' 假设第一行是标题，所以从第二行开始遍历' 检查D列是否有日期且大于等于当前日期，并且E列为空If IsDate(ws.Cells(i, 4).Value) And ws.Cells(i, 4).Value <= currentDate And IsEmpty(ws.Cells(i, 5).Value) Then' 弹窗警告MsgBox "当前行未处理，请处理后才能保存。行号：" & i' 取消保存操作Cancel = TrueExit SubEnd IfNext iEnd Sub
▲左右滑动查看完整代码
代码演示如下：
然后将文件保存为 .xlsb 或者 .xlsm 格式就行，现在如果存在时间小于当前时间（即已超期状态），并且状态为空，就可以看到 Excel 的弹窗警告啦~
是不是非常简单呢？下次再也不怕漏签合同了，就想问问你，是不是比闹钟管用多了！当然你不打开 Excel 它永远也无法提醒你~
关于「Excel自动提醒」的技巧分享就到这里了，你还使用过哪些自动提醒工具呢？欢迎在评论区中留言分享哦~
最后，我是小芒，希望今天的这篇分享对大家有用呀~
想了解数据处理和信息图表的更多思路与技巧？「Excel实战课，让你的图表会说话」超值 Excel 课程了解一下——
