嗨，同学们大家好！我是小E~

小E为学生准备了100多个Excel模板。获取方式可直接在公众号【秋叶Excel】回复【头条新闻】即可~

临近过年，负责采购的同事小袁又开始盘点签约的合同。有多少合同尚未到期不急于续签，有多少合同即将到期需要尽快续签，有多少合同已经到期需要重新签订。

其实小圆已经整理得很清楚了，只是他认为“状态”栏和“处理中”栏可以自动生成，而不必手动填写。毕竟，一个人再仔细，在看密集的数据时，也可能会犯错误。

而且最好用颜色来标记，这样可以更直观的区分。

那么这些如何自动实现呢？我们来看看吧~

需求排序

小袁想要实现以下需求：

如果当前时间距过期时间大于等于30天，则状态显示“未过期”，流程显示“已签名”；

如果当前时间距离到期日期不足30天，则状态显示“未到期”，流程显示“尽快签名”，该行显示为浅黄色；

如果已经过期，状态会显示“Expired”，流程会显示“Re-sign”，并且该行会显示为红色。

解决方案

我们可以将上述需求分解为两步。第一步判断是否过期，并用文字标记；第二步是根据逾期情况用颜色进行标记。

第一步：文字标记函数法

状态列：单条件判断的 IF 函数

白色单元格的条件中，到期时间和当前时间的差值都大于 0 时，也都属于「未到期」

所以可以根据差值是否大于 0 这个条件是否成立，分为「未到期」和「已过期」。

接下来，我们将人类版本的条件翻译成Excel 可以理解的语言。下图以过期时间列的D2单元格为例：

标准日期格式直接进行加减运算，然后判断是否大于0。使用Today()函数动态获取当前系统日期，所以条件为(D2-TODAY())0；

不同结果返回的内容是未过期或过期的文本，需要用英文字符用双引号括起来。

最后将条件和结果放入“条件判断的IF函数”中。

=IF((D2-TODAY())0,'未过期','过期') 分析：=IF（条件，成立时返回结果，不成立时返回结果）

处理列：多条件判断的 IFS 函数

处理列有三种条件和对应的结果，是一个多条件判断。

如果这时候使用IF函数，就需要重叠多层，新手分分钟就糊涂了。

不过Excel2016及以后版本中有一个函数IFS，可以直接实现多条件判断（WPS也有）。

再说一次，我们先翻译一下人类的文字。 IFS 函数的每个条件仅在为真时返回结果，因此我们不会列出不为真的情况。

最后插入IFS函数

=IFS((D2-TODAY())30,'签订合同',(D2-TODAY())0,'尽快签订合同',(D2-TODAY())=0,'重新-标志')左右滑动查看

分析：=IFS(条件1，条件1成立时的结果，条件2，条件2成立时的结果，条件3，条件3成立时的结果)

第二步：颜色标记条件格式法

接着就是标颜色了

选择需要设置条件格式的区域（即要自动变色的区域，这里是单元格A1:F7），点击【开始】选项卡-【条件格式】-》新建规则”;

选择“使用公式确定要设置格式的单元格”并在编辑栏中输入：

=($D2-TODAY())=0（即日期距过期时间小于等于0时。）

然后在【格式】设置中，将单元格填充设置为红色。

继续创建3条新规则，范围仍然是A1:F7单元格：

在编辑栏中输入“=($D2-TODAY())0”，并在【格式】设置中将单元格填充为黄色；

在编辑栏中输入“=($D2-TODAY())30”，并在【格式】设置中将单元格填充设置为无颜色；

在公式栏中输入“=$D2=''”（没有填写日期时），并在【格式】设置中将单元格填充设置为无颜色。

确保每个条件的顺序如下图所示。如果没有，您可以选择规则并通过单击向上和向下箭头按钮进行调整。

好了，准备工作已经完成，我们来看看结果吧。

看起来是不是很棒！

综上所述

在编辑IFS 函数的长公式时，您常常会感到头疼。在这种情况下，您可以根据需要使用[Alt+Enter]将公式栏中的长公式换行，并使用空格键将它们分开。

这有助于理清长公式的逻辑关系。

如果在条件格式中使用公式并将其应用于其他单元格，则必须注意行和列的锁定格式。在此示例中，列被锁定。

当多个条件（规则）并存时，优先级是从上到下。当上一级条件能够满足时，不再进行下一级条件判断。因此，需要保证范围小的条件在上一级，范围大的条件在下一级。

更详细的IF函数，可以阅读我们之前的文章：大家必须知道的IF函数，超全解析，就看这篇文章吧！

您更喜欢使用哪种方法进行标记？

手动打标

文字标记-函数方法

颜色标记-条件格式化方法

除了用来“签合同”之外，你的朋友们通常在什么场景下使用标记呢？欢迎在留言区分享给大家~

小E为学生准备了100多个Excel模板。获取方式可直接在公众号【秋叶Excel】回复【头条新闻】即可~