【EXCEL教程】手把手教你制作excel动态交叉查询

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-06-29 23:25:48 * 浏览: 276

用excel制作动态查询表，你会吗？

效果图如下：

动图如下：

看完上图，心中是否有疑惑，查询的公式是怎么写的，下拉列表是怎么出来的，结果高亮显示又是怎么制作的？下面就这几个问题一一解惑！

一、公式怎么写？

交叉查询的公式很多，今天介绍两种，一种是使用函数INDEX+MATCH组合，另一种是使用函数VLOOKUP+MATCH组合。

1、INDEX+MATCH

输入公式：

=INDEX(B2:D8,MATCH(F2,A2:A8,),MATCH(G1,B1:D1,))

MATCH(F2,A2:A8,) 部分找到F2 单元格姓名“爱知趣”在区域A2:A8中的位置4，把它作为INDEX函数的第2参数；

MATCH(G1,B1:D1,) 部分找到G1 单元格内容“数学”在区域B1:D1中的位置2，把它作为INDEX函数的第3参数；

INDEX (单元格区域，行号，列号）

公式就是：=INDEX(B2:D8,4,2)

返回结果就是B2:D8单元格区域中的4行2列，即C5单元格中的值100。

2、VLOOKUP+MATCH

输入公式：

=VLOOKUP(F2,A2:D8,MATCH(G1,A1:D1,),0)

VLOOKUP(查找值，查找区域，返回值在查找区域的第几列，查找方式）

MATCH(G1,A1:D1,)部分找到G1 单元格内容“数学”在区域A1:D1中的位置3，把它作为VLOOKUP函数的第3参数；

公式就是：=VLOOKUP(F2,A2:D8,3,0)

查找F2 单元格姓名“爱知趣”

返回值在区域A2:D8中的第3列，即C列

即返回C5单元格中的值100。

二、下拉菜单怎么制作？

选中F2单元格，【数据】-【数据验证】

选中G1单元格，【数据】-【数据验证】

三、怎么高亮显示？

选中数据区域B2:D8，【开始】-【条件格式】-【新建规则】

在弹出的【新建格式规则】对话框中选中【使用公式确定要设置格式的单元格】

输入公式：=AND($A2=$F$2,B$1=$G$1)

【格式】-【填充】-选择要填充的颜色-【确定】-【确定】