【原】小白讲Excel:你的“条件格式”的什么段位？高级用法了解一下！

我爱极客 2019-10-12

展开全文

大家好，我是廖晨，在我爱极客工作，个子矮，身材微胖，不自信，在矮矬穷的标准下，我可谓天才型人物，唯独在Excel的使用上才有点自信，闲言少叙，书归正传，上文介绍了“条件格式”主要的常规命令的用法，遗留了三个命令没有说，接着介绍一下管理工具的三个命令。

管理工具：包含了条件格式规则的增，删，改等功能；

1）新建规则(N)：对应关键字“增”,点击按钮会弹出的一个命令窗口，看似一个简单却包括几乎所有条件格式的常规功能，而且里面有个重要的命令就是因为有它才有的中高级的用法，所谓一图解千言，上张图：

新建规则界面示意图(图1）

如图所示，不光是包含关系，而在新建规则窗口的命令对常规按钮的功能进行扩展和增强，更多的设置项，如果你使用常规按钮并不能做到功能一次到位，建议你直接使用【新建规则(N)】可以创建，预览，修改一条龙服务，节省一些没有必要的操作步骤。这里遗漏了一个【使用公式确定要设置格式的单元格】功能没说，先卖个关子。

2）清除规则(C)：对应的关键字“删”，当鼠标停留在这个按钮上，会在右侧弹出4个按钮的菜单，包括：

清除所选单元格的规则(S):开始的时候我常用，后来慢慢发现它太难用了，如果选择范围小了，会需要你再操作一次，并不能保证一次清除完整，虽然遇到几率的很小，一旦遇到总让你心里很不爽，于是就有了选择使用下一个命令的欲望；清除整个工作表的规则(E):这个按钮确实厉害，一键全删，确实很爽，如果误删有用的条件规则怎么办？ctrl+z，重来一次，不过小概率事件，可以接受！清除此表的规则(T):是不是看到按钮的时候，有点懵，从字面上看，这个上一个按钮没啥区别，实际上它指的是新版的Excel中一个好用的功能“智能表”，最简的使用智能表的方法就是一键美化表格，选择使用的范围，ctrl+t,就能将选区开启“智能表”的模式；在“智能表”中使用的”规则“，就可以用“清除此表的规则（T）”来清除。透视表的规则(T):透视表也是Excel中的一个功能，点击按钮就会删除当前透视表中所有的条件格式规则，如果想更细致化管理条件规则就需要用到下面介绍的命令了；3）管理规则(R):对应的关键字 “增”、“删”、“改”；它在条件格式规则的管理上非常灵活，范围小到一个单元格，大到整个工作薄，只要在这个工作薄出现的条件格式，不管是智能表，透视表，还是工作表，它都有相对独立的管理的界面，至于详细的操作，我们会逐一讲解。

条件格式规则管理器(图2）

注：工作表和智能表在默认情况，区别是命名规则，工作表的命名为Sheet+数字，而智能表在“表”+ 数字；

新建规则(N)：功能和【条件格式】下的【新建规则】按钮没有任何区别，这里就不再赘述了。编辑规则(E)：选中要修改的规则，除了点击按钮弹出编辑规则窗口，你还以直接双击规则；如果误点或无修改，直接点击‘取消’按钮返回管理器界面，除了修改规则设置，管理器还提供了修改条件格式的使用范围，单击规则的相应的引用范围公式就可以编辑，可以手工录入，也可以通过鼠标拖拽方式录入，完成直接【应用】可以预览编辑后的效果；

规则管理器修改示意图（图3）

删除规则(D) ：操作就简单多了，只需选中要删除的规则，然后点击删除按钮，就是不能一次同时删除多条数据，在过程中需要预览就点【应用】按钮，如果不想预览，完成删除操作后点确定按钮；介绍完了【条件格式】下的常用按钮，聪明的你有没有发觉，初级玩法一直都是围绕着单元格格式比如颜色，形状；如果将你使用熟练的公式应用到条件格式里，恭喜你进入中级阶段。

中级用法

想玩好“条件”需要了解一下“条件格式”中的公式验证原理，共2点：

录入的公式最终结果为真，启用设置的格式，为假，则保持原来格式状态结果能转为真假的常量只有逻辑值的TRUE和FALSE，数值的0代表FALSE,非0代表TRUE面试题目：A列数据中只有随机TURE和FALSE，要求false字体设置为红色加粗，TRUE为蓝色正常字体，用条件格式实现最多几种方法，并写明操作步骤？

浅析：如果明白原理的，这个题就简单了：

格式设置：一种整列为蓝色正常字体，条件格式为红色加粗字体，一种整列为红色加粗字体，条件格式为蓝色正常字体；条件公式：A列的数据只有两个值，不是TRUE就是FALSE，比如录入=A1则表示A列中TRUE的单元格使用条件格式;这公式换个思路就是A列中不等于FALSE的单元格，公式就可以写成=A1<>FALSE;这样每一套格式设置对应2种公式写法，所有答案是4种方法，不知道我表达清楚了吗？操作步骤：按格式设置分为2种：

第1种

样式设置：选择A列，ctrl+1,调出设置单元格格式，字体设置为加粗，颜色将”自动“改为红色，确定，条件格式设置：点击【开始】菜单下的【条件格式】中的【新建规则(N)】,选“使用公式确定要设格式的单元格”，公式输入=A1或=A1<>FALSE,点击设置格式，字体设置常规，字体颜色为蓝色，点击确定；

条件格式设置步骤示意图（图4）

第2种：

样式设置：重复上一做法，设置格式为字体：正常，颜色：蓝色条件格式设置：在录入公式中输入：=A1=FALSE或=A1<>TRUE,格式设置为字体：加粗，字体颜色：红色，具体步骤参考图4。看到这可能有人会说：什么啊，我只会一种还不是照样好好工作！

确实，每个人对”好“的定义不同，一般的公司面试注重的点是知识点和技巧，而真正大公司更注重的是否具备编程思维；现在已经不是那个只要你会作表格就能找到好工作的时代了，现在需要你做的表可维护性强，易扩展，易上手。而这些必须有编辑的思想才能做到，往往简单的面试题，通常考察都不光是知识点，有的是思维，有的是细心，有的是某些数值的边界问题，如果以后遇到了要多加留意！

简单面试2：A列中包含随机数字正负10之间的数字，如何用条件格式隐藏0的显示？

浅析：这题难点就是如何通过格式隐藏0，只需自定义格式中录入；（英文状态分号），千万别用0的字体颜色设置成白色，表格的背景色一旦改变就”露馅";

操作步骤：

选择A列，然后点击条件格式菜单下的新建规则(N),录入公式=A1=0;点击【格式】按钮选“数字”选项卡下的自定义，在类型输入;;(注:你也可以只输入一个;，系统会自动补全) ,点2次确定

设置隐藏0操作示意图（图5）

说完了两个小例子来总结一下录入公式的注意的地方：

在录入公式时，对应录入选择范围第一个引用位置的公式，如果条件列和格式设置列不是一列的时候，应录入条件列引用位置的公式，否则会导致错误的格式结果。数值只有0的结果为FALSE,其它的结果均为TRUE;但0不等于FALSE，其它数值也不等于TRUE；而不同的类型的关系为：数值<文本<逻辑值FALSE<逻辑值TRUE；数值的逻辑结果互换可并不像逻辑值那么简单了，A1=0等效的公式为=OR(A1>0,A1<0),或=(A1>0)+(A1<0);最后还有条件格式中的“格式”除了字体中只能设置：加粗，斜体，增删下划线，颜色外，其他的数字，边框，填充都与“设置单元格格式”窗口中功能一样，这就是我所知道中级用法！

高级用法

高级用法就是用编程的思想来使用条件格式，什么很抽象？哪我们就举个例子，来领略一下高级用法的魅力吧。先来中级的多条件+自定义格式的用法：

要求： A列为物品名，使用条件格式实现B列数量后自动增加单位：比如牛：头；马：匹；鱼：条；鸡：只；米，面：斤；

浅析：这个题目有两个点需要注意，

条件判断列和设置格式列并不在一列，比如判断条件A列，格式设置列在B列，选择的范围b2:b27,录入公式的时候，录入=A2(相关的公式)，如果选择整列这则录入A1(相关的公式)设置数值带单位正确的做法就是设置单元格中的自定义：格式编码如：G/通用格式+单位或#＋单位或0+单位，录入时不用特意输入“”，完成录入后，系统会自动补充完整；操作步骤：选择整列B列，然后【条件格式】下的【管理规则】,点击【新建按钮(N)】选“使用公式确定要设置格式的单元格”，录入公式=A1=”牛”,设置格式：【自定义】操作界面的类型中，在默认：G/通用格式后面直接输入头，点击确定完成设定；

重复新建规则步骤，直到完成所有条件规则；这里的面和米对应的是一个单位，公式用=or(A1=”米”,A1=”面”)或=(A1="米")+(A1="面"),其它都是单条件公式。