大家好，欢迎回到IT微课程。
临近期末，小编所在学校，又要我去接单做成绩统计了。
成绩统计总体并不难，难点是要将各班的成绩进行分别统计，用筛选，再将学生的成绩拿出来，或用我之前所说的在数据透视表中各班成绩分别生成数据表，但是这样的方法，都是比较麻烦。
而这节课，就跟大家分享下小编的方法吧。
大家可以先看看效果。
当选择不同的班时，下面表格中的成绩跟着改变。
这涉及到两个方面的知识点。
一是下拉菜单；
二是按条件返回多个结果的公式运用。
【学习内容】
1、下拉菜单的制作；
2、按条件返回多个结果的公式编写。
【学习目标】
1、会制作下拉菜单；
2、理解公式各个部分的含义，能根据需要更改公式。
【学习过程】
1、规划好数据表格
这个实例，分三个数据表。
（1）成绩“总表”，将整个年级的成绩都汇总在这里，并做做年级排名。
（2）班级表，如上表所示。
（3）是“设置”表，用于存放基本的信息。
2、下拉菜单的制作
下拉菜单有两种方法，一是采用控件，二是利用【数据】-【数据验证】方法。本文采用后一种方法。
（1）先在“设置”表里，设置好班级，如下表所示。
（2）转到“班级”表，选中B1单元格。
选择【数据】-【数据验证】-【数据验证】，弹出如下的对话框，选择【设置】。
1）在“允许”中选择序列。
2）在“来源”填写：
=设置!$C$1:$C$21
3）单击“确定”，下拉菜单就做好了。
3、转到“班级”表，在第3行做好标题栏，如下表所示。
=INDEX(成绩总表!B:B,SMALL(IF(成绩总表!$B$2:$B$2000=$B$1,ROW(成绩总表!$A$2:$A$2000),4^8),ROW(成绩总表!B1)))&""
这是数组公式，按Ctrl+Shift+Enter组合键，就可以得到与B1单元格的值对应的班级了。
往右边拖拉填充公式，往下边拖拉填充公式，就可以得到如下的数据表了。
（2）公式解释
=INDEX(成绩总表!B:B,SMALL(IF(成绩总表!$B$2:$B$2000=$B$1,ROW(成绩总表!$A$2:$A$2000),4^8),ROW(成绩总表!B1)))&""
可以看到，一对多的查询公式，共用到INDEX+SMALL+ROW三个函数，一个IF判断语句。
1）SMALL(IF(成绩总表!$B$2:$B$2000=$B$1,ROW(成绩总表!$A$2:$A$2000),4^8)
是指从成绩总表的B列查找其值等于B1值的行。
2）ROW(成绩总表!$A$2:$A$2000),4^8)
表示往下查询，直到没有符合条件的值。
3）4^8是指一个表格中的最大行数，其值是65536，当然，在其它版本，其值更大，但小编想，这65536行已足够了。
4）ROW(成绩总表!B1)：从成绩总表的B1单元格开始查找。
5）INDEX函数，返回表或区域中的值或对值的引用。
其格式为INDEX（数据区域，行号，列号），具体可以看小编之前写的INDEX与MATCH函数文章。
【小结】
利用这个一对多的查询公式，可以很方便地进行成绩统计，如果是在企业，也可以利用这个公式组合来处理多部门的数据问题。
再来小结下本课所学的内容吧。
1、先要规划好三个表，一个是信息设置，另一个是总表，还有一个是用于显示各班的表。
2、利用数据-数据验证，可做下拉菜单；
3、利于如下的公式，实现一对多的信息查询。
INDEX(结果列,SMALL(IF(条件,ROW(条件列),4^8),ROW(A1)))&""
不用数据透视表，实现多个表格信息的切换，是不是很方便呢？
本课分享就到这了，欢迎关注IT微课程，一起学习EXCEL。
