Excel超强函数组合index match 动态显示查询结果 行列高亮上色

关键词：数据有效性,条件格式,高光,特效,

划重点：数据有效性,条件格式,INDEX+MATCH函数,

效果图预览：

根据行列表头查找数据并高亮显示

操作步骤：

步骤一：准备原始数据，明确查询条件及所求结果。

步骤二：选中单元格H2，在菜单栏中选择 数据-数据有效性，如下所示:

步骤三：在数据有效性中，有效性条件选择'序列'；来源处输入列标签即'=$A$2:$A$7'，科目同理，如下图。

步骤四：在H5处输入查询公式：=INDEX(A:E,MATCH(H2,A:A,0),MATCH(H3,1:1,0))

1） 公式中： A:E为查询结果区域；

2） MATCH(H2,A:A,0)以及MATCH(H3,1:1,0)分别是由MATCH函数查询出H2单元格在A:A区域中所在位置,本例结果是3; H3单元格在1:1区域中所在位置, 本例结果是3；

3） 这两个结果作为INDEX函数指定要返回的行列数，在调整'姓名'及'科目'时，MATCH函数的结果是动态变化的，作用给INDEX函数，就返回对应的内容。

步骤五：设置条件格式，我们要达到的效果:

1)当列标题等于H2中的姓名时，这一列的内容就高亮显示；

2)当行标题等于H3中的科目时，这一行的内容就高亮显示。

操作如下:

1)选中A2:E7区域,在菜单栏中选择'开始'-'条件格式'-'新建规则',规则类型选择'使用公式确定要设置格式的单元格',录入公式=AND($A2=$H$2,COLUMN()<=MATCH($H$3,$A$1:$E$1,0)),在格式中选择填充颜色;

2) 选中B1:E7区域,在菜单栏中选择'开始'-'条件格式'-'新建规则',规则类型选择'使用公式确定要设置格式的单元格',录入公式AND(A$1=$H$3,ROW()<=MATCH($H$2,$A$1:$A$7,0)),在格式中选择填充颜色;如下图。

设置完毕,简单又方便的查询数据区域高亮显示就完成啦~

Excel无理论纯实战项目实操