excel函数公式,多个条件串联起来就出错了。求指点

=IF(U4="白关卡航普快",IF(T4<=100,AB4,IF(T4<=110,AB5,IF(T4<=120,AB6,IF(T4<=130,AB7,IF(T4<=140,AB8,IF(T4<=150,AB9,IF(T4<=160,AB10,IF(T4<=170,AB11,IF(T4<=180,AB12,IF(T4<=190,AB13,IF(T4<=200,AB14,IF(T4<=250,AB15,IF(T4<=300,AB16,IF(T4<=350,AB17,IF(T4<=400,AB18,IF(T4<=500,AB19,IF(T4<=600,AB20,IF(T4<=800,AB21,IF(T4<=1000,AB22,IF(T4>1000,AB23)))))))))))))))))))),IF(U4="西线单一品名特快",IF(T4<=100,AC4,IF(T4<=110,AC5,IF(T4<=120,AC6,IF(T4<=130,AC7,IF(T4<=140,AC8,IF(T4<=150,AC9,IF(T4<=160,AC10,IF(T4<=170,AC11,IF(T4<=180,AC12,IF(T4<=190,AC13,IF(T4<=200,AC14,IF(T4<=250,AC15,IF(T4<=300,AC16,IF(T4<=350,AC17,IF(T4<=400,AC18,IF(T4<=500,AC19,IF(T4<=600,AC20,IF(T4<=800,AC21,IF(T4<=1000,AC22,IF(T4>1000,AC23)))))))))))))))))))))),IF(U4="北疆特快散货-百货",IF(T4<=100,AE4,IF(T4<=110,AE5,IF(T4<=120,AE6,IF(T4<=130,AE7,IF(T4<=140,AE8,IF(T4<=150,AE9,IF(T4<=160,AE10,IF(T4<=170,AE11,IF(T4<=180,AE12,IF(T4<=190,AE13,IF(T4<=200,AE14,IF(T4<=250,AE15,IF(T4<=300,AE16,IF(T4<=350,AE17,IF(T4<=400,AE18,IF(T4<=600,AE19,IF(T4<=800,AE20,IF(T4<=1000,AE21,IF(T4>1000,AE22)))))))))))))))))))),IF(U4="北疆特快散货-服装",IF(T4<=100,AG4,IF(T4<=110,AG5,IF(T4<=120,AG6,IF(T4<=130,AG7,IF(T4<=140,AG8,IF(T4<=150,AG9,IF(T4<=160,AG10,IF(T4<=170,AG11,IF(T4<=180,AG12,IF(T4<=190,AG13,IF(T4<=200,AG14,IF(T4<=250,AG15,IF(T4<=300,AG16,IF(T4<=350,AG17,IF(T4<=400,AG18,IF(T4>400,AG19))))))))))))))))),IF(U4="普快散货-百货",IF(T4<=100,AI4,IF(T4<=110,AI5,IF(T4<=120,AI6,IF(T4<=130,AI7,IF(T4<=140,AI8,IF(T4<=150,AI9,IF(T4<=160,AI10,IF(T4<=170,AI11,IF(T4<=180,AI12,IF(T4<=190,AI13,IF(T4<=200,AI14,IF(T4<=250,AI15,IF(T4<=300,AI16,IF(T4<=350,AI17,IF(T4<=400,AI18,IF(T4<=600,AI19,IF(T4<=800,AI20,IF(T4<=1000,AI21,IF(T4>1000,AI22)))))))))))))))))))),IF(U4="普快散货-服装",IF(T4<=100,AK4,IF(T4<=110,AK5,IF(T4<=120,AK6,IF(T4<=130,AK7,IF(T4<=140,AK8,IF(T4<=150,AK9,IF(T4<=160,AK10,IF(T4<=170,AK11,IF(T4<=180,AK12,IF(T4<=190,AK13,IF(T4<=200,AK14,IF(T4<=250,AK15,IF(T4<=300,AK16,IF(T4<=350,AK17,IF(T4<=400,AK18,IF(T4>400,AK19))))))))))))))))),IF(U4="一带一路小百拼柜",IF(T4<=100,AM4,IF(T4<=110,AM5,IF(T4<=120,AM6,IF(T4<=130,AM7,IF(T4<=140,AM8,IF(T4<=150,AM9,IF(T4<=160,AM10,IF(T4<=170,AM11,IF(T4<=180,AM12,IF(T4<=190,AM13,IF(T4<=200,AM14,IF(T4<=250,AM15,IF(T4<=300,AM16,IF(T4<=350,AM17,IF(T4<=400,AM18,IF(T4<=500,AM19,IF(T4<=600,AM20,IF(T4<=800,AM21,IF(T4<=1000,AM22,IF(T4>1000,AM23))))))))))))))))))))),IF(U4="玩具专线",IF(T4<=120,AO4,IF(T4<=130,AO5,IF(T4<=140,AO6,IF(T4<=150,AO7,IF(T4<=160,AO8,IF(T4<=170,AO9,IF(T4<=180,AO10,IF(T4<=190,AO11,IF(T4<=200,AO12,IF(T4>200,AO13)))))))))))

浙江省
浏览 603
收藏
5
分享
5 +1
11
+1
全部评论 11
 
Boyuan
=LET(S,VSTACK(SEQUENCE(11,,10),SEQUENCE(4,,25,5))*10,T,SWITCH(U4,"白关卡航普快",HSTACK(VSTACK(S,{50;60;80;100}*10),AB4:AB23),"西线单一品名特快",HSTACK(VSTACK(S,{50;60;80;100}*10),AC4:AC23),"北疆特快散货-百货",HSTACK(VSTACK(S,{60;80;100}*10),AE4:AE22),"北疆特快散货-服装",HSTACK(S,AG4:AG19),"普快散货-百货",HSTACK(VSTACK(S,{60;80;100}*10),AI4:AI22),"普快散货-服装",HSTACK(S,AK4:AK19),"一带一路小百拼柜",HSTACK(VSTACK(S,{50;60;80;100}*10),AM4:AM23),"玩具专线",HSTACK(SEQUENCE(9,,12)*10,AO4:AO13)),XLOOKUP(1,FREQUENCY(T4,TAKE(T,,1)),TAKE(T,,-1)))
· 河南省
回复
 
懒得批爆
懒得批爆

创作者俱乐部成员

请移步https://bbs.wps.cn/topic/22141查看优化后的公式。 如果您有任何疑问,或者需要进一步的帮助,请留言,谢谢
· 四川省
1
回复
 
Jerry-谭
IFS不能处理吗?
· 江苏省
1
回复
 
亂雲飛渡
佩服,我也有两个if公式很长,但和你的相比还是小巫见大巫。
· 广东省
1
回复
 
wps新路
wps新路

WPS函数专家

这是在写作文吗?
· 重庆
1
回复
 
千叶
若你的价钱阶段一致的话,可以做个辅助表格,像我这图片这样,看起来也简单
· 江西省
回复
千叶
=XLOOKUP(C24,C2:J2,XLOOKUP(C25,B3:B22,C3:J22,,1))
· 江西省
回复
 
千叶
乖乖,拆开了149行,
· 江西省
回复
 
亂雲飛渡
从Excel 2007版本开始,IF函数的嵌套层数最多可以达到64层。wps应该也是64层吧
· 广东省
1
回复
 
千叶
你先把你的需求写一下 或者截个WPS表格的图片展示一下
· 江西省
2
回复
 
千叶
· 江西省
1
回复