如何重新制定嵌套层数过多的 Excel 公式

Question

我需要能够在 Excel 中使用以下公式，根据单元格和下拉菜单中的值选择在不同工作表上使用的公式D2。D3唯一的问题是该公式包含超过 7 个嵌套的 IF 语句，Excel 告诉我这是无效的。

无法输入指定的公式，因为它使用的嵌套级别超出了当前文件格式所允许的级别。

我怎样才能分解这个公式才能达到相同的结果？

=如果(D2=1,下拉菜单！C8,如果(AND(D2=2,D3="I"),下拉菜单！C9,如果(AND(D2=2,D3="II"),下拉菜单！C10,如果(AND(D2=2,D3="IIIa,b"),下拉菜单！C11,如果(AND(D2=3,D3="I"),下拉菜单！C12, 如果(AND(D2=3,D3="II"),下拉菜单！C13, 如果(and(D2=3,D3="IIIa"),下拉菜单！C14, 如果(and(D2=3,D3="IIIb"),下拉菜单！C15, 如果(and(D2=4,D3="I"),下拉菜单！C16, 如果(and(D2=4,D3="II"),下拉菜单！C17,如果（和（D2=4,D3="IIIa,b"),下拉菜单！C18, "错误")))))))

Answer 1

替代多个嵌套 IF

当您需要一长串嵌套 IF 时，还有另一种方法不会将您限制在嵌套 IF 限制中。它使用“选择”方法，这种方法也更容易创建，因为它在视觉上更直观。

嵌套 IF 连续评估条件，直到找到一个为真的条件。逻辑和输出值是混合系列。替代方法在一个步骤中评估所有条件，并使用结果从列表中选择正确的值。

它结合了两种技术，CHOOSE 函数和布尔表达式。在 Excel 2003 中，您可以从最多 29 个值中进行选择。Excel 2007 及更高版本允许最多 254 个选择，而无需使用任何 IF 语句。

CHOOSE 函数

你的公式很长，所以我不会重现整个公式，但这是方法（占位符“索引”将在下一部分中解释）：

=CHOOSE(<index>,"Error",Dropdowns!C8,Dropdowns!C9,Dropdowns!C10, ...)

您的整个 IF 语句结果值列表都会被包括在内。其工作方式是根据嵌套 IF 链中的所有条件计算“索引”。结果将是 CHOOSE 列表中值的序列号。

索引

该指数使用布尔算法（基于 True/False 条件的 0/1 值进行计算）。您可以像下面这样以表达式形式构建指数：

1 + condition1 * 1 + condition2 * 2 + condition3 * 3 + ...

稍后将解释初始的 1。其余的是按相关顺序排列的所有 IF 测试条件，以匹配结果列表。每个测试的结果为 1 或 0，然后将其乘以其关联的索引号（每个条件后面的 1、2、3……）。由于这些测试中只有一个为真，因此它决定了索引的值（所有假条件的零和加上真条件的索引值）。

替换嵌套 IF 示例中的测试条件将如下所示：

1 + (D2=1)*1 + AND(D2=2,D3="I")*2 + AND(D2=2,D3="II")*3 + ...

整个表达式代替了<index>CHOOSE 函数标题下显示的公式中的占位符。然后，Choose 函数根据索引从列表中选择目标值。

您的错误条件是，如果所有测试都不为真，则总和将为零。索引是列表中值的位置编号，从 1 开始。将 1 添加到索引计算（显示为第一项）中，使错误条件计算为 1，并将所有其他结果增加 1。因此，列表中的第一个结果就是您的错误消息。