Excel合同登记表制作：统计及到期提醒功能

编按：合同到期提醒很多人都知道，但是大家设置的到期提醒到最后整个登记表一片红（红色表示已到期），很刺眼，并且无法动态的查看和统计不同预警天数下即将到期、到期尚未处理的合同数量。今天，我们教大家设计一个更人性化、更实用的带合同统计功能和到期提醒功能的合同登记表。一起来看看吧！

公司合同管理是一项非常重要的工作，及时、准确掌握合同状态是避免法律纠纷、商业损失的基础。

今天以员工合同为例，教大家制作一个动态的、更人性化的可以到期提醒的合同登记表模板，功能效果如下。

（1）可按需统计不同合同预警或提醒天数下的即将到期的合同数。

（2）用黄色标注即将到期的合同，用红色标注已到期未处理的合同。

（3）可以统计尚未处理的到期合同数。

（4）可以消除已处理的到期合同的颜色提醒，避免刺眼的一片红。

下面就和大家一起分享制作步骤。

第一，公式设置和处理结果下拉菜单

在这个模板中一共有五处公式，分别是当前日期、合同到期日和是否到期、即将到期数、到期未处理数。

当前日期的公式很简单，=TODAY()

合同到期日用到了EDATE函数，公式为=EDATE(D4,E4*12)

是否到期的公式为=IF(F4<$B$2,"已到期",IF(F4<$B$2+$D$2,IF($D$2>0,"即将到期"),""))

温馨提示：多嵌套了一个$F$2>0的判断是为了避免提醒日期错误输入非正数后的错误提醒。

即将到期合同数公式=COUNTIF(G4:G43,"即将到期")。

已到期未处理的合同数公式=COUNTIFS(G4:G43,"已到期",H4:H43,"")

合同处理状态下拉菜单设置方法如下：

第二,设置条件格式突出显示

这一个阶段就比较简单了，一共需要设置两个规则，分别针对已到期和即将到期进行标注。

1.即将到期的设置步骤

操作要点：

填入公式=$G4="即将到期"，公式中的引号必须是英文状态的引号。

2.未处理的已到期的设置步骤

操作方法如图所示。

操作要点：

公式=AND($G4="已到期",$H4="")。

至此，一个人性化的、动态的、可统计不同状态的合同数量，并自动消除已处理的到期合同的颜色提醒的合同登记表模板就做好了，你学会了吗？

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

学习交流请加微信hclhclsc进群。

如何计算两个日期间的工作日天数？超实用的5类日期函数来了！

常用日期函数太简单？那是你不知道WEEKNUM函数

Excel教程：如何制作带有层次和透视感的图表？

八大查找函数公式，轻松搞定数据中的多条件查找

版权申明：

本文作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。