EXCEL表格条件判断后进行计算

Question

假定X为A1，Y为B1，判断后计算的数值为C1，有4个判断条件，X>20%时，Y乘以0.02，15%≤X<20%时,Y乘以0.015，10%≤X<15%时,Y乘以0.01，X<10%时，Y乘以0.005；计算后显示数值。

该怎么写函数？

Answer 1

c1输入
=b1*if(a1>0.2,0.02,if(a1>=0.15,0.015,if(a1>=0.1,0.01,0.005)))
即可本回答被提问者采纳

Answer 2

C1公式
=B1*lookup(a1,{0,0.1,0.15,0.2},{0.005,0.01,0.015,0.02})

Answer 3

请在C1输入公式：
=IF(A1>=20%,B1*0.02,IF(A1>=15%,B1*0.015,IF(A1>=10%,B1*0.01,B1*0.005)))

Answer 4

C1单元格写入：
=IF(A1>0.2,B1*0.02,IF(A1>=0.15,B1*0.015,IF(A1>=0.1,B1*0.01,B1*0.005)))
或者
=B1*IF(A1>0.2,0.02,IF(A1>=0.15,0.015,IF(A1>=0.1,0.01,0.005)))
或者
=PRODUCT(B1,IF(A1>0.2,0.02,IF(A1>=0.15,0.015,IF(A1>=0.1,0.01,0.005))))

Answer 5

=IF(A1>=0.2,B1*0.02,IF(A1>=0.15,B1*0.015,IF(A1>=0.1,B1*0.01,B1*0.005)))