excel中怎样用函数求提成率？

Question

要同时满足X区间及Y区间，求出对应比例
如X=18，Y=74，那么提成率就是0.3%
这样用excel函数公式怎么求？

Answer 1

公式：

=MIN(VLOOKUP(A2,{16,0.3;18,0.8;20,1.5;22,2},2),

VLOOKUP(B2,{60,0.3;75,0.8;90,1.5;105,2},2))

示例：

哦，补充一点，最后结果再除以100，也就是公式为=MIN(VLOOKUP(),VLOOKUP())/100

Answer 2

建议参照如下：

在J4单元格中输入公式：

=IF(I4<1000,"小微",IF(AND(I4>=1000,I4<5000),"三级",IF(AND(I4>=5000,I4<10000),"二级",IF(AND(I4>=10000,I4<50000),"一级","特级"))))

如图：

Answer 3

如图，参考c2公式：

=MIN(LOOKUP(A2,{0,16,18,20,22},{0,0.003,0.008,0.015,0.02}),LOOKUP(B2,{0,60,75,90,105},{0,0.003,0.008,0.015,0.02}))

其中为了解决xy低于16和60的问题，进行了容错，取0.