Excel – 制作一组带开关的条件格式设置

请在微信客户端打开

公众号平台最新的推送规则对技术类文章不太友善，如果不想错过干货，请务必“设为星标”哦！！！

点击任意文章上方的“☆星标”即可。

Excel 中的控件，很多人平时不常用。但是大多用到了控件的作品，都非常有意思，我个人很喜欢。

之前我写过不少利用控件制作动态图表或工作表的案例，感兴趣的同学可以找找历史文章。

今天再教大家一个有趣的案例：将控件用作开关，打开或关闭条件格式设置。

案例：

下图 1 为原始数据表，需要按如下要求添加控件和设置条件格式，从而实现根据输入条件动态高亮显示整行。效果如下图 2 所示。

• “高亮显示规则”复选框：相当于总开关，勾选后，输入条件可以高亮显示整行；若取消勾选，输入条件不会高亮显示

• 班级：在下拉框中选择班级，对应的班级整行显示为蓝色

• 分数高于：输入分数，大于这个分数的整行会显示为红色

• “班级”和“分数高于”两个条件为“或”条件；如果同时满足两个条件，则以“班级”优先，即显示蓝色

解决方案：

1. 选择菜单栏的“开发工具”–>“插入”–>“复选框”

2. 将复选框的名称改为“高亮显示规则”–> 根据文字长度适当调整文本框宽度

3. 选中复选框 –> 右键单击，在弹出的菜单中选择“设置控件格式”

4. 在弹出的对话框中选择“控制”选项卡 –> 在“单元格链接”中选择任意一个单元格 –> 点击“确定”

此时，选中或取消选中复选框，G2 单元格就会显示 TRUE 或 FALSE。

5. 将 G2 单元格的字体设置为白色，与单元格背景色一致，就隐藏起来了。

6. 在 F3 和 F4 分别添加新的条件“班级”和“分数高于”–> 开始制作“班级”下拉菜单：选中 G3 单元格 –> 选择菜单栏的“数据”–>“数据验证”

7. 在弹出的对话框中进行以下设置 –> 点击“确定”：

• 允许：选择“序列”

• 来源：输入“一班,二班,三班”

* 请注意：班级之间的“,”是英文半角符号

8. 现在开始设置高亮显示的条件格式：选中需要高亮显示的区域 A2 至 C19 –> 选择菜单栏的“开始”–>“条件格式”–>“新建规则”

9. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”–> 在公式区域输入“=NOT($G$2)”–> 点击“确定”

公式释义：

• G2 单元格就是我们刚才设置为链接到复选框的单元格

• =NOT($G$2)：表示不勾选复选框时触发条件

10. 选择菜单栏的“开始”–>“条件格式”–>“管理规则”

11. 勾选“如果为真则停止”复选框 –> 点击“新建规则”继续设置

设置说明：

• “如果为真则停止”：勾选了这个选项，表示当“=NOT($G$2)”公式结果为真时，即不勾选复选框时，就停止条件触发

12. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”–> 进行以下设置 –> 点击“确定”：

• 公式区域：=$A2=$G$3

• 通过“格式”按钮将背景填充色设置为蓝色

* 请注意单元格的绝对和相对引用，$G$3 为班级下拉菜单，单元格固定不变，需要绝对引用；$A2 的列号需要固定，而行号要动态变化，所以混合引用。

13. 再次点击“新建规则”

14. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”–> 进行以下设置 –> 点击“确定”：

• 公式区域：=IF($G$4<>””,$C2>$G$4)

• 通过“格式”按钮将背景填充色设置为红色

公式释义：

• IF($G$4<>””,…)：当分数值不为空时，才触发条件；这样可以避免不输入分数时，整个区域都变成红色

• $C2>$G$4：当 C 列的单元格大于 $G$4 中输入的分数时，触发条件；仍需注意单元格的绝对和相对引用

15. 通过右上角的上下三角箭头，将设置好的三个条件顺序调整如下：

    1) 复选框条件

    2) “班级”条件

    3) “分数高于”条件

条件顺序说明：

• 公式的顺序代表了同时满足多个条件时，执行的优先顺序

• 复选框条件优先级排第一，所以“如果为真则停止”这个规则对所有次优先级条件都有效；也就是说，当条件为真时，下面的所有条件都不再触发

• “班级”的优先级比“分数高于”高，因此当两个条件同时满足时，执行“班级”的条件

16. 最后稍微美化一下条件区域：将 G 列的单元格都设置为右对齐，给条件区域设置边框，并将底色改为白色

以下就是实际使用效果。