在工作中经常要用到分级累进的计费方式,一直想编一个公式,但能力有限,请各位不吝赐教! 具体计费如下: 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函数也可。 |
|