excel 给成绩等级划分四个,最常用方案是使用嵌套 IF 函数,按照分数区间划分四个等级,例如优秀良好及格不及格,也可以自定义分数阈值和等级名称,公式输入之后下拉填充,整列成绩自动生成对应等级,除嵌套 IF 之外,新版 excel 还可以使用 IFS 函数,写法更加简洁,不需要多层括号嵌套,两种方法都可以实现四档成绩等级划分,适合学生成绩表考核评分表,核心要点是区间边界不能重叠,判断顺序要从高分往低分依次判断,不然会出现等级判断错误,同时可以搭配条件格式,给不同等级单元格设置不同底纹,让成绩等级显示更加直观。
等级划分标准参考
| 等级 | 分数区间 |
|---|---|
| 优秀 | 85 分及以上 |
| 良好 | 70 分到 84 分 |
| 及格 | 60 分到 69 分 |
| 不及格 | 60 分以下 |
这个区间可以根据实际需求自行修改,比如改成 90 以上优秀,80 到 89 良好,70 到 79 及格,70 以下不及格。
方法一 嵌套 IF 函数(兼容全部版本 excel)
假设原始成绩存放在 B2 单元格,等级输出放在 C2 单元格
1 点击 C2 单元格,输入公式
=IF (B2>=85,"优秀",IF (B2>=70,"良好",IF (B2>=60,"及格","不及格")))
2 按下回车键,C2 单元格自动显示对应等级
3 鼠标移动到 C2 单元格右下角,等光标变成黑色十字填充柄,按住向下拖动,整列成绩批量生成等级
4 如果需要修改分数分界,直接修改公式里面的数字,等级文字也可以自定义替换
原理说明,公式优先判断最高区间,满足优秀就返回优秀,不满足继续判断下一档良好,再判断及格,剩下全部归入不及格。
方法二 IFS 函数(新版 excel 推荐)
新版 excel 支持 IFS,公式结构更清晰,不用多层嵌套括号,同样 B2 为成绩单元格
1 在 C2 单元格输入公式
=IFS (B2>=85,"优秀",B2>=70,"良好",B2>=60,"及格",TRUE,"不及格")
2 回车确认,下拉填充整列
3 最后一项 TRUE 代表前面条件都不满足,也就是不及格
注意低版本 excel 没有 IFS 函数,打开会报错,老版本继续使用嵌套 IF 方案。
方法三 VLOOKUP 区间查表法
如果后续经常调整分数线,可以单独建立等级对照表,使用 VLOOKUP 近似匹配实现四等级划分,适合分数线经常变动的场景。
1 在空白区域建立对照表,第一列为区间下限,第二列为对应等级,比如 85 优秀,70 良好,60 及格,0 不及格
2 在等级单元格输入 VLOOKUP 近似匹配公式
3 下拉填充,后续只需要修改对照表分数线,等级结果自动更新
搭配条件格式美化等级
等级生成之后,可以给四个等级设置不同颜色底纹,方便快速识别。
1 选中等级整列区域
2 点击开始选项卡,条件格式,新建规则
3 分别新建四条规则,单元格等于优秀设置绿色,良好蓝色,及格黄色,不及格红色
4 确认规则之后,不同等级自动填充对应底色
常见错误排查
第一等级全部判断成同一档,一般是判断顺序写反,没有从高分往低分判断,比如先写大于 60,那么 85 分也会判定成及格
第二公式提示 NAME,是低版本 excel 使用了 IFS 函数,替换成嵌套 IF 即可
第三分数带小数判断出错,确认区间符号大于等于小于等于书写正确,边界分数要明确归属哪一档
第四成绩单元格是文本格式数字,公式识别异常,把成绩列转为数值格式
第五空成绩单元格也生成不及格,可以增加判断,成绩为空的时候等级单元格留空
【常见问题】
问题 1 excel 给成绩等级划分四个,老版本 excel 可以用 IFS 吗
回答 1 excel 给成绩等级划分四个,老版本 excel 不支持 IFS 函数,需要使用嵌套 IF 公式完成四档等级划分,新版 excel 才可以直接使用 IFS 简化 excel 给成绩等级划分四个的公式。
问题 2 excel 给成绩等级划分四个,公式判断顺序为什么要从高分开始
回答 2 excel 给成绩等级划分四个,嵌套 IF 需要从高分往低分判断,如果顺序颠倒,高分成绩会提前命中低区间条件,造成等级错误,是 excel 给成绩等级划分四个最常见出错点。
问题 3 excel 给成绩等级划分四个,能不能自定义分数线和等级名称
回答 3 excel 给成绩等级划分四个,可以自由修改公式里面的分数阈值,同时替换等级文字,按照学校考核要求自定义四档等级,完成 excel 给成绩等级划分四个。
问题 4 excel 给成绩等级划分四个,等级能不能自动变色
回答 4 excel 给成绩等级划分四个之后,可以添加条件格式,为优秀良好及格不及格四个等级分别设置填充颜色,实现 excel 给成绩等级划分四个之后可视化标记。
【免责申明】
本文基于 excel 通用版本成绩等级划分公式讲解,不同版本函数支持情况存在差异,内容仅供学习参考,不构成业务决策建议。


