Excel教程

Excel函数
Excel技巧

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

当前位置:网站首页 > Excel教程

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

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-06-29 23:25:48 * 浏览: 0
用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)
【格式】-【填充】-选择要填充的颜色-【确定】-【确定】