Excel函数中AND中的OR中的AND? [英] AND within OR within AND in Excel function?

问题描述

我需要检查A是否超过200，如果B是否超过150，那么结果应该为true.

I need to check if a A is over 200, and if B is over 150, if that's the case, the result should be true.

但是，如果A正好是200，则仍然必须得出结论，即B大于100.

However, if A is exactly 200, it still has to result in true is B is over 100.

简而言之

A>200 AND B>150 = True

但是如果

A=200 AND B>100 = True
A=200 AND B<=100 = False

我自己尝试过，但是我被卡住了，我的大脑无法处理所有的嵌套，而且我认为我缺少一些简单的东西，我还认为我使用的语法不正确，但是我使用的是到目前为止想出的是:

I tried it myself, but I'm stuck, my brain can't handle all the nesting, and I think I'm missing something easy, I also think the syntax I'm using is not correct, but what I came up with so far is:

=IF(AND(A>200;B>150;OR(AND(A=200;B>100);"True";"False")

推荐答案

这主要是一个数学逻辑问题，但是无论如何，这也应适用于更复杂的场景，尤其是在您难以掌握不同结果的情况下.

This is mainly a math-logic question, but anyway, this should help with more complex scenarios as well, especially if you are having difficulty grasping the different outcomes.

我将绘制一个类似于以下内容的概率树，显示每个概率树的所有不同结果:

I would draw a probability tree, similar to the below, showing all the different outcomes for each:

         A              B
                ----  > 150  -- True
                |
 ----  > 200  --|
 |              |
 |              ----  <=150  -- False
 |
 |              ----  > 100  -- True
 |              |
-|---  = 200  --|
 |              |
 |              ----  <= 100 -- False
 |
 |
 ----  < 200  ----- Anything -- False

一旦正确，请收集我们需要的内容.在这种情况下，我们需要将True与False分开.最简单的方法是在True上工作(5个结果中有2个是True，因此工作量更少！)

Once this is correct, gather the ones we need. In this case, we need to separate the True from the False. The simplest way would be to work on the True (2 out of the 5 outcomes are True, so less work!)

跟随每个"True"分支，并AND它们:

Follow each 'True' branch, and AND them:

AND(A>200;B>150)
AND(A=200;B>100)

一旦您拥有了所有东西，就将它们一起OR:

Once you got everything, OR them together:

OR(AND(A>200;B>150);AND(A=200;B>100))

最后的结果:

IF(OR(AND(A>200;B>150);AND(A=200;B>100)); "True"; "False")

---

额外:如果您想获取"False"，则:

---

Extra: if you wanted to get the "False" instead:

跟随每个"False"分支，然后将它们分别为AND(请注意，由于最后一个是单独的，因此我们可以删除AND):

Follow each 'False' branch, and AND them (note since the last one is alone, we can drop the AND):

AND(A>200;B<=150)
AND(A=200;B<=100)
A<200

一旦您掌握了所有内容，就将它们一起OR:

Once you got everything, OR them together:

OR(AND(A>200;B<=150);AND(A=200;B<=100);A<200)

最后的结果:

IF(OR(AND(A>200;B<=150);AND(A=200;B<=100);A<200); "False"; "True")

这篇关于Excel函数中AND中的OR中的AND?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！