【原】小白讲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（错误编码不参与比较，直接返回错误编码）

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

步骤：

选中A列，点击【开始】下【条件格式】，选【新建规则(N)】，选“使用公式确定设置格式的单元格”，录入=A1<”;”,设置格式字体为蓝色，点击确定，重复上述步骤，录入公式=A1<false,设置字体颜色：红色，确定；具体操作参考图4；点击【条件格式】下的【管理规则】，调整两条规则的顺序，点击确定。

区分文本数值逻辑符案例示意图（图5）

这个案例中，我们可以通过解析知道：

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

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

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

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

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

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

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

条件格式案例效果图（图6）

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

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

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

【清除所选单元格的规则(S)】:适合清理局部的单元格规则，在删除整条的条件格式时，就显得力不从心，除非你对条件格式规则使用的范围非常了解，否则建议你使用【管理规则(R)】【清除整个工作表的规则(E)】:适合用于旧表改造或是下载的表格，需要重建条件格式规则，一键清零，干净彻底，包括当前工作表的智能表和数据透视表的规则。【清除此表规则(T)】:专门用于清理智能表中的条件格式；“此表”指的不是工作表，而是Excel的一个功能叫智能表，智能表的默认命名规则为“表”+数字；如果工作表中没有智能表，则此按钮无法使用；【清除数据透视表规则(P)】专门清除数据透视表中的条件格式规则，选中的单元格在数据透视表内，点击按钮，就会删除整个透视表的所有条件规则；这些清除的方法都虽然便捷，但实际的操作中，大部分删除的某条或某个系列的条件规则，就需要用到管理规则(R);

规则管理器界面详解（图7）

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

修改条件规则的小技巧：我们通常编辑条件规则都是，先选中条件规则，然后点击【编辑规则(E)】弹出编辑窗口的，其实你直接双击某条规则就能直接弹出编辑窗口；规则管理器提供了修改条件规则的使用范围功能，这样就能更加灵活的方便我们移花接木，使用他人已经编辑好的条件格式；操作相当简单，支持手动录入或是用鼠标拖拽选择，完成后可以点【应用】按钮预览效果。规则管理器提供修改条件规则优先级的功能，其实就是调整顺序，顺序越靠上，优先级越高；比如录入的两个公式，结果都为真，则会显示位置相对靠上的条件规则设置的样式。至于【删除规则(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(复制)或点【格式刷】按钮，在任意想用的工作表中用格式刷框选单元格范围就能复制条件格式，不用修改应用范围喔，（注：带引用其它表的条件格式无法复制），是不是比上一个更便捷呢？如果你喜欢我的文章，请关注我，我是我爱极客的廖晨，爱讲彩蛋小胖子！也欢迎有问题就留言，我看到后第一时间回复你！

举报/反馈