☰

如何在一个单元格中添加多个公式？

tag-icon • tag-icon microsoft-excel
如何在一个单元格中添加多个公式？

这是我需要的公式，但我不知道如何将它们组合起来。

=IF(H4=1,IF(J30<=E29,"Pre-Qualified","Unqualified"))
=IF(H4=2,IF(J30<=E30,"Pre-Qualified","Unqualified"))
=IF(H4=3,IF(J30<=E31,"Pre-Qualified","Unqualified"))
=IF(H4=4,IF(J30<=E32,"Pre-Qualified","Unqualified"))
=IF(H4=5,IF(J30<=E33,"Pre-Qualified","Unqualified"))
=IF(H4=6,IF(J30<=E34,"Pre-Qualified","Unqualified"))

答案1

因为您正在测试1，，， ...，2所以3有一个特殊的答案适合您：

=CHOOSE(H4, IF(J30<=E29,"Pre-Qualified","Unqualified"),
            IF(J30<=E30,"Pre-Qualified","Unqualified"),
            IF(J30<=E31,"Pre-Qualified","Unqualified"),
            IF(J30<=E32,"Pre-Qualified","Unqualified"),
            IF(J30<=E33,"Pre-Qualified","Unqualified"),
            IF(J30<=E34,"Pre-Qualified","Unqualified"))

这与其他答案中给出的复合公式IF具有相同的效果。THEN

但是，由于除一个元素外，六个内部公式都相同，因此可以大大简化它：

=IF(J30<=CHOOSE(H4, E29, E30, E31, E32, E33, E34), "Pre-Qualified", "Unqualified")

FALSE一个区别：如果H4不是1、、、、或，其他答案将评估为，2而我的答案将导致错误。3456#VALUE!

答案2

=IF(H4=1,IF(J30<=E29,"Pre-Qualified","Unqualified"),
IF(H4=2,IF(J30<=E30,"Pre-Qualified","Unqualified")),
IF(H4=3,IF(J30<=E31,"Pre-Qualified","Unqualified")),
....)

如果第一个 if 语句失败，则将测试第二个 if 语句。因此，如果 H4 != 1，它将再次测试 H4 2...

答案3

IF 语句包括 IF、THEN、ELSE。因此，请将下一个“IF”放在最后一个 IF 语句的 ELSE 位置。

=IF(H4=1,IF(J30<=E29,"Pre-Qualified","Unqualified"),
 IF(H4=2,IF(J30<=E30,"Pre-Qualified","Unqualified"),
 IF(H4=3,IF(J30<=E31,"Pre-Qualified","Unqualified")...)))

答案4

您可以尝试如果-并且像这样的语句：

=IF(AND(H1<4,J30<=E29),"Pre-Qualified","Unqualified")

您可以在 and 语句中输入多个变量，如果所有变量都为真，它将返回 TRUE，如果任何变量不为真，它将返回 false。您可以尝试的另一件事是，=IF(OR(它与 AND 的工作原理相同，除非任何变量为真，否则它将为整个语句返回 true。

相关内容