凭栏袖手阶梯分段累进计算的EXCEL简化公式与模型
今天谈谈日常生活中常见的阶梯分段累进计算的实例，大致有水电费、天然气费、的士费、停车费、销售提成、个人所得税、土地增值税和规划核实测量建筑面积与规划许可建筑面积两者之间的合理误差等。从理论到实践一一说清楚，最后均给出简便计算公式及模板，方面大家直接运用，甚至达到举一反三的目标。
问题一，电费。
深圳的电费是最规范的，定价精确到小数点后面6位，要知道上面的单位是分，也就是说精确到百万分之一分。实际上也是这么操作的。
这是我家今年三月的电费发票，用电期间就是3月1日到31日，整整一个月。
还有三档计算过程，和上面发票金额一致。下面开始验算。
对所有的阶梯分段累进计算而言，我们的简化策略都是先算出速算扣除数，然后利用我在《利润奖、个税及Excel数组运算应用的数学证明》http://hwmumu.top/shuxue/149.html
一文中证明的命题二的结论列出简明的Excel公式及模板来计算。
3月非夏季，按200度一档来算，a只有两个值，因此n=2，b须有n+1=3个值，然后计算单价差Δb，最后得出速算扣除数。注意黄框中的MAX函数中的速算扣除数的数组中第一个值总是零。我家3月用电总量为453度，算出电费326.17954元，供电局算的是326.17元，少算了0.954分，原因在于供电局是按三档分别算的，用了三次四舍五入，我的公式更精确，没用四舍五入。我在想有朝一日供电局会用我的更精准的公式来计算电费吗？
问题二，水费。
深圳公布的价格如下：
和深圳的电价相比，水价只精确到分；另外一点，就是污水处理费和垃圾处理费的计价基数是用水总量的9折。
水价是两月抄一次表，元月一日到三月一日。家庭户每月一档是22立方，两个月就是44立方，上图计算也算规矩，没什么问题。下面验算。
水费和供水公司的结果完全一致。
污水处理和垃圾处理就有问题了，供水公司是按总用水量打九折精确到立方米计算，太粗了，多收了我家7毛6的污水处理费和4毛7的垃圾处理费。但长远看来，应该也是平衡的。
问题三，天然气费。
最不规范的就是天然气，是分大小月抄表，时间还不一定。
2021年9月26日到2021年11月11日，一个半月，才38立方，38*3.5=133(元），没问题。
2021年11月11日到2021年11月27日，不知是算16天还是半个月，不过按冬季一档每月40立方米的量，怎么看28立方米也像是超过了；但算账还是一样：28*3.5=98（元）
2021年11月27日到2022年1月11日，又是一个半月，48*3.5=168（元），这个账很清楚，不必验算，但是本着宁滥勿缺的精神，下面还是给出冬季按月的计算公式及模板。
燃气集团的行为模式，我还是很喜欢的，大小月，还是有规律的，关键是老百姓得到了实惠，赞一个！
问题四，销售提成。
| 推销额(元） | 提成率 |
| 不超过10000（含） | 2% |
| 10000-30000（含） | 3% |
| 30000-50000（含） | 4% |
| 50000-80000（含） | 6% |
| 超过80000 | 8% |
张三、李四、王二推销额分别为：20000元、60000元、100000元，求各自的提成奖。
这是典型的超额累计计算问题，直接套用我们的模板就可以。
因此，三人的提成奖分别为：500元、2200元、5000元。
问题五，个人所得税。
其实，去年文章里谈到了个税，但玩了些花样，今天实打实。
直接上模板。
这就是个税公式，简明易懂，上一篇用网上的刻意做了些故弄玄虚的变换来增加神秘感，没必要，都是革命同志，不用藏着掖着。为简化，连四舍五入函数都省了。
对经营所得，有所不同。
此时，n=4，公式模板如下：
问题六，土地增值税。
按《土地增值税暂行条例》的规定，土地增值税实行四级超率累进税率，具体税率如下：
（1）增值额未超过扣除项目金额50%的部分，税率为30%。
（2）增值额超过扣除项目金额50%、未超过扣除项目金额100%的部分，税率为40%。
（3）增值额超过扣除项目金额100%、未超过扣除项目金额200%的部分，税率为50%。
（4）增值额超过扣除项目金额200%的部分，税率为60%。
前述四级超率累进税率，每级“增值额未超过扣除项目金额”的比例，均包括本比例数。
和前面五个问题有明显区别，有两个金额，增值额和扣除项目金额；外加两个率，超过的固定比率50%、100%、200%和税率30%-60%。处理起来略有不同。
到现在，是不是觉得命题二很伟大？它似乎为所有的简化计算铺平了道路。
问题七，的士费。
2022年深圳出租车运价标准：
（一）起步价：10元/2公里。
（二）里程价：2.7元/公里。
（三）返空费：20-35公里部分按里程价加收30%、35公里及以上部分按里程价加收60%。
（四）候时费（慢速等候费）：时速低于10公里或者等于0时，每分钟0.8元。
（五）夜间附加费：每日23:00至次日6:00，按起步价和里程价加收30%。
（六）重点区域特殊时段附加费：每日23:30至次日6:00，从深圳机场巡游车轮排候客区出发的，每运次加收15元；从深圳北站巡游车轮排候客区出发的，每运次加收5元。
（七）春节附加费：春节期间（除夕00:00—年初六23:59）,每运次加收10元。
（八）预约服务费（电召服务费）：最高收费6元/次，具体收费标准由巡游车经营者与服务平台协商确定。
（九）大件行李费，体积超过0.2立方米、重量超过20公斤的大件行李，0.5元/件。
起步价是个新问题，凡是短于两公里都是10元，要调整模型解决。我们只看一般正常情况，后面的特殊费用先忽略。
问题八，停车费。
我们只看一下路边停车。
另外，还有个规定——在非免费停车时段，免费操作时间：10分钟。也就是说10分钟之内免费，我们只看看一类区域工作日的情况。
问题七和问题八已经有些超纲了。最后上一个终极大BOSS 。
问题九，规划核实测量建筑面积与规划许可建筑面积两者之间的合理误差。
规划核实测量建筑面积与规划许可建筑面积两者之间的合理误差按以下规定累进计算（建筑面积指计容积率面积）：
1000平方米以内（含1000平方米）部分3%。
1000～5000平方米以内（含5000平方米）部分2%。
5000～10000平方米以内（含10000平方米）部分1.5%。
10000平方米以上部分1.3%，累进计算的建筑面积合理误差不得超过500平方米。
猛一看，你可能觉得没什么了不起的，不过是又一个分段计算而已，但细看一下，你会发现这个问题的序列bi是单调递减的。要知道前面八个问题的序列ai和bi都是单调递增的，这是命题二的前提条件，现在命题二用不了，怎么办？
简单，重新构造在序列bi单调递减的条件下成立的命题三和命题四。
证明：1、证明命题一和命题二时，有些事没想明白，强行引入了m，现在看来是不必要的，但为了保持一致性，就不做修改了，命题三和命题四也依然用包含m的方式来表达，但必须 明确的是——真正变量是j，不是m。因此，命题一与命题二应调整表述如下：
下面，我们用命题四解决问题九。
命题二已经够伟大了，现在又有了命题四，这是阶梯分段累进计算方法的终结吗？欢迎大家提出新的问题来探讨，关键是平时要多多运用，同一个模板重复了十次，我不相信你还敢说自己不会用。
15条回应：“阶梯分段累进计算的EXCEL简化公式与模型”
发表评论 取消回复
热门推荐
- 负指标完成率的计算之实战篇
- 从负指标完成率的计算谈起
- 利润奖、个税及Excel数组运算应用的数学证明
I really enjoy examining on this website , it has got cool stuff . Jules Hubler
Great, thanks for sharing this article post. Thanks Again. Awesome. Enrique Thomson
These are genuinely wonderful ideas in about blogging. You have touched some pleasant points here. Any way keep up wrinting. Solomon Wuerz
Really enjoyed this blog article. Really looking forward to read more. Much obliged. Reyes Crowl
What a information of un-ambiguity and preserveness off precious know-how about unpredicted feelings. Basil Uptgraft
I think the problem for me is the energistically benchmark focused growth strategies via superior supply chains. Compellingly reintermediate mission-critical potentialities whereas cross functional scenarios. Phosfluorescently re-engineer distributed processes without standardized supply chains. Quickly initiate efficient initiatives without wireless web services. Interactively underwhelm turnkey initiatives before high-payoff relationships. Felix Lamos
Together with every little thing that seems to be building inside this area, your points of view happen to be fairly stimulating. Nonetheless, I beg your pardon, but I do not subscribe to your entire idea, all be it radical none the less. It would seem to everybody that your commentary are generally not totally justified and in fact you are generally yourself not even entirely confident of your argument. In any event I did enjoy reading it. Clyde Schiel
I in addition to my friends were actually reading the good hints located on the blog then then came up with a horrible suspicion I never thanked the site owner for those strategies. All the people happened to be so thrilled to read through them and have sincerely been using these things. Thanks for getting indeed kind and for considering these kinds of brilliant guides millions of individuals are really desperate to be informed on. My personal sincere regret for not expressing appreciation to sooner. Rodney Einwalter
Thanks for sharing superb informations. Your web site is very cool. I am impressed by the details that you have on this web site. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for extra articles. You, my pal, ROCK! I found just the info I already searched everywhere and simply could not come across. What an ideal website. Samuel Demetro
Excellent post! I also agree and sadly I think the way freestyling has evolved now; and people with so little patience, its almost a MUST to only have prewrittens, and never the old-fashioned off-the-top frees. And in the future it will probably evolve even more .. kinda like how music sucks today.. its just all going down the drain.. Harrison Bihm
I not to mention my friends came looking at the nice procedures found on your web page while quickly came up with a horrible feeling I never expressed respect to the website owner for those tips. These boys were totally happy to read through them and have in effect surely been tapping into them. Appreciate your indeed being well considerate and also for using this form of good subject areas most people are really wanting to know about. Our own honest apologies for not saying thanks to you sooner. Diego Mckenney
Many thanks for this article. I will also like to mention that it can possibly be hard when you are in school and just starting out to initiate a long credit rating. There are many college students who are just trying to endure and have a lengthy or beneficial credit history are often a difficult issue to have. Patrick Reinders
I wanted to post you that tiny remark to say thanks a lot over again just for the pleasant solutions you have featured in this case. It was really shockingly open-handed with you to grant publicly just what many of us might have offered for an ebook to generate some dough for themselves, mostly now that you might have tried it in case you desired. These secrets as well served to be a good way to fully grasp that other people online have the identical fervor much like my personal own to know the truth more regarding this matter. I am sure there are numerous more pleasant periods in the future for many who read your site. Julian Lasane
An impressive share, I just given this onto a colleague who was doing just a little evaluation on this. And he in truth purchased me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I feel strongly about it and love studying extra on this topic. If possible, as you turn out to be expertise, would you mind updating your weblog with extra details? It is highly helpful for me. Huge thumb up for this blog put up! Edison Zinzow
I simply wanted to jot down a note to be able to express gratitude to you for the splendid tricks you are writing at this website. My rather long internet lookup has at the end been compensated with excellent facts to write about with my friends and classmates. I would assume that we website visitors are really blessed to be in a great site with many lovely people with beneficial points. I feel pretty happy to have seen the weblog and look forward to so many more amazing moments reading here. Thank you again for everything. Jamel Abdool