二维码 购物车
部落窝在线教育欢迎您!

自动核算工龄、考勤和社保的Excel工资表制作

 

作者:ITFANS来源:部落窝教育发布时间:2023-01-16 15:35:56点击:2155

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

你知道一张工资表里到底囊获了多少Excel知识吗?这里面包括计算工龄工资、考勤、社保等,麻烦而复杂,简直是HR们的噩梦!没关系,今天就来和我们一起学习自动计算工龄、考勤的工资表的制作吧!文中提供了最新个税计算公式。

 

哈喽,大家好呀!每个打工人都喜欢发工资的那一天,但是如何快速做出准确无误的工资表,却是很多HR们的噩梦。今天就教HR们制作自动核算工龄、考勤等的工资表,快速为每个伙伴算对工资。

功能包括:

◎自动计算并提取工龄工资、提成、奖金

◎自动判定全勤奖

◎自动核算加班、请假、旷工、迟到早退金额

◎自动统计应发工资

◎自动核算五险一金和实发工资

 

工资表的核算建立在考勤汇总表、员工信息表(本文中的工龄工资表就是一种员工信息表)、业绩提成表、奖金发放表的基础上。文中将用到《由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(下篇)》中的考勤汇总表。

虽然每个公司的工资表组成不一样,但你在本文学到的表格配置、公式搭建可以借用到任何工资表里。

一、 完整的工资表组成

工资的组成有很多。要实现自动核算,就必须提供必要的组件供工资表调用。通常一份工资表核算包括:

◎工资表(最终呈现工资数据的主表)

 

 

◎员工信息表。提供员工的基本信息,如工龄、基本工资、社保档次等等。有了这些数据,工资表才能自动根据员工姓名核算基本工资、工龄工资、五险一金等等。

 

 

◎五险一金基数与比例表。如果不是按照应发工资作为缴费基数,必须要单独提供缴费基数。

 

 

◎月度考勤汇总表。提供员工的上班天数、请假天数、迟到早退数据、加班数据、旷工数据等。有了这些数据,工资表才能自动计算员工当月的考勤工资部分。

 

 

◎业绩提成表。提供员工的业绩数据、提成比例数据。

 

 

◎奖金表。提供员工每月的奖金等数据。

二、 输入当月员工数据

打开准备好的各种表格。如果各位没有准备,可以用本文提供的配套文件:工资表模板、12月(考勤)汇总表。

复制12月考勤汇总表中的姓名、编号粘贴到工资表中备用。

 

 

 

三、引用工龄工资和基本工资

根据公司的规定,员工的工龄补贴计算方法如下,以每年的11日为计算基准(如果读者所在的公司规定不同,以此为例修改即可):

 

1.入职年限少于2年,工龄补助为100

2.入职年限在2~5年(含5年),工龄补助=入职年限*100

3.入职年限在5年以上的,工龄补助封顶为500元。

 

切换到“工龄工资”(员工信息表)工作表,在E2单元格输入公式“=IFERROR(DATEDIF(D2,$G$2,"Y"),0)”(注意:$G$2使用绝对引用),下拉填充完成入职年限的计算。

 



 

继续定位到F单元格输入公式“=IFS(E2<=1,100,AND(1E2,E2=5),E2*100,E2>5,500)”,下拉完成工龄工资的计算。

 

 

公式解释:

使用IFS函数,按照入职年限的分类,依次为不同年限的工龄赋予不同的金额,相比IF函数多层嵌套要简洁一些。

 

接着返回“工资表”工作表,C5单元格输入公式=工龄工资!C2D5单元格输入公式=工龄工资!F2 ,选中C5:D5下拉填充完成基本工资和工龄工资引用。

 

 

提示:

因为工资表和工龄工资表都是根据考勤汇总表输入(或粘贴)的,姓名和编号相同并且排列顺序是一致的,所以直接用等号引用数据,结构非常简洁。

如果“工龄工资表”并非根据考勤表汇总创建,而是人事依据入职、离职员工统计的,其人数、姓名排序不一定与考勤汇总表一样,那么基本工资公式改为=IFERROR(VLOOKUP(A5,工龄工资!A2:F201,3,0),""),工龄工资公式改为=IFERROR(VLOOKUP(A5,工龄工资!A2:F201,6,0),"")

四、引用提成金额

切换到“提成表”工作表,首先根据F列的业绩提取规则,在G1:H3创建查找辅助列。

 

 

