編按：合同到期提醒很多人都知道，但是大家設(shè)置的到期提醒到最后整個(gè)登記表一片紅（紅色表示已到期），很刺眼，并且無法動(dòng)態(tài)的查看和統(tǒng)計(jì)不同預(yù)警天數(shù)下即將到期、到期尚未處理的合同數(shù)量。今天，我們教大家設(shè)計(jì)一個(gè)更人性化、更實(shí)用的帶合同統(tǒng)計(jì)功能和到期提醒功能的合同登記表。一起來看看吧！

公司合同管理是一項(xiàng)非常重要的工作，及時(shí)、準(zhǔn)確掌握合同狀態(tài)是避免法律糾紛、商業(yè)損失的基礎(chǔ)。

今天以員工合同為例，教大家制作一個(gè)動(dòng)態(tài)的、更人性化的可以到期提醒的合同登記表模板，功能效果如下。

（1）可按需統(tǒng)計(jì)不同合同預(yù)警或提醒天數(shù)下的即將到期的合同數(shù)。

（2）用黃色標(biāo)注即將到期的合同，用紅色標(biāo)注已到期未處理的合同。

（3）可以統(tǒng)計(jì)尚未處理的到期合同數(shù)。

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

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

第一，公式設(shè)置和處理結(jié)果下拉菜單

在這個(gè)模板中一共有五處公式，分別是當(dāng)前日期、合同到期日和是否到期、即將到期數(shù)、到期未處理數(shù)。

當(dāng)前日期的公式很簡(jiǎn)單，=TODAY()

合同到期日用到了EDATE函數(shù)，公式為=EDATE(D4,E4*12)

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

溫馨提示：多嵌套了一個(gè)$F$2>0的判斷是為了避免提醒日期錯(cuò)誤輸入非正數(shù)后的錯(cuò)誤提醒。

即將到期合同數(shù)公式=COUNTIF(G4:G43,"即將到期")。

已到期未處理的合同數(shù)公式=COUNTIFS(G4:G43,"已到期",H4:H43,"")

合同處理狀態(tài)下拉菜單設(shè)置方法如下：

第二,設(shè)置條件格式突出顯示

這一個(gè)階段就比較簡(jiǎn)單了，一共需要設(shè)置兩個(gè)規(guī)則，分別針對(duì)已到期和即將到期進(jìn)行標(biāo)注。

1. 即將到期的設(shè)置步驟

操作要點(diǎn)：

填入公式=$G4="即將到期"，公式中的引號(hào)必須是英文狀態(tài)的引號(hào)。

2.未處理的已到期的設(shè)置步驟

操作方法如圖所示。

操作要點(diǎn)：

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

至此，一個(gè)人性化的、動(dòng)態(tài)的、可統(tǒng)計(jì)不同狀態(tài)的合同數(shù)量，并自動(dòng)消除已處理的到期合同的顏色提醒的合同登記表模板就做好了，你學(xué)會(huì)了嗎？

相關(guān)推薦：

如何計(jì)算兩個(gè)日期間的工作日天數(shù)？超實(shí)用的5類日期函數(shù)來了！

常用日期函數(shù)太簡(jiǎn)單？那是你不知道WEEKNUM函數(shù)

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者老菜鳥；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。