实验报告一(人事管理中的应用)

实验报告一Excel在人事管理中的应用

一、实验事项

1.实验项目名称

Excel在人事管理中的应用

2.实验目标

通过对人事管理过程中最常用的几个表格，如“员工信息登记表”、“员工培训成绩计算分析表”、“员工年龄分析表”、“员工工资核算表”等的创建掌握Excel 的基础操作方法和技巧，以及员工信息的管理方法。

3.实验任务

（1）创建员工信息登记表并在报表页脚添加公司LOGO。

（2）创建员工培训成绩计算分析表，编制培训成绩汇总公式，编制公式进行成绩的结果分析，编制成绩排名公式。

（3）对人事信息表分别使用方法一COUNTIF和SUM函数和方法二FREQUENCY实现不同年龄段员工人数的统计。

（4）根据员工资料、基本工资、考核记录、相关福利补助等资料制作工资明细表，并根据工资明细表创建发放工资零钱统计表并制作打印工资条。

二、实验数据

1.数据背景

在公司管理中，人事管理是一项非常重要的内容，包括员工的基本信息登记、员工的月工资发放情况等日常琐碎工作。员工信息表是公司对员工基本信息掌握的一个途径，员工信息表上必备项目包括：姓名、身份证件号码、联系方式和居住地址等。每次培训项目结束后，培训主管部门都会组织培训员工进行考试，及时评定、汇总学员成绩，并将成绩和培训成果报表一同作为员工个人完整的培训资料保存。在人事信息统计的过程中，经常需要按照不同的年龄段对职工进行分组，从而获得不同年龄段职工的人数情况。

2.实验数据

详见附表。

三、实验中所用到的公式

1.中国式排名公式

SUM(IF(H$4:H$18>H4,1/COUNTIF(H$4:H$18,H$4:H$18)))+1

数组公式，按组合键

2.员工工资核算表

(1)年功工资=50*[工资核算时间（年）-进厂时间（年）]

datedif(进厂时间，工资核算时间，”Y”)

(2)实出勤天数=应出勤天数-缺勤天数

(3)基础工资=基础工资（员工基础资料表）/应出勤天数*实出勤天数

绩效工资=绩效工资（员工基础资料表）*绩效考核系数（相关信息表)

应发合计=基础工资+绩效工资+年功工资+通讯补助

日工资=应发合计/应出勤天数

正常加班工资=日常加班天数*日工资*2

节日加班工资=节日加班天数*日工资*3

工资合计=应发合计+正常加班工资+节日加班工资

应税所得额=工资合计-起征点

个人所得税=应税所得额*税率-速算扣除数

实发合计=工资合计-个人所得税-住宿费-代扣养老保险

3.制作打印工资条

=IF(MOD(ROW(),3)=0," ",IF(MOD(ROW(),3)=1,工资明细表!A$3,INDEX(工资明细表!$A:$Q,INT((ROW()-1)/3)+4,COLUMN())))

四、实验步骤

1.创建员工信息登记表，并在报表中插入公司logo。

（1）打开“员工基本信息登记表.xls”工作簿，将“员工信息登记表”中的数据按照sheet2中的样式编辑。

（2）给“员工信息登记表”的报表页脚插入公司Logo。

2.编制员工培训成绩计算分析表

（1）打开培训成绩统计分析表，对“总分”一列编制成绩汇总公式

（2）对“成绩是否达标”一列编制成绩是否达标公式，判断总分是否达标，并通过条件格式设置“未达标”以红色字体显示。

（3）编制公式分别以西方式排名和中国式排名两种方式对员工总分进行排名。

3.编制员工年龄分析表

（1）打开“员工年龄分析表.xls”工作簿，在“人事数据表”中I4:I7单元格区域使用COUNTIF函数SUM函数编制公式统计不同年龄段员工人数，并对H2:I8单元格区域设置背景填充颜色“淡蓝色”。

（2）在“人事数据表”中L4:L7单元格区域使用FREQUENCY函数编制数组公式统计不同年龄段员工人数, 并对K2:L8单元格区域设置背景填充颜色“淡蓝色”。

4.编制员工工资核算表

（1）打开“工资表.xls”工作簿，在“基础资料表”中取消网络线显示。（2）在“员工基础资料表”中H2单元格输入使用DATEDIF函数计算年功工资的公式，年功工资计算方法是从进厂时间开始每年50元。

（3）在“相关信息表”中“姓名”和“部门”一列，使用VLOOKUP函数根据员工代码从“员工基础资料表”中取出相关姓名部门信息。

（4）在“相关信息表”中编制公式计算“实出勤天数”一列。

（5）打开“工资明细表”，设置该表标签颜色为浅绿色。编制“基础工资”、“绩效工资”、“年功工资”、“通讯补助”、“应发合计”、“日工资”、“正常加班工资”、“节日加班工资”、“工资合计”、“个人所得税”、“住宿费”、“代扣养老保险”、“实发合计”、“应税所得额”、“税率”、“速算扣除数”等列的计算公式并复制公式填充各列。

（6）新建一个“工资条表”，并设置标签颜色为“淡紫色”，在“工资条表”中编制公式将“工资明细表”中的各列数据复制过来，同时每一个员工的工资记录都有标题行，员工工资记录之间空一行，通过设置条件格式给非空行单元格加边框。取消零值显示，取消网格线显示，设置表格字体大小为10号字并居中显示。

五、实验总结

六、实验评价分析

1. 创建员工信息登记表，并在报表中插入公司logo

2.编制员工培训成绩计算分析表

3. 编制员工年龄分析表

4. 编制员工工资核算表

始数据文件。

2 在教师规定的时间内提交文件。

《实验一Excel在人事管理中的应用》分数统计