excel中if超出7层要怎么办，x需要在12个区间内判断，每个区间对应的函数不一样，应该怎么办，谢谢啊

Question

=IF(C22=0,0,
if(and(c22>0,c22<=200),0.045*c22,
if(and(c22>200,c22<=500),9+0.03967*(c22-200),
if(and(c22>500,c22<=1000),20.9+0.03580*(c22-500),
if(and(c22>1000,c22<=3000),38.8+0.0325*(c22-1000),
if(and(c22>3000,c22<=5000),103.8+0.03005*(c22-3000),
if(and(c22>5000,c22<=8000),163.9+0.02857*(c22-5000),
if(and(c22>8000,c22<10000),249.6+0.0276(c22-8000),
if(and(c22>10000,c22<=20000),304.8+0.0262*(c22-10000),
if(and(20000,c22<=40000),566.8+0.02436*(c22-20000),
if(and(c22>40000,c22<=60000),1054+0.02306*(c22-40000),
if(and(c22>60000,c22<=80000),1515.2+0.02225*(c22-60000),
if(and(c22>80000,c22<=100000),1960.1+0.0245*(c22-80000)
)))))))))))))

Answer 1

2003版IF函数如果超出7个条件就无法实现，这是本身的限制。
解决方法：
1、使用自定义名称，
自定义名称示例：
=IF(A3=100,100,IF(A3=99,99,IF(A3=98,98,IF(A3=97,97,IF(A3=96,96,IF(A3=95,95,IF(A3=94,94,IF(A3=93,93,aa))))))))
aa=IF(Sheet1!A2=92,92,IF(Sheet1!A2=91,91,IF(Sheet1!A2=90,90,IF(Sheet1!A2=89,89,IF(Sheet1!A2=88,88,IF(Sheet1!A2=87,87,IF(Sheet1!A2=86,86,IF(Sheet1!A2=85,85,101))))))))

2、choose函数（可以扩展到29项）
示例如下：

=CHOOSE(MATCH(E21,C21:C49,0),"a","b","h","i","j","k","l","m","c","d","e","f","g","n","w","x","y","z","a1","a2","a3","o","p","q","r","s","t","u","v")

3、使用&
示例如下：
=IF(B54=1,"一",IF(B54=2,"二",IF(B54=3,"三",IF(B54=4,"四",IF(B54=5,"五",IF(B54=6,"六",IF(B54=7,"七",IF(B54=8,"八",""))))))))&IF(B54=9,"九",IF(B54=10,"十",IF(B54=11,"十一",IF(B54=12,"十二",""))))

Answer 2

你将我给的公式复制过去试一试吧：
=LOOKUP(C22,{0,200,500,1000,3000,5000,8000,10000,20000,40000,60000,80000,100000},{0.045,0.03967,0.0358,0.0325,0.03005,0.02857,0.0276,0.0262,0.02436,0.02306,0.02225,0.0245})*C22+LOOKUP(C22,{0,200,500,1000,3000,5000,8000,10000,20000,40000,60000,80000,100000},{0,1.066,3,6.3,13.65,21.05,28.8,42.8,79.6,131.6,180.2,0.1})

好像你的最后的一个数据0.0245有误吧？请自行检查和调整！
成功后请及时去采纳ouyangff的答案吧！！！本回答被提问者采纳

Answer 3

不知道你用的excel是哪个版本的
我用的2010里，if函数可以嵌套64层追问

2003的

Answer 4

不知道2个6层的if能否实现你的需要，最好下载2007版或2010版office