表格中自适应成绩查询系统

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

这种方式有两个缺点：

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所示。然后单击“确定”按钮返回工作表。

图B 设置数据有效性

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

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

本公式中，利用MATCH函数计算A1系别在“成绩表”中A列中的排位，以及计算B1年级在“成绩表”中1行中的排位，然后通过这两个座标返回区域A1:I100中的相应单元格的值。

而公式中的“&""”可以将零值转化为空白。如INDEX引用的区域是空白时，结果会为0，为了将此0值转化为空白，则在公式后面添加“&""”。

1.在单元格A1单击，从下拉列表中选择“法律系”，见图C所示；

2.在单元格B1单元，下拉列表中产生法律系对应的两个年级。从中选择“二年级”，在C1单元格将会自动产生查询结果：外语系三年级的成绩84。见图D所示；

3.单击A1选择“文学系”，则B1的下拉列表将产生四个年级；

4.如果在“成绩表”中添加新的系别或者添加年级数，A1和B2的下拉列表将自动更新。

图C 选择系别

图D 选择年级后产生查询结果

总 结：在利用名称配套数甩有效性产生下拉列表时，都利用OFFSET函数的第四参数或者第五参数指定一个区域引用来达成。而为了让公式适应数据的增减，通常 使用COUNTA函数来获取行或者列中的非空单元格个数，做为OFFSET的参数。本例中展示了公式、名称、数据有效性结合的多功能、自适应查询系统。

……