从年1月1日起，个人所得税的起征点有元调至元，原来的工资表就不能再用了，应同事的要求就有今天这版工资表，由于我们其他的城市有分公司，就加入了根据不同地区计算出五险一金的个人部分，并日后可以调整个税参数，就可以自动应用的到数据中。随着我的制作过程，一起来看看这个功能表是怎么做的吧！首先具体来看一下该表的功能要求！
完整的工资计算程序效果功能需求：
1.可调整个税的各种参数，调整后，应用到已有的表格中
2.可根据当地的五险一金的具体情况计算个人的保险部分
3.最后根据工资计算个税以及实际发放工资的。
制作前的资料：最新的个人所得税的参数表，各个城市的五险一金的上缴标准等基础数据外
个税信息和各地五险一金税率表最重要的是个税的计算公式和五险一金的计算公式；
个税计算公式=（应发工资-五险一金个人部分-个税基数）*对应级别的个税税率-速算扣除数
五险一金的计算就比较简单了，=应发工资*各项对应的税率
注：个人所得税表名：个税，各地五险一金数据表名：五险一金
具体怎么做？我们先来看一下公式，如果想求出个人所得税的应缴部分需要知道两个条件，1.是我们所交五险一金的个人部分，2.个税基数，个税基数我们知道，所以我们首先要计算出五险一金的个人所交的部分，而五险一金个人的承担的部分，由于不同城市有着不同的地方财政政策，规定所交税率也不同，我们需要先获得员工所在的城市，进一步确认各项保险所缴纳的金额；开始我们第一步作出可以根据城市变化的自动重新计算出保险的个人的公式！
自动扩充的下拉菜单
由于我这只有一个城市员工工资信息，就在工资表中增加了城市选择的功能，这种下拉菜单很容易实现，不过要自动扩充就要费点劲了，制作之前简单复习一下，制作的下拉菜单的步骤：
1.选中要填充选项的单元格，点下的,验证条件中允许选，普通的选择列表范围，一旦数据表有有所增加，需要重新调整列表范围，接着就是无公式的自动扩充方式
2.要自动扩充列表内容，如果应用在当前表，就不用公式，直接将列表内容变成“超级表”就可以了即：选中数据范围，ctrl+t，然后在重复第1步内容就可以，但也仅限于列表和数据表在一个工作表中，跨表就不好用了
3.需要用公式解决，一劳永逸，我们选择数据一般从第2行开始，到没有内容位置，这里就需要一个没有学过的函数叫counta(范围),就可以统计范围内有数据的个数，这样就可以同过总长度减去无用行数，这里只有标题无用，减去1，这也就能得到行数，光有行数并不能生成范围，我们需要一个函数，来将这些数据转化成有数据的单元格的范围，这个函数就是offset。
自动扩充下拉列表的范围公式offset函数有5个参数，第1参数为开始位置，第2参数为偏移多少行，第3参数为偏移多少列，第4参数为范围行数，第5函数则引用范围的列数，在A1模式下举个简单的例子来熟悉一下这个函数=offset(A1,2,3,5,1),在第2参数偏移行数和第3参数偏移列数作用下，A则偏移3列到D,行则偏移2则为3即D3,第5个参数表示宽度即引用范围包1列，第4参数表示为5行，则公式的最终结果为：D3:D7.
如果你想选择一个单元格，则需要将公式改为=offset(A1,2,3,1,1)最后的结果为D3单元格，好了知道这些，我们开始引用范围公式编写，案例中使用的数据在第一列，所以我们要生成第一列所有有数据的范围除了标题行：=OFFSET(五险一金!$A$1,1,0,COUNTA(五险一金!$A:$A)-1,1)；
自动扩充下拉菜单搞定了城市的选择问题，接着我们就来搞定根据城市来编写计算公式吧，我们需要从五险一金表的数据就要用了vlookup函数了，这个函数有4个参数,第1参数就查找的值，第2参数就是查找引用范围
，第3参数所在选择范围列的索引号，第4参数有两个值，true,false或0,1；false和0表示精确查找，1和true则是模糊查询，我们这需要精确查询，我们先预定第4参数位0。
注：拖拽公式，引用单元格的行号和列数都会自动增加1，绝对引用符号能限制这种行为
城市所在的单元格为N1,我们需要获取五险一金的中对应的数值，为了方便的查询，我们在做表的时，是两个表中的对应数据的顺序一致，我们先写一个养老保险的公司，工资在C2的单元格而养老税率在表格第二列，我们在选择查找范围的时候，选择是整列数据及6列，为拖拽公式做准备，好了需要的条件都有了，公式也就好写=vlookup(n1,五险一金!$A:$F,2,0),结果也有了，可以拖拽公式，却发现无发查找正却结果，原来有几个地方需要优化一下才行：
首先：第1参数，需要绝对引用$N$1,否拖拽时会自动扩延单元格；
其次：就是顺序索引2，拖拽时并不能自动增加，这里需要具有返回单元格所在列数函数COLUMN帮忙，养老保险所在单元格为D列，在减去偏移的部分2即COLUMN(D2)-2,为了防止拖拽时发生变化，需要绝对引用行号，公式变为COLUMN(D$2)-2，不过我们最后是要计算出个人部分是多少钱，所以根据计算需改为=vlookup($N$1,五险一金!$A:$F,COLUMN(D$2)-2,0)*$C2,最后来一个严谨判断=ifna(vlookup($N$1,五险一金!$A:$F,COLUMN(D$2)-2,0)*$C2,0),然后拖拽公式就可以完成个人部分的计算。
个人所得税计算公式
完成了保险个人承担的部分，个人上缴基数我是通过自定义名称实现，当然你也可以把它存在个税的表中，然后通过跨表引用也可以实现，这个就留给你自己尝试吧，我们一起看看定义名称实现步骤：
创建名称的三种方法搞定基数，剩下我们就需要调取相应的个人所得税税率，如果咱们使用if函数做判断，至少6层嵌套，这样就太繁琐，我们可以采用一个讨巧的办法，就是用lookup函数来完成提取对应税率和速算扣数，下面先介绍一下lookup的用法：
lookup使用的结构图它的功能有些像向下取值的意思，就是VLAUE不够第二个就会匹配出最小的值，比如=lookup(3,{1,5,7},{2,4,6})则会返回2，如果=lookup(6,{1,5,7},{2,4,6})则返回4，这个公式的含义：大于等于1小于5则返回2，=5,7则返回4，=7返回6，是不是很符合我们的要求啊，只要符合这种关系的，我们就可以用lookup来代替if语句，不光语句少，还极容易维护，好了开始这部分的公式编写：
为了方便编写公式，我们提前定义4个名称：
“下限”为个税表中的金额级别；
“税率”为个税表中“金额”对应的税率；
“扣数”为个税表中对应速算扣除数的范围；
“基数”为；
数据大概情况：
五险一金的个人部分的总和为：sum(d2:g2),
应发工资的引用单元格为：c2,
对应个税税率=lookup(c2-sum(d2:g2)-基数,下限,税率)；
速算扣除数=lookup(c2-sum(d2:g2)-基数,下限,扣数)；
注：如果lookup无法查找出的内容则为，c2-sum(d2:g2)-基数结果小于0时，上面的两个公式会返回错误码#N/A;如果你用if来处理也没有问题，不过ifna来处理就更好了，当条件的结果为#N/A,就返回我设定内容0了；
调整后的公式为：
个人税率=ifna(lookup(c2-sum(d2:g2)-基数,下限,税率),0)；
速算扣除数=ifna(lookup(c2-sum(d2:g2)-基数,下限,扣数),0),
再按照个税的计算公式，把我们准备的公式合在一起就是个税的计算公式了：
=(c2-sum(d2:g2)-基数)*ifna(lookup(c2-sum(d2:g2)-基数,下限,扣数),0)-ifna(lookup(c2-sum(d2:g2)-基数,下限,扣数),0)，然后拖拽公式完成填充；
最后发现每个人的工资涉及到医疗大额的问题，由于没有相关的数据，就自定义名称为“大额”，值为.60元，扣除为每年1月，其他月份为0，这个公式相对也比较简单，我们可以month函数提取工资的月份，判断是不是1，是为1，否为0，再与大额相乘，公式=if(month($p$1)=1,1,0)*大额，严谨性完善=iferror(if(month($p$1)=1,1,0)*大额,0)，拖拽公式完成填充，最后工资公式为=c2-sum(d2:i2),
严谨性判断优化：=iferror(c2-sum(d2:i2),异常)。
说到这，这个个人所得税的表就制作也暂时搞一段落了，因为我们公司的员工收入都比较低，至于一些五险一金达到限额的部分没有做，这也是我给在工作的你的一个小建议，我们在规定的时间完成满足日常需求的功能就好，至于那些暂时用不上的功能，就可以放在以后再做。如果想要已完成的文件，可以在下方留下邮箱或其他的联系方式，我看到后，第一时间将文件发送给你。人海茫茫，网络相遇即是缘，期待与你下文在会，期待你对文中的纰漏留言指正！学习Excel路上，我们结伴同行!