excel条件格式高级应用(Excel表格中条件格式的用法)

我是廖晨,一个爱聊Excel的小胖子,上文聊了条件格式的初级用法,意犹未尽,今天来续前篇,条件格式中高级用法,你值得拥有。

中级

中级用法也没有那么神秘,只是学会了如何使用“条件格式”的条件,就是根据自己编写的公式来启用单元格格式;然而它并没有放在一个显眼的位置上,而是隐藏在“管理工具”中。

       

管理工具示意图(图1)

“管理工具”中有3个命令按钮

1)【新建规则(N)】:包括规则类型有6种,其中大部分我在前文已经解释过了,不再赘述了,这里要补充前文遗漏知识点 “只包含以下内容的单元格设置格式”;

       

只包含一下内容的单元格设置格式(图2

只为包含以下内容的单元格设置格式”:低调的外表下,却有着彪悍的功能,它提供了7种常用的类型选项,单元格值,特定文本,发生日期,空值,无空值,错误,无错误;

1.单元格值:弥补了“突出显示单元格规则”中缺失的逻辑关系,比如:<>(不等于),<=,>=,不介于,在应用范围上更加广泛了,不过它还有一个容易被我们忽略的点就是它输入的内容不光是数字,还支持日期,文本;

  • 日期:符合日期自动识别的规则比如:1.年月日用符号”/”或”-“链接,2.数字符合年月日的规则;
  • 文本:文本间的比较都是根据字符所在编码库中编码号大小比较,知道容易掌握,基本可以忽略,因为很少会用到文本字符比较大小。

2.特定文本:包括4种逻辑关系:包含,不包含,始于,止于;并且支持通配符”*?~”,类似Excel搜索功能,不过它的优势你不需要会任何公式,只需选择应用范围,输入要内容的关键词,设置格式就能标记处结果。

       

特定文本操作示意图(图3)

包含:单元格的内容只要有相关的内容,不论位置的单元格显示设置格式;

不包含:与包含正好相反,排除含有录入内容的单元格显示设置格式;

始于:以录入内容开头的单元格显示设置格式;

止于:以录入内容结尾的单元格显示设置格式

例:如何对A列数据中第2个字博的单元格统一设置字体颜色为蓝色?

现在再看到这样的问题,是不是就太小儿科了,只需选中A列,点击【开始】下【条件格式】,选择【新建规则(N)】,类型选“只为包含以下内容的单元格设置格式”,选特殊文本,包含,输入:?博,点击格式设置字体为蓝色,点击确定完成,整个操作过程只需2-3秒,是不是很爽啊。

       

特殊文本实例操作示意图(图4)

3.发生日期:类似“突出显示单元格”中的“发生日期”想了解可以查看上一篇文章。

4.空值:空值指得是单元格最终结果为空,比如公式=T(123),内容为一个空格的单元格都会显示设置格式;

5.非空值:排除所有为空值的单元格以外的单元格显示设置格式;

6.错误:指包含错误编码的单元格显示设置格式;错误编码有:#VALUE!,#DIV/0!,#NAME?,#N/A,#REF!,#NUM!,#NULL!;

7.无错误:不包含错误编码的单元格显示设置格式;

说完了遗漏的知识点,就需要介绍跟“条件”息息相关的“使用公式确定要设置格式的单元格”,操作界面异常简单,真有点大巧不工的意思,好多Excel好汉都折在条件格式上,不是函数学的不好,而是不知道真正的入门心法:判断的原理以及录入公式的3个要点;

a)判断原理:录入的结果为真,显示设置格式,为假,维持原来的状态。

什么啊这么简单,这个我知道啊,这也能算什么中高级心法吗?

确实简单,重要的是理解,可真正理解这句话的人并不多,先从简单入手:

问:在excel中有哪些常量或值为真呢?

答:TURE和FALSE,数值0和非0,在效果上,TURE和非0,FALSE和0等效,但并不相当,不同的类型常量比较结果:数值<文本<逻辑值false<逻辑值true(错误编码不参与比较,直接返回错误编码)< p="">

例子:一列数据中,有数值,文本,逻辑值,原字体颜色为黑色,用条件格式使得文本为红色字体,数字为蓝色字体(不能用函数)?

步骤