然后在D2单元格输入公式“=C2*VLOOKUP(C2,$G$2:$H$4,2)”,下拉完成提成数据的计算。

 

 

公式解释:

使用VLOOKUP函数进行分区段模糊查找得到状态值,然后和指定比例相乘得到提成金额。VLOOKUP分区段模糊查找可以《多条件判断,劝你用VLOOKUP函数模糊查找取代IF函数的一长串公式!》

 

返回“工资表”工作表,定位到E6单元格输入公式“=IFERROR(VLOOKUP(A5,提成表!$A$2:$D$201,4,0),"")”,完成提成数据的引用。

 

 

五、全勤奖的判断

公司规定,每月的全勤奖是100元,但是员工只要有一次迟到、早退或者请假、旷工,就没有。定位到F5单元格输入公式“=IF(AND(A5<>"",H5=0,I5=0,J5=0),100,0)”,下拉填充完成设置。

 

 

公式解释:

使用IF函数嵌套AND函数,只有满足A5不为空(即有员工姓名),并且H5I5J5为空(即没有迟到/早退/旷工/请假数据),那么就显示为100。否则显示为0

 

六、加班、迟到/早退数据引用

加班和迟到早退金额可以直接引用考勤汇总表中的统计。

相比《由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(下篇)》中的考勤汇总表模板,12月汇总表把迟到早退、加班进行了分别统计。想了解分别统计过程的见编辑提供的附件操作。

 

G5单元格输入公式“=[12月汇总表.xlsm]汇总表!F5”、在H5单元格输入公式“=[12月汇总表.xlsm]汇总表!E5”,依次引用考勤汇总表的当月加班和当月扣款的数据即可。

 

 

 

七、请假扣款计算

公司规定职工月计薪天数21.75天,职工请假扣费=“请假天数*基本工资/21.75”。请假天数可以引用考勤汇总表中的统计。定位到I5单元格输入公式“=-ROUND([12月汇总表.xlsm]汇总表!C5*C5/21.75,2)”,下拉填充完成统计。

 

 

八、旷工扣款计算

每旷工1天扣费=“2*基本工资/21.75”。定位到I5单元格输入公式“=- ROUND([12月汇总表.xlsm]汇总表!D5*2*C5/21.75,2)”,下拉公式完成统计。

 

 

九、应发小计

K5中输入公式=SUM(C5:J5)然后下拉填充即可。

 

 

十、五险一金的代扣计算

五险一金的计算涉及两个数:缴费基数与缴费比例。

各企业员工五险的缴费比例统一;公积金的缴费比例在符合各地规定的范围内,每个企业可能不一样。

缴费基数更复杂,每年一变,具体要看所在地与公司的规定。

 

本处按以下规则计算五险一金。

1)缴费基数:五险与公积金都以应发工资作为缴费基数;同时满足笔者所在地区的规定,3488缴费基数≤17442元。

2)缴费比例:五险与公积金个人缴费比例合计22.02%

 

L5单元格中输入以下公式并下拉填充:

=ROUND(IFS(K5<=3488,3488*0.2202,AND(K5>3488,K5<=17442),K5*0.2202,K5>17442,17442*0.2202),2)

 

 

公式解释:

缴存比例是一个小数,为了避免计算后出现小数误差,这里使用ROUND函数进行四舍五入的计算。

 

提示:

1.如果你所在公司每个人的缴费基数一致,但是养老保险、医疗保险、公积金的缴费基数不同,则可以新增一个“五险一金”工作表,列出缴费比例、缴费基数,供计算引用。五险一金公式=ROUND((五险一金!$C$2*0.082+五险一金!$C$3*0.02+五险一金!$C$5*0.12),2)

 

 

2.如果你所在公司缴费基数分成了多个档次,则需要根据姓名查到档次,再由档次查到不同缴费基数。首先在“工龄工资”中增加一列档次,然后在五险一金中分档次列出缴费基数。五险一金公式=ROUND(VLOOKUP(VLOOKUP(A5,工龄工资!$A$2:$G$25,7,0),五险一金!$E$2:$H$4,2,0)*0.082+VLOOKUP(VLOOKUP(A5,工龄工资!$A$2:$G$25,7,0),五险一金!$E$2:$H$4,3,0)*0.02+VLOOKUP(VLOOKUP(A5,工龄工资!$A$2:$G$25,7,0),五险一金!$E$2:$H$4,4,0)*0.12,2)

 

 

 

十一、个税统计

