Excel中如何基于科目匹配等级阈值自动评定成绩等级

问题背景 #

我有一份学生成绩计算用的Excel文档，布局为顶部单元格显示科目名称，下方依次列出作业名称、原始分数、百分制分数等字段，最后一列需要根据对应科目的等级阈值自动生成A-E字母等级。例如科目为化学时，等级单元格需引用GRADES表中化学科目的阈值——该表顶行是科目名称，左侧列是A-E等级，单元格内为对应等级的最低百分制分数。

最初使用嵌套IF公式实现：

=IF(G9>=80,"A",IF(G9>=60,"B",IF(G9>=40,"C",IF(G9>=20,"D",IF(G9>=1,"E","")))))

其中G9为百分制分数单元格，但该公式需针对每个科目手动修改阈值，效率极低且无法适配不同学生的不同科目。后续尝试的INDEX+MATCH组合公式未达到预期效果：

=IF(G9="","",INDEX('B7'!B1:B5,MATCH(H8,'B7'!A1:A5,1)))

示例数据 #

当前成绩表

GRADES表

解决方案 #

使用嵌套INDEX+MATCH组合公式，可自动定位对应科目的阈值列，并匹配出正确等级，无需手动修改阈值。

公式写法（需根据实际单元格位置调整） #

假设当前成绩表中：

• 科目名称位于单元格A1
• 百分制分数位于单元格F2
• 等级输出单元格为G2

公式如下：

=IF(F2="","",INDEX(GRADES!$A$2:$A$6,MATCH(TRUE,F2>=INDEX(GRADES!$B$2:$D$6,,MATCH(A1,GRADES!$B$1:$D$1,0)),0)))

公式拆解 #

1. 定位科目列：MATCH(A1,GRADES!$B$1:$D$1,0) 找到当前科目在GRADES表顶行的列序号（如Chemistry对应第2列）
2. 提取科目阈值列：INDEX(GRADES!$B$2:$D$6,,[科目列序号]) 提取该科目所有等级的最低分数阈值
3. 匹配对应等级：MATCH(TRUE,F2>=[阈值列],0) 找到第一个满足「百分制分数≥阈值」的行位置，对应GRADES表左侧的等级行
4. 输出等级：INDEX(GRADES!$A$2:$A$6,[匹配行号]) 提取对应的A-E等级
5. 空值处理：外层IF(F2="","",...) 确保百分制分数为空时，等级单元格保持空白

注意事项 #

• 确保GRADES表中的等级阈值按A到E从高到低排列，否则MATCH无法正确匹配首个满足条件的等级
• 公式中的单元格引用需根据实际表格范围调整，建议使用绝对引用（如$A$2）避免下拉填充时引用偏移

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