ydzf.net
当前位置:首页 >> ExCEl计算业绩提成公式 >>

ExCEl计算业绩提成公式

假定业绩在A1单元格,在B1单元格计算提成,则在B1写公式:=LOOKUP(A1,{0,10001,20001,30001,50001,100001,150001},{0.45,0.48,0.5,0.52,0.55,0.58,0.65})*A1-LOOKUP(A1,{0,10001,20001,30001,50001,100001,150001},{0,0,0,600,2100,

提成减0.2%是指业绩提成(业绩乘以相应的百分数)减还是指对应的百分数减?这两种算法的结果是不一样的.假定基本工资在A1,业绩在A2,不考虑省略号以后的条件,工资的公式是=A1+B1*LOOKUP(B1,{0,2,4,6}*10000,{0,5,6.5,8}%)

简短的未想到,有个容易理解的先给你用用,想到简短的再说,业绩在A1,可在B1输入公式:=IF(A1<=6000,A1*10%,IF(A1<=12000,6000*10%+(A1-6000)*15%,IF(A1<=20000,6000*10%+6000*15%+(A1-12000)*20%,IF(A1<=40000,

比如A1为业绩 B1为提成 =LOOKUP(A1,{0,3000,6000,10000}+0.001,{0,0.1,0.15,0.2})*A1 如果是指计算提成点就是 =LOOKUP(A1,{0,3000,6000,10000}+0.001,{0,0.1,0.15,0.2})

若从a列从a2开始是业绩,在b2输入公式:=lookup(a2-1%%,{0,9999,30000,50000,80000,100000,120000,150000,180000,210000,230000},{0,0.04,0.06,0.08,0.1,0.12,0.14,0.16,0.18,0.2,0.22}) 复制并下拉,b2开始的b列就是对应的提成比率,

设业绩在c列 在d2输入:=if(c2<=5000,c2*15%,5000*15%+if(c2<=10000,(c2-5000)*20%,5000*20%+if(c2<=20000,(c2-10000)*25%,10000*25%+if(c2<=40000,(c2-20000)*30%,20000*30%+(c2-40000)*35%)))) 当业绩为5000到10000之间时,小于

=if(int(业绩专属/15000),(业绩-15000)*0.2+5000*0.15+10000*0.08,(if(int(业绩/10000),(业绩-10000)*0.15+10000*0.08,0))

假设A1单元格是业绩,A2单元格用公式计算提成则公式为:=IF(AND(A1>0,A1<12000),A1*28%,IF(AND(A1>=12000,A1<14000),A1*29%,IF(AND(A1>=14000,A1<16000),A1*30%,IF(AND(A1>=16000,A1<18000),A1*31%,IF(A1>=18000,A1*32%)))))

=IF(Q5<60%,P5*1%,IF(Q5<80%,P5*4%,IF(Q5<90%,P5*5%,IF(Q5<100%,P5*7%,IF(Q5=100%,P5*9%,P5*12%)))))

如果业绩在A列B列显示提成=IF(A1>200000,A1*0.16,IF(A1>=150000,A1*0.155,IF(A1>=120000,A1*0.145,IF(A1>=100000,A1*0.14,IF(A1>=80000,A1*0.135,IF(A1>=60000,A1*0.13,IF(A1>=40000,A1*0.22,IF(A1>=30000,A1*0.2))))))))这个的意思如果A1(业绩)大于20w,B1(提成)等于20w*0.16.如果A1大于15w,B1等于20W*0.155

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