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

答案:4 悬赏:0 手机版

解决时间 2021-01-24 18:52

提问者网友：半生酒醒
2021-01-24 14:14

=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)
)))))))))))))

最佳答案

五星知识达人网友：梦中风几里
2021-01-24 14:52

你将我给的公式复制过去试一试吧：
=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的答案吧！！！

全部回答

1楼网友：不想翻身的咸鱼
2021-01-24 18:20

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

2楼网友：举杯邀酒敬孤独
2021-01-24 16:42

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

3楼网友：长青诗
2021-01-24 15:52

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

我要举报

如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！