rpct.net
当前位置:首页 >> 求ExCEl中用函数计算销售提成方式 >>

求ExCEl中用函数计算销售提成方式

=a1*(if(a1>=3000,if(a1<20000,0.12,if(a1<50000,0.16,0.2)),0)) 如果等于5万数据不存在,那么再添加一下if() 在0.2处

=MAX(MAX(MAX(MAX(MAX((A1-20000)*0.013,0),15000*0.013+(A1-35000)*0.015),15000*0.013+20000*0.015+(A1-55000)*0.017),15000*0.013+20000*0.015+20000*0.017+(A1-75000)*0.019),15000*0.013+20000*0.015+20000*0.017+30000*

=IF(销售/目标<0.3,回笼资金-50,回笼资金*IF(销售/目标<1,2%,5%)) 更多精彩,尽在Excel疑难千寻千解,欢迎到我的百度文库下载阅读.

如果完成金额是C1,考核工资为D1那么B1=if((C1/A1)*100%=150%,D1+(150%-100%)/2%*100,if((C1/A1)*100%=70%,D1+0,D1+((C1/A1)*100%-100%)/2%*100))将销售额指标完成率每增加或降低2%,改成3%或4%,把公式中的2%替换成3%或者4%就可以了

=IF(D2/C2 评论0 0 0

=IF(D4<E4,0,IF(D4<F4,D4*0.2,IF(D4<G4,D4*0.3,IF(D4<H4,D4*0.5,IF(D4<I4,D4*0.7,D4*1)))))

=MIN(C2,4200)*0.12+(MIN(C2,8000)-4200)*0.13*(C2>4200)+(MIN(C2,12000)-8000)*0.14*(C2>8000)+(MAX(12000,C2)-12000)*0.15*(C2>12000)也可以用下面公式=LOOKUP(A1,{0,4200,8000,12000},{0.12,0.13,0.14,0.15})*(A1-LOOKUP(A1,{0,4200,8000,12000}))+LOOKUP(A1,{0,4200,8000,12000},{0,504,1544,3224})

=IF(A1>2000,A1*15%,IF(A1>1000,A1*10%,A1*5%))

公式:=C2 * VLOOKUP(C2/10000, {0,2.5; 6,3; 10,3.3; 15,3.5; 20,3.8; 30,4}, 2) / 100

如果业绩在B2单元格,那么销售提成输入 =SUM(TEXT(B2-{0,4,6,8,10}*10^4,"0;!0")*{0,8,2,2,2}%) 详见附图示例

网站首页 | 网站地图
All rights reserved Powered by www.rpct.net
copyright ©right 2010-2021。
内容来自网络,如有侵犯请联系客服。zhit325@qq.com