大家好，欢迎回到IT微课程临近期末，小编所在学校，又要我去接单做成绩统计了成绩统计总体并不难，难点是要将各班的成绩进行分别统计，用筛选，再将学生的成绩拿出来，或用我之前所说的在数据透视表中各班成绩分别生成数据表，但是这样的方法，都是比较麻烦。
而这节课，就跟大家分享下小编的方法吧。大家可以先看看效果。当选择不同的班时，下面表格中的成绩跟着改变。
这涉及到两个方面的知识点一是下拉菜单；二是按条件返回多个结果的公式运用【学习内容】1、下拉菜单的制作；2、按条件返回多个结果的公式编写【学习目标】1、会制作下拉菜单；2、理解公式各个部分的含义，能根据需要更改公式。
【学习过程】1、规划好数据表格这个实例，分三个数据表（1）成绩“总表”，将整个年级的成绩都汇总在这里，并做做年级排名（2）班级表，如上表所示（3）是“设置”表，用于存放基本的信息2、下拉菜单的制作下拉菜单有两种方法，一是采用控件，二是利用【数据】-【数据验证】方法。
本文采用后一种方法（1）先在“设置”表里，设置好班级，如下表所示。
（2）转到“班级”表，选中B1单元格选择【数据】-【数据验证】-【数据验证】，弹出如下的对话框，选择【设置】1）在“允许”中选择序列2）在“来源”填写：=设置!$C$1:$C$213）单击“确定”，下拉菜单就做好了。
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。
亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。