excel if嵌套过多，怎么解决啊

Question

=if(q5=117110,if(s5<=20,"844","918"),if(q5=116110,if(s5<=20,"887","992"),if(q5=115210,if(s5<=20,"938","1043",if(q5=115200,if(s5<=20,"960","1064"),if(q5=115110,if(s5<=20,"1005","1132"),"0"))))))，系统说用参数过多？这个谁来帮帮我啊

Answer 1

excel中if语句最多嵌套7曾，超过7层就会提示参数过多了。
最简单的改法如下：
=if(q5=117110,if(s5<=20,"844","918"),“0”)+if(q5=116110,if(s5<=20,"887","992"),"0")+if(q5=115210,if(s5<=20,"938","1043"),"0")+if(q5=115200,if(s5<=20,"960","1064"),"0")+if(q5=115110,if(s5<=20,"1005","1132"),"0")

PS：我后来发现，其实你的嵌套没有超过7层，不应该出现参数过多的问题。仔细一看，发现是你的公式打错了。
“1043”,这里少了一个括号，使得公式出错。同时，最后多了一个括号。
修改之后，整个公式还是成立的。

Answer 2

=LOOKUP(S5,{0,21},IF(Q5=117110,{884,918},IF(Q5=116110,{887,992},IF(Q5=115210,{938,1043},IF(Q5=115200,{960,1064},IF(Q5=115110,{1005,1132}))))))本回答被提问者采纳

Answer 3

=if(q5=117110,if(s5<=20,"844","918"),if(q5=116110,if(s5<=20,"887","992"),if(q5=115210,if(s5<=20,"938","1043"),if(q5=115200,if(s5<=20,"960","1064"),if(q5=115110,if(s5<=20,"1005","1132"),"0")))))

你中间有一个“)”放错位置了

Answer 4

=IF(G2=6,4,IF(G2=14,8,IF(G2<21,LOOKUP(G2,,),"error"))),完全可以获得你要的结果

Answer 5

=HLOOKUP(Q5,{117110,116110,115210,115200,115110;844,887,938,960,1005;918,992,1043,1064,1132},IF(S5<20,2,3),0)