Excel-嵌套IF和AND函数 [英] Excel - nesting the IF and AND function

问题描述

我想知道是否有人可以帮助以下方面.我发现了一个与查询有关的问题和答案(我认为！)，但是我不能把它分解成自己想要做的事.

I was wondering if anyone could help with the following. I have found one question and answer relating to my query (I think!!) but I can't break it down to do what I want.

我创建了一个电子表格，并使用了IF& AND功能可完成我想要的一半.
基本上，如果答案在一个列中为否"，而在另一列中为四个选项之一(111,112,118,119)，我希望它引入C2的值.这是我想出的:

I have created a spreadsheet and used the IF & AND functions to do half of what I want.
Basically if the answer is "no" in one column and one of four options in another (111,112,118,119) I want it to bring in the value of C2. This is what I have come up with:

=IF(AND(E2= "No",F2=112),C2,0)  

这部分工作正常，但是如果选择了是"(E2)并且D2的值等于F2，那么我也需要它引入C2的值-这就是我的意思想到了

This part works fine but I also need it to bring in the value of C2 if "Yes" (E2) is selected AND the value of D2 is equal to F2 - this is what I have come up with

=IF(AND(E2="No",F2=111),C2,0),IF(E2 ="Yes",D2=F2,C2)

它不起作用.
我在帖子的顶部找到了链接的答案，但是我的Excel技能不足以将其拆开以适合我想要的内容.

It doesn't work.
I found the answer linked at the top of the post, but my Excel skills aren't up to pulling it apart to fit what I want.

推荐答案

之所以无法使用该公式，是因为在一个单元格中有两个单独的公式:

The reason you can't get the formula to work is because there are two separate formula thrown together in a cell:

=IF(AND(E2="No",F2=111),C2,0)-仅这是一个完整的公式，并且将返回C2或0.

=IF(AND(E2="No",F2=111),C2,0) - alone this is a complete formula and will return either C2 or 0.

如果要嵌套if语句，则需要将它们彼此链接在一起:If([something],[do this],If([something else],[do this],If(...您明白了.

If you want to nest your if statements you need to look at chaining them off each other: If([something],[do this],If([something else],[do this],If(... you get the point.

就您要实现的目标而言，还有一种更有效的方法，通过使用另一个公式，MATCH()可以返回匹配项在您提供的列表(数组或范围)中的位置.您可以只检查该公式的结果是否为数字，如下所示:

In terms of what you are trying to achieve there is a more effective method though, by using another formula, MATCH() that returns the position of a match in the list (array or range) you provide. You can just check whether the result of that formula is a number, like so:

=IF(AND(E2="No",ISNUMBER(MATCH(F2,{111,112,118,119}))),C2,0)

编辑

因此，当AND()的结果为false时，不提供0，而是启动另一个(如果是)"Yes"子句.如果失败则给出0.希望首先计算是".

So instead of providing 0 when the result of the AND() is false, we kick off another if that is your "Yes" clause... This then gives the 0 if it fails... Flip them round if you want "Yes" to be calculated first.

=IF(AND(E2="No",OR(F2=111,F2=112,F2=118,F2=119)),C2,IF(E2="Yes",D2=F2,0))

因此，根据您的评论得出的最终结果将是:

So the final result based off your comment would have been:

=IF(OR(AND(E2="Yes",D2=F2),AND(E2="No",OR(F2=111,F2=112,F2=118,F2=119))),C2,0)

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