搜索
写经验 领红包

在excel中如何设置条件格式(怎么在excel中设置条件格式)

导语:Excel表格条件格式如何设置指定条件下填充单元格字体颜色

excel日常工作中,几乎每个人都会碰到需要使用条件格式功能的问题,今天作者就遇上了人事考勤方面的一个问题。

通过系统导出了工作人员的考勤打卡记录,现在需要给上班打卡时间设置3个条件,当符合条件时,自动填充相应的字体颜色。

如下图所示:

我们仔细看一下它的3个条件:

条件1:字体变黄色

超过8:45小于09:00

条件2:字体橘色

超过09:00小于09:15

条件3:字体变红色

超过09:15小于12:00

这类大小于的条件设置属于基础运算,使用符号`及,就可以进行条件判断。

而在条件格式中,对于处于两个数值之间,也就是介于两者之间的条件判断,我们可以直接选择功能中的”介于“设置:

当然,我们也可以使用公式来设置条件格式:

在这里作者使用公式来进行设置,因为条件格式的精华和难点都在公式设置条件格式上。

现在我们按照习惯性思维输入一个公式:=<$A$3<

那么这个公式对了吗?!

答案是不对!

那么错在哪里?

有3点:

一是条件格式公式设置输入单元格时要使用相对引用,因为引用单元格时系统默认为绝对引用,而绝对引用,会使得条件格式无法向下运行公式,而无法应用到整个区域,因此要记住手动调整单元格引用方式。

二是公式表达式错误,大于一个数字我们可以直接用号,但如果还有一个条件是小于一个数,却不能直接用它。比如3>2,这个表达式的结果是逻辑值,如果2<3<4,也就是,那明显是错误的。

三是公式与数据时间值不对应。啥意思?在数据表中,时间值是文本的格式,很多情况下,软件或者erp系统导出的数字通常是文本格式,如何快速辨认一个数字是不是文本格式,可以参考作者的这篇文章《Excel小技巧:一眼识别表格中的数字是不是文本格式》。而在公式中,“08:46”它不是你想当然的时间值,而是一个文本值,文本值能够执行计算吗?当然不能,除非你把文本值转换为数值。

因此即便是通过上面直接使用“介于”的功能操作,执行的结果仍然是无效的,错误的,因为数据表中的值是文本值,无法进行基础运算。

所以,大家注意一个关键词“把文本值转换为数值”。

在excel中,有几个函数是可以将文本值直接转换为数值的,比如求时间值的timevalue函数,公式如下图所示:

timevalue函数可以将一个文本的时间转换为数值,结果以一个包含众多小数位的数字表示。

那么当数据表中的文本值都可以转换为数值,便可以通过公式进行大小的比较。

接下来我们输入一个条件判断公式:=AND(TIMEVALUE(A3)>TIMEVALUE(),TIMEVALUE(A3)<TIMEVALUE())

这个公式包含了and函数和timevalue函数,两个函数的具体含义和用法,大家可以关注作者的专栏《excel100个常见函数快速入门》。

and函数是多条件判断中的“且”的关系判断,也就是两个条件都要符合,如果都符合它的结果会返回一个逻辑值true,如果任意一个条件不符合,则返回为假的结果,false!

而条件格式为什么叫”条件“格式,是因为它就是通过判断一组条件是否成立,并设置指定的单元格格式,当条件符合时,系统便自动调整单元格格式为设置的指定格式,比如单元格颜色填充,字体大小颜色等。

所以,条件格式的关键点又在于必须是对一个或一组条件的判断,如果要写公式,那么这个公式的结果必须是返回一个逻辑值,也就是真和假——true和false。

接下来,我们复制刚才在单元格输入的公式,然后进入条件格式的公式设置界面,直接粘贴复制的公式,并在格式设置中进行相应的字体颜色选择。

具体操作如下面动图所示:

免责声明:本站部份内容由优秀作者和原创用户编辑投稿,本站仅提供存储服务,不拥有所有权,不承担法律责任。若涉嫌侵权/违法的,请反馈,一经查实立刻删除内容。本文内容由快快网络小樊创作整理编辑!