问题描述:
需要根据所属区域以及公斤数判断快递费,用ifs写,写的像长城那么长,麻烦大佬可以缩减下:
IFS(AND(OR(D2="一区",D2="二区"),E2<=0.5),2.5,AND(OR(D2="一区",D2="二区"),AND(E2>0.5,E2<=1)),3,AND(OR(D2="一区",D2="二区"),AND(E2>1,E2<=3)),3+(E2-1)*0.8,AND(OR(D2="一区",D2="二区"),AND(E2>3,E2<=5)),4.6+(E2-1)*1.3,AND(OR(D2="一区",D2="二区"),AND(E2>5)),7+(E2-1)*1.9,AND(D2="三区",E2<=0.5),2.5,AND(D2="三区",AND(E2>0.5,E2<=1)),3,AND(D2="三区",AND(E2>1,E2<=3)),3+(E2-1)*1.2,AND(D2="三区",AND(E2>3,E2<=5)),5.7+(E2-1)*1.8,AND(D2="三区",E2>5),8.7+(E2-1)*2.7,AND(D2="三区一",E2<=0.5),2.5,AND(D2="三区一",AND(E2>0.5,E2<=1)),3,AND(D2="三区一",AND(E2>1,E2<=3)),3+(E2-1)*1.2,AND(D2="三区一",AND(E2>3,E2<=5)),5.7+(E2-1)*1.8,AND(D2="三区一",E2>5),9.2+(E2-1)*3,AND(D2="四区",E2<=0.5),4.5,AND(D2="四区",AND(E2>0.5,E2<=1)),5.5,AND(D2="四区",AND(E2>1,E2<=3)),5.5+(E2-1)*1.7,AND(D2="四区",AND(E2>3,E2<=5)),8.6+(E2-1)*2.6,AND(D2="四区",E2>5),13+(E2-1)*3.8,AND(D2="五区",E2<=0.5),13,AND(D2="五区",AND(E2>0.5,E2<=1)),17,AND(D2="五区",AND(E2>1,E2<=3)),17+(E2-1)*13,AND(D2="五区",AND(E2>3,E2<=5)),17+(E2-1)*13,AND(D2="五区",E2>5),17+(E2-1)*13,AND(D2="五区一",E2<=0.5),15,AND(D2="五区一",AND(E2>0.5,E2<=1)),17,AND(D2="五区一",AND(E2>1,E2<=3)),17+(E2-1)*13,AND(D2="五区一",AND(E2>3,E2<=5)),17+(E2-1)*13,AND(D2="五区一",E2>5),17+(E2-1)*13,AND(D2="特殊区",E2<=0.5),6.6,AND(D2="特殊区",AND(E2>0.5,E2<=1)),9,AND(D2="特殊区",AND(E2>1,E2<=3)),9+(E2-1)*2.8,AND(D2="特殊区",AND(E2>3,E2<=5)),9+(E2-1)*2.8,AND(D2="特殊区",E2>5),8.7+(E2-1)*2.8)
需要根据所属区域以及公斤数判断快递费,用ifs写,写的像长城那么长,麻烦大佬可以缩减下:
IFS(AND(OR(D2="一区",D2="二区"),E2<=0.5),2.5,AND(OR(D2="一区",D2="二区"),AND(E2>0.5,E2<=1)),3,AND(OR(D2="一区",D2="二区"),AND(E2>1,E2<=3)),3+(E2-1)*0.8,AND(OR(D2="一区",D2="二区"),AND(E2>3,E2<=5)),4.6+(E2-1)*1.3,AND(OR(D2="一区",D2="二区"),AND(E2>5)),7+(E2-1)*1.9,AND(D2="三区",E2<=0.5),2.5,AND(D2="三区",AND(E2>0.5,E2<=1)),3,AND(D2="三区",AND(E2>1,E2<=3)),3+(E2-1)*1.2,AND(D2="三区",AND(E2>3,E2<=5)),5.7+(E2-1)*1.8,AND(D2="三区",E2>5),8.7+(E2-1)*2.7,AND(D2="三区一",E2<=0.5),2.5,AND(D2="三区一",AND(E2>0.5,E2<=1)),3,AND(D2="三区一",AND(E2>1,E2<=3)),3+(E2-1)*1.2,AND(D2="三区一",AND(E2>3,E2<=5)),5.7+(E2-1)*1.8,AND(D2="三区一",E2>5),9.2+(E2-1)*3,AND(D2="四区",E2<=0.5),4.5,AND(D2="四区",AND(E2>0.5,E2<=1)),5.5,AND(D2="四区",AND(E2>1,E2<=3)),5.5+(E2-1)*1.7,AND(D2="四区",AND(E2>3,E2<=5)),8.6+(E2-1)*2.6,AND(D2="四区",E2>5),13+(E2-1)*3.8,AND(D2="五区",E2<=0.5),13,AND(D2="五区",AND(E2>0.5,E2<=1)),17,AND(D2="五区",AND(E2>1,E2<=3)),17+(E2-1)*13,AND(D2="五区",AND(E2>3,E2<=5)),17+(E2-1)*13,AND(D2="五区",E2>5),17+(E2-1)*13,AND(D2="五区一",E2<=0.5),15,AND(D2="五区一",AND(E2>0.5,E2<=1)),17,AND(D2="五区一",AND(E2>1,E2<=3)),17+(E2-1)*13,AND(D2="五区一",AND(E2>3,E2<=5)),17+(E2-1)*13,AND(D2="五区一",E2>5),17+(E2-1)*13,AND(D2="特殊区",E2<=0.5),6.6,AND(D2="特殊区",AND(E2>0.5,E2<=1)),9,AND(D2="特殊区",AND(E2>1,E2<=3)),9+(E2-1)*2.8,AND(D2="特殊区",AND(E2>3,E2<=5)),9+(E2-1)*2.8,AND(D2="特殊区",E2>5),8.7+(E2-1)*2.8)
