哈喽!同学们,好久不见,不知道有没有想我这个外表黄内心更黄的土豆呢?
哈哈哈哈,当然是有没有想我继续给大家分享一点Excel的技巧啦!!
好啦,今天就来给大家分享一个“美化”工作表的技巧-条件格式!
首先来看一下问题吧,
不难看出,这是一个题库,一共有2557行,咱们要看问题还能在A列通过标识来找,可是要看答案就有点眼花了!!
若是对它进行一个美化,把问题和答案分别标记上不同的颜色,那看起来是不是会轻松一些呢?
数据量少的情况下,还可以手工一个一个的做,问题呢在A列可以筛选,然后再填充颜色,可答案就麻烦了,每个问题选项都不一样,2千多行,手工做的话时间老长啦!!
不怕不怕,答案在C列有选项,可以利用这个选项,让Excel自动给我们标记颜色(此功能叫条件格式,根据条件来自动设置单元格格式-简称条件格式)
对填充规则有了了解后,再来看数据规则,筛选A列标识,很容易发现,每个题都是5个选项,知道这数据规则以后,问题就简单多了!
解题思路很多,这里就拿比较常用的Countif、If、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:条件格式中不仅看函数功底,更看重操作细节(单元格地址引用、对应行号列标等),这是必须要掌握的,掌握不到,设置出来就会不对!所以要想掌握它就一定要多加练习。