选中A列,点击【开始】下【条件格式】,选【新建规则(N)】,选“使用公式确定设置格式的单元格”,录入=A1<”;”,设置格式字体为蓝色,点击确定,< p="">

重复上述步骤,录入公式=A1

点击【条件格式】下的【管理规则】,调整两条规则的顺序,点击确定。

       

区分文本数值逻辑符案例示意图(图5)

这个案例中,我们可以通过解析知道:

b)录入公式相关的3个要点:

1.录入公式的规则:选择范围内,判断条件列,关于第一个引用单元格的公式;比如选择A列,判断条件在A列,则应录入与A1有关的公式,比如录入b2:e5,判断条件在c列,则录入为C2相关的公式;

2.条件格式运行过程:在录入公式=A1<”;”,设置完格式,点击确定时,会在选择的范围,第1行执行=a1<”;”,如果小于字体显示为蓝色,依次第2行=a2<”;”,如果小于,则字体显示为蓝色,否则保持不变,直到完成选择范围的所有单元格判断;< p="">

问:如果选择的范围扩充到b列,c列,其它两列的单元格样式会根据什么条件显示呢?是用=b1<”;”还是=c1<”;”呢?< strong="">

答:我不知啊!但我们可以验证一下,在b列录入全是数值,c列录入全是文本,将条件格式的使用范围扩充到C列后,结果B列的字体颜色为蓝色,C列字体颜色为红色,由此可以判断出b列执行的是=b1<”;”系列的公式,c列执行=c1<”;”系列的公式;< p="">

问:如何让B列和C列的单元格按照以A列为判断条件来显示单元格格式呢?

答:你读到这,聪明的你是不是发现,条件格式的执行过程是不是有点像,在A1录入公式,然后拖拽填充到其它的单元格的情况,如果想限定行或列,只需在行或列的前面加上$(绝对引用符号),只需将公式改为=$A1<”;”,这样b列,c列的单元格样式都会按照a列判断结果显示啦< p="">

       

条件格式案例效果图(图6)

3.条件格式优先级:规则创建的时间越早,优先级越低,而体现在规则管理器中,位置越考上,优先级越高;如果没有第3步是并不能实现效果,因为第2步的效果直接覆盖掉第一步的效果,因为小于FALSE的不光是文本,还有数值,这也是为什么有第三步的原因,当然你可以创建规则时,提前设计好创建顺序,就可以省略第三步;

如果你的理解并掌握上述的内容, 遇到的大部分条件格式的问题都可以迎刃而解;之前的内容一直围绕的创建,毕竟有些规则随的数据变化,它也会变成无用之物,需要删除清理,就要用到下面要讲的【清除规则(C)】。

2)【清理规则(C)】共有4个命令:

【清除所选单元格的规则(S)】:适合清理局部的单元格规则,在删除整条的条件格式时,就显得力不从心,除非你对条件格式规则使用的范围非常了解,否则建议你使用【管理规则(R)】

【清除整个工作表的规则(E)】:适合用于旧表改造或是下载的表格,需要重建条件格式规则,一键清零,干净彻底,包括当前工作表的智能表和数据透视表的规则。

【清除此表规则(T)】:专门用于清理智能表中的条件格式;“此表”指的不是工作表,而是Excel的一个功能叫智能表,智能表的默认命名规则为“表” 数字;如果工作表中没有智能表,则此按钮无法使用;

【清除数据透视表规则(P)】专门清除数据透视表中的条件格式规则,选中的单元格在数据透视表内,点击按钮,就会删除整个透视表的所有条件规则;

这些清除的方法都虽然便捷,但实际的操作中,大部分删除的某条或某个系列的条件规则,就需要用到管理规则(R);

       

规则管理器界面详解(图7)

3)【管理规则(R)】:点击管理规则按钮弹出条件格式规则管理器窗口,简称:规则管理器,包含了新建,删除,编辑以及调整规则优先级的功能,不过新建规则的界面操作都一样,我已经介绍过了,这需要重点解说的有3点,

  1. 修改条件规则的小技巧:我们通常编辑条件规则都是,先选中条件规则,然后点击【编辑规则(E)】弹出编辑窗口的,其实你直接双击某条规则就能直接弹出编辑窗口;
  2. 规则管理器提供了修改条件规则的使用范围功能,这样就能更加灵活的方便我们移花接木,使用他人已经编辑好的条件格式;操作相当简单,支持手动录入或是用鼠标拖拽选择,完成后可以点【应用】按钮预览效果。
  3. 规则管理器提供修改条件规则优先级的功能,其实就是调整顺序,顺序越靠上,优先级越高;比如录入的两个公式,结果都为真,则会显示位置相对靠上的条件规则设置的样式。

