求excel奖学金等级

Question

函数

Answer 1

=IF(AND(H2>=85,K2="优秀",C2>=80,D2>=80,E2>=80,F2>=80,G2>=80),"一等奖学金",IF(AND(H2>=80,K2="良",C2>=75,D2>=75,E2>=75,F2>=75,G2>=75),"二等奖学金",IF(AND(H2>=75,K2="良",C2>=70,D2>=70,E2>=70,F2>=70,G2>=70),"三等奖学金","")))

Answer 2

L3公式：
=IF(AND(K3="优秀",H3>=85,COUNTIF(C3:G3,">=80")=5),"一等",IF(AND(OR(K3="优秀",K3="良"),H3>=80,COUNTIF(C3:G3,">=75")=5),"二等",IF(AND(OR(K3="优秀",K3="良"),H3>=75,COUNTIF(C3:G3,">=70")=5),"三等","")))
下拉。