根据现在个税政策,员工应税工资(应发工资-五险一金)超过5000部分需要按照不同税率进行缴存。定位到M5单元格输入公式“=ROUND(MAX((K5-L5-5000)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2)”,下拉可以完成数据的计算。

 

 

公式解释:

使用MAX函数根据税务总局的规定,分段计算不同工资的扣税记录。最后在外层嵌套ROUND函数进行四舍五入。公式含义大家不必深究。

十二、实发工资的计算

1.个人实发工资

定位到N5单元格输入公式“=SUM(L5:M5)”,下拉完成应扣金额的统计,定位到O5单元格输入公式“=K5-N5”,下拉即可完成实发工资的计算。

 

 

2.公司整体实发工资

最后在B29输入“=SUM(O5:O28)”完成实发工资总额的统计。

B30单元格输入公式“=TEXT(INT(C29),"[dbnum2]")&""&IF(INT(C29*10)-INT(C29)*10=0,"",TEXT(INT(C29*10)-INT(C29)*10,"[dbnum2]")&"")&IF(INT(C29*100)-INT(C29*10)*10=0,"",TEXT(INT(C29*100)-INT(C29*10)*10,"[dbnum2]")&"")”,转换为大写金额。

 

 

公式解释:

大写转换公式可以参《Excel最常用的4种格式转换,添加前缀、单位,大小写转换等!,大家只要替换“C29”单元格即可使用。

 

十三、工资模板实际运用

完成上述的设计后保存模板。

小伙伴以后需要制作其他月份工资表,只需以下5步,Excel就可以自动完成工资核算:

1)更改一下“工资表”的标题。

2)利用考勤汇总模板生成月份考勤汇总表。

3)复制月份考勤汇总表的员工名单到“工资表”。可以设置公式等进行自动复制,想知道怎么设置的可以留言。

4)在“工龄工资表”中添加新员工信息。

5)复制提成数据到“提成表”。

 

当然在实际使用中,工资表制作还会涉及到很多方面,本期限于篇幅的限制就介绍到这里了。当前工资表适合在职员工的工资核算;如果是当月离职、新入职员工的核算,则需要小修改——增加实际出勤天数。欢迎关注下篇《离职和新入职员工工资核算》。

 

附录:考勤汇总模板的修改

 

Step 01 打开汇总表模板文件,进入“整理模板表”工作表。

 

 

Step 02 K列前添插入一列。J列为当日迟到/早退扣款K列为当日加班工资

 

 

Step 03 修改J列的扣款公式。在J2中输入如下按公式并下拉填充。

=SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,"<0")

 

 

Step 04 K2中输入如下按公式并下拉填充得到当日加班工资。

=SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,">0")


 

Step 05 Alt+F11进入VBA编辑器,修改模块1中的遍历工作表代码。很简单,将复制范围C1:M200修改成C1:N200,因为我们增加了一列数据。修改后单击VBA编辑器上的保存按钮

 

 

Step 06 返回到“汇总表”工作表。首先清除G列后所有数据。

 

 

Step 07 在“当月扣款/加班列前插入两列,分别在当日扣款/加班列和“9:00:00”列前插入一列,取消日期和星期的跨列居中。重新填写各列的名称如下。

 

 

Step 08 复制G2:G3,然后单击H2进行粘贴。

 

 

Step 09 定位到L2单元格输入公式“=G2+1”,向右拖至FE2单元格,完成日期的填充。

 

 

Step 10 单击E4,输入公式=SUMIF($G$4:$FE$4,G$4,$G5:$FE5),右拉填充到F4。选中E4:F4,再向下填充获得当月迟到/早退扣款、当月加班工资。设置单元格格式为货币格式。

 

 

Step 11 单击H5,输入公式=IFERROR(INDIRECT(G$2&"!k"&ROW(A2)),"")并下拉填充获得当日加班工资。

 

 

Step 12 分别修改I5J5K5中的公式如下:

=IFERROR(INDIRECT(I$2&"!L"&ROW(A2)),"")

=IFERROR(INDIRECT(J$2&"!M"&ROW(B2)),"")

=IFERROR(INDIRECT(K$2&"!N"&ROW(A2)),"")

然后下拉填充。

 

 

Step 13选中G3:K28向右填充至FE28完成数据的引用。

 

 

单击保存,完成考勤汇总表模板的修改。

修改模板后,按照《由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(下篇)》中的方法运行模板即可快速得到某月,如202212月,的考勤汇总表文件——12月汇总表文件。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel做库存电子台账案例:仓库出入库台账汇总

《由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(下篇)》

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者ITFANS;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。