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

2019年7月13日19:45:50 评论 804 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

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

划重点:数据有效性,条件格式,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)),在格式中选择填充颜色;如下图。

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

  • 郑广学老师微信号
  • EXCEL880A
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!