Excel中如何将单元格关联到下拉列表实现成绩联动查询

提前统一工作表命名，减少后续公式调整成本：

• 将存储全量学生信息的第一个工作表重命名为成绩总表，统一列顺序：A列学号、B列姓名、C列对应学科、D列成绩，第一行留作表头，所有学生数据从第二行开始逐行填写，不要留空行；如果你的实际列顺序不同，后续公式对应调整列号即可。
• 第二个用于查询的工作表重命名为成绩查询表。

第一步：创建学号选择下拉列表 #

1. 打开成绩查询表，选中要放置学号选择入口的单元格（例如B2，可在A2提前输入「请选择学号：」做提示）
2. 点击顶部菜单栏「数据」选项卡，选择「数据验证」（2016及更早版本叫「数据有效性」）
3. 验证条件的「允许」下拉项选「序列」，来源框输入公式：

   =OFFSET(成绩总表!$A$2,0,0,COUNTA(成绩总表!$A:$A)-1,1)
   

   该公式会自动抓取成绩总表中所有非空学号，后续总表新增学生信息时，下拉选项会自动同步，不需要手动修改选择范围。
4. 勾选「提供下拉箭头」，点击确定即可，此时点B2单元格就能看到全部学号的下拉选项。

第二步：设置选中学号后自动匹配对应成绩 #

同一学号对应化学、物理、生物3条学科成绩记录，直接在查询表对应位置写函数即可自动关联展示：

1. 先在成绩查询表做展示区表头：A4单元格输入「学科」，B4输入「姓名」，C4输入「成绩」
2. 选中A5单元格，输入匹配公式（365/2021及以上版本Excel输完直接回车，2019及更早版本输完按Ctrl+Shift+Enter三键确认数组公式）：

   =IF($B$2="","",FILTER(成绩总表!$C$2:$C$1000,成绩总表!$A$2:$A$1000=$B$2,"无匹配学号"))
   

   公式会自动拉取选中号对应的所有学科名称，365版本会自动向下溢出填充全部结果，老版本手动选中A5到A7单元格（对应3个学科）再输入公式按三键即可。
3. 选中B5单元格，输入姓名匹配公式，下拉填充到B7：

   =IF(A5="","",INDEX(成绩总表!$B:$B,MATCH($B$2,成绩总表!$A:$A,0)))
   

   同一学号对应唯一学生姓名，只要学科列有内容就会自动显示对应姓名。
4. 选中C5单元格，输入成绩匹配公式，下拉填充到C7：

   =IF(A5="","",INDEX(成绩总表!$D:$D,MATCH(1,(成绩总表!$A$2:$A$1000=$B$2)*(成绩总表!$C$2:$C$1000=A5),0)+1))
   

   公式会和学科列一一对应，返回每个学科的准确成绩，不会出现匹配错位问题。

兼容说明 #

如果使用的是2019及更早无FILTER函数的Excel版本，除了上述数组公式方案，也可以直接插入数据透视表，将学号拖到筛选器、学科拖到行标签、姓名和成绩拖到值区域，选中学号后透视表会自动筛选出对应学生的全部成绩，不需要写公式。

内容的提问来源于stack exchange，提问作者AbuElhaytham