快過年了，負(fù)責(zé)采購的同事小圓又開始盤點(diǎn)合同簽約情況，清算有多少合約未到期不著急續(xù)簽，有多少快到期需要盡快續(xù)簽，多少已到期需要補(bǔ)簽合同的。

　　其實(shí)小圓已經(jīng)整理得很清晰了，但是他想「狀態(tài)」列和「處理」列可以自動生成，不用手動填寫，畢竟再怎么細(xì)心的人，看著密密麻麻的數(shù)據(jù)也有看錯眼的時候。

　　而且最好通過標(biāo)記上顏色，可以更直觀地區(qū)分。

　　那這些要怎樣自動實(shí)現(xiàn)呢？我們一起來看~

需求梳理

　　小圓想實(shí)現(xiàn)如下需求：

　　? 如果當(dāng)前時間離到期時間大于等于 30 天，則狀態(tài)顯示「未到期」，處理顯示「簽約」；

　　? 如果當(dāng)前時間離到期時間不足 30 天，則狀態(tài)顯示「未到期」，處理顯示「盡快簽約」，該行顯示為淺黃色；

　　? 如果已到期，則狀態(tài)顯示「已過期」，處理顯示「補(bǔ)簽」，該行顯示為紅色。

　　解決方法我們可以將上面的需求分解成兩步，第一步判斷是否逾期，并用文字標(biāo)注；第二步根據(jù)逾期情況，用顏色標(biāo)記。

　　▋第一步：文字標(biāo)記——函數(shù)法

　　? 狀態(tài)列：單條件判斷的 IF 函數(shù)

　　白色單元格的條件中，到期時間和當(dāng)前時間的差值都大于 0 時，也都屬于「未到期」。

　　所以可以根據(jù)差值是否大于 0 這個條件是否成立，分為「未到期」和「已過期」。

　　接著，我們將人話版條件翻譯成 Excel 能懂的語言，下圖以到期時間列 D2 單元格為例：

　　標(biāo)準(zhǔn)的日期格式直接進(jìn)行加減運(yùn)算，然后判斷是否大于 0 即可，使用 Today()函數(shù)可以動態(tài)獲取當(dāng)前系統(tǒng)日期，所以條件是(D2-TODAY())>0；不同結(jié)果返回的內(nèi)容分別是未到期或已到期的文本，需要加上英文字符的雙引號。

　　最后，將條件和結(jié)果套進(jìn)「用來進(jìn)行條件判斷的 IF 函數(shù)」。

　　<code style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; text-align: left; font-size: 14px; white-space: pre; display: flex; position: relative; font-family: Consolas, quotLiberation Monoquot, Menlo, Courier, monospace;"><span class="code-snippet_outer" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important;">=<span class="code-snippet__keyword" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; color: rgb(202, 125, 55);">IF</span>((D2-TODAY())gt<span class="code-snippet__number" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; color: rgb(14, 156, 229);">0</span>,nbsp<span class="code-snippet__string" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; color: rgb(221, 17, 68);">"未到期"</span>,nbsp<span class="code-snippet__string" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; color: rgb(221, 17, 68);">"已過期"</span>)</span></code>

　　解析：= IF (條件, 成立時返回的結(jié)果, 不成立時返回的結(jié)果）

? 處理列：多條件判斷的 IFS 函數(shù)

　　處理列有三種條件和對應(yīng)的結(jié)果，是一個多條件判斷。

　　此時如果用 IF 函數(shù)，就要進(jìn)行多層重疊，新手分分鐘被繞暈。

　　不過，在 Excel2016 以后的版本有一個函數(shù) IFS，可以直接實(shí)現(xiàn)多條件判斷（WPS 也有）。

　　同樣，我們先將人話翻譯一下。IFS 函數(shù)每一個條件只返回成立時的結(jié)果，所以不成立的情況我們就不列出來了。

　　最后套進(jìn) IFS 函數(shù)↓↓↓

　　<section style="margin: 0px 16px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important;"><code style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; text-align: left; font-size: 14px; white-space: pre; display: flex; position: relative; font-family: Consolas, quotLiberation Monoquot, Menlo, Courier, monospace;"><span class="code-snippet_outer" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; font-family: quotHelvetica Neuequot, Helvetica, quotHiragino Sans GBquot, quotMicrosoft YaHeiquot, Arial, sans-serif;">=IFS((D2-TODAY())gt30,"簽約",(D2-TODAY())gt0,"盡快簽約",(D2-TODAY())lt=0,"補(bǔ)簽")</span></code></section>

　　解析：=IFS(條件 1, 條件 1 成立時的結(jié)果,條件 2, 條件 2 成立時的結(jié)果,條件 3, 條件 3 成立時的結(jié)果)

　　▋第二步：顏色標(biāo)記——條件格式法

　　接著就是標(biāo)顏色了↓↓↓

　　選中需要設(shè)置條件格式的區(qū)域（就是要自動變色的區(qū)域，這里是 A1：F7 單元格），點(diǎn)擊【開始】選項卡-【條件格式】-「新建規(guī)則」；

　　選擇「使用公式確定要設(shè)置格式的單元格」，在公式欄中輸入：

　　<code style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; text-align: left; font-size: 14px; white-space: pre; display: flex; position: relative; font-family: Consolas, quotLiberation Monoquot, Menlo, Courier, monospace;"><span class="code-snippet_outer" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important;">=(<span class="code-snippet__variable" style="margin: 0px; padding: 0px; max-width: 1000%; box-sizing: border-box !important; overflow-wrap: break-word !important; color: rgb(14, 156, 229);">$D2</span>-TODAY( ))lt=0</span></code>

　?。串?dāng)日期距離到期時間小于等于 0。）

　　然后在【格式】設(shè)置中，將單元格填充設(shè)為紅色。

　　繼續(xù)新建 3 個規(guī)則，范圍仍然是 A1:F7 單元格：

　　? 公式欄中輸入「=($D2-TODAY())>0」，在【格式】設(shè)置中將單元格填充設(shè)為黃色；

　　? 公式欄中輸入「=($D2-TODAY())>30」，在【格式】設(shè)置中將單元格填充設(shè)為無顏色；

　　? 公式欄中輸入「=$D2=""」（未填寫日期時），在【格式】設(shè)置中將單元格填充設(shè)為無顏色。

　　保證各條件的順序如下圖所示。如果不是，可以選中規(guī)則后，通過點(diǎn)擊上下箭頭按鈕進(jìn)行調(diào)整。

　　好啦，準(zhǔn)備工作已經(jīng)完成了，來看看結(jié)果吧。

　　看起來是不是很棒！

總結(jié)一下

　　? IFS 函數(shù)等長公式編輯時，經(jīng)常會看得頭疼，這時可以用【Alt+Enter】，在公式欄中對長公式按需要進(jìn)行換行，并使用空格鍵進(jìn)行間隔。

　　這樣有助于梳理長公式的邏輯關(guān)系。

　　? 條件格式中如果使用公式，并且要應(yīng)用到其他單元格的時候要注意行列鎖定的格式，本例中就鎖定了列。

　　? 當(dāng)多個條件（規(guī)則）共存時，優(yōu)先級是從上到下，當(dāng)能滿足上一級條件的時候，就不再進(jìn)行下一級的條件判定。因此要保證范圍小的條件在上一級，范圍大的在下一級。