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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

操作要點(diǎn)：

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

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

操作方法如圖所示。

操作要點(diǎn)：

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

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

相關(guān)推薦：

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

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

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

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

版權(quán)申明：

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