|
由于EXCEL公式只允许IF嵌套到七层，而当前个调税税制正好七个梯级，虽然平时不会有什么问题，但是在计算年终奖税点时，由于还要判断12月是否已纳税，因此势必再次嵌套第八重，很多同学在这里卡壳了，因为套上第八层IF后，系统报错。于是，同学们被迫采取了以下方法：舍去部分高收入梯级（反正一般领“薪金”而要用EXCEL作工资表的人，都不会有80000多的月薪）。 但是这种方法总是存在着隐患，总让有完美癖的人感觉有点不放心，当然，也有的同学，再舍去部分梯级以后，再加上一个文字提示形如“if(A1＝80000，"税率未设置")”这样的东西，但是由于在数字列夹着文字，看着还是不太舒服。那么有没有其他的方法可以避免七重IF嵌套，而且鬼才知道我们可爱的税务总局会不会再细化这个征税梯级，如果增加到八层九层十层，那就真坑爹了。夏侯经过仔细研究，发现了几种方法，供大家参考。 一、先说一下常规思路（IF顺序嵌套） 公式： =IF(A2-3500<=0,0,IF((A2-3500)<=1500,(A2-3500)*0.03,IF((A2-3500)<=4500,(A2-3500)*0.1-105,IF((A2-3500)<=9000,(A2-3500)*0.2-555,IF((A2-3500)<=35000,(A2-3500)*0.25-1005,IF((A2-3500)<=55000,(A2-3500)*0.3-2755,IF((A2-3500)<=80000,(A2-3500)*0.35-5505,(A2-3500)*0.45-13505))))))) 说明：这就是那个恰好用完七重嵌套的if公式，在计算单月税金时是可以用的。 二、二叉树if =IF((A2-3500)>=35000,IF((A2-3500)>=80000,(A2-3500)*0.45-13505,IF((A2-3500)>=55000,(A2-3500)*0.35-5505,(A2-3500)*0.3-2755)),IF((A2-3500)<=0,0,IF((A2-3500)<=1500,(A2-3500)*0.03,IF((A2-3500)<=4500,(A2-3500)*0.1-105,IF((A2-3500)<=9000,(A2-3500)*0.2-555,(A2-3500)*0.25-1005))))) 说明：不允许嵌套，并不等于不允许出现七个以上的条件，本例中，不按上例中“是否属于第一级，如果不是判断是否属于第二级，如果还不是，判断是否属于第三级……”这样子“一条道走到黑”，而是从中间先判断，这样，先用一个IF把可能情况分成“上下半区”，然后再继续分别判断，显然，用这种二叉树，可以节省很多嵌套层次，因为每个半区还各有六层可以使用。 三、分段计算（一） =SUM(IF(AND((A2-3500)>0,(A2-3500)<=1500),(A2-3500)*0.03)+IF(AND((A2-3500)>1500,(A2-3500)<=4500),(A2-3500)*0.1-105)+IF(AND((A2-3500)>4500,(A2-3500)<=9000),(A2-3500)*0.2-555)+IF(AND((A2-3500)>9000,(A2-3500)<=35000),(A2-3500)*0.25-1005)+IF(AND((A2-3500)>35000,(A2-3500)<=55000),(A2-3500)*0.3-2755)+IF(AND((A2-3500)>55000,(A2-3500)<=80000),(A2-3500)*0.35-5505)+IF((A2-3500)>80000,(A2-3500)*0.45-13505)) 说明：从逻辑计算上思考，多重if的嵌套，其实是可以分拆的。本例中，对计税薪金的上下限用AND语句来确定，显然，对于一个定值，它只能属于计税速算表中某一个对应行。也就是如果它在1500至4500区间内，则必然不满足其他行的条件，本例中，不设置不满足条件的值（系统默认为零），然后再将所有区间的值累和，达到了同样的效果。（计算最后一步会出现形如sum(0,1,0,0,0,0,0)这样的步骤）。 四、使用vlookup公式 既然存在一个所谓的速算表，那么显然，查表本质上是在进行查表计算，那么，我们是否可以使用查询公式呢？答案是肯定的。 先新建一个工作表（名称为“查询公式用级数表”），在里面填入速算表，并调整如下（为什么要调整，下面会说到）
公式： =MAX(A2-3500,0)*VLOOKUP(MAX(A2-3500,0),查询公式用级数表!A1:C8,2)-VLOOKUP(MAX(A2-3500,0),查询公式用级数表!A1:C8,3) 本例中，用vlookup公式查询到对应的税率和扣除数，然后直接计算：薪金*税率—扣除数。 同学们可能注意到两点： （1）级数表中的薪金档次比官方薪金档多了1分钱，这是因为，VLOOKUP查询的结果是查找等于的，但计税表对于下限值是“大于下限且小等于上限”的，因此，如果不多加1分钱，将造成处于临界值的薪金被调高一档税率，考虑到我国货币的最小单位是分，因此将这个值调高1分。 （2）用于乘以系数的薪金加了一个MAX函数，这是因为，如果当月薪金低于起征额时（A2-3500<0），是不征税的,为了避免出现“负税金”的情况，对于低于起征额的薪金，统一用零来过滤掉。 这种方式的好处是，不管今后梯级税率怎么改，只要对这个税率表进行维护一下，并调整一下公式中的查询范围就可以了。 当然有的同学会说，这样要多一张专门的表，很是麻烦，有没有不用表的方法呢，有！ 把表直接写入公式中，如下： =MAX(A2-3500,0)*VLOOKUP(MAX(A2-3500,0),{0,0,0;0.01,0.03,0;1500.01,0.1,105;4500.01,0.2,555;9000.01,0.25,1005;35000.01,0.3,2755;55000.01,0.35,5505;80000.01,0.45,13505},2)-VLOOKUP(MAX(A2-3500,0),{0,0,0;0.01,0.03,0;1500.01,0.1,105;4500.01,0.2,555;9000.01,0.25,1005;35000.01,0.3,2755;55000.01,0.35,5505;80000.01,0.45,13505},3) 上式中，花括号中的部分，实际上就是那张表，其中，列与列之间用逗号隔开，行与行之间用分号隔开。 五、使用数组公式 数组公式似乎是EXCEL的不传之秘，微软给的官方帮助中，在数组公式一部分中，秉承了它的帮助文档“完全正确，绝对没用”的优良传统。让人看了觉得自己非常弱智。好在现在网上高人辈出，如果有的同学对数组公式不甚了了的话，可以另外搜索一下。这里不展开了。 直接上公式 当然，这也要新建一个工作表，用以包含速算级数以供查询（同学注意到，本表舍弃掉了最后一行，下面会说明）。
公式（花括号不必输入）： {=SUM(IF(((A2-3500)>数组公式用级数表!A1:A6)*((A2-3500)<=数组公式用级数表!B1:B6),(A2-3500)*数组公式用级数表!C1:C6-数组公式用级数表!D1:D6),IF((A2-3500)>80000,(A2-3500)*0.45-13505))} 输入完了以后，按照数组公式的要求，按下CTRL+SHIFT+ENTER完成数组公式的输入（系统自动加上花括号）。 这个公式的含义是，第一部分：如果计税值大于级数表中第A列的某个单元格，且小等于对应的B列的某个单格，则将计税额乘以对应行的第C列（税率），并减去对应行的D列（扣除数）。第二部分：对于大于80000以上的，由于没有上限值，无法在表格中体现，因此单独列出，对于满足大于80000条件的（必然不满足第一部分条件），直接乘以该级税率0.45并减去扣除数13505.然后对两部分求和（必然是sum(值，0)或者sum(0,值））中的一种情况。 同理，单独列表的好处是便于维护，如果不愿意多加一张表，也同样可以把表溶入公式中。 =SUM(IF(((A2-3500)>{0;1500;4500;9000;35000;55000})*((A2-3500)<={1500;4500;9000;35000;55000;80000}),(A2-3500)*{0.03;0.1;0.2;0.25;0.3;0.35}-{0;105;555;1005;2755;5505}),IF((A2-3500)>80000,(A2-3500)*0.45-13505)) 六、分段计算（二）——不用速算法 会出现IF判断，事实上是源于我们要引用速算表，那么，能否绕开速算表呢。答案仍然是肯定的。 让我们来分析一下所谓的速算表中扣除数的真正含义。 税法中说，对于超过XXX部分且小于XXX部分的按 XXX税率征税 所以，税额的本质是，比如5001元，税金是三部分组成 （1）0至3500元，免征；税金0元 （2）3500.01至5000，第一档税率计 （5000－3500）*0.03＝45元 （3）5000.01至5001，第二档税率，（5001－5000）*0.1＝0.1元 以上合计45.1元 按照速算表算，则是 （5001－3500）*0.1－105＝45.1元 那这个105元的扣除数是怎么得来的呢？ 实际上，它是那本属于第一档税率的1500元按第二档税率计税后的差价 1500*（0.1-0.03)=105 好了，废话说了一堆，现在上公式 =MAX(0,MIN(A2-3500,1500)*0.03)+MAX(0,MIN(A2-5000,3000)*0.1)+MAX(0,MIN(A2-8000,4500)*0.2)+MAX(0,MIN(A2-12500,26000)*0.25)+MAX(0,MIN(A2-38500,20000)*0.3)+MAX(0,MIN(A2-58500,25000)*0.35)+MAX(0,(A2-83500)*0.45)
这个公式中，不同的加号之间，就是不同的征税区间，如：其中MIN(A2-3500,1500)*0.03 （1）如果不足上限，则该区间的基数就是这个差额————类比于上面的5000.01至5001元 （2）如果超过上限，这一区间满征，应按这一区间的跨度征税——类比于上面的3500.01至5000 那，为什么还要有个MAX(0,）呢？ 这是因为，对于未达到下限的区间，公式会产生负数，所以还要对不同区间的计算值与零进行比较，如果出现负数，就用0来过滤掉。
|
|
|
