Excel教程

Excel函数
Excel技巧

【EXCEL教程】条件格式

当前位置:网站首页 > Excel教程

【EXCEL教程】条件格式

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-06-08 22:50:43 * 浏览: 16

哈喽!同学们,好久不见,不知道有没有想我这个外表黄内心更黄的土豆呢?

哈哈哈哈,当然是有没有想我继续给大家分享一点Excel的技巧啦!!

好啦,今天就来给大家分享一个“美化”工作表的技巧-条件格式!


首先来看一下问题吧,



不难看出,这是一个题库,一共有2557,咱们要看问题还能在A列通过标识来找,可是要看答案就有点眼花了!!


若是对它进行一个美化,把问题和答案分别标记上不同的颜色,那看起来是不是会轻松一些呢?


数据量少的情况下,还可以手工一个一个的做,问题呢在A列可以筛选,然后再填充颜色,可答案就麻烦了,每个问题选项都不一样,2千多行,手工做的话时间老长啦!!


不怕不怕,答案在C列有选项,可以利用这个选项,让Excel自动给我们标记颜色(此功能叫条件格式,根据条件来自动设置单元格格式-简称条件格式


填充规则有了了解,再来看数据规则,筛选A列标识,很容易发现,每个题都是5个选项,知道这数据规则以后,问题就简单多了!


解题思路很多,这里就拿比较常用的CountifIf、Row和Offset 个函数来解,


Countif 条件计数,它有两个参数,

第一个参数:条件区域(统计区域),

第二个参数:条件(统计什么)


Offset 函数用作Countif的参数一,因为它可以返回一个区域


比如:offset($C2,,,-6) 表示以C2单元格为基点,返回上6个单元格(包含自己)


至此条件格式中的公式为:=Countif(Offset($C2,,,-6),$B2)

来看操作动画

条件格式中写公式要注意两点:

1、 公式中的行号必须要与激活单元格对应(特殊需求除外)

2、 必须要绝对引用列,因为我们要将格式应用到多列。


通过动画,我们看到效果已经实现了,但是却出现了一个问题,第一个题答案没有标出来!


那是因为此公式在第6行之前,offset函数会返回Ref错误所以它不能返回正常结果


解析刚才的 offset($C2,,,-6),C2往上的6个单元格地址是C-2:C2(C列负2行到C列第2行),因为我们单元格是从第1行开始,不存在比1小的行号(这里还要返回负的行号,太为难Excel了),因此它就出错了!


所以要解决此问题,我们可以用IF函数来判断一下,当行号(Row)小于6的时候,让它返回另一个值,大于等于6的时候才返回 -6


Row函数在不使用参数的情况下,它将返回公式所在单元格行号


因此我们的逻辑处理公式为:If(row()<6,Row()*-1,-6)


当然,也可以使用 Max(Row()*-1,-6) 效果是一样的。

思考:为什么要*-1



将此公式用作offset的参数4,替换掉原来的 -6


更改后的公式如下:

=COUNTIF(OFFSET($C1,,,IF(ROW()<6,ROW()*-1,-6)),$B1)


公式更新以后,再看一下效果吧!!

正所谓 学而不思则罔,思而不学则殆接下来是练习与思考时间!


练习题:

添加一个条件格式,将题目标记另一个颜色(第二张图片的效果),此难度非常低,只需要判断A列的值即可实现。


思考题:

如何修改条件格式中的公式?


PS:条件格式中不仅看函数功底,更看重操作细节(单元格地址引用、对应行号列标),这是必须要掌握的,掌握不到,设置出来就会不对!所以要想掌握它就一定要多加练习。