|
本文于2023年3月10日首发于自己同名公众号：Excel活学活用，敬请关注！
以前发过一篇介绍新税法下工资表的文案，最新个人所得税计税模板，近期又做了重大改进，只用一张表处理所有问题，下个月复制、改名就可：
工资表的格式已完全展示给大众了，有需要的小伙伴能够参照着自己动手，公式、函数亦基本可以看到，下面我再把所有触及到的公式函数都分享给大众：
1、定义名叫作
（一）表名 =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列起始，重点是用来取上个月的累计数据之用。
2、编制公式
应发合计 =SUM(D5:G5)
专项扣除合计 =SUM(I5 5)
税前工资 =H5-M5本月应纳税所得额 =N5+O5-P5-Q5累计应纳税所得额 =IFERROR(VLOOKUP($B5,上月工资表,MATCH(S$4,上月表头,0),0),0)+R5VLOOKUP和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、工资表“本月其他收入”列，指的是本月已发过工资，后续另一发放年终奖、季度奖等，且并入工资一块计税的状况，则应将另一发放的金额填到相应月份参与计税，把多出来的税金在另一造表发放的奖金发放表中扣除。亦可做为其他非现金发放项目、公司为员工购买商场保险等需要扣税的项目填写。该列不参与实发工资的计算。
十、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活学活用，敬请关注！
| 
发表于 2024-10-1 17:00:55