按销售额区间分段,各区间的提成比例阶梯式递增

更新时间2021-09-10 17:57:10

销售区间划分为0-10、10-20、20-50、50-80、80-100、100以上
对应的提成比例0% 、   15、    20%、   25%、   30%、    35%
例如某员工的销售额为72，对应的提成=（10-0）*0%+（20-10）*15%+（50-20）*20%+（72-50）*25%
在线求大神赐一个excel函数公式！万分感谢！

效果图如下：

  

               单元格E2输入公式：

                         =IF(LOOKUP(D2,{0,10,20,50,80,100},{0,0.15,0.2,0.25,0.3,0.35})=0,0,IF(LOOKUP(D2,{0,10,20,50,80,100},{0,0.15,0.2,0.25,0.3,0.35})=0.15,(D2-10)*0.15,IF(LOOKUP(D2,{0,10,20,50,80,100},{0,0.15,0.2,0.25,0.3,0.35})=0.2,(20-10)*0.15+(D2-20)*0.2,IF(LOOKUP(D2,{0,10,20,50,80,100},{0,0.15,0.2,0.25,0.3,0.35})=0.25,(20-10)*0.15+(50-20)*0.2+(D2-50)*0.25,IF(LOOKUP(D2,{0,10,20,50,80,100},{0,0.15,0.2,0.25,0.3,0.35})=0.3,(20-10)*0.15+(50-20)*0.2+(80-50)*0.25+(D2-80)*0.3,IF(LOOKUP(D2,{0,10,20,50,80,100},{0,0.15,0.2,0.25,0.3,0.35})=0.35,(20-10)*0.15+(50-20)*0.2+(80-50)*0.25+(100-80)*0.35+(D2-100)*0.35))))))

              下拉公式就OK了

(A1>10)*A1/10+SUM((A1>{10;20;50;80;100})*((A1-{10;20;50;80;100}))/20