多个IF AND语句excel [英] Multiple IF AND statements excel
问题描述
我需要在Excel中根据两个不同单元格中的文本编写一个if语句。
如果E2 =正在播放,F2 =关闭输出3
如果E2 =并且F2 ='挂起'输出2
如果E2 ='在播放中'和F2 ='空'输出1
如果E2 ='播放前'和F2 ='空'输出-1
如果E2 ='Completed',F2 ='Closed'输出2
如果E2 ='Suspended',F2 ='Null'输出3
如果有其他输出-2
其中Null在单元格中没有值
我试图请执行以下代码,但我似乎无法获得两个或更多的IF AND语句一起工作。如何解决这个问题?
= IF(AND(E2 =In Play,F2 =Closed 3,-2),IF(AND(E2 =In Play,F2 =Suspended),3,-2)
考虑您有多个测试,例如
- 如果E2 =正在播放,F2 =关闭,则输出3
- 如果E2 =正在播放,F2 =暂停,则输出2
- 等
您真正需要做的是在 False
参数中连续测试。您目前正在尝试用逗号分隔每个测试,这将无法正常工作。
您的前三个测试都可以加入到一个表达式中,如: IF(F2 =已关闭,3,IF(F2 =暂停,2,中频) (F2 =Null,1)))))
记住,每个连续的测试需要是前面测试的嵌套FALSE参数你可以这样做:
= IF(E2 =In Play,IF(F2 =Closed,3,IF = 暂停,2,IF(F2 = 空,1))),IF(AND(E2 = 预播放,F2 = 空), - 1,IF(AND(E2 =完成,F2 =closed),2,IF(AND(E2 =suspended,F2 =Null),3,-2)))))
I need to write an "if" statement in Excel based on text in two different cells.
If E2 ='in play' and F2 ='closed' output 3
If E2= 'in play' and F2 ='suspended' output 2
If E2 ='In Play' and F2 ='Null' output 1
If E2 ='Pre-Play' and F2 ='Null' output -1
If E2 ='Completed' and F2 ='Closed' output 2
If E2 ='Suspended' and F2 ='Null' output 3
If anything else output -2
where Null is no value in the cell
I was trying to do this with the code below but I can't seem to get two or more IF AND statements working together. How can I solve this problem?
=IF(AND(E2="In Play",F2="Closed"),3, -2), IF(AND(E2="In Play",F2=" Suspended"),3,-2)
Consider that you have multiple "tests", e.g.,
- If E2 = 'in play' and F2 = 'closed', output 3
- If E2 = 'in play' and F2 = 'suspended', output 2
- Etc.
What you really need to do is put successive tests in the False
argument. You're presently trying to separate each test by a comma, and that won't work.
Your first three tests can all be joined in one expression like:
=IF(E2="In Play",IF(F2="Closed",3,IF(F2="suspended",2,IF(F2="Null",1))))
Remembering that each successive test needs to be the nested FALSE argument of the preceding test, you can do this:
=IF(E2="In Play",IF(F2="Closed",3,IF(F2="suspended",2,IF(F2="Null",1))),IF(AND(E2="Pre-Play",F2="Null"),-1,IF(AND(E2="completed",F2="closed"),2,IF(AND(E2="suspended",F2="Null"),3,-2))))
这篇关于多个IF AND语句excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!