EXCEL的IF函数嵌套超过七层怎么办，具体公式如下:(在问题补充中)谢谢！！

　　你好：可以用if()的两种情况，分段，就可以到14层。也可以用lookup()，可以传个样表，我可以用函数向导分析一下，哪里可以改。

　　发现有重复现象，如D10=男怎么样，是女就不用说了，是第二情况呀。

=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")))),1,

Alan_hehe 举报

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

举报 yobyob

在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"))))

Alan_hehe 举报

不好意思啊，高人，刚又忘了条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")),否（不是一个月）继续……,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"," ")))))

VD 6      

IVD 6 

IVB 6      

IVA 6      

IIID 3      

IIIB 6      

IIIA 6      

IIIA+ 6      

IID 0      

IIB 3      

IIA 6      

IIA+ 6      

=VLOOKUP($H$4&$I$4,$A$1:$B$12,2,0)

=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(OR(M7={"F","LK"}),G343,IF(M7="T",G344,IF(M7="L",G345,IF(M7="X",G346,IF(M7="A",G347,IF(M7="B",G348,IF(M7="C",G349,IF(M7="D",G350,""))))))))O7

再缩简

=IF(OR(M7={"F","LK"}),G343,IF(M7="T",G344,IF(M7="L",G345,IF(M7="X",G346,IF(OR(M7={"A","B","C","D"}),INDIRECT("G"&346+CODE(M7)-64),"")))))O7

你写的公式数据与表格中的数据不对应

公式可以写成

=IF(P6="","",LOOKUP(P6,{0,"F";50,"D";53,"C-";58,"C";63,"C+";68,"B-";72,"B";78,"B+";83,"A-";88,"A";93,"A+"}))

07及以上的版本可以支持7层以上的嵌套,如果是03版且一定要用IF进行嵌套的话,公式可以改成

=IF(P6="","",IF(P6<72,IF(P6<50,"F",IF(P6<53,"D",IF(P6<58,"C-",IF(P6<63,"C",IF(P6<68,"C+","B-"))))),IF(P6<78,"B",IF(P6<83,"B+",IF(P6<88,"A-",IF(P6<93,"A","A+"))))))

=IF(AND(D6>=440,E6<=4425),"A",IF(AND(D6>=4425,E6<=445),"B",IF(AND(D6>=445,E6<=4475),"C",IF(AND(D6>=4475,E6<=450),"D",IF(AND(D6>=450,E6<=4525),"E",IF(AND(D6>=4525,E6<=455),"F",IF(AND(D6>=455,E6<=4575),"G","")))))))&IF(AND(D6>=4575,E6<=460),"H",IF(AND(D6>=460,E6<=4625),"I",IF(AND(D6>=4625,E6<=465),"J")))