Question

举例求教：excel区间函数公式

例如：A1（输入任意数），A2（输出条件：当A1=(0.5~3)，A2=0.2；当A1=（>3~6）,A2=0.3；当A1=（>6~30），A2=0.5；当A1=（>30~120），A2=0.8，当A1=（>120~400），A2=1.2

Answer 1

在A2单元格输入公式：
=IF(A1<0.5,"",IF(A1<3,0.2,IF(A1<6,0.3,IF(A1<30,0.5,IF(A1<120,0.8,IF(A1<400,1.2,""))))))
或者：
=IF(OR(A1<0.5,A1>400),"",LOOKUP(A1,{0,3,6,30,120},{0.2,0.3,0.5,0.8,1.2}))

Answer 2

A2=IF(AND(A1>=0.5,A1<3),0.2,IF(AND(A1>=3,A1<6),0.3,IF(AND(A1>=6,A1<30),0.8,IF(AND(A1>=30,A1<120),0.8,IF(AND(A1>=120,A1<400),1.2,"错误")))))