快过年了，负责采购的同事小圆又开始盘点合同签约情况，清算有多少合约未到期不着急续签，有多少快到期需要尽快续签，多少已到期需要补签合同的。 
其实小圆已经整理得很清晰了，但是他想「状态」列和「处理」列可以自动生成，不用手动填写，毕竟再怎么细心的人，看着密密麻麻的数据也有看错眼的时候。 而且最好通过标记上颜色，可以更直观地区分。 
那这些要怎样自动实现呢？我们一起来看~ 
需求梳理 小圆想实现如下需求： ❶ 如果当前时间离到期时间大于等于 30 天，则状态显示「未到期」，处理显示「签约」； ❷ 如果当前时间离到期时间不足 30 天，则状态显示「未到期」，处理显示「尽快签约」，该行显示为浅黄色； ❸ 如果已到期，则状态显示「已过期」，处理显示「补签」，该行显示为红色。 
解决方法我们可以将上面的需求分解成两步，第一步判断是否逾期，并用文字标注；第二步根据逾期情况，用颜色标记。 ▋第一步：文字标记——函数法 ❶ 状态列：单条件判断的 IF 函数 白色单元格的条件中，到期时间和当前时间的差值都大于 0 时，也都属于「未到期」。 所以可以根据差值是否大于 0 这个条件是否成立，分为「未到期」和「已过期」。 
接着，我们将人话版条件翻译成 Excel 能懂的语言，下图以到期时间列 D2 单元格为例： 
标准的日期格式直接进行加减运算，然后判断是否大于 0 即可，使用 Today()函数可以动态获取当前系统日期，所以条件是(D2-TODAY())>0；不同结果返回的内容分别是未到期或已到期的文本，需要加上英文字符的双引号。
最后，将条件和结果套进「用来进行条件判断的 IF 函数」。 <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 (条件, 成立时返回的结果, 不成立时返回的结果） 
❷ 处理列：多条件判断的 IFS 函数 处理列有三种条件和对应的结果，是一个多条件判断。 
此时如果用 IF 函数，就要进行多层重叠，新手分分钟被绕晕。 不过，在 Excel2016 以后的版本有一个函数 IFS，可以直接实现多条件判断（WPS 也有）。 同样，我们先将人话翻译一下。IFS 函数每一个条件只返回成立时的结果，所以不成立的情况我们就不列出来了。 
最后套进 IFS 函数↓↓↓ <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,"补签")</span></code></section> 解析：=IFS(条件 1, 条件 1 成立时的结果,条件 2, 条件 2 成立时的结果,条件 3, 条件 3 成立时的结果) 
▋第二步：颜色标记——条件格式法 接着就是标颜色了↓↓↓
选中需要设置条件格式的区域（就是要自动变色的区域，这里是 A1：F7 单元格），点击【开始】选项卡-【条件格式】-「新建规则」；
选择「使用公式确定要设置格式的单元格」，在公式栏中输入： <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> （即当日期距离到期时间小于等于 0。） 然后在【格式】设置中，将单元格填充设为红色。
继续新建 3 个规则，范围仍然是 A1:F7 单元格： ❶ 公式栏中输入「=($D2-TODAY())>0」，在【格式】设置中将单元格填充设为黄色； ❷ 公式栏中输入「=($D2-TODAY())>30」，在【格式】设置中将单元格填充设为无颜色； ❸ 公式栏中输入「=$D2=""」（未填写日期时），在【格式】设置中将单元格填充设为无颜色。
保证各条件的顺序如下图所示。如果不是，可以选中规则后，通过点击上下箭头按钮进行调整。
好啦，准备工作已经完成了，来看看结果吧。
看起来是不是很棒！
总结一下 ❶ IFS 函数等长公式编辑时，经常会看得头疼，这时可以用【Alt+Enter】，在公式栏中对长公式按需要进行换行，并使用空格键进行间隔。
这样有助于梳理长公式的逻辑关系。 ❷ 条件格式中如果使用公式，并且要应用到其他单元格的时候要注意行列锁定的格式，本例中就锁定了列。 ❸ 当多个条件（规则）共存时，优先级是从上到下，当能满足上一级条件的时候，就不再进行下一级的条件判定。因此要保证范围小的条件在上一级，范围大的在下一级。
| 
