☰

已达到 Excel 中的嵌套 IF 限制

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
已达到 Excel 中的嵌套 IF 限制

有谁知道如何缩短这个 Excel 公式，因为我已经达到了嵌套的“IF”限制？

=IF(Sheet!$G$2=Sheet1!I2,"Y",IF(Sheet!$H2=Sheet1!I2,"Y",
 IF(Sheet!$I2=Sheet1!I2,"Y",IF(Sheet!$J2=Sheet1!I2,"Y",
 IF(Sheet!$K2=Sheet1!I2,"Y",IF(Sheet!$L2=Sheet1!I2,"Y",
 IF(Sheet!$M2=Sheet1!I2,"Y",IF(Sheet!$N2=Sheet1!I2,"Y",
 IF(Sheet!$O2=Sheet1!I2,"Y",IF(Sheet!$P2=Sheet1!I2,"Y",
 IF(Sheet!$Q2=Sheet1!I2,"Y",IF(Sheet!$R2=Sheet1!I2,"Y",""))))))))))))

答案1

笔记：下面我的回答与数字逻辑电路的处理方式类似，其中条件是并行评估的。在这种情况下，您需要明确提供每个条件。这完全是矫枉过正；比必要的要复杂得多。

thilina R 的答案中显示的方法利用了 Excel 按顺序评估公式和范围的事实，因此 Excel 已经完成了限制可能条件（类似于问题中的嵌套 IF）的大部分工作。

我们都想出了下面显示的作为我最后一种方法的 OR 逻辑。我将保留这个答案，无论它可能提供什么教育或新颖性价值。但 thilina R 的答案包含实用的解决方案，我投票支持方法 1：HLOOKUP。

目前尚不清楚您是否使用了比必要更严格的公式。我将在这里使用一些简写符号来简化讨论。我将省略工作表名称，并以这种方式引用相等性测试：如果为真G2=I2，我将称之为G2。如果 G2=I2 为假，我将称之为Not-G2。使用这种简写，您的嵌套 IF 可以这样表示：

    G2 
or: H2 and Not-G2
or: I2 and Not-G2 and Not-H2
or: J2 and Not-G2 and Not-H2 and Not-I2
etc.

如果这就是您需要的规则，您可以使用一个 IF 以及所有 AND 和 OR 逻辑来实现。简写形式如下：

=IF(OR(G2,AND(H2,Not-G2),AND(I2,Not-G2,Not-H2),...),"Y","")

填写实际公式如下：

=IF(OR(Sheet!$G$2=Sheet1!I2,
       AND(Sheet!$H2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2),
       AND(Sheet!$I2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2),
       AND(Sheet!$J2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2),
       AND(Sheet!$K2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2),
       AND(Sheet!$L2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2),
       AND(Sheet!$M2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2),
       AND(Sheet!$N2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2),
       AND(Sheet!$O2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2),
       AND(Sheet!$P2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2),
       AND(Sheet!$Q2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2),
       AND(Sheet!$R2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2,Sheet!$Q2<>Sheet1!I2)),"Y","")

（我不能保证那里没有拼写错误）。那么长的公式很难输入和维护。使用辅助单元格来输入公式的片段会很有帮助。

如果这实际上不是您所需要的规则，那么这里有几个更简单的情况：

  • 如果您不需要该序列，而只关心任何一个（且只有一个）单元格等于 I2，那么可以使用一个 IF 执行相同逻辑的方法：

    =IF((Sheet!$G$2=Sheet1!I2)+(Sheet!$H2=Sheet1!I2)+(Sheet!$I2=Sheet1!I2)+ (Sheet!$J2=Sheet1!I2)+(Sheet!$K2=Sheet1!I2)+(Sheet!$L2=Sheet1!I2)+ (Sheet!$M2=Sheet1!I2)+(Sheet!$N2=Sheet1!I2)+(Sheet!$O2=Sheet1!I2)+ (Sheet!$P2=Sheet1!I2)+(Sheet!$Q2=Sheet1!I2)+ (Sheet!$R2=Sheet1!I2)=1,"Y","")

    每组括号包含一个测试。如果相等性为真，则会产生一个值1，如果不为真，则该值为0。这就是 Excel 表示真和假的方式。

    将所有这些测试的结果相加。如果总和为1，则意味着其中只有一个测试为真。

  • 如果实际上你只关心其中至少一种情况是否属实，那么你可以使用简单的 OR：

    =IF(OR(Sheet!$G$2=Sheet1!I2,Sheet!$H2=Sheet1!I2,Sheet!$I2=Sheet1!I2, Sheet!$J2=Sheet1!I2,Sheet!$K2=Sheet1!I2,Sheet!$L2=Sheet1!I2, Sheet!$M2=Sheet1!I2,Sheet!$N2=Sheet1!I2,Sheet!$O2=Sheet1!I2, Sheet!$P2=Sheet1!I2,Sheet!$Q2=Sheet1!I2,Sheet!$R2=Sheet1!I2),"Y","")

