假设销售额在A1单元格,则公式为:=IF(A1<15000,A1*0.03,IF(A1<22500,750+(A1-15000)*0.08,1350+(A1-22500)*0.1)) 上面的公式是按阶梯提成来计算的.

请参考LOOKUP公式

=IF(A1 再看看别人怎么说的.

假设本月业绩保存在a2单元格,b2单元格算提成 b2单元格公式:=if(a1

=SUMPRODUCT(TEXT(A1-{0,6,9,12,16,19,22,25}*10000,＂0.00;\0＂)*{0.1,0.03,0.02,0.01,0.02,0.02,0.01,0.01})

假设销售额数据是在b2单元格开始的b列,则可以在c2单元格输入公式:=lookup(b2/10^6,{0,3,8,15,30},{6,7,8,9,10}/10^4)复制并下拉,即可得到提成比例.提成额公式为: =b2*lookup(b2/1000000,{0,3,8,15,30},{6,7,8,9,10}/10000)

=if(a1<=5000, a1*10%, if(a1<=10000, (a1-5000)*15%+5000*10%, if(a1<=20000, (a1-10000)*20%+5000*15%+5000*10%, if(a1<=60000, (a1-20000)*25%+10000*20%+5000*15%+5000*10%, (a1-60000)*30%+40000*25%+10000*20%+5000*15%+5000*10%))))

=if(a1>150000,4.5%,if(a1>=90000,4%,if(a1>=60000,3.5%,if(a1>=0,3%,0))))

基数 级差 比例 提成 累计提成5000 5000 10% 500 50010000 5000 15% 750 125020000 10000 20% 2000 325060000 40000 25% 10000 13250 30% 可以先列一个如上核算表,很容易用if函数,比如b2=if(a2<=5000,a2*10%,if(a2<=10000,(a2-5000)*15%+500,if(a2<=20000,(a2-10000)*20%+1250,if(a2<=60000,(a2-20000)*25%+3250,(a2-60000)*30%+13250))))

在C2 D2中分别输入或复制粘贴下列公式=IF(AND(A2>=80,A2=140,4,0)*A2选择C2:D2,下拉填充