excel制作自动关联的查询列表

效果演示

这个实例有两个工作表，一个是数据表第一行为年级，第二行开始为该年级对应的班级。一个是查询表，第一行是标题，分别是年级和班级，A2单元格以下拉列表显示年级，数据来源于数据表的第一行，更改A2单元格中的年级，在 B列从B2单元格开始显示该年级下的所有班级！

算法分析

第一步年级下拉列表的实现其实比较简单，用数据验证实现就可以。

年级更改后显示相应的所有班级则需要用公式完成，需要显示年级对应列从第2行开始所有数据，这里需要用到IF、INDIRECT、ADDRESS、ROW、MATCH这几个函数，关于这几个函数的用法和详细的算法，我们将在演示时详细的给大家讲解。

大家可以到信息技术爱好者网站www.51kdn.com的办公应用-电子表格栏中“excel制作自动关联的查询列表”中下载本视频的实例文件。大家在学习和工作中遇到问题欢迎到评论区一起研究学习！

虽然我水平有限但一直很用心的坚持原创，请大家长按红心点赞或赞赏支待，同时也欢迎大家关注转发！

建立工作表

新建一个空白的EXCEL工作簿，在sheet1工作表里，在A1单元格输入年级，在B1单元格输入班级。新建一个sheet2工作表，在A1单元格输入高一，B1单元格输入高二，C1单元格输入高三；然后在A2单元格输入高一1班，在B2单元格输入高二1班，在C2单元格输入高三1班；再然后将A2:C2区域选中，向下拖动填充柄直到10班。这样，我们所需要的工作表就建成了。

班级查询列表第一步：获取年级所在的列MATCH函数

当年级发生改变时，我们首先要获到在数据表中年级所在的列，这里就需要用到MATCH函数。

MATCH函数：可在单元格区域 （区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）中搜索指定项，然后返回该项在单元格区域中的相对位置。

MATCH 函数语法具有下列参数：

lookup_value 必需。 要在 lookup_array 中匹配的值。 例如，当您在电话薄中查找某人的电话号码时，您将其姓名作为查找值，但是电话号码是您需要的值。 lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

lookup_array 必需。 要搜索的单元格区域。

match_type 可选。 数字 -1、0 或 1。 match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。 此参数的默认值是 1。

1 或省略 MATCH 查找小于或等于 lookup_value 的最大值。 lookup_array 参数中的值必须以升序排序。

0 MATCH 查找完全等于 lookup_value 的第一个值。 lookup_array 参数中的值可按任何顺序排列。

-1 MATCH 查找大于或等于 lookup_value 的最小值。 lookup_array 参数中的值必须按降序排列。

班级查询列表第二步：获取班级所对应的行ROW函数

我们在查询结果中班级对应的行数和数据表中的行数是一致的，也就是查询结果列表中第2行的数据对应数据中相应列第2行的数据，这里我们需要用到ROW函数。

ROW函数返回引用的行号，如果省略参数的话返回的函数所在单元格的行号。

班级查询列表第三步：根据行号和列号返回单元格地址

我们已经获得了相应的行号和列号，还需要将其转换为单元格地址，这里就需要用到ADDRESS函数。

ADDRESS 函数，根据指定行号和列号获得工作表中的某个单元格的地址。

语法规则：ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num 必需。 一个数值，指定要在单元格引用中使用的行号。

column_num 必需。 一个数值，指定要在单元格引用中使用的列号。

sheet_text 可选。 一个文本值，指定要用作外部引用的工作表的名称。

班级查询列表第四步：根据行号和列号返回单元格地址

我们已经计算出了需要引用的单元格的名称，但这时它还只是一个字符串，我们还需要引用该字符串指定的单元格，这里需要用到INDIRECT函数。

INDIRECT函数：返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数 INDIRECT。

班级查询列表第五步：无数据单元格处理

这时我们会发出，没有数据的空单元格显示#VALUE!，我们只需要用if函数判断一下，如果返回值<>0就显示数据，否则返回一个空字符。

班级查询的完整公式/h2>

刚刚我们分步操作中将结果放在不同单元格中，我们班级查询的公式放在一个单元格中，其完整公式如下：

=IF(INDIRECT(ADDRESS(ROW(),MATCH($A$2,数据!$1:$1,0),,,"数据"))<>0,INDIRECT(ADDRESS(ROW(),MATCH($A$2,数据!$1:$1,0),,,"数据")),"")

欢迎到下方观看视频中去观看详细的视频讲解，并长按点赞支持我！