Question

excel函数求教，3个条件中满足3个，任意2个和任意1个，则返回不同值，函数怎么写？

excel函数求教，3个条件中满足3个，任意2个和任意1个，则返回不同值，函数怎么写？比如有ABCD4列，ABC3列单元格显示优秀，一般，较差3个数值，如果3个都是优秀，则D列显示优秀，任意2个优秀，则显示一般，任意1个优秀，则显示较差，没有优秀，则显示很差，这样的函数怎么写？求教大神。

Answer 1

见截图

D2输入

=IF(COUNTIF(A2:C2,"优秀")=3,"优秀",IF(COUNTIF(A2:C2,"优秀")=2,"一般",IF(COUNTIF(A2:C2,"优秀")=1,"较差","很差")))

公式下拉

Answer 2

假设数据从第2行起。在D2输入
=IF(COUNTIF(A2:C2,"优秀")=3,"优秀",IF(COUNTIF(A2:C2,"优秀")=2,"一般",IF(COUNTIF(A2:C2,"优秀")=1,"较差","很差")))
回车并向下填充。

追答

公式还可以用：
=CHOOSE(COUNTIF(A2:C2,"优秀")+1,"很差","较差","一般","优秀")
或
=LOOKUP(COUNTIF(A2:C2,"优秀"),{0,1,2,3},{"很差","较差","一般","优秀"})

Answer 3

你只以“优秀”个数 来判断 成 优秀、一般、较差、很差 4个等级，那就查找“优秀”个数就好了。

就这样写

=LOOKUP(COUNTIF(A1:A3,"优秀"),{0,1,2,3},{"很差","较差","一般","优秀"})

A1到A3是评定的优秀个数。

Answer 4

=IF(COUNTIF(A1:C1,"优秀")=3,"优秀",IF(COUNTIF(A1:C1,"优秀")=2,"一般",IF(COUNTIF(A1:C1,"优秀")=1,"差","很差")))