excel中用函数、条件格式填充“总评等级”并使不及格为红色

Question

数值在“成绩”
100~89为优秀
80~89为良好
70~79为中等
60~69为及格
不到60为不及格

Answer 1

公式为下列公式之一

=IF(A2>=89,"优秀",IF(A2>=80,"良好",IF(A2>=70,"中等",IF(A2>=60,"及格","不及格"))))

=IF(A2<60,"不及格",IF(A2<70,"及格",IF(A2<80,"中等",IF(A2<90,"良好","优秀"))))

=IF(A2="","",LOOKUP(A2,{0,60,70,80,89},{"不及格","及格","中等","良好","优秀"}))

下拉填充

条件格式设置如下：

或

Answer 2

公式为这个：=VLOOKUP(A6,{0,"不及格";60,"及格";70,"中等";80,"良好";90,"优秀"},2,1)

条件格式的设置如图所示，

Answer 3

假设成绩所在的单元格为c1，用嵌套的if条件判断语句就可以了，你只要根据实际情况把以下代码中C1做一下更改，复制到相应的单元格就可以了
=IF(AND(C1>=89,C1<=100),"优秀",IF(AND(C1>=80,C1<89),"良好",IF(AND(C1>=70,C1<=79),"中等",IF(AND(C1>=60,C1<=79),"及格","不及格"))))

Answer 4

假如,"评语"在F列,"成绩"在B列,F2=IF(B2>89,"优秀",IF(B2>=80,"良好",IF(B2>=70,"中等",IF(B2>=60,"及格","不及格")))),
在菜单栏"格式"---"条件格式"---条件1里"单元格数值","等于","不及格",---"格式"---"图案"---选"红"颜色---"确定",下拉!

Answer 5

=IF(A2<60,"不及格",IF(A2<70,"及格",IF(A2<80,"中等",if(A2<90,"良好","优秀"))) )
至于不及格为红色则可以通过条件格式设置。