至于【删除规则(D)】的操作,选择要删除的规则后,点击删除按钮,若发现误删且并没有应用,点击取消就好,如果点了应用,不用紧张,点击窗口关闭或确定,然后就用万能的后悔药键:ctrl z,是不是删除的条件格式又回来了,是不是有种生活中要是有个ctrl z 就好了啊!

高级

易说不易练的技能,它需要的额外的知识比较多,比如函数的使用,自定义格式运用,还就就是如何让你的条件格式有更好的维护性,易操作,而操作者并不需要有专业的知识就可以完成。那如何能做到呢?听我娓娓道来。这要从我接到一个case讲起:

我有一个远方的表叔,他家有个小牧场,里面饲养了3种动物,牛马羊,过一段时间统计一次数量,他知道我是学电脑的(学电脑在他们眼里,只要关于电子东西,你应该都会,不过这个恰巧我真会),就问我:晨儿,我的表格不好用,你能不能给我搞一下?

我:你想怎么搞啊!

表叔:不同的动物的数量用不同颜色显示,要是在数量上增加相应头,匹,只就更好了,你能搞吗?

我拿到表格,表格有3列:A列为统计日期,b列为动物名称,C列为数量,牛的字体颜色为蓝色,羊的字体的颜色为粉红色,马的字体颜色为绿色,分别在数量上增加头,匹,只等数量单位;为了方便对比,我在H1:J1分别标注牛,马,羊的字体颜色,做完这些就开始创建条件规则:

这个case的每种动物,对应的两条条件规则,设置一行的字体颜色和根据条件为数量增添单位;

设置一行的字体颜色:选中A,B,C三列,点击【开始】下【条件格式】,选【管理规则】按钮,点击【新建规则】,公式录入=$B1=”牛”,设置字体颜色为蓝色,点击确定,重复创建规则步骤公式录入=$B1=”羊”和=$B1=”马”,分别设置字体颜色为粉红色和绿色;完成点击应用预览效果。

设置数量自定增加单位:选中C列,重复创建规则步骤,分别录入公式=$B1=”牛”, =$B1=”羊”, =$B1=”马”,设置格式,选数字选项,自定义在 通过格式/G后分别加上头,只,匹;完成后,点击应用预览效果。

       

案例效果示意图(图8)

:因为整个范围的字体颜色都是根据B列的判断结果来显示字体颜色的,如果没有绝对引用符号,则A1的判断公式为=b1="牛",B1的判断公式变为=C1="牛",C1的判断公式变为=D1=”牛”,结果只有A列单元格的显示设置字体颜色,其它列判断条件不成立,无法显示设置的字体颜色。

建议:你创建条件规则时,如果都是根据某一列的数据为判断条件,不论应用的范围是一列还是多列都建议使用绝对引用符号,这样即便将来范围有变化,也不会影响条件格式的效果。

后来由于表叔农场饲养的动物种类多了,就再次找到我:晨儿,上次弄的表挺好里,不过我新养的动物就不行,你再搞一下吧!

为了一劳永逸,我重新设计了表格的结构,动物的数量单位总共4-5,暂时只用到4:头,匹,条,只,于是我就创建了一个配置表,

配置表的结构:A列动物,B列为数量单位,A1为牛,b1为头,A2为马,b2为匹,A3为羊,b3为只;

而条件格式的判断公式我们可以用vlookup来做,只不过判断的条件不在是以动物为标准,而是单位,具体操作如下:

打开规则管理器,将“显示其格式规则(s)”调整到数据录入表,先修改字体颜色的规则,将牛录入公式改为=vlookup($B1,配置表!$A:$B,2,0)=”头”,,羊的判断公式改为=vlookup($B1,配置表!$A:$B,2,0)=”只”,马对应的判断公式为=vlookup($B1,配置表!$A:$B,2,0)=”匹”,修改完成点击管理器的应用按钮预览效果,当然我们也许要增加“条”对应的规则,至于方法就不在这里赘述了。

       

