☰

Excel 嵌套 IF 查询

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 嵌套 IF 查询

我创建了一个嵌套的 IF 函数并正在尝试使用。

=IF(AND(K2>=Summary!$D$1,I2>Summary!$A$8,J2>0),"Poor 
Performer",IF(AND(J2=0,F2>30),"Pause",IF(AND(J2=0,F2<30,I2>10),"High 
Spender",IF(OR(E2<Summary!$A$13,E2="-"),"Low Traffic 
Boost",IF(K2<Summary!$C$1,"Under Invested","No Change")))))

我正在尝试调整低流量提升部分以增加附加条件，以便输出准确。它就是这样的：

IF(OR(E2<Summary!$A$13,E2="-"),"Low Traffic Boost"

我正尝试把它改成这样。

IF(OR(AND (K2<=Summary!$D$1,E2<Summary!$A$13),
AND(E2="-,K2<=Summary!$D$1),"Low Traffic Boost"

但是，我一直收到错误。你能帮我解决一下吗？我不确定为什么我的嵌套 IF 和 AND 、 OR 语句无法正常工作。

答案1

将公式复制到文本编辑器并尝试添加换行符以查看哪里出了问题。

=IF(
    AND(K2>=Summary!$D$1,I2>Summary!$A$8,J2>0),
    "Poor Performer",
    IF(
       AND(J2=0,F2>30),
       "Pause",
       IF(
          AND(J2=0,F2<30,I2>10),
          "High Spender",
          IF(
             OR(E2<Summary!$A$13,E2="-"),
             "Low Traffic Boost",
             IF(
                K2<Summary!$C$1,
                "Under Invested",
                "No Change"
               )
            )
         )
      )
    )

每个IF语句都有三个参数。大多数情况下，这是另一个IF语句，但没关系。每个语句都使用括号( )来捕获它的三个参数。现在对您的公式执行相同的操作。

IF(
   OR(
      AND(K2<=Summary!$D$1,E2<Summary!$A$13),
      AND(E2="-,K2<=Summary!$D$1),
      "Low Traffic Boost"

看起来您想要替换IF可以返回的语句Low Traffic Boost，因此这不是完整的公式，只是一个片段。问题是您没有使用任何括号来结束语句OR。它应该看起来像这样：

IF(
   OR(
      AND(K2<=Summary!$D$1,E2<Summary!$A$13),
      AND(E2="-,K2<=Summary!$D$1)
     ),
  "Low Traffic Boost"

我在第二个AND和逗号之间添加了一个结束括号,。整个公式应如下所示：

=IF(
    AND(K2>=Summary!$D$1,I2>Summary!$A$8,J2>0),
    "Poor Performer",
    IF(
       AND(J2=0,F2>30),
       "Pause",
       IF(
          AND(J2=0,F2<30,I2>10),
         "High Spender",
         IF(
            OR(
               AND(K2<=Summary!$D$1,E2<Summary!$A$13),
               AND(E2="-,K2<=Summary!$D$1)
              ),
            "Low Traffic Boost",
            IF(
               K2<Summary!$C$1,
               "Under Invested",
               "No Change"
              )
           )
         )
      )
    )

折叠成一行，就是这个：

=IF(AND(K2>=Summary!$D$1,I2>Summary!$A$8,J2>0),"Poor Performer",IF(AND(J2=0,F2>30),"Pause",IF(AND(J2=0,F2<30,I2>10),"High Spender",IF(OR(AND(K2<=Summary!$D$1,E2<Summary!$A$13),AND(E2="-,K2<=Summary!$D$1)),"Low Traffic Boost",IF(K2<Summary!$C$1,"Under Invested","No Change")))))

相关内容