编辑

▲ 开关效果 | 用复选框控制条件格式

大家好，我是徐军泰——有讲特约讲师、唯库函数课《一次性学透Excel函数，5分钟搞定一天工作量》的主讲老师。

今天教大家一个非常实用的技巧——用开关控制条件格式。

如上效果图所示，左侧为数据报表，右侧提供了三个下拉菜单单元格，可以选择你想查看的银行名称（当然也可以制作多个下拉单元格）。

右下方还有一个复选框控件，勾选后设定好的条件格式就会将所选银行对应的数据行自动加上颜色进行强调（Highlight），再次勾选则取消条件格式。

设置条件格式进行highlight的目的是让我们更准确、迅速地定位到目标数据，不至于眼花。再配上这个「开关」后，报表显得更加智能了，不是吗？

下面就给大家介绍制作过程和思路。

01 条件格式效果的实现

本例中报表区域为：B4:I48，数据为中信等8家股份制银行的信用卡经营指标（来自银行年报）。

L6:L8区域通过数据有效性设置了三个下拉单元格，可以选择想要强调的银行。

我们看到的直观效果是，当下拉单元格选择某家银行（如光大）后，报表区域的所有光大银行所在数据行就自动变成了橙色。

编辑

▲ 点击查看清晰大图

那么，这个自动强调效果是如何实现的呢？

答案就是条件格式，让Excel根据我们设定的条件公式自动对报表区域的每个单元格进行判断，一旦这个单元格满足我们设定的条件，就自动设置为橙色。

由于强调效果是按行添加的，我们只要判断报表每一行的银行名称是否是L6:L8中我们选择的银行。

比如第1行为民生，不在右侧所选列表（光大、平安），就不添加格式；而第2行为光大，在右侧所选列表，因此自动添加条件格式。

按照这个逻辑，我们编写公式如下：

编辑

如上图所示，条件公式为：

=ISNUMBER(MATCH($B4,$L$6:$L$8,0))

其中，MATCH函数返回报表B列银行在L6:L8银行列表中的次序。从第1行的银行名称开始判断，如果是L6:L8中的所选银行，则MATCH一定返回一个数字；否则，如果不属于我们的选择列表，则返回错误值。因此，可以外嵌ISNUMBER函数进行判断。

理解了这个公式和条件格式的生效逻辑，我们来看操作步骤：

------------------------------------------------

选择报表数据区域，打开条件格式，新建规则，选择使用公式确定要设置格式的单元格；

编辑

编写公式后，确定。

编辑

02 开关效果（On / Off）的制作

做好了条件格式的基础效果，重点来了——下面的复选框开关效果是如何实现的呢？

核心的秘密在这里 ▼

编辑

▲ 点击查看清晰大图

如上图所示，条件格式规则管理器中有一个控制条件格式生效与否的小方框（上图的红色框），如果勾选后，如果对应的条件公式判断结果为真则停止条件格式生效，否则，如果判断结果为假则维持条件格式这个功能平时很容易被忽略。

下面我们来看如何通过复选框进行开关控制。

-----------------------------------------------------

激活「开发工具」选项卡，插入并绘制一个复选框控件；

编辑

编辑

改名并链接到粘贴到任意空白单元格，如L13；

编辑

▲设置完成后，勾选复选框，L13输出逻辑值TRUE，取消勾选则输出FALSE

将新增加一条条件格式，公式为：=NOT($L$13)，并勾选「如果真则停止」。

编辑

确定后，即可实现开关效果，再来看一下最终的效果。

编辑