返回目录：excel表格制作
假设数据在A1在B1输入=if(A1>1000,10%,if(A1>500,20%,if(A1>100,30%,"")))
=IF(A19>100000,(A19-100000)*0.01%+75.55,IF(AND(A19>10000,A19<=100000),(A19-10000)*0.05%+30.55,IF(AND(A19>5000,A19<=10000),(A19-5000)*0.2%+20.55,IF(AND(A19>1000,A19<=5000),(A19-1000)*0.35%+6.55,IF(AND(A19>500,A19<=1000),(A19-500)*0.55%+3.8,IF(AND(A19>100,A19<=500),(A19-100)*0.7%+1,IF(AND(A19<100),(A19-100)*1%)))))))
一个问题，A10中是100000以上是吧，回你的答图片中是1000000，这样的话断档了，公式中最后一档按100000以上算的
货物类：e799bee5baa6e78988e69d83366
=LOOKUP(A1%,{0,1,5,10,50,100,1000;0,1.5,5.9,9.9,29.9,42.4,87.4})+(A1%-LOOKUP(A1%,{0,1,5,10,50,100,1000}))*LOOKUP(A1%,{0,1,5,10,50,100,1000;1.5,1.1,0.8,0.5,0.25,0.05,0.01})
服务类：
=LOOKUP(A1%,{0,1,5,10,50,100,1000;0,1.5,4.7,6.95,16.95,21.95,66.95})+(A1%-LOOKUP(A1%,{0,1,5,10,50,100,1000}))*LOOKUP(A1%,{0,1,5,10,50,100,1000;1.5,0.8,0.45,0.35,0.2,0.05,0.01})
工程类：
=LOOKUP(A1%,{0,1,5,10,50,100,1000;0,1,3.8,6.55,20.55,30.55,75.55})+(A1%-LOOKUP(A1%,{0,1,5,10,50,100,1000}))*LOOKUP(A1%,{0,1,5,10,50,100,1000;1,0.7,0.55,0.35,0.2,0.05,0.01})
首先将费率部分除10000以上转换为金额获得辅助数据e799bee5baa6e4b893e5b19e339：
100-500:400万元*0.1%=4000元
500-1000:500万元*0.09%=4500元
1000-3000:2000万元*0.08%=16000元
3000-5000：2000万元*0.06%=12000元
5000-10000:5000万元*0.03%=15000元
基于以上数据，公式如下（注A1单元格输入数据必须为万元）：
2.1计算结果单位为元的公式
=IF(A1>10000,(A1-10000)*0.02%*10000+52700,IF(A1>5000,(A1-5000)*0.03%*10000+37700,IF(A1>3000,(A1-3000)*0.06%*10000+25700,IF(A1>1000,(A1-1000)*0.08%*10000+9700,IF(A1>500,(A1-500)*0.09%*10000+5200,IF(A1>100,(A1-100)*0.1%*10000+1200,1200))))))
2.2计算结果单位为万元的公式
=IF(A1>10000,(A1-10000)*0.02%+52700/10000,IF(A1>5000,(A1-5000)*0.03%+37700/10000,IF(A1>3000,(A1-3000)*0.06%+25700/10000,IF(A1>1000,(A1-1000)*0.08%+9700/10000,IF(A1>500,(A1-500)*0.09%+5200/10000,IF(A1>100,(A1-100)*0.1%+1200/10000,1200/10000))))))
另外看样子您是招标公司或者甲方吧，这个是不是用来算招标代理费的，这个跟1980号文也差太多了吧，利润有点低呀！
，