请注意，我在所有公式中都添加了换行符和额外的空格，以便更容易看清逻辑。如果你想复制和粘贴，你需要删除它们。

答案2

基本上，您想知道一张工作表中的任意单元格是否与另一张工作表中的G2:R2单元格具有相同的值。I2

有几种方法可以做到这一点。我想到的最简单的方法是这个，因为你检查的值在一个连续的范围内：

方法 1：HLOOKUP/VLOOKUP

=IF(ISERROR(HLOOKUP(Sheet1!I2,Sheet!G2:R2,1,FALSE)),"","Y")

这将查找I2一张纸上的单元格中的值，并检查该值是否在另一张纸的范围内G2:R2。如果HLOOKUP在范围内找到至少一个与另一张纸上的单元格G2:R2匹配的值I2，则返回Y，否则不返回值。

我使用过，HLOOKUP因为提供的范围是水平范围。如果范围是垂直范围，则可以改用VLOOKUP。

如果值是不是处于连续范围内，无论出于何种原因，您都可以使用下面的另外两种方法。

这是另一种方法：

方法 2：连接

=IF(ISERROR(FIND(Sheet1!I2,CONCATENATE(Sheet!G2,Sheet!H2,Sheet!I2,Sheet!J2,Sheet!K2,Sheet!L2,Sheet!M2,Sheet!N2,Sheet!O2,Sheet!P2,Sheet!Q2,Sheet!R2))),"","Y")

这基本上会将单元格中的所有值组成一个大字符串G2:R2，并检查另一张表的单元格中的值是否I2在其中。 如果是，则返回Y，否则不返回值。

请注意，由于此方法会将您需要查找的单元格范围内的所有数据组成一个大字符串，因此根据这些单元格中的数据类型，它可能会返回Y错误。例如：如果Sheet1!I2有值123和Sheet!G2有值12和Sheet!H2有值34，则此方法仍会显示，Y因为“大字符串”将包含“1234...”，并且值123将在此字符串中。

还有另一种方式来实现这一点：

方法 3：布尔运算符 - OR

[@fixer1234 首先提到了这一点]

=IF(OR((Sheet!G2=Sheet1!I2), (Sheet!H2=Sheet1!I2),(Sheet!I2=Sheet1!I2), (Sheet!J2=Sheet1!I2), (Sheet!K2=Sheet1!I2), (Sheet!L2=Sheet1!I2), (Sheet!M2=Sheet1!I2), (Sheet!N2=Sheet1!I2), (Sheet!O2=Sheet1!I2), (Sheet!P2=Sheet1!I2), (Sheet!Q2=Sheet!I2), (Sheet!R2=Sheet1!I2) ),"Y","")

这使用布尔运算符OR来检查范围内的任何值是否G2:R2位于I2另一张表的单元格中。如果布尔运算符找到至少一个与另一张I2表中的单元格匹配的值，则返回值Y，否则不返回值。

方法 4：展开公式

  1. 如果您只想使用IF条件（出于某种我无法想象的原因），您可以将条件分散IF到多个单元格中，然后使用这些单元格的结果来确定最终结果。例如：您可以IF在一个单元格中设置几个条件，然后在另一个单元格中使用此条件的结果以及其他几个IF条件，依此类推。

A1您可以在工作表的单元格中包含以下内容：

=IF(Sheet!$G$2=Sheet1!I2,"Y",IF(Sheet!$H2=Sheet1!I2,"Y", IF(Sheet!$I2=Sheet1!I2,"Y",IF(Sheet!$J2=Sheet1!I2,"Y", IF(Sheet!$K2=Sheet1!I2,"Y","")))))

然后，您可以在A2工作表的单元格中包含以下内容：

=IF(A1="Y", "Y", IF(Sheet!$L2=Sheet1!I2,"Y", IF(Sheet!$M2=Sheet1!I2,"Y",IF(Sheet!$N2=Sheet1!I2,"Y", ""))))

A3最后，您可以在工作表的单元格中包含以下内容：

=IF(A2="Y","Y",IF(Sheet!$O2=Sheet1!I2,"Y",IF(Sheet!$P2=Sheet1!I2,"Y", IF(Sheet!$Q2=Sheet1!I2,"Y",IF(Sheet!$R2=Sheet1!I2,"Y","")))))

方法 5：VBA

如果您愿意，可以使用 VBA 来创建自定义用户定义函数，其中可以包含任意数量的嵌套If语句。

注意：这两个公式的输出与您提供的公式相同。

相关内容