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

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

我正在尝试将多个(超过 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"
)))))))))))))))))))))))))))))))

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

答案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 帮助。

答案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 中的值转换为我们需要的列号,然后选择该列中该项目的值进入当前单元格。

相关内容