这个可变量用EXCEL公式如何实现

来源：百度知道 编辑：UC知道 时间：2024/06/27 21:56:34
若A1的值是4~7,B1会显示US$4.00
若A1的值是8~14,B1会显示US$3.00
若A1的值是15~30,B1会显示US$1.00
若A1的值是31~50,B1会显示US$0.30
若A1的值是51~300,B1会显示US$0.00
若A1的值是301~500,B1会显示US$3.50
若A1的值是500~700,B1会显示US$4.50

=LOOKUP(A1,{4,8,15,31,51,301,500,701},{"US$4.00","US$3.00","US$1.00","US$0.30","US$0.00","US$3.50","US$4.50",""})

B1的公式为
=if(and(a1>4,a1<7),"us$4.00",if(and(a1>8,a1<14),"us$3.00",if(and(a1>15,a1<30),"us$1.00",if(and(a1>31,a1<50),"us$0.30",if(and(a1>51,a1<300),"us$0.00",if(and(a1>301,a1<500),"us$3.50",if(and(a1>500,a1<700),"us$4.50","")))))))
需要的话可下拉

若有等于的情况自己添加

用IF函数嵌套即可.
B2单元格录入:
=if((A1>=4)*(A1<=7),us$4.00,if((A1>=8)*(A1<=14),US$3.00,IF ....))
注意:省略号部分按照前面的格式直接录入条件即可,直接用省略号可不行的...

=IF(AND(A1>=4,A1<=7),"us$4.00",IF(AND(A1>=8,A1<=14),"us$3.00",IF(AND(A1>=15,A1<=30),"us$1.00",IF(AND(A1>=31,A1<=50),"us$0.30",IF(AND(A1>=51,A1<=300),"us$0.00",IF