IF-ELSE Excel中的IF-ELSE结构 [英] IF - ELSE IF - ELSE Structure in Excel

问题描述

要求:

如果单元格A1中的字符串包含"abc"作为字符串的一部分

If the string in cell A1 contains "abc" as part of the string

然后分配值绿色"

否则，如果单元格A1中的字符串包含"xyz"作为字符串的一部分

Else if the string in cell A1 contains "xyz" as part of the string

然后分配值黄色"

其他

分配值红色"

我尝试了此操作:

=IF(FIND("~",SUBSTITUTE(A1,"abc","~",1))<>"#VALUE!", "Green", IF(FIND("~",SUBSTITUTE(A1,"xyz","~",1))<>"#VALUE!", "Yellow", "Red"))

如果在其他情况下第一个IF为True，则它可以提供#VALUE！作为输出.

It works if first IF is True in other cases it gives #VALUE! as output.

无法找出问题所在.

推荐答案

当FIND返回#VALUE!时，这是一个错误，而不是字符串，因此您无法将FIND(...)与"#VALUE!"进行比较，您会需要检查FIND是否返回ISERROR错误. FIND也可以使用多个字符.

When FIND returns #VALUE!, it is an error, not a string, so you can't compare FIND(...) with "#VALUE!", you need to check if FIND returns an error with ISERROR. Also FIND can work on multiple characters.

因此，您的公式的简化工作版本为:

So a simplified and working version of your formula would be:

=IF(ISERROR(FIND("abc",A1))=FALSE, "Green", IF(ISERROR(FIND("xyz",A1))=FALSE, "Yellow", "Red"))

或者，要消除双重否定:

Or, to remove the double negations:

=IF(ISERROR(FIND("abc",A1)), IF(ISERROR(FIND("xyz",A1)), "Red", "Yellow"),"Green")

这篇关于IF-ELSE Excel中的IF-ELSE结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！