案例效果图以及涉及到公式示意图(图9)

不久后,表叔农场又增养新的动物种类,让我帮他弄表,我电话告诉他在配置表里增加对应动物和单位就好了,结果一试真的好用了,自己十分开心,就好像小时候的我,把心心念念的骑车学会了,一样的开心。

文章到这,也把我知道的条件格式的用法说完了,希望阅读的人,能从中有所启发,文章最后,依然是彩蛋,上文移花接木的升级版:首先选中带有条件格式的单元格,ctrl c(复制)或点【格式刷】按钮,在任意想用的工作表中用格式刷框选单元格范围就能复制条件格式,不用修改应用范围喔,(注:带引用其它表的条件格式无法复制),是不是比上一个更便捷呢?如果你喜欢我的文章,请关注我,我是我爱极客的廖晨,爱讲彩蛋小胖子!也欢迎有问题就留言,我看到后第一时间回复你!

举报/反馈

(0)

相关推荐

  • 怎么使用Excel表格中的格式功能实现条件判断

    我们在使用Excel表格的时候,经常会选择使用函数公式判断条件,其实还可以借助格式实现条件判断,今天就跟大家介绍一下怎么使用Excel表格中的格式功能实现条件判断的具体操作步骤.1. 首先打开电脑上想 ...

  • excel表格中日期格式转换为XXXX-XX-XX的样式

    怎么讲Excel表格中的日期样式批量转换为规范化的日期样式,将“XXXXXXXX”日期格式转换为“XXXX-XX-XX”的样式。 方法/步骤 创建一个Excel表格,要求表格中有需要进行规范化显示的日 ...

  • 怎么在excel表格中使用格式刷功能?

    我们在日常工作中经常会使用到excel表格,里面有许多的小功能可以提高我们的工作效率,比如说格式刷,可是有些小伙伴不知道怎么使用,今天,小编教大家在excel表格中使用格式刷功能.具体如下:1. 一开 ...

  • EXCEL表格中HEX2OCT函数的用法详解

    Excel是一个常用办公工具,它本身功能很是强大,自带了很多已经预定义的函数,可以随时根据需要进行调用.而HEX2OCT函数是将十六进制数值转换成八进制数值的函数,那如何在EXCEL表格中使用该函数呢 ...

  • Excel如何通过rept函数在表格中输入多个方格图标

    Excel是现在十分常用的办公软件之一,有些新用户不知道如何通过rept函数在表格中输入多个方格图标,接下来小编就给大家介绍一下具体的操作步骤.具体如下:1. 首先第一步先右键单击桌面空白处,接着在弹 ...

  • WPS表格中条件格式如何操作?

    WPS表格中条件格式如何操作呢?下面小编来教大家. 操作方法 01 首先,我们打开我们电脑上面的wps表格,然后我们在单元格中输入一些数字,之后我们选中这些单元格: 02 然后我们点击条件格式: 03 ...

  • Excel表格中自定义格式代码怎么设置?

    EXCEL表格中,若想对某个或某些单元格进行自定义格式设置,这需要用到对应的格式代码才行的.那么,这些代码是怎样设置的呢? 操作方法 01 比如,在一个有文本,有正数,有负数,亦有0的区域里,把文本给 ...

  • 怎么样把excel表格中的格式列宽行高一起复制

    在制表过程中,以前制作的表格不想在重复制作那么如何操作才能保留原来的样式呢? 操作方法 01 在这里为了演示,我准备了下列数据,如下图所示. 02 如果只想要布局而不想要列宽行高的话,可以直接复制原有 ...

  • excel怎么突出显示数据(表格中怎么设置突出显示数据)

    Office 系列教程 EXCEL篇 第八节, EXCEL 如何突出显示特殊数据!这节课我们用一个完整操作实例演示一个如何在EXCEL表中突出显示符合条件的特殊数据项.首先打开一个EXCEL示例性的表 ...

  • 条件格式如何设置条件(表格中条件格式怎么设置)

    如果说我们有一个简单的数据表的话,比如说我有一个小店,到年底了,我把一月份从12月份的总数据想对比一下,这时候怎么做呢,这时候就要说今天我们的课程了,设置excel2010中的数据条件格式方法,我们根 ...