Excel案例5员工薪资管理.docx

案例5：员工的薪资管理案例描述：员工薪资表用于记录员工的基本信息和工资情况，一般包括员工姓名、基本工资、奖金、保险与公积金上缴额等项目，通过本案例可以大大提高编制的效率，而且能使编制出的工资表美观、规范。本案便将通过制作交税规则工作表、税后工资表、工资总报表和工资报表，全方位介绍薪资管理要领。本案例要掌握的要领：1、如何办理入员工信息记录2、如何制作交税规则工作表3、如何制作税后工资表4、如何制作工资总报表5、如何阅读与分析工资报表6、如何打印工资条制作难点：制作税后工资表、制作工资总报表、工资报表的阅读与分析、工资条的打印。操作步骤:1. 制作“税前工资表”将标题文字设置为：“强调文字颜色4”、18号（单元格样式），在A列和F列合并居中；将A2:F22设置“表样式中等深浅24”（套用表格格式）。所有数据居中显示，D、E、F列中数据采用货币格式2. 制作“纳税标准表”（1）将素材中的“键昆科技公司的工资标准”表中的数据复制到Sheet2中，从B4单元格开始存放，并将Sheet2命名为“纳税标准”；（2）将素材中的“员工和企业承担的社会保险/住房公积金比例”表中的数据也复制到“纳税标准”表中，从E4单元格开始存放。将B3：G3合并居中后，添加标题“基本工资标准语保险缴纳比例”，设置宋体，18号，宋体；将小标题设置为单元格样式“检查单元格”，数据居中显示，将表中小数设置为“百分比样式”；（3）将素材中“个人工资的纳税率”表中的数据复制到“纳税标准”表中，自I4单元格开始存放，将I3：M3合并居中后设置标题为“个人所得税缴纳比例”，18号，宋体。将小标题设置为单元格样式“检查单元格”，数据居中显示，将表中小数设置为“百分比样式”，将金额数据设置为“货币”类型；（4）取消工作表中的网格线显示。3.制作税后工资表（1）复制“税前工资表”中A2：F22单元格区域数据到Sheet3中，从A3开始存放，继续增加字段：“税前工资”、“社会保险”、“住房公积金”、“个人所得税”、“其他扣款”、“实发工资”、“领讫确认”，将Sheet3表重命名为“税后工作表”。（2）计算“税前工资”。“税前工资”=“基本工资”+“奖金”+“加班费”，可以在G4单元格中输入“=D3+E3+F3”（或用SUM函数），用填充柄向下填充，完成税前工资的计算。（3）计算“社会保险”。“社会保险”=（“纳税标准”表中“养老保险”+“医疗保险”+“失业保险”+“工伤保险”）个人应该承担的比列*“税前工资”，所以在H4中输入“=G4*(Sheet2!F6+Sheet2!F7+Sheet2!F8+Sheet2!F9)”，如果此时用填充柄向下填充就会出现异常，修正的方法是：将H4单元格的公式中纳税标准表中的地址变为绝对地址，正确的公式应该是“=G4*(Sheet2!$F$6+Sheet2!$F$7+Sheet2!$F$8+Sheet2!$F$9)”，此时再用填充柄向下填充就没有问题了！（4）计算“住房公积金”。“住房公积金”=“税前工资”*“纳税标准”表中个人住房公积金承担的比例。（5）计算“个人所得税”。计算个人所得税比较复杂，根据个人税前工资扣除个人所得税起征额得到应纳税额。在由应纳税额的多少根据不同的纳税比例计算个人所得税例如：税前工资为5880元，个人所得税起征额为1600元，那么应纳税额为5880-1600=4280（元），即为：500*0.05+（2000-500）*0.1+（4280-500-1500）*0.15=517（元）如果用if函数会有点复杂。回到税后工资表中，将计算公式.txt中的公式复制到J4单元格中作为计算公式，再用填充柄向下填充，完成个人所得税的计算。（6）计算实发工资项。“实发工资”=“税前工资”“社会保险”“住房公积金”“个人所得税”“其他扣款”，因此在L4单元格中输入“=G4-H4-I4-J4-K4”回车即可计算出正确结果，再用填充柄向下填充。（7）在“领讫确认”栏中输入“复选框”按钮。具体操作：Office按钮Excel选项常用在功能区显示“开发工具”选项卡确定开发工具控件插入复选框控件，在M4单元格中绘制一个“复选框”按钮，删除复选框后面的文字信息，可以用填充柄向下填充。4. 制作工资总报表（1）新建一个工作表，将其命名为“工资总报表”要求：B2中输入标题“工资报表统计与分析”，在B2：K2区域中合并居中显示，B3和C3合并，用斜箭头分隔，上面输入“类别”，下面输入“部门”，再依次输入字段：工资总额、奖金总额、加班费、保险与公积金、人均支出、所占比列、人均实际收入、备注。B4:C4合并居中输入“公司总概况”；B5:B8合并居中输入“各部门情况”；C5:C8依次输入“销售部”、“财务部”、“人事部”、“研发部”，这样表的结构就出来了，选中B2：K8区域，为该区域添加边框。（2）创建“2012年3月份工资报表”，要求：B14中输入标题“工资报表统计与分析”，在B14：K14区域里合并居中显示，在B15：K15中依次输入：所属部门、员工编号、姓名、基本工资、奖金、加班费、保险总额、住房公积金、公司总支出金额、员工实际收入；在B38中输入“合计”。将B15:K38区域设置边框。为了快速插入数据，可以通过复制的方法将“税后工资表”中的数据拷贝到“2012年3月份工资报表”中。继续在“工资总报表”中建立一个“部门判断表”，该表仅为一个辅助表，用来判断员工所在的部门。M16：P16区域分别输入：研发部、财务部、销售部、人事部。将M15：P38区域设置边框。接下来输入公式计算工作报表中的相关项目。E38=SUM(E16:E35)F38=SUM(F16:F35)G38=SUM(G16:G35)H16=SUM(E16:G16)*(纳税标准!$G$6+纳税标准!$G$7+纳税标准!$G$8+纳税标准!$G$9)I16=SUM(E16:G16)*纳税标准!$G$10J16=SUM(E16:I16)K16=税后工资表!L4输入公式后，并分别选中各单元格填充柄向下填充。H38=SUM(H16:H35)I38=SUM(I16:I35)J38=SUM(J16:J35)K38=SUM(K16:K35)这样就完成了“2012年3月份工资报表”的全部数据填充接下来，输入公式计算“部门判断”中的项目。M16=IF(INT(C16/10000)=2,1,0)N16=IF(INT(C16/10000)=3,1,0)O16=IF(INT(C16/10000)=5,1,0)P16=IF(INT(C16/10000)=8,1,0)该公式利用员工编号对员工所属部门进行判断，用员工编号除以10000后取整，得出的结果不同，对应不同的部门，数字1表示员工属于该部门，数字0表示员工不属于该部门。并用填充柄自动向下填充，完成所属部门的分类。M38=SUM(M16:M35)，用填充柄向右填充，完成“部门判断表”的数据填充。接下来，输入公式计算“工资报表统计与分析”表。D4=E38D5=SUMPRODUCT($E$16:$E$35,$O$16:$O$35)D6=SUMPRODUCT($E$16:$E$35,$N$16:$N$35)D7=SUMPRODUCT($E$16:$E$35,$P$16:$P$35)D8=SUMPRODUCT($E$16:$E$35,$M$16:$M$35)E4=F38E5=SUMPRODUCT($F$16:$F$35,$O$16:$O$35)E6=SUMPRODUCT($F$16:$F$35,$N$16:$N$35)E7=SUMPRODUCT($F$16:$F$35,$P$16:$P$35)E8=SUMPRODUCT($F$16:$F$35,$M$16:$M$35)F4=G38F5=SUMPRODUCT($G$16:$G$35,$O$16:$O$35)F6=SUMPRODUCT($G$16:$G$35,$N$16:$N$35)F7=SUMPRODUCT($G$16:$G$35,$P$16:$P$35)F8=SUMPRODUCT($G$16:$G$35,$M$16:$M$35)G4=SUM(H38:I38)G5=SUMPRODUCT($H$16:$H$35,$O$16:$O$35)+SUMPRODUCT($I$16:$I$35,$O$16:$O$35)G6=SUMPRODUCT($H$16:$H$35,$N$16:$N$35)+SUMPRODUCT($I$16:$I$35,$N$16:$N$35)G7=SUMPRODUCT($H$16:$H$35,$P$16:$P$35)+SUMPRODUCT($I$16:$I$35,$P$16:$P$35)G8=SUMPRODUCT($H$16:$H$35,$M$16:$M$35)+SUMPRODUCT($I$16:$I$35,$M$16:$M$35)H4=J38/COUNT(C16:C35)H5=SUM(D5:G5)/$O$38H6=SUM(D6:G6)/$N$38H7=SUM(D7:G7)/$P$38H8=SUM(D8:G8)/$M$38I5=SUM(D5:G5)/$J$38I6=SUM(D6:G6)/$J$38I7=SUM(D7:G7)/$J$38I8=SUM(D8:G8)/$J$38J4=K38/COUNT(C16:C35)J5=SUMPRODUCT($K$16:$K$35,$O$16:$O$35)/$O$38J6=SUMPRODUCT($K$16:$K$35,$N$16:$N$35)/$N$38J7=SUMPRODUCT($K$16:$K$35,$P$16:$P$35)/$P$38J8=SUMPRODUCT($K$16:$K$35,$M$16