最全的条件格式教程,做一个好色的Excel达人(下篇)

小雅 友情提示:请加入QQ群:624072350,下载本文的Excel课件,一边看教程,一边打开课件练习操作,学习效果更好。

上一节我们分享了条件格式的套用自动化格式的应用,接下来,我们来点难度更大的实用案例。前面已经说了,条件格式与函数也能结合进行自定义条件格式。

➤ 案例1:提前7天提醒待办的事情

今天是2017年8月12日,作为人事部门,办理员工转正事宜是一项日常工作,员工试用期为90天,设置提前7天提醒哪些人转正期快到了,好准备相关事项。

➜  操作步骤:

1、选择A3:C9,然后单击功能区的条件格式中的新建规则。

2、在弹出的新建格式规则对话框中,选择最后一个“使用公式确定要设置格式的单元格”,然后在下方的输入框中输入函数:=AND(TODAY()-$C3>83,TODAY()-$C3<90),点击确定。

知识补充:

today()是日期函数,自动获取今天的日期。在单元格输入=today(),然后回车,该单元格就会显示今天的日期。如下图。

and()是逻辑函数,就是且的意思。括号内可容纳多个表达式,当所有的表达式同时成立时,返回的结果才成立。

语法:and(表达式1,表达式2,表达式3……)

只有当所有表达式全部成立,它返回的结果才是TRUE,否则是False。

比如:

我们都知道,试用期为90天,如果下周有人试用期将到期,那说明入职时间在83天-90天之间。所以我们就用今天的日期-入职的日期,如果得到的结果:大于83,小于90,则说明下周将转正。所以我们用And(Today()-$C3>83,Today()-$C3<90)。注意这边的$C3是混合引用,3前面是没有$符号的,因为我们的数据是从第3行开始往下到第9行,这个数值要动态变化。你们可以试一下Today()-$C$3看看有什么不一样。

PS:如果要做员工生日提醒,函数要怎么写呢?

➤ 案例2:利用条件格式“自动生成边框线”,A列单元格中数值为空,边框线也自动去掉。

➜  操作步骤:

选中A:D列,然后在功能区的条件格式中选择“新建规则”。选择最后一个“使用公式确定要设置格式的单元格”,然后在下方的输入框中输入函数:=$A1<>””,然后点击格式,将格式中的边框设置为外边框,表示当条件满足时,给单元格加上外边框突出显示。

➤ 案例3:库存上下限预警

当库存量小于安全库存量50时,显示红色警报。当库存量等于入库量时,显示黄色警报

➜  操作步骤:

1、选择D2:D11,然后在功能区的条件格式中选择“新建规则”。选择最后一个“使用公式确定要设置格式的单元格”,然后在下方的输入框中输入函数:=$D2<=50,然后点击格式,设置填充红色警报。

2、继续选择D2:D11,然后在功能区的条件格式中选择“新建规则”。选择最后一个“使用公式确定要设置格式的单元格”,然后在下方的输入框中输入函数:=$B2=$D2,然后点击格式,设置填充黄色警报。

是不是很神奇呀?还有更神奇的,你见过有的Excel表格上隔行或隔列一个颜色或者在表格上画国际象棋的棋盘,你知道怎么画出来的吗?

➤ 案例4:隔行填充不一样的颜色

效果:1、隔行颜色效果(奇数行背景颜色为青色)

2、隔行颜色效果(偶数行字体颜色为红色)

➜  操作步骤:

1、选中A2:H8,然后在功能区的条件格式中选择“新建规则”。选择最后一个“使用公式确定要设置格式的单元格”,然后在下方的输入框中输入函数:=MOD(ROW(),2)=1。设置好格式后按确定。

2、继续选中A2:H8, 然后在功能区的条件格式中选择“新建规则”。选择最后一个“使用公式确定要设置格式的单元格”,然后在下方的输入框中输入函数:=MOD(ROW(),2)=0。设置好格式后按确定。

知识补充:

我们知道什么叫奇数和偶数,就是某个数除以2,如果能除尽,则说明它是偶数;如果除不尽,余数为1,则为奇数。那么问题来了,这个数怎么动态选取?求余函数是哪一个函数?

在这里我们用到了两个函数:

(1) Mod模函数,也就是求余函数。语法也简单,顾名思义,MOD(被除数,除数),返回值是余数。比如:Mod(5,2)=1,5除以2,商为2,余数为1,所以函数返回余数1。

(2) Row()行函数,返回的是当前行的行号。语法:Row()或者Row(A1),一般情况下,括号内没有参数,表示获取当前单元格的行号。括号内有参数,则表示获取该参数单元格所在的行号。

在这里,可能会有网友会问,那如果是隔列呢?这里有个函数Column()列函数,返回当前单元格的列号,用法同ROW()。

所以我们棋盘纹要出现了。眼睛不要眨。

它的条件格式设置如下:讲了这么多了,步骤省略几十个字。

该条件格式的公式用于判断行号与列号之和除以2的余数是否为0。如果为0,说明行数与列数的奇偶性相同,则填充单元格为指定色,否则就不填充。在条件格式中,公式结果返回一个数字时,非0数字即为TRUE,0为FALSE。因此,上面的公式也可以写为:=MOD(ROW()+COLUMN(),2)<>0

只要思想不滑坡,格式的办法总比困难多。条件格式的应用还有很多,由于时间关系,剩下的就留给大家来探讨分享了。

Excel函数的强大,在于它的应用没有定性,每个人的思维不一样,所以解决问题的方法也不一样,没有人能穷尽它的用法,哪怕是它的开发者。

有一个关于Excel的故事跟大家分享一下:

有一个Excel发烧友在一次偶然的学术交流会上认识了一个微软的软件工程师,刚好这个软件工程师是负责Excel函数模块的开发。好不容易碰到一尊“创世神”,这位Excel发烧友肯定得好好交流一下。于是他就把自己学习研究Excel的案例拿出来探讨,问“创世神”有没有什么可改进的,结果这位“创世神”说:“当初开发这个函数时,我都没想过让它有这个功能,你居然把它挖掘出来了。”。

确实如此,我们看Office软件自带的帮助文件,里面的案例都是最基本的功能案例,而这远远不能满足我们解决问题的需要。所以很多人研究它,通过功能的叠加产生了意想不到的神奇效果。

友情提示:宝贝们想学习更多的教程,扫下方二维码,收藏部落窝教育官方微店哦。

部落窝推出的原创Word、Excel、PPT、PS、AI、CDR、CAD、3Dmax、PR、AE、网店美工设计等成套基础视频教程,每门只需9.9元,购买后直接发送到你qq邮箱,下载永久学习。

(0)

相关推荐