表格中自适应成绩查询系统【百科全说】

菜单式成绩查询
每个学校都会用到成绩查询。常规的查询查询方式是输入待查询对象的相关信息后让系统提取成绩资料。

这种方式有两个缺点：

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

2.如果录入了错别字、同音字，则无法查询到正确的结果。

今天教大家一种新的查询方式，不需要录入任何字符就可以查询任何资料。同时因为不需要录入字符，也就避免了错误的产生。

假设图A是学校中所有班级的平均成绩表，其中各系别的学期长度不同，分别有2年、3年、4年，所以中间存在空白区。

图A 平均成绩表

在本例中，“成绩表”中存放所有数据，需要在“查询表”中显示结果。设计查询功能步骤如下：

一：定义名称

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

2.在名称处键入“系别”，在引用位置处键入：

“=OFFSET(成绩表!A1,1,,COUNTA(成绩表!A:A)-1)”

然后单击“添加”按钮完成第一个名称的定义过程。

3.继续在名称框中键入“年级”，在引用位置处键入以下公式：

“=OFFSET(成绩表!$A$1,,1,,COUNTA(INDIRECT("成绩表!"&(MATCH(查询表!$A$1,成绩表!$A：A,0)&":"&MATCH(查询表!$A$1,成绩表!$A：A,0))))-1)”

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

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

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

1.“=OFFSET(成绩表!A1,1,,COUNTA(成绩表!A:A)-1)”

这个公式是指以成绩表中A1单元格为参照点，偏移1行、0列(Offset的第二参数和第三参数被忽略时默认值为1)，偏移的高度为COUNTA函数所返回的结果减1，即A列中非空单元格个数减1。这个公式用于自适应系别的增减。当在工作表中添加新的系别如“演艺系”时，公式可以自动将之提取出来

2.“=OFFSET(成绩表!A1,,1,,COUNTA(INDIRECT("成绩表!"&(MATCH(查询表!A1,成绩表!A:A,0)&":"&MATCH(查询表!A1,成绩表!A:A,0))))-1)”

这个公式的功能是根据A1的系别返回其对应的年级。结果是一个包含多单元格引用的数组。如果A1是“文学系”，则本公式产生一个一至四年级的数组。如果A1是“法律系”，则本公式将产生一个一至二年级的数组。

本公式的运算较复杂。可以分段理解。其中，MATCH函数用来计算“查询表”中A1单元格的系别在“成绩表”中A列中的排位，并将其结果返回给INDIRECT函数转换为行引用。而 COUNTA函数则计算该引用行中非空单元格的个数，此个数控制着年级的数量，使用公式具有自适应的能力。整个公式将会以“成绩表”中A1单元格为参照，偏移0行、1列，产生一个高度为1（Offset的第四参数和第五参数忽略时默认值为1）、宽度为COUNTA函数返回值的区域引用。

二：生成下拉菜单

为了实现下拉菜单选择条件以查询成绩，需要将前一步所定义的名称套用到数据有效性，产生下拉菜单序列，供用户选择。

1.选择“查询表”中的A1，单击数据有效性，打开“数据有效性”对话框；

2.在“允许”下拉列表中选择“序列”，在“来源”框中输入公式“=系别”，见图B所示。然后单击“确定”按钮返回工作表。