Excel公式函数/个人所得税计算/跟我一步步做新税法下工资表模版

​​​​​本文于2023年3月10日首发于本人同名公众号：Excel活学活用，敬请关注！

以前发过一篇介绍新税法下工资表的文章，最新个人所得税计税模板，最近又做了重大改进，只用一张表解决所有问题，下个月复制、改名即可：

工资表的格式已完全展示给大家了，有需要的小伙伴可以参照着自己动手，公式、函数也基本可以看到，下面我再把所有涉及到的公式函数都分享给大家：

一、定义名称

（一）表名

=REPLACE(GET.DOCUMENT(1),1,FIND(“]”,GET.DOCUMENT(1)),)&T(NOW())

GET.DOCUMENT(1)是一种宏表函数，用于检索当前工作簿的完整路径和名称，结果如“[20XX年工资表(公式版修改中）v4.xlsm]202301”，然后再利用find，replace函数取出工作表名称“202301″，加上个“T(NOW())”即时更新值。

（二）上月

=TEXT((–表名)-1,“000000”)

把本月减去1就是上月，这里把“表页”名与月份相结合，实现取得上个月有关数据的目的。

（三）上月表头

=OFFSET(INDIRECT(上月&“!$b$4“),0,0,1,COUNTA(INDIRECT(上月&“!4:4”))-1)

OFFSET：是Excel中一个非常有用的函数，它可以基于给定的引用或单元格范围，返回一个新的范围或单元格。OFFSET函数的语法如下：

OFFSET(reference, rows, cols, [height], [width])

其中：

reference：要从中开始偏移的引用或单元格范围。rows：要偏移的行数。如果这是正数，则结果向下移动；如果这是负数，则结果向上移动。cols：要偏移的列数。如果这是正数，则结果向右移动；如果这是负数，则结果向左移动。height：要返回的范围的高度。如果省略，则返回从偏移引用开始的所有行。width：要返回的范围的宽度。如果省略，则返回从偏移引用开始的所有列。OFFSET函数非常有用，因为它可以动态地引用范围或单元格，而不必手动更改引用。它还可以用于创建动态的名称范围，以及在处理数据表格和报表时进行数据汇总和统计。

这里是取得上月工资表表头区域，如果每张表的格式严格一致，”上月表头”也可不定义，直接用本月工资表的第4行表头字段，主要是用来定位“累计应纳税所得额”等字段用的，也可以数列数，给个定值，但是有增、减列的情况公式就需要改动了。

INDIRECT函数返回一个引用

COUNTA计算非空单元格数量

（四）上月工资表

=OFFSET(INDIRECT(上月&“!$b$4“),0,0,COUNTA(INDIRECT(上月&“!$A:$A“))-3,COUNTA(INDIRECT(上月&“!4:4”))-1)

取得上个月工资表的数据区域，从第2列开始，主要是用来取上个月的累计数据之用。

二、编制公式

应发合计

=SUM(D5:G5)

专项扣除合计

=SUM(I5:L5)

税前工资

=H5-M5

本月应纳税所得额

=N5+O5-P5-Q5

累计应纳税所得额

=IFERROR(VLOOKUP($B5,上月工资表,MATCH(S$4,上月表头,0),0),0)+R5

VLOOKUP和MATCH结合，从上月工资表中取得上个月的累计应纳税所得额加上本月的应纳税所得额，即为截止本月的累计应纳税所得额。

累计应纳税额（这里有多种方法，提供2种，自行选择，自行验证）

=ROUND(MAX(S5*{3;10;20;25;30;35;45}%-{0;252;1692;3192;5292;8592;18192}*10,),2) =LOOKUP(S5,{0;36000;144000;300000;420000;660000;960000},S5*{3;10;20;25;30;35;45}%-{0;2520;16920;31920;52920;85920;181920})

累计已交税额

=IFERROR(VLOOKUP($B5,上月工资表,MATCH(U$4,上月表头,0),0),0)+IFERROR(VLOOKUP($B5,上月工资表,MATCH(V$4,上月表头,0),0),0)

上月“累计已交税额”+上月“本月应交税额”，这里有一个假设前提：每个月的应交税金均正常缴纳。

本月应交税额

=MAX(0,T5-U5)

如果本月应交税额为负数，则本月应交税金为0.

实发工资

=N5-V5-W5-X5-Y5

表中的说明内容：

1、安全级别调低，要启用宏，标色的地方有公式，不要轻易改动，所有表格的格式不要改动，除非你知道你在做什么。

2、表页名称：2023XX，从202301到202312，每月一张，不可删除以前月份的工资表。下年格式相仿。工资表表头所属期间、发放月份不用手工修改，根据当前表名自动变化，当然也可以手工修改，只要不错就行。

3、每月把上月的表复制一份改名为2023XX，把当月工资、社保数据修改填写完毕。复制用Ctrl+鼠标左键拖动标签，这样可以保持格式不变。

4、有新增人员的，在当月工资表中插入空白行，选中空白行，按“Ctrl”+D，复制上行内容，修改工资、社保、专项附加扣除、减除费用等数据。主要目的是完整复制公式、格式。

5、年中启用该表格的，则需增加一张表名为上个月的工资表，将累计数填入。可将截止上月累计数(累计应发工资、专项扣除）填在上月表格中，再将减除费用5000乘上（上月月份数-1）所得金额填到“本月其他扣除”列中，核对“累计应纳税额”与税务个税申报系统应该一致。如果所有员工不存在多交税的情况那么，累计数就算完成。

如果有员工存在多交税的情况，则需要修改“累计已交税额”为实际已交税额（注意：如果修改的，该表的公式已被破坏，不能再作为次月模板使用），可填上月数，也可填上上月数），则在正式使用月份可以取到“累计已交税额”为截止上月累计已交税额。

或者是从1月开始逐月填写历史数据，如果没有异常情况，税金应该与实际申报的一致。”

6、202301，表示1月份实际发放的工资，正常情况下应该是上月的工资。

7、工资表中有表列用不到的，可以隐藏掉，不要删除。

8、人数超过本表设定行数的，可自行插入空行，然后选中空行前一行及所有空行，按Crt+D复制公式，修改人员数据即可。

人数不足本表设定行数的，可以整行删除。”

9、工资表“本月其他收入”列，指的是本月已发过工资，后续另外发放年终奖、季度奖等，且并入工资一起计税的情况，则应将另外发放的金额填到相应月份参与计税，把多出来的税金在另外造表发放的奖金发放表中扣除。也可作为其他非现金发放项目、公司为员工购买商业保险等需要扣税的项目填写。该列不参与实发工资的计算。

10、2021年新政策，有员工上年收入不足6万的，满足一定条件可先行一次性扣除6万，省得先交税后退税的麻烦。实际影响不大，主要差别是发年终奖的当月，年终奖并入综合收入计税的，可能会交税，采用一次性扣除6万的，可能不要交税。适用前提是在个人所税税客户端已确认一次性扣除6万的，使用方法是在1月当月减除费用填写60000,后续月份填0.

11、员工有重名的，应加数字、字母或者其他方式加以区分（只要EXCEL认为不是同一个人即可）。

12、本表定义了一些名称，不能删除

13、假设前提：“累计已交税额”,默认以前月份税金都按月正常缴纳; 任何员工当月工资减社保不小于0。

14、本月应纳税所得额,累计应纳税所得额有可能出现负数,不能处理为0,因为要参与以后月份累计数的计算。已设置了条件格式，如果相应单元格的值小于0,字体颜色设置与背景一致，这样就看不到负数了。

15、为了防止误操作，对部分包含公式的单元格、表头单元格进行了锁定并保护工作表。需要增删行的，请取消保护后再操作。日常建议保护工作表。

16、有问题可以发邮件：leeson7502@163.com

17、复制工资表页时，要用Ctrl+鼠标左键拖动表页的方式，然后改名。这样所有的格式，公式都复制过来了。

其他没有什么好说的了，就分享到这吧，跟上篇（Excel VBA 个人所得税筹划方案/年终奖筹划方案/个人所得税计算自定义函数）一样，本文所涉及的excel文件模板不能免费分享，请见谅。如果有需要，大家按照本文的内容，应该百分之百能自己做出来，万一还是不想自己动手来做，也可以付费获取，链接公众号支付后可见。

​​​​​​本文于2023年3月10日首发于本人同名公众号：Excel活学活用，敬请关注！