如何在 Excel 中组合多个 If 语句

Question

我正在尝试将多个（超过 64 个）If 语句合并到一个单元格中。我对 Excel 不是很熟练，所以我可能做错了。

我想要实现的目标是：

如果[(Cell 1 = X) AND (Cell 2 = Y) then Cell 3 = Z]
或
如果[(Cell 1 = X) AND (Cell 2 = B) the Cell 3 = C]

对于单元格 1，我有 100 种可能性，对于单元格 2，我有 2 种可能性。单元格 1 和 2 的组合将在单元格 3 中产生特定的答案。

我希望这是有意义的。

到目前为止，我已经尝试了以下 If 语句，它可以工作，但是太长（超过 64）。

=IF(AND(B6="AF001.1",C6="US"),"5.5",
IF(AND(B6="AF010",C6="US"),"17.2",
IF(AND(B6="Z284.1",C6="US"),"8.5",
IF(AND(B6="Z284.2",C6="US"),"17.8",  
IF(AND(B6="AF016.1",C6="US"),"7.5",
IF(AND(B6="AF016.2",C6="US"),"7.5",
IF(AND(B6="AF016.3",C6="US"),"7.5",
IF(AND(B6="AF016.4",C6="US"),"7.5",
IF(AND(B6="AF016.5",C6="US"),"7.5",
IF(AND(B6="AF016.6",C6="US"),"7.5",
IF(AND(B6="AF016.7",C6="US"),"7.5",
IF(AND(B6="AF016.8",C6="US"),"7.5",
IF(AND(B6="AF016.9",C6="US"),"7.5",
IF(AND(B6="AF016.10",C6="US"),"7.5",
IF(AND(B6="AF016.11",C6="US"),"7.5",
IF(AND(B6="AF016.12",C6="US"),"7.5",
IF(AND(B6="AF016.13",C6="US"),"7.5",
IF(AND(B6="ST001",C6="US"),"6",
IF(AND(B6="ST002.2",C6="ALL"),"36.5",
IF(AND(B6="ST002.3",C6="ALL"),"36.5",
IF(AND(B6="ST002.4",C6="ALL"),"36.5",
IF(AND(B6="ST002.5",C6="ALL"),"36.5",
IF(AND(B6="ST002.6",C6="ALL"),"36.5",
IF(AND(B6="ST002.7",C6="ALL"),"36.5",
IF(AND(B6="ST002.8",C6="ALL"),"36.5",
IF(AND(B6="ST002.9",C6="ALL"),"36.5",
IF(AND(B6="ST002.10",C6="ALL"),"36.5",
IF(AND(B6="ST002.11",C6="ALL"),"36.5",
IF(AND(B6="ST002.12",C6="ALL"),"36.5",
IF(AND(B6="ST003.1",C6="ALL"),"36.5",
IF(AND(B6="ST003.2",C6="ALL"),"36.5",
IF(AND(B6="ST003.3",C6="ALL"),"36.5",
IF(AND(B6="ST003.4",C6="ALL"),"36.5",
IF(AND(B6="ST003.5",C6="ALL"),"36.5",
IF(AND(B6="ST003.6",C6="ALL"),"36.5",
IF(AND(B6="ST003.7",C6="ALL"),"36.5",
IF(AND(B6="ST003.8",C6="ALL"),"36.5",
IF(AND(B6="ST003.9",C6="ALL"),"36.5",
IF(AND(B6="ST003.10",C6="ALL"),"36.5",
IF(AND(B6="ST003.11",C6="ALL"),"36.5",
IF(AND(B6="ST003.12",C6="ALL"),"36.5",
IF(AND(B6="ST004.1",C6="US"),"11.5",
IF(AND(B6="ST004.2",C6="US"),"11.5",
IF(AND(B6="ST004.3",C6="US"),"11.5",
IF(AND(B6="ST004.4",C6="US"),"11.5",
IF(AND(B6="ST005",C6="ALL"),"21.5",
IF(AND(B6="ST006.1",C6="ALL"),"80",
IF(AND(B6="ST006.2",C6="ALL"),"80",
IF(AND(B6="ST006.3",C6="ALL"),"80",
IF(AND(B6="ST006.4",C6="ALL"),"80",
IF(AND(B6="ST006.5",C6="ALL"),"80",
IF(AND(B6="ST006.6",C6="ALL"),"80",
IF(AND(B6="ST006.7",C6="ALL"),"80",
IF(AND(B6="ST006.8",C6="ALL"),"80",
IF(AND(B6="ST006.9",C6="ALL"),"80",
IF(AND(B6="ST006.10",C6="ALL"),"80",
IF(AND(B6="ST006.11",C6="ALL"),"80",
IF(AND(B6="ST006.12",C6="ALL"),"80",
IF(AND(B6="ST006.13",C6="ALL"),"80",
IF(AND(B6="ST006.14",C6="ALL"),"80",
IF(AND(B6="ST006.15",C6="ALL"),"80",
IF(AND(B6="ST006.16",C6="ALL"),"80",
IF(AND(B6="ST006.17",C6="ALL"),"80",
IF(AND(B6="ST006.18",C6="ALL"),"80",
IF(AND(B6="ST007",C6="ALL"),"19",
IF(AND(B6="ST008.1",C6="US"),"12.5",
IF(AND(B6="ST008.2",C6="US"),"12.5",
IF(AND(B6="ST008.3",C6="US"),"12.5",
IF(AND(B6="ST008.4",C6="US"),"12.5",
IF(AND(B6="ST009.1",C6="ALL"),"108",
IF(AND(B6="ST009.2",C6="ALL"),"108",
IF(AND(B6="ST009.3",C6="ALL"),"108",
IF(AND(B6="ST009.4",C6="ALL"),"108",
IF(AND(B6="ST009.5",C6="ALL"),"108",
IF(AND(B6="ST009.6",C6="ALL"),"108",
IF(AND(B6="ST009.7",C6="ALL"),"108",
IF(AND(B6="ST009.8",C6="ALL"),"108",
IF(AND(B6="ST009.9",C6="ALL"),"108",
IF(AND(B6="ST009.10",C6="ALL"),"108",
IF(AND(B6="ST009.11",C6="ALL"),"108",
IF(AND(B6="ST009.12",C6="ALL"),"108",
IF(AND(B6="ST009.13",C6="ALL"),"108",
IF(AND(B6="ST009.14",C6="ALL"),"108",
IF(AND(B6="ST009.15",C6="ALL"),"108",
IF(AND(B6="ST009.16",C6="ALL"),"108",
IF(AND(B6="ST009.17",C6="ALL"),"108",
IF(AND(B6="ST009.18",C6="ALL"),"108",
IF(AND(B6="ST009.19",C6="ALL"),"108",
IF(AND(B6="ST009.20",C6="ALL"),"108",
IF(AND(B6="ST009.21",C6="ALL"),"108",
IF(AND(B6="ST009.22",C6="ALL"),"108",
IF(AND(B6="ST009.23",C6="ALL"),"108",
IF(AND(B6="ST009.24",C6="ALL"),"108",
IF(AND(B6="ST009.25",C6="ALL"),"108",
IF(AND(B6="ST009.26",C6="ALL"),"108",
IF(AND(B6="ST009.27",C6="ALL"),"108",
IF(AND(B6="ST009.28",C6="ALL"),"108",
IF(AND(B6="ST010",C6="US"),"5.3",
IF(AND(B6="ST010",C6="ALL"),"6.8",
IF(AND(B6="ST008.1",C6="ALL"),"14",
IF(AND(B6="ST008.2",C6="ALL"),"14",
IF(AND(B6="ST008.3",C6="ALL"),"14",
IF(AND(B6="ST008.4",C6="ALL"),"14",
IF(AND(B6="ST004.1",C6="ALL"),"13",
IF(AND(B6="ST004.2",C6="ALL"),"13",
IF(AND(B6="ST004.3",C6="ALL"),"13",
IF(AND(B6="ST004.4",C6="ALL"),"13",
IF(AND(B6="ST001",C6="ALL"),"7.5",
IF(AND(B6="AF016.1",C6="ALL"),"9",
IF(AND(B6="AF016.2",C6="ALL"),"9",
IF(AND(B6="AF016.3",C6="ALL"),"9",
IF(AND(B6="AF016.4",C6="ALL"),"9",
IF(AND(B6="AF016.5",C6="ALL"),"9",
IF(AND(B6="AF016.5",C6="ALL"),"9",
IF(AND(B6="AF016.7",C6="ALL"),"9",
IF(AND(B6="AF016.8",C6="ALL"),"9",
IF(AND(B6="AF016.9",C6="ALL"),"9",
IF(AND(B6="AF016.10",C6="ALL"),"9",
IF(AND(B6="AF016.11",C6="ALL"),"9",
IF(AND(B6="AF016.12",C6="ALL"),"9",
IF(AND(B6="AF016.13",C6="ALL"),"9",
IF(AND(B6="Z284.1",C6="ALL"),"9.5",
IF(AND(B6="AF001.1",C6="ALL"),"7"
)))))))))))))))))))))))))))))))

我会感激任何能得到的帮助。显然我需要它，哈哈！

Answer 1

创建一个包含 3 列的查找表会更好：

1. The 100 possibilities for Cell 1.
2. The 100 answers for Cell 1 and possibility 1 of Cell 2.
3. The 100 answers for Cell 1 and possibility 2 of Cell 2.

然后使用 VLOOKUP() 查找单元格 1，并根据单元格 2 的值从查找表的第 2 列或第 3 列返回答案。

这是一个只有 10 行的示例：

VLOOKUP() 函数在查找表的第 1 列中查找单元格 1 的值，并从第 2 列或第 3 列返回相应的值，使用 IF() 子句检查单元格 2 的值来决定列。

B6中的公式为：

=VLOOKUP(A2,D2:F11,IF(B2="Value 1",2,3),FALSE)

如有必要，您可以隐藏查找表或将其移动到另一个工作表。

以下是有关查找()和如果（）。您还应该查看 Excel 帮助。

Answer 2

首先，请允许我赞同 Brandon Ibbotson 的观点：我很抱歉你不得不费尽心思想出如此可怕的东西。

现在，您需要从 if 语句中删除业务逻辑，并将其放在可维护的位置，然后使用（相对）简单的查找函数来检索正确的值。说真的，如果 ST008.6 的值发生变化，您会怎么做？

你应该做的是：

在新的工作表上，设置一个表格。将 B 值放在侧面（在 A 列，从第 2 行开始），将 C 值放在第 1 行顶部。在交叉点处填写目标值。按 A 列排序。

         US    CA    ALL
AF001.1  5.5         7
AF010   17.2
...etc...
Z284.1   8.5         9.5

设置第二个表，将顶行标签转换为列号：

US      2
CA      3
ALL     4

请注意，零件编号或任何内容都位于第 1 列；我们不需要在此查找表中对其进行定义。

第三，为这两个表定义名称。这不是绝对必要的，但如果以后需要其他公式，这将使我们的公式更容易编写，并且表格更容易重复使用。

• 突出显示第一个表格（不包括顶行）
• 右键单击并选择“定义名称...”
• 给它一个合理的名字（在这个例子中我称之为 XREF）
• 对第二张表执行相同操作（我将其称为 CLKP）

最后，在主表上，用一个或两个 vlookup 替换该可怕的语句：

=VLOOKUP(B6,XREF,VLOOKUP(C6,CLKP,2,FALSE),FALSE)

这将在 XREF 范围的第一列中找到单元格 B6 中的值，然后将 C6 中的值转换为我们需要的列号，然后选择该列中该项目的值进入当前单元格。