這天小陸，小皮和小度在茶水間閑聊，就看着黴黴滿臉疑惑的表情就來了，對着小陸說：你給我講的函數，什麼today()返回當天的日期，edate函數設定返回間隔幾個月的日期，date設置日期函數，我都記住了，怎麼遇到問題我還是不會寫公式呢？

小陸笑了說：這很正常的，你能記住我給你講的函數已經很不錯了，我學習的時候，需要反複的練習，做筆記才記得住的，你這過了兩天你既然還能記得已經很好了，而且記得跟上手用是兩回事，走吧，先幫你搞定你的表格，再看看有什麼好告訴你的，慢慢來吧！說着就朝黴黴工位走去。

說時遲那時快，打開電腦就看到這樣的一張表：

數據表格示意圖

​然後小陸就是開始他的常規操作：

首先：ctrl g，【定位條件】按鈕，選常量，然後點确定就會選擇已有内容的單元格，然後我們通過開始菜單下的【套用表格樣式】随便的選個樣式，這樣就把選取的内容變成“智能表格”，

注：Excel基礎知識之一張退休統計表引發的搭讪

然後：E1單元格填寫“合同提示”回車确認，會自動将表格樣式擴充到這一列（智能表格優點），輸入公式如下：

=iferror(if(year(c2) d2&month(c2)=year(today())&month(edate(today(),1),text(c2,"m月d")&"日到期",""),“日期異常”);回車後搞定。所有有“8月X日到期”内容的單元格就是要下個月續簽合同的人。

黴黴：厲害厲害，我自己在這想了半天了，沒有一點頭緒，你是怎麼想的啊？

小陸：我跟你說一下思路：續簽合同的規則你知道嗎？

黴黴：不知道！續簽合同有什麼規則啊？

小陸：續簽合同是有兩種方式，一種就是到期當日續簽，還有一種為了節省人力資源的時間，簽訂到合同到期月末，這兩種方式都是被法律認可，你也可以跟老闆提一下按第二種方法做，這樣能提高工作效率。

黴黴：頻頻點頭，沒有想到你懂這麼多啊，我算是找對人啦！哪公式的思路呢？

小陸：因為我們求的是下個月的簽合同的人，我們就可以比較月份就好，先獲得合同到期的日期，方法：簽合同日期的年份 合同期限再加上月份，而下個月的日期就是今天的日期的月份加1，要寫這部分公式需要知道日期函數的三劍客：year（獲取年份），month(獲取月份),day(獲取日期），使用語法結構圖如下：

日期獲取年份，月份，日期的函數語法結構圖

​獲取合同到期日期的年份=year(c3) d3，月份=month(c3),

而今天的日期年份=year(today()),下個月的月份=month(edate(today(),1)),分别拼接一下，對比這兩組數：用if函數做判斷，如果相等，返回簽訂合同的日期（獲取簽合同的月份和日期拼接），不相等返回“”，公式如下

if(year(c3) d3&month(c3)=year(edate(today(),1))&month(edate(today(),1)),MONTH(C2)&"月"&DAY(C2)&"日到期","")

再加一個嚴謹性判斷，用iferror函數，如果出現錯誤，就提示日期異常；

黴黴正要點頭，小度插話道：公式寫的不錯，公式也算講的清楚，不過最大的敗筆就是沒有優化，比如說，我要你彙總出三個月以内要的合同到期的人員該怎麼寫公式啊？如果用相減小于等3，卻還需要排除負數的情況，就比較符在，我來幫你改改公式吧！

小度推開小陸，為了避開智能表格的自動擴展，需要隔一列選中一單元格，點【數據】菜單下的【數據驗證】按鈕，然後選擇驗證條件 ：【允許】選擇序列，填充數字序列，以逗号為分隔符，然後點确定就創建了G1下拉菜單，操作如下圖：

創建下拉菜單單元格操作示意圖

然後将公式改為：=iferror(if(mod(year(c2) d2&month(c2),year(today())&month(today) 1)<=g1,month(c2)&"月"&day(c2)&"日到期",""),"日期異常"),搞定。

小陸：你的想法不錯，不過公式寫的就有點問題啦！你明明選的是3月以内到期的，怎麼沒有統計出十月份的人呢？而且你的公式還有個隐形的問題啊。

小度紅着臉說：等等，我看看啊。

小陸：等什麼啊，看我的啦！說這就将公式寫成：

=iferror(if(mod(year(c2) d2&text(month(c2),"00"),text(edate(today(),1),"yyyymm"))<=$g$1,text(c2,"m月d日")&"到期",""),"日期異常"),這裡需要注意，在引用智能表格以外的單元格需要加絕對引用符号，否則會自動遞增，這樣就行了！數學好，經驗少，還是嫩啊！

小度不服氣的說：我的公式有什麼問題，你到時是說說！

小陸：在計算日期的時候，别相當然的加減運算，需要考慮邊界的問題，比如12月 1确還是等于1月，而年份有變動，使用日期函數就不用擔心這種問題啦！還有你在取餘的時候，隻是因為8，9月沒有進位，拼接的取餘就沒有問題，如果遇到10/11/12，就是相差10倍的關系，怎麼可能求出相差為幾的結果，我寫的公式雖然現在看沒有問題，等遇到現在日期為12月的時候，取餘算出的結果就肯定是錯的啦，你覺得呢？

小陸轉過頭對黴黴說：學東西的時候，要盡量多開發新的功能，不過在工作的時候，寫出第一個公式行了。然後白了小陸一眼說：對于哪些功能雖好，但不太實用的就不用浪費時間了，錦上添花的事，有也更好，沒有也不影響工作！

不知什麼時候，小皮也加入觀戰的行列，不懈的說：有些錦上添花卻很必要啊。然後用身體把小陸從座位擠走，開始了小皮式操作：先複制第一個公式的if條件year(c3) d3&month(c3)=year(edate(today(),1))&month(edate(today(),1))，然後【開始】菜單下的條件格式按鈕，然後選擇創建新規則，選使用公式設置單元格格式選項，然後在公式欄輸入=，然後粘貼剛才的公式，然後點格式按鈕設置想顯示過的格式，然後點擊格式刷，托選需要填充格式的單元格，搞定，這樣就完美了！

設置條件格式步驟示意圖

黴黴一臉茫然說：原來表格還有這麼多能玩的花樣，我都快看不過來啦，怎麼辦，三位師傅？

這次卻一口同聲的說：慢慢來吧，我們也不是一天就學成的啊！

黴黴：哪我先學什麼呢？

小皮和小度異口同聲說：哪你就跟小陸學噻！

黴黴徹底迷茫了！小陸悄悄的說：沒事，有我呢！

好了今天的故事就到這，希望通過看故事也能對Excel做功能有個系統的了解，從什麼步驟開始，怎麼思考，也希望你留下的寶貴建議，我看到後第一時間回複，哪我們就下個故事見啦！最後放張效果圖，有需要表格下方留下郵箱，統一發送！

合同到期統計效果圖

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!