Excel – 销售奖金二维表查询，累计求和的区域自动变色

今天的案例需求听上去有点复杂，但是通过巧妙设置，在演示汇报的时候领导一眼就能明白公式所要表达的意图，反而简化了工作沟通。

而所需的设置，无非是老瓶装新酒，只要稍加点拨，我的老读者基本都能够独立完成。

案例：

下图 1 为某公司销售人员去年每个季度的奖金统计表。要求根据姓名和季度进行查询：

对指定销售人员的 Q1 至查询季度的所有奖金求和；

上述查询区域自动高亮显示

效果如下图 2、3 所示。

解决方案：

先制作“姓名”和“截止季度”下拉菜单。

1. 选中 G2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

2. 在弹出的对话框中点击“设置”选项卡 --> 按以下方式设置 --> 点击“确定”：

允许：序列

来源：选择 A2:A13 区域

3. 选中 H2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

4. 在弹出的对话框中点击“设置”选项卡 --> 按以下方式设置 --> 点击“确定”：

允许：序列

来源：选择 B1:E1 区域

接下来设置求和公式。

5. 在 I2 单元格中输入以下公式：

=SUM(INDEX($B$2:$B$13,MATCH(G2,$A$2:$A$13,0)):INDEX($B$2:$E$13,MATCH(G2,$A$2:$A$13,0),MATCH(H2,$B$1:$E$1,0)))

公式释义：

INDEX($B$2:$B$13,MATCH(G2,$A$2:$A$13,0))：

如果 $A$2:$A$13 区域中的值与 G2 单元格的内容完全匹配，则定位到 $B$2:$B$13 区域中的同等行；

这段公式的目的是根据姓名查找到对应的 Q1 列的值，即求和的起始单元格

INDEX($B$2:$E$13,MATCH(G2,$A$2:$A$13,0),MATCH(H2,$B$1:$E$1,0))：

根据“姓名”和“截止季度”两个条件，定位到查询区域内的交叉单元格；

也就是求和区域的截止单元格

SUM(...:...)：对起始和截止单元格之间的所有区域求和

* 除了 match 函数中的第一个参数以外，公式中的所有参数都必须绝对引用。

最后设置查询区域自动高亮显示。

6. 选中 B2:E13 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

7. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮：

=AND($A2=$G$2,COLUMN(A1)<=MATCH($H$2,$B$1:$E$1,0))

公式释义：

$A2=$G$2：

姓名列的值等于 G2 单元格中的姓名

通过该公式定位到行

COLUMN(A1)<=MATCH($H$2,$B$1:$E$1,0)：

列值为选中区域内的第一列至 H2 单元格相同值所在的列

AND(...)：同时满足上述行、列值要求的区域将被高亮显示

8. 在弹出的对话框中选择“填充”按钮 --> 选择所需的填充色 --> 点击“确定”

9. 点击“确定”

现在就已经全部设置完成。从 G、H 列的下拉菜单中选择不同的值，数据表中该员工从 Q1 至查询季度的区域会自动高亮显示，I 列则自动对高亮区域求和。

转发、在看也是爱！