Excel 中好用又强大的功能 —— 条件格式

在 Excel 中有一个很特别的功能，会使人眼前一亮。

它会让你的表格数据重点突出，更加醒目地展示在众人的面前；

它会帮你自动完成想要的格式设置，不论是原有数据还是新增数据；

它还可以对表格进行美化……

这个它就是：条件格式。

如下图，这是一个资金流水账：

摘要中如果是「期初余额」和「本期合计」的内容，就把整行自动设置为黄色。变成下面这样：

该怎么做？

问题分析

这个问题如果手动解决的话，可以直接在摘要列中进行筛选，然后分别填充单元格颜色。

如下图：

设置好之后，再取消筛选即可。

但是，如果内容比较多的话，设置起来会比较繁琐。

想让某一单元格的内容等于某一个值时，就让它自动着色的话，当然还是要请出 Excel 中的「条件格式」啦！

下面就跟我一起来看看怎么做到的吧！

解决问题

❶ 选中数据区域，并调出【条件格式】对话框。

选中【A2:F10】单元格区域，然后点击【开始】==》【条件格式】==》【新建规则】，调出【新建格式规则】对话框：

选中【使用公式确认要设置格式的单元格】，并在其中输入如下内容：

公式如下：

=$C2={"期初余额","本期合计"}

意思是：

如果【C2】单元格的内容等于 "期初余额" 或者 "本期合计"，该条件格式成立。将会按照我们下面的设置进行单元格格式的设置。

这里的 C 列要固定住，因为我们只用比较摘要位的内容。

点击【格式】按钮，进行如下设置：

之后点【确定】回到上一界面。

设置好之后，点【确定】。

阿欧！

出了啥问题？

看提示框中写的：

不能在条件格式中使用数组常量！

那这个问题怎么办？

别急，好办！好办！

即然不给写在这里的文本框中，那我们可以写在单元格中，然后去引用这些单元格不就行了！

新建一张【表 2】工作表，将内容写在【A1:A3】。

然后在【编辑格式规则】中进行如下设置：

最后点【确定】，如下图：

欧，好像还是不太对劲？

期初余额所在的行已经自动填色，但是本期合计行并没有发生变化。

又是啥问题？

这次问题的原因是出在公式本身。

=$C2=表2!$A$2:$A$3

因为这个公式的结果会生成两个逻辑值。

当在第 2 行的时候结果如下：

当在第 9 行的时候结果如下：

此公式虽然生成两个值，但是最终只会返回数组中的第一个值，如果第一个值是 TRUE，就符合条件格式的设置要求，该行就会填充黄色；如果第一个值是 FALSE，就不符合条件格式的设置要求，就不会填充颜色。

原理知道了，这下就好办了！在公式的外面套一个 OR 函数。

=OR($C2=表2!$A$2:$A$3)

公式的意思是：

只要两个值当中有一个值是 TRUE, 最终结果就返回 TRUE，该行就会自动着色。

如果两个值都是 FALSE，最终结果就返回 FALSAE，该行就不会自动着色。

最终效果如下：

问题解决了！

知识扩展

❶让条件格式中的公式条件自动扩展

之前我们在【表 2】工作表中设置了两个条件，一个是期初余额，一个是本期合计。

如果想要再新增一个条件：「本年累计」的话，需要在【表 2】工作表【A4】单元格中输入本年累计。

并且还要再重新设置下条件格式中的公式。如下图：

最终结果如下：

这样重复设置条件格式的工作效率比较低下。

能不能让条件增加时，格式的设置自动化呢？

当然可以，我们按照下面的思路来改善下。

① 将【表 2】工作表中的条件区域设置成超级表。

选中表 2 中的【A2】单元格，然后按【CTRL+T】，如下图：

最后点击确定即可。

② 修改条件格式中的公式。

如下图：

公式如下：

=OR($C2=INDIRECT("表1[名称]"))

将原来条件中直接引用单元格区域改成如下形式：

INDIRECT + 表名称 + [+ 表标题名称 + ]

这里的超级表的名称是：表 1

超级表的标题名称是：名称

最后结合使用 INDIRECT 函数构成单元格引用。

通过以上两个步骤的设置之后，我们来验证下效果。

在【表 2】工作表中【A4】单元格中输入本年累计，如下图：

【表 1】工作表中的本年累计行将自动着色。

是不是很神奇！

❷ 让数据源中的条件格式区域自动扩展

我们一开始设置条件格式的时候，是只选择了有数据的【$A$2:$F$10】单元格区域。

我们也可以手动把这个条件格式的区域放的足够大，比如选择【$A$2:$F$9999】区域等。

这样设置的好处是可以满足后期数据增加，不需要手动再重新设置条件格式区域。

缺点是：当表格数据较多时，可能会出现表格卡顿。

当然，我们同样也可以像上面一样，把数据源区域设置成超级表形式。比如把数据源设置成超级表【表 2】这个表名称。

条件格式的区域选择实际的【$A$2:$F$10】数据区域。

此时，当我们有新增数据的时候，比如：在【C11】单元格中写入期初余额的时候，该行将会自动上色。

我们再来看下【条件格式规则管理器】中的应用于数据区域。

这个区域将自动扩展为【=$A$2:$F$11】。

是不是很神奇！

这样设置的优点是数据增加时，条件格式的区域将也自动扩展，避免设置过多的无用区域。

写在最后

今天我们分享了，Excel 中最亮眼的一个功能【条件格式】。

并且可以将数据区域中条件格式【应用于区域】和条件格式中的【公式条件设置】这两个都变成自动扩展功能，大大地提高了我们的工作效率。真正实现了一次设置，终身使用，不需要再手动改变区域或者重新设置条件。

利用条件格式功能可以提醒领导或者老板需要重点关注哪些数据。真的是一项非常好用又强大的功能。