Excel案例5员工薪资管理

案例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:$M$35)/$M$38这样就完成了数据的填充，“部门判断表”只是一个辅助表，为了美观可以将这部分内容隐藏。

选中M～P列，将光标放这些列上面，然后单击右键，选中“隐藏”。

5. 工资报表的阅读与分析如果希望按照不同的属性来查看员工的工资水平、企业为员工承担的社会保险及公积金项目，如查询销售部门所有人的工资水平，可以用自动筛选功能实现。

还可以用条件查询。

例如查询所有实际收入大于或等于4000元的员工信息。

注意：与和或的区别。