如果是Else，则在Excel中使用AND或OR

Question

我有以下格式的excel数据：

severity    Type    Overview    Classification  Result
critical    defect  closed      Fix             Good
serious     enhance no change   no fix          Low
Medium      defect  no change   no fix          Low
Low         enhance closed      fix             Poor
critical    defect  Duplicate   fix             Good
Medium      defect  Duplicate   fix             Low
Low         defect  Duplicate                   Poor

我已经写了计算结果的公式，但由于我的excel知识不好，我无法执行。任何帮助都将不胜感激。

以下是条件：

 Severity           Type               Overview     Classifcation       Result
Citical/Serious     Defect/Enhance      Closed          Fix                 Good
                    Defect/Enhance      Closed          No fix              Poor
                    Defect/Enhance      Closed          Updated             Low
                    Defect/Enhance      Duplicate       No fix              poor


Medium/low          Defect/Enhance      closed          No fix              Low
                    Defect/Enhance      Duplicate       No fix              Poor

Answer 1

假设您的数据在范围A1:D8中，其中第一行是标题。您可以使用这个(丑陋的)公式，它的功能与您在条件表中描述的完全相同：

=IF(AND(OR(A2="Critical",A2="Serious"),OR(B2="Defect",B2="Enhance")),IF(C2="closed",IF(D2="Fix","Good",IF(D2="No fix","Poor",IF(D2="Updated","Low"))),IF(AND(C2="Duplicate",D2="No"),"poor","No match")),
IF(AND(OR(A2="Medium",A2="Low"),OR(B2="Defect",B2="Enhance")),IF(AND(C2="closed",D2="No fix"),"Low","No match"),IF(AND(C2="duplicate",D2="No fix"),"Poor")))

诀窍是使用else条件作为else条件。当你有很多东西的时候，它真的很难读懂。

Answer 2

这不是很好，但上一个表中定义的条件的解决方案如下所示：

=IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Fix");"Good";IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="No fix");"Poor";IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Updated");"Low";IF(AND(OR(A2="Critical";A2="Serious");C2="Duplicate";D2="No fix");"Poor";IF(AND(OR(A2="Medium";A2="Low");C2="Closed";D2="No fix");"Low";IF(AND(OR(A2="Medium";A2="Low");C2="Duplicate";D2="No fix");"Poor";"Undefined"))))))

以一种更具可读性的、多行的方式编写(我不确定这是否能通过复制和粘贴)：

=IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Fix");"Good";
   IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="No fix");"Poor";
     IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Updated");"Low";
       IF(AND(OR(A2="Critical";A2="Serious");C2="Duplicate";D2="No fix");"Poor";
         IF(AND(OR(A2="Medium";A2="Low");C2="Closed";D2="No fix");"Low";
           IF(AND(OR(A2="Medium";A2="Low");C2="Duplicate";D2="No fix");"Poor";
             "Undefined"
           )
         )
       )
     )
   )
 )

正如您所看到的，您没有介绍的任何组合都将导致"Undefined"。您可以组合一些条件，但这不一定更具可读性。

新的解决方案可能是一个单独的表中的完整查找表，带有CHOOSE()和VLOOKUP()...