按销售额区间分段,各区间的提成比例阶梯式递增
销售区间划分为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