大家好 ， 欢迎回到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)))&""
不用数据透视表，实现多个表格信息的切换，是不是很方便呢？
【EXCEL：利用一对多的查询公式，实现动态显示各班学生成绩】本课分享就到这了，欢迎关注IT微课程，一起学习EXCEL 。