本文总共1898个字,阅读需7分钟,全文加载时间:2.570s,本站办公入门专栏收录该内容! 字体大小:

文章导读:嗨,各位同学们好呀!我是小E~ 小E为同学们准备了100 Excel模板,获取直接在公种号【秋叶Excel】即可~ 快过年了,负责采购的同事小圆又开始盘点合同签约情况,清算有多少合约未到期不着急续签,有多少快到期需要……各位看官请向下阅读:

嗨,各位同学们好呀!我是小E~

小E为同学们准备了100 Excel模板,获取直接在公种号【秋叶Excel】即可~

快过年了,负责采购的同事小圆又开始盘点合同签约情况,清算有多少合约未到期不着急续签,有多少快到期需要尽快续签,多少已到期需要补签合同的。

其实小圆已经整理得很清晰了,但是他想「状态列和「处理」列可以自动生成,不用手动填写,毕竟再怎么细心的人,看着密密麻麻的数据也有看错眼的时候。

而且最好通过标记上颜色,可以更直观地区分。

那这些要怎样自动实现呢?我们一起来看~

需求梳理

小圆想实现如下需求:

❶ 如果当前时间离到期时间大于等于 30 天,则状态显示「未到期」,处理显示「签约」;

❷ 如果当前时间离到期时间不足 30 天,则状态显示「未到期」,处理显示「尽快签约」,该行显示为浅黄色;

❸ 如果已到期,则状态显示「已过期」,处理显示「补签」,该行显示为红色。

解决方法

我们可以将上面的需求分解成两步,第一步判断是否逾期,并用文字标注;第二步根据逾期情况,用颜色标记。

▋第一步:文字标记——函数法

❶ 状态列:单条件判断的 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】即可~

想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注