Question

excel函数if公式怎么嵌套超过7层啊

大家帮忙我这个公式应该怎么才能设置成功啊
=IF(AND(N4="一个月",O4="高",P4="配合"),"A"
,IF(AND(N4="一个月",O4="中",P4="配合"),"A"
,IF(AND(N4="一个月",O4="高",P4="配合"),"A"
,IF(AND(N4="半年",O4="高",P4="配合"),"B"
,IF(AND(N4="三个月",O4="中",P4="配合"),"B"
,IF(AND(N4="一个月",O4="低",P4="配合"),"B"
,IF(AND(N4="三个月",O4="高",P4="不配合"),"B","")))))))&
,IF(AND(N4="三个月",O4="低",P4="配合"),"B"
,IF(AND(N4="三个月",O4="低",P4="配合"),"B"
,IF(AND(N4="一个月",O4="中",P4="不配合"),"B"
,IF(AND(N4="一个月",O4="高",P4="不配合"),"B"
,IF(AND(N4="半年",O4="中",P4="配合"),"B"
,IF(AND(N4="一个月",O4="低",P4="不配合"),"C"
,IF(AND(N4="三个月",O4="中",P4="不配合"),"C"
,IF(AND(N4="三个月",O4="低",P4="不配合"),"C","")))))))&
,IF(AND(N4="半年",O4="高",P4="不配合"),"C"
,IF(AND(N4="半年",O4="中",P4="不配合"),"C"
,IF(AND(N4="半年",O4="低",P4="配合"),"C"
,IF(AND(N4="半年",O4="低",P4="不配合"),"C"," ")))))

Answer 1

=IF(AND(N4="一个月",O4="高",P4="配合"),"A"
,IF(AND(N4="一个月",O4="中",P4="配合"),"A"
,IF(AND(N4="一个月",O4="高",P4="配合"),"A"
,IF(AND(N4="半年",O4="高",P4="配合"),"B"
,IF(AND(N4="三个月",O4="中",P4="配合"),"B"
,IF(AND(N4="一个月",O4="低",P4="配合"),"B"
,IF(AND(N4="三个月",O4="高",P4="不配合"),"B","")))))))&
IF(AND(N4="三个月",O4="低",P4="配合"),"B"
,IF(AND(N4="三个月",O4="低",P4="配合"),"B"
,IF(AND(N4="一个月",O4="中",P4="不配合"),"B"
,IF(AND(N4="一个月",O4="高",P4="不配合"),"B"
,IF(AND(N4="半年",O4="中",P4="配合"),"B"
,IF(AND(N4="一个月",O4="低",P4="不配合"),"C"
,IF(AND(N4="三个月",O4="中",P4="不配合"),"C","")))))))&
IF(AND(N4="三个月",O4="低",P4="不配合"),"C"
,IF(AND(N4="半年",O4="高",P4="不配合"),"C"
,IF(AND(N4="半年",O4="中",P4="不配合"),"C"
,IF(AND(N4="半年",O4="低",P4="配合"),"C"
,IF(AND(N4="半年",O4="低",P4="不配合"),"C","")))))

“&”后面不要“,”，第二段超过7层。
其实公式还可精简，把N4的三种情况分开处理，就可以减少嵌套数。看到这么长的IF，有点烦！

重新组合精简后，可以不超过7层嵌套.
=IF(OR(N4="",O4="",P4=""),"",IF(N4="一个月",IF(P4="配合",IF(O4="低","B","A"),IF(O4="低","C","B")),IF(N4="三个月",IF(P4="配合",IF(O4="低","C","B"),IF(O4="高","B","C")),IF(P4="配合",IF(O4="低","C","B"),"C"))))

追问

谢谢，可是我想插一条IF(AND(N3="三个月",O3="高",P3="配合"),"A",这种条件在里面怎么添加啊

追答

在N4＝“三个月”、P4＝“配合”，这一节添加。
=IF(OR(N4="",O4="",P4=""),"",IF(N4="一个月",IF(P4="配合",IF(O4="低","B","A"),IF(O4="低","C","B")),IF(N4="三个月",IF(P4="配合",IF(O4="高","A",IF(O4="中","B","C")),IF(O4="高","B","C")),IF(P4="配合",IF(O4="低","C","B"),"C"))))

追问

不好意思啊，高人，刚又忘了条IF(AND(N4="半年",O4="高",P4="配合"),"A"应该怎么添加啊，脑子都弄乱了

追答

在N4＝“半年”（就是除了前两项外的最后一项IF(P4="配合",IF(O4="低","C","B"),"C"))）这一节添加。
=IF(OR(N4="",O4="",P4=""),"",IF(N4="一个月",IF(P4="配合",IF(O4="低","B","A"),IF(O4="低","C","B")),IF(N4="三个月",IF(P4="配合",IF(O4="高","A",IF(O4="中","B","C")),IF(O4="高","B","C")),IF(P4="配合",IF(O4="高","A",IF(O4="低","C","B")),"C"))))
你应该将它公节理解：
IF(N4="一个月",IF(P4="配合",IF(O4="低","B","A"),IF(O4="低","C","B")),否（不是一个月）继续……

Answer 2

可以用VLOOKUP来解决。而且你这个IF条件很多都可以用AND（OR（））来合并。