Excel公式函数/个人所得税计算/跟我一步步做新税法下工资表模版
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"></span><span style="color: black;">本文于2023年3月10日首发于<span style="color: black;">自己</span>同名公众号：Excel活学活用，敬请关注！</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">以前发过一篇介绍新税法下工资表的<span style="color: black;">文案</span>，<span style="color: black;">最新个人所得税计税模板</span>，<span style="color: black;">近期</span>又做了重大改进，只用一张表<span style="color: black;">处理</span>所有问题，下个月复制、改名<span style="color: black;">就可</span>：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1d0d5a9e2fff4cc89b241bbc4247f3e2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976097&x-signature=D9%2BiMlb9pQSFEw54Pa1fQfYumIw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">工资表的格式已完全展示给<span style="color: black;">大众</span>了，有需要的小伙伴<span style="color: black;">能够</span>参照着自己动手，公式、函数<span style="color: black;">亦</span>基本可以看到，下面我再把所有<span style="color: black;">触及</span>到的公式函数都分享给<span style="color: black;">大众</span>：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1、</span>定义名<span style="color: black;">叫作</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/dbe96371bcab4986a5637860f6e8ced3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976097&x-signature=y%2Bmb0GX7gP7tjigONqFfNDQqEl0%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">（一）<strong style="color: blue;">表名</strong></p>=<span style="color: black;">REPLACE</span>(GET.DOCUMENT(<span style="color: black;">1</span>),<span style="color: black;">1</span>,FIND(<span style="color: black;">"]"</span>,GET.DOCUMENT(<span style="color: black;">1</span>)),)&T(<span style="color: black;">NOW</span>())<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">GET.DOCUMENT(1)</span>是一种宏表函数，用于检索当前工作簿的完整路径和名<span style="color: black;">叫作</span>，结果如“202301”，<span style="color: black;">而后</span>再利用find，replace函数取出工作表名<span style="color: black;">叫作</span>“202301"，加上个“<span style="color: black;">T(NOW())”</span>即时更新值。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">（二）<strong style="color: blue;">上月</strong></p>=TEXT((--表名)-1,<span style="color: black;">"000000"</span>)<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">把本月减去1<span style="color: black;">便是</span>上月，<span style="color: black;">这儿</span>把“表页”名与月份相结合，实现取得上个月<span style="color: black;">相关</span>数据的目的。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">（三）<strong style="color: blue;">上月表头</strong></p>=OFFSET(INDIRECT(上月&<span style="color: black;">"!<span style="color: black;">$b</span><span style="color: black;">$4</span>"</span>),0,0,1,COUNTA(INDIRECT(上月&<span style="color: black;">"!4:4"</span>))-1)<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">OFFSET：<span style="color: black;"><span style="color: black;">是Excel中一个非常有用的函数，它<span style="color: black;">能够</span>基于给定的引用或单元格范围，返回一个新的范围或单元格。OFFSET函数的语法如下：</span></span></span></p><span style="color: black;">OFFSET</span>(<span style="color: black;">reference</span>, <span style="color: black;">rows</span>, <span style="color: black;">cols</span>, <span style="color: black;"></span>, <span style="color: black;"></span>)<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">其中：</p>reference：要从中<span style="color: black;">起始</span>偏移的引用或单元格范围。rows：要偏移的行数。<span style="color: black;">倘若</span>这是正数，则结果向下移动；<span style="color: black;">倘若</span>这是负数，则结果向上移动。cols：要偏移的列数。<span style="color: black;">倘若</span>这是正数，则结果向右移动；<span style="color: black;">倘若</span>这是负数，则结果向左移动。height：要返回的范围的高度。<span style="color: black;">倘若</span>省略，则返回从偏移引用<span style="color: black;">起始</span>的所有行。width：要返回的范围的宽度。<span style="color: black;">倘若</span>省略，则返回从偏移引用<span style="color: black;">起始</span>的所有列。<span style="color: black;"><span style="color: black;">OFFSET函数非常有用，<span style="color: black;">由于</span>它<span style="color: black;">能够</span>动态地引用范围或单元格，而不必手动更改引用。它还<span style="color: black;">能够</span>用于创建动态的名<span style="color: black;">叫作</span>范围，以及在处理数据表格和报表时进行数据汇总和统计。</span></span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>是取得上月工资表表头区域，<span style="color: black;">倘若</span>每张表的格式严格一致，"上月表头"<span style="color: black;">亦</span>可不定义，直接用本月工资表的第4行表头字段，<span style="color: black;">重点</span>是用来定位“累计应纳税所得额"等字段用的，<span style="color: black;">亦</span><span style="color: black;">能够</span>数列数，给个定值，<span style="color: black;">然则</span>有增、减列的<span style="color: black;">状况</span>公式就需要改动了。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">INDIRECT函数</span>返回一个引用</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">COUNTA</span>计算非空单元格数量</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">（四）<strong style="color: blue;">上月工资表</strong></p>=OFFSET(INDIRECT(上月&<span style="color: black;">"!<span style="color: black;">$b</span><span style="color: black;">$4</span>"</span>),0,0,COUNTA(INDIRECT(上月&<span style="color: black;">"!<span style="color: black;">$A</span>:<span style="color: black;">$A</span>"</span>))-3,COUNTA(INDIRECT(上月&<span style="color: black;">"!4:4"</span>))-1)<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">取得上个月工资表的数据区域，从第2列<span style="color: black;">起始</span>，<span style="color: black;">重点</span>是用来取上个月的累计数据之用。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2、</span>编制公式</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">应发合计</span></strong></p><span style="color: black;">=SUM(D5:G5)</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">专项扣除合计</span></strong></p><span style="color: black;">=SUM(I5:L5)</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">税前工资</span></strong></p>=H5-M5<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">本月应纳税所得额</strong></p>=N5+O5-P5-Q5<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">累计应纳税所得额</span></strong></p>=IFERROR(VLOOKUP(<span style="color: black;">$B5</span>,上月工资表,MATCH(S<span style="color: black;">$4</span>,上月表头,0),0),0)+R5<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">VLOOKUP</span></span>和<span style="color: black;"><span style="color: black;">MATCH</span></span>结合，从上月工资表中取得上个月的累计应纳税所得额加上本月的应纳税所得额，即为截止本月的累计应纳税所得额。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">累计应纳税额</span></strong><span style="color: black;">（<span style="color: black;">这儿</span>有多种<span style="color: black;">办法</span>，<span style="color: black;">供给</span>2种，<span style="color: black;">自动</span><span style="color: black;">选取</span>，<span style="color: black;">自动</span>验证）</span></p>=ROUND(MAX(S5<span style="color: black;">*{3;10;20;25;30;35;45}%-{0;252;1692;3192;5292;8592;18192}*</span>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})<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">累计已交税额</span></strong></p>=IFERROR(VLOOKUP(<span style="color: black;">$B5</span>,上月工资表,MATCH(U<span style="color: black;">$4</span>,上月表头,0),0),0)+IFERROR(VLOOKUP(<span style="color: black;">$B5</span>,上月工资表,MATCH(V<span style="color: black;">$4</span>,上月表头,0),0),0)<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">上月“累计已交税额”+上月“本月应交税额”，<span style="color: black;">这儿</span>有一个假设前提：<span style="color: black;">每一个</span>月的应交税金均正常缴纳。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">本月应交税额</span></strong></p>=MAX(0,T5-U5)<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">倘若</span>本月应交税额为负数，则本月应交税金为0.</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">实发工资</strong></p>=N5-V5-W5-X5-Y5<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">表中的说明内容：</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、安全级别调低，要启用宏，标色的<span style="color: black;">地区</span>有公式，不要轻易改动，所有表格的格式不要改动，除非你<span style="color: black;">晓得</span>你在做什么。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、表页名<span style="color: black;">叫作</span>：2023XX，从202301到202312，每月一张，不可删除以前月份的工资表。下年格式相仿。工资表表头所属<span style="color: black;">时期</span>、发放月份<span style="color: black;">不消</span>手工修改，<span style="color: black;">按照</span>当前表名自动变化，当然<span style="color: black;">亦</span><span style="color: black;">能够</span>手工修改，只要不错就行。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、每月把上月的表复制一份改名为2023XX，把当月工资、社保数据修改填写完毕。复制用Ctrl+鼠标左键拖动标签，<span style="color: black;">这般</span><span style="color: black;">能够</span>保持格式不变。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4、有新增人员的，在当月工资表中<span style="color: black;">插进</span>空白行，选中空白行，按“Ctrl”+D，复制上行内容，修改工资、社保、专项附加扣除、减除<span style="color: black;">花费</span>等数据。<span style="color: black;">重点</span>目的是完整复制公式、格式。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5、年中启用该表格的，则需<span style="color: black;">增多</span>一张表名为上个月的工资表，将累计数填入。可将截止上月累计数(累计应发工资、专项扣除）填在上月表格中，再将减除<span style="color: black;">花费</span>5000乘上（上月月份数-1）所得金额填到“本月其他扣除”列中，核对“累计应纳税额”与税务个税申报系统应该一致。<span style="color: black;">倘若</span>所有员工不存在多交税的<span style="color: black;">状况</span><span style="color: black;">那样</span>，累计数就算完成。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">倘若</span>有员工存在多交税的<span style="color: black;">状况</span>，则需要修改“累计已交税额”为<span style="color: black;">实质</span>已交税额（<span style="color: black;">重视</span>：<span style="color: black;">倘若</span>修改的，该表的公式已被破坏，<span style="color: black;">不可</span>再<span style="color: black;">做为</span>次月模板<span style="color: black;">运用</span>），可填上月数，<span style="color: black;">亦</span>可填上上月数），则在正式<span style="color: black;">运用</span>月份<span style="color: black;">能够</span>取到“累计已交税额”为截止上月累计已交税额。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">或</span>是从1月<span style="color: black;">起始</span>逐月填写历史数据，<span style="color: black;">倘若</span><span style="color: black;">无</span><span style="color: black;">反常</span><span style="color: black;">状况</span>，税金应该与<span style="color: black;">实质</span>申报的一致。"</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">6、202301，<span style="color: black;">暗示</span>1月份<span style="color: black;">实质</span>发放的工资，正常<span style="color: black;">状况</span>下应该是上月的工资。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">7、工资表中有表列用不到的，<span style="color: black;">能够</span><span style="color: black;">隐匿</span>掉，不要删除。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">8、人数超过本表设定行数的，可<span style="color: black;">自动</span><span style="color: black;">插进</span>空行，<span style="color: black;">而后</span>选中空行前一行及所有空行，按Crt+D复制公式，修改人员数据<span style="color: black;">就可</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">人数不足本表设定行数的，<span style="color: black;">能够</span>整行删除。"</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">9、工资表“本月其他收入”列，指的是本月已发过工资，后续<span style="color: black;">另一</span>发放年终奖、季度奖等，且并入工资<span style="color: black;">一块</span>计税的<span style="color: black;">状况</span>，则应将<span style="color: black;">另一</span>发放的金额填到相应月份参与计税，把多出来的税金在<span style="color: black;">另一</span>造表发放的奖金发放表中扣除。<span style="color: black;">亦</span>可<span style="color: black;">做为</span>其他非现金发放项目、公司为员工购买<span style="color: black;">商场</span>保险等需要扣税的项目填写。该列不参与实发工资的计算。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">十、</span>2021年新政策，有员工上年收入不足6万的，满足<span style="color: black;">必定</span><span style="color: black;">要求</span>可先行一次性扣除6万，省得先交税后退税的麻烦。<span style="color: black;">实质</span>影响不大，<span style="color: black;">重点</span>差别是发年终奖的当月，年终奖并入综合收入计税的，可能会交税，采用一次性扣除6万的，可能不要交税。适用前提是在个人所税税客户端已确认一次性扣除6万的，<span style="color: black;">运用</span><span style="color: black;">办法</span>是在1月当月减除<span style="color: black;">花费</span>填写60000,后续月份填0.</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">11、员工有重名的，应加数字、字母<span style="color: black;">或</span>其他方式加以区分（只要EXCEL认为不是同一个人<span style="color: black;">就可</span>）。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">12、本表定义了<span style="color: black;">有些</span>名<span style="color: black;">叫作</span>，<span style="color: black;">不可</span>删除</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">13、假设前提：“累计已交税额",默认以前月份税金都按月正常缴纳; 任何员工当月工资减社保不<span style="color: black;">少于</span>0。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">14、本月应纳税所得额,累计应纳税所得额有可能<span style="color: black;">显现</span>负数,<span style="color: black;">不可</span>处理为0,<span style="color: black;">由于</span>要参与以后月份累计数的计算。已设置了<span style="color: black;">要求</span>格式，<span style="color: black;">倘若</span>相应单元格的值<span style="color: black;">少于</span>0,字体颜色设置与背景一致，<span style="color: black;">这般</span>就看不到负数了。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">15、为了防止误操作，对部分<span style="color: black;">包括</span>公式的单元格、表头单元格进行了锁定并<span style="color: black;">守护</span>工作表。需要增删行的，请取消<span style="color: black;">守护</span>后再操作。<span style="color: black;">平常</span><span style="color: black;">意见</span><span style="color: black;">守护</span>工作表。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">16、有问题<span style="color: black;">能够</span>发邮件：leeson7502@163.com</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">17、复制工资表页时，要用Ctrl+鼠标左键拖动表页的方式，<span style="color: black;">而后</span>改名。<span style="color: black;">这般</span>所有的格式，公式都复制过来了。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">其他<span style="color: black;">无</span>什么好说的了，就分享到这吧，跟上篇（<span style="color: black;">Excel VBA 个人所得税筹划<span style="color: black;">方法</span>/年终奖筹划<span style="color: black;">方法</span>/个人所得税计算自定义函数</span>）<span style="color: black;">同样</span>，本文所<span style="color: black;">触及</span>的excel文件模板<span style="color: black;">不可</span>免费分享，请见谅。<span style="color: black;">倘若</span>有需要，<span style="color: black;">大众</span><span style="color: black;">根据</span>本文的内容，应该百分之百能自己做出来，万一还是不想自己动手来做，<span style="color: black;">亦</span><span style="color: black;">能够</span>付费获取，链接公众号支付后可见。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"></span><span style="color: black;">本文于2023年3月10日首发于<span style="color: black;">自己</span>同名公众号：Excel活学活用，敬请关注！</span></p>
你的见解独到，让我受益匪浅，期待更多交流。
页:
[1]