Excel学生成绩分级嵌套IF公式异常修复及E2=0判F逻辑实现

一、修复嵌套IF公式的逻辑错误 #

你当前的嵌套IF公式逻辑顺序错误，只要AM9≥1就会直接返回"D"，后续更高等级的判断完全不会触发。嵌套IF需要从最高阈值到最低阈值依次判断，修正后的GPA等级判断公式如下：

=IF(AM9=5,"A+",IF(AM9>=4,"A",IF(AM9>=3.5,"A-",IF(AM9>=3,"B",IF(AM9>=2,"C",IF(AM9>=1,"D",IF(AM9<1,"F","Null")))))))

二、添加E2=0时返回F的逻辑 #

将上述修正后的公式作为外层IF的分支，先判断E2是否为0，完整公式如下：

=IF(E2=0,"F",IF(AM9=5,"A+",IF(AM9>=4,"A",IF(AM9>=3.5,"A-",IF(AM9>=3,"B",IF(AM9>=2,"C",IF(AM9>=1,"D",IF(AM9<1,"F","Null"))))))))

补充说明 #

• 若公式所在行是第9行（对应学生的成绩单元格是E9），请将公式中的E2替换为E9，确保判断的是当前学生的对应单元格。
• 如果GPA不会出现小于1的非有效值，可以去掉最后的"Null"分支，简化公式：

  =IF(E2=0,"F",IF(AM9=5,"A+",IF(AM9>=4,"A",IF(AM9>=3.5,"A-",IF(AM9>=3,"B",IF(AM9>=2,"C",IF(AM9>=1,"D","F")))))))
  

内容的提问来源于stack exchange，提问作者Md. Imran Hossain