Excel – 不用VBA，也能制作聚光灯效果

Excel 表格的聚光灯效果，即表格区域选中的行、列高亮显示，较早之前我写过一个用 VBA 实现的方法，具体可参见 Excel实用tips(11) – 聚光灯效果了解一下

今天我教大家另外一种方式，不需要 VBA，只要设置条件格式就能实现聚光灯效果。

案例：

下图 A 至 G 列是数据源，I 和 J 列制作成了下拉菜单；下拉选择 I、J 列后，K 列会自动列出对应的奖金金额，同时左侧的数据源会高亮显示，出现聚光灯效果。

制作步骤：

一、制作下拉菜单：

1. 选中 I2 单元格 --> 选择菜单栏的 Data（数据）--> Data Validation（数据有效性）

2. 在弹出的对话框中选择 Settings（设置）--> 进行如下设置 --> OK：

• 在 Allow（允许）下拉菜单中选择：List（序列）
• 在 Source（来源）中用鼠标选中：=$A$2:$A$95，即所有人员姓名

3. 用同样的方式在 J2 单元格设置月份下拉菜单：

选中 J2 单元格 --> 选择菜单栏的 Data（数据）--> Data Validation（数据有效性）--> 在弹出的对话框中选择 Settings（设置）--> 进行如下设置 --> OK：

• 在 Allow（允许）下拉菜单中选择：List（序列）
• 在 Source（来源）中用鼠标选中：=$B$1:$G$1，即所有月份

4. 下拉菜单已经制作完成：

二、设置奖金查询公式：

1. 在 K2 单元格中输入以下公式：

=INDEX($B$2:$G$95,MATCH($I2,$A$2:$A$95,0),MATCH($J2,$B$1:$G$1,0))

关于 Index+ Match 组合公式的详解，请参见 Excel 如何多条件查询？即同时查询行、列并返回值？

2. 现在公式也已经设置完成了

三、制作聚光灯效果：

1. 选中奖金数值区域 B2:G95 --> 选择菜单栏的 Home（开始）--> Conditional Formatting（条件格式）--> New Rule

2. 在弹出的对话框中选择最下底下一个选项 Use a formula to determine which cells to format（使用公式确定要设置格式的单元格）--> 在公式区域输入 =OR($A2=$I$2,B$1=$J$2) --> 选择行、列高亮显示的浅蓝色 --> OK

公式释义：

• 条件格式的公式区域，对应的是活动单元格，根据整个特性：人名列需要固定列，活动行；月份行反之
• OR 表示二者满足其一，即行列分别高亮显示

3. 现在行、列的聚光灯效果已经设置好了，还需要设置焦点单元格的深蓝色

​

4. 选中奖金数值区域 B2:G95 --> 选择菜单栏的 Home（开始）--> Conditional Formatting（条件格式）--> New Rule

5. 在弹出的对话框中选择最下底下一个选项 Use a formula to determine which cells to format（使用公式确定要设置格式的单元格）--> 在公式区域输入 =AND($A2=$I$2,B$1=$J$2) --> 选择焦点单元格高亮显示的深蓝色 --> OK

公式释义：

• AND 表示行列两个条件都满足筛选条件，结果即是焦点单元格

6. 现在就已经完成了所有设置，以下是选择不同的姓名和月份后的显示效果：