试图使用if的公式的错误(在三个条件下

Question

我试图编写一个公式来评估三个单元格中的所有可能值并在另一个单元格中评分。Excel说我的公式存在问题，但我无法找到它。感谢您的帮助。

我正在使用if(在整个电子表格中，但只有两个值。这些公式被Excel接受。

=IF(AND(B2="No",B3="Red",B5="No"),0,
 IF(AND(B2="No",B3="Green",B5="No",2,
 IF(AND(B2="No",B3="Blue",B5="No",3,
 IF(AND(B2="No",B3="Yellow",B5="No",5,5,
 IF(AND(B2="No",B3="Red",B5="Yes"),0,
 IF(AND(B2="No",B3="Green",B5="Yes"),2,
 IF(AND(B2="No",B3="Blue",B5="Yes",3,
 IF(AND(B2="No",B3="Yellow",B5="Yes",5,
 IF(AND(B2="Yes",B3="Red",B5="Yes",0,
 IF(AND(B2="Yes",B3="Green",B5="Yes",1,
 IF(AND(B2="Yes",B3="Blue",B5="Yes",2,
 IF(AND(B2="Yes",B3="Yellow",B5="Yes",5,
 IF(AND(B2="Yes",B3="Red",B5="No",0,
 IF(AND(B2="Yes",B3="Green",B5="No",1,
 IF(AND(B2="Yes",B3="Blue",B5="No",2,
 IF(AND(B2="Yes",B3="Yellow",B5="No",3))))))))))))))))

excel状态"此公式存在问题"。

Answer 1

您有一个5,5，您应该拥有5，并且缺少许多)来关闭AND S

=IF(AND(B2="No",B3="Red",B5="No"),0,
 IF(AND(B2="No",B3="Green",B5="No"),2,
 IF(AND(B2="No",B3="Blue",B5="No"),3,
 IF(AND(B2="No",B3="Yellow",B5="No"),5,
 IF(AND(B2="No",B3="Red",B5="Yes"),0,
 IF(AND(B2="No",B3="Green",B5="Yes"),2,
 IF(AND(B2="No",B3="Blue",B5="Yes"),3,
 IF(AND(B2="No",B3="Yellow",B5="Yes"),5,
 IF(AND(B2="Yes",B3="Red",B5="Yes"),0,
 IF(AND(B2="Yes",B3="Green",B5="Yes"),1,
 IF(AND(B2="Yes",B3="Blue",B5="Yes"),2,
 IF(AND(B2="Yes",B3="Yellow",B5="Yes"),5,
 IF(AND(B2="Yes",B3="Red",B5="No"),0,
 IF(AND(B2="Yes",B3="Green",B5="No"),1,
 IF(AND(B2="Yes",B3="Blue",B5="No"),2,
 IF(AND(B2="Yes",B3="Yellow",B5="No"),3,""))))))))))))))))

，但我认为您可以使用更简单的公式来完成此操作：

=IFERROR(IF(B2 = "No",CHOOSE(MATCH(B3,{"Red","Green","Blue","Yellow"},0),0,2,3,5),IF(B5="Yes",CHOOSE(MATCH(B3,{"Red","Green","Blue","Yellow"},0),0,1,2,5),CHOOSE(MATCH(B3,{"Red","Green","Blue","Yellow"},0),0,1,2,3))),"")

Answer 2

您可以将逻辑形式放在一个单元格中(例如，f1(：

0NOREDNO 2NOGREENNO 3NOBLUENO 5NOYELLOWNO 0NOREDYES 2NOGREENYES 3NOBLUEYES 5NOYELLOWYES 0YESREDYES 1YESGREENYES 2YESBLUEYES 5YESYELLOWYES 0YESREDNO 1YESGREENNO 2YESBLUENO 3YESYELLOWNO

然后您的代码还原为：

=MID(F1, FIND(UPPER(B2&B3&B5),F1)-1, 1)

这也将使将来添加更多条件变得非常容易。

Answer 3

尝试以下：

=IF(B3="Red",0,
 IF(B3="Green",
   IF(B2="Yes",1,
   IF(B2="No",2,NA())),
 IF(B3="Blue",
   IF(B2="Yes",2,
   IF(B2="No",3,NA())),
 IF(B3="Yellow",
   IF(AND(B2="Yes",B5="No"),3,5),
 NA()))))

希望有帮助