在工作中经常要用到分级累进的计费方式,一直想编一个公式,但能力有限,请各位不吝赐教!
具体计费如下:
100以下 费率:0.004
101-200部份 费率:0.003
201-1000部份 费率:0.002
1001-2000部份 费率:0.0015
2001-5000部份 费率:0.0008
5001-10000部份 费率:0.0004
10000 以上 费率:0.0001
累进求和。在EXCEL中怎样编一个简单易用的公式?
这是求分级累进的结果,类似个所税税率,因此直接找费率乘恐怕不对。
参考个所税弄个速加数:学学if函数,很简单阿~~
例如你的数据在A1单元格
=IF(A1<=100,A1*0.004,(IF(A1<=200,((A1-100)*0.003+100*0.004),(IF(A1<=,,,
=VLOOKUP(A1,{0,0.004;100,0.003;200,0.002;1000,0.0015;2000,0.0008;5000,0.0004;10000,0.0001},2)*A1+VLOOKUP(A1,{0,0;100,0.1;200,0.3;1000,0.8;2000,2.2;5000,4.2;10000,7.2},2)
=IF(A1<100,A1*0.004,IF(A1<200,A1*0.003,IF(A1<1000,A1*0.002,IF(A1<2000,A1*0.0015,IF(A1<5000,A1*0.0008,IF(A1<10000,A1*0.0004,IF(A1>10000,A1*0.0001))))))) choose函数也可。
联系客服