三:设计成果公式下拉列表设计完成后

菜单等级查询每个学校都会使用等级查询。常规的查询方法是输入待查询对象的相关信息，然后让系统提取评分数据。

这种方法有两个缺点：

1.需要输入文字，对于不会打字或者输入速度慢的人不方便；

2.如果输入错别字和同音字，就找不到正确的结果。

今天教大家一个新的查询方法。您可以在不输入任何字符的情况下查询任何信息。同时，由于不需要输入字符，避免了出错。

假设图A是学校所有班级的平均分表，其中每个系的学期长度不一样，分别是两年、三年、四年，所以中间有一个空白区。

平均成绩表

在本例中，所有数据都存储在“报告表单”中，结果需要显示在“查询表单”中。设计查询函数的步骤如下：

1.定义名称1。进入工作表的“查询表”，点击单元格A1，在菜单工具栏中选择插入名称定义，打开定义名称对话框；

2.在名称中键入“部门”，在参考位置中键入“部门”:

"=OFFSET(成绩单！A1，A1，1，COUNTA(成绩单！A:A)-1”)

然后点击添加按钮，完成名字的定义过程。

3.继续在名称框中键入“Grade”，并在参考位置键入以下公式：

"=OFFSET(成绩单！$ a $1，1，县(间接('报表！'(MATCH(查询表！1澳元，成绩单！$ a: a，0)' : '匹配(查表！1澳元，成绩单！$A:A，0))))-1)"

然后单击添加按钮并关闭窗口。

提示：定义名称时，当前活动单元格的位置非常重要。在本例中，您需要选择A1来定义名称。

本例中两个公式的含义如下：

1.”=offset(成绩单！A1，A1，1，COUNTA(成绩单！A:A)-1”)

这个公式是指以评分表中的单元格A1为参考点，移位1行0列(忽略Offset的第二个和第三个参数时默认值为1)，移位的高度为COUNTA函数返回的结果减1，即A列非空单元格数减1。该公式用于自适应系统的增减。当工作表中新增“演艺部”等部门时，公式可以自动提取。

2.”=offset(成绩单！A1，1 A1，1，COUNTA(INternet('报表'！)(MATCH(查询表！A1，成绩单！A:A，0)':'MATCH(查表！A1，成绩单！A:A，0))))-1)"

这个公式的作用是根据其所属部门返回相应的A1等级。结果是一个包含多个单元格引用的数组。如果A1是“文学系”，那么这个公式会产生一个一到四年级的数组。如果A1是“法律部”，这个公式会产生一个一到两级的数组。

这个公式的操作很复杂。可以分段理解。其中MATCH函数用于计算成绩表A列查询表中A1单元格的排名，结果返回间接函数转换为行引用。COUNTA函数计算参考行中非空单元格的数量，它控制等级的数量，公式是自适应的。整个公式将以“成绩表”中的A1单元格为参照，移位0行1列，得到一个高度为1的区域参照(第四个和第五个参数为1时默认值为1(Offset被忽略)，COUNTA函数返回值的宽度。

二、生成下拉菜单为了实现下拉菜单选择条件查询结果，需要将上一步定义的名称应用于数据有效性，生成下拉菜单序列供用户选择。

1.在查询表中选择A1，点击〖数据有效性〗按钮，弹出数据有效性对话框；

2.在“允许”下拉列表中选择“序列”，在“来源”框中输入公式“=部门”，如图b所示，然后点击确定按钮返回工作表。

图b设置数据有效性

3.选择单元格B1，重复步骤1和2，将数据有效性添加到B1，其源公式为“=grade”。

注意：“来源”框中的公式必须使用半角的等号，否则无法得到正确的结果。

第三，设计成果公式下拉列表设计完成后，需要一个单元格来显示查询结果。1.选择C1单元格，输入以下公式：=INDEX(评分表！A1:I100，MATCH(A1，成绩单！A:A，0)，MATCH(B1，成绩单！1:1，0))在这个公式中，使用MATCH函数计算“报表”A列的A1等级和“报表”第1行的B1等级的等级，然后通过这两个坐标返回A1:I100区域对应单元格的值。