EXCEL教程人力资源篇

1、第2篇人力弈源篇:人民邮电出版社POSTS & TELECOM PRESS2.1项目4制作员工人事松余管理表2.1.1项目背景员工人事档案的整理分类、汇总、分 析、查询等是一件比较复杂的工作。使用Excel可以实现人事管理的自动化， 提高人事档案管理的条理化ora本项目通过制作“员工人事档案管理 表”,来介绍Exce嗽件在人力资源管理中 的应用。2.1.2项目效果人民邮电出版社POSTS & TELECOM PRESSI J 出生日期E 1962-11-419807-151380-7-5斬D |3人力谨源韶0市场部曙训邻入力熒源部410121621104626二编号Bas G）身份证号fiS

2til 620121600724348柏国力 5101-21195703138215 菖盔 2101210X5158021964-8-51960-7-241957-3-131960-5-15离级经丽 无离级婭济师 工程师J_KY0024 KYOO35 KY00415* KY014比专士专士专 学大硕中陨大 间仓E 廿1-l?sr a/J_KY00611 KY010 林菱51O1211St85O62O8452 财务部5211211983042984 市场諾0EF I GH IEj入IRBJ间学fl习职称 二 性也出生日IS E2001-7-20本科 会计师男

3、 1985-6-20200L6-28 大专 无,! I 1983-4-29图2-1员工人事档案筛选效果图:人民邮电出版社POSTS & TELECOM PRESS12 5统计表2部门人数3行政部34人力资源部35市场部76物流部37财务部48物业部29培训部310孙司各部门人员比例12% 12%图22公司各部门人员分配效果图2J.3知识与技能工作簿的创建工作表重命名导入外部数据数据链接数据的筛选 COUNTIF函数的使用创建图表214解决方案任务1创建工作簿，重命名工作表(1) 启动Excel2003,新建一个空白 工作簿。(2) 将创建的工作薄以“员工人事 档案管理表”为名保存。(3) 将“

4、员工人事档案管理表”工 作簿中的Sheetl工作表重命名为“员 工档案信息”。任务2导入“员工信息”将行政篇中导出的“员工信息”数据 导入到当前的文件中，轻松获取外部人事 档案信息。(1) 选中“员工人事档案管理表” 工作簿中的“员工档案信息”工作表。(2) 选择【数据】菜单中【导入外 部数据】命令的子命令【导入数据】， 打开【选取数据源】对话框。（3）选择数据源文件“员工信 息prn”，（4）从对话框的“原始数据类型”中选择“固定长度”作为最合适的文 件类型，在“导入起始行”文本框中 保持默认值“1，在文件原始格式下 拉列表中选择“936：简体中文（GB2312） ” o人民邮电出版社POS

5、TS & TELECOM PRESS（5）按文本导入向导进行操作。在 “数据预览”区内设置字段宽度，也就是列间隔；设置每列的数据类型。（6）单击【确定】按钮，完成数据 导入。任务3创建“员工个人详细资料”链(1) 链接到已有文件。(2) 链接到新建文件o:人民邮电出版社POSTS & TELECOM PRESSIH j I ! r+H卜卜十十十卜十十十卜十十十TTT任务4查阅“员工个人详细资料” 当需要查阅某个员工的个人详细资料时，我们可通过激活超链接进行访问。任务5复制“员工档案信息”工作表（1）选中“员工档案信息”工作表。（2）将该工作表复制三份，并分别 重命名为“女员工档案信息”、“入

6、职时间最早的5名员工信息”、“2005年2007年入职的员工”。人民邮电出版社POSTS & TELECOM PRESS任务6通过数据筛选查询女员工档案信息任务7通过数据筛选查询入职时间最 早的5名员工信息任务8通过数据筛选查询2005年2007年入职的员工人民邮电出版社POSTS & TELECOM PRESST任务9统计公司各部门员工人数(1) 将Sheet2工作表重命名为“公 司各部门员工人数”。(2) 建立表格框架。(3) 选中“公司各部门员工人数” 工作表的B3单元格。:人民邮电出版社POSTS & TELECOM PRESS人民邮电出版社POSTS & TELECOM PRESST

7、(4) 输入公式a=COUNTIF(员工档 案信息!$D$2:$D$26畀行政部”)，,按Enter键，在B3中显示“行政部” 人数。(5) 同样地，统计出其他部门的人 数。任务10创建公司各部门人员比例图(1) 选中“公司各部门员工人数” 工作表中的A2:B9单元格区域。(2) 创建“三位饼图”。(3) 设置图表的标题格式。参人民邮电出版社乡 N)卜II I 1()i rm 淞酝瀰215项目小结本项目通过制作“员工人事档案管理 表”,主要介绍了工作簿的创建、工作表 重命名、导入外部数据创建员工档案信息 表、利用超链接查阅员工个人详细资料、 使用筛选查询员工档案信息、使用 COUNTIF函数进

8、行各部门人数统计。1=在此基础上，利用统计数据创建各部 门人员比例图表。:人民邮电出版社POSTS & TELECOM PRESS2.1.6拓展项目/通过救鴉晞选麦諭后的员工 郴案信息。z通过敎鴉韩选圭询WWW 的男員工郴案信息。M免针公司员工曇历精况畀制 作比神|圏。2.2项韓5制作员工塔洌管理表2.2.1项目背景企业在对员工进行培训后，通常要根 据培训项目进行相应的考核。 人力资源部需要针对考核成绩制定相 应的培训成绩管理表。r Vlookup IF、 INDEX、ROW、COLUMN INT的使用公式的使用数据透视表数据透视图2.3.4解决方案任务1创建工作簿，重命名工作表:人民邮电出版

9、社POSTS & TELECOM PRESS(1)启动Excel 2003,新建一个空白 工作簿。(2) 将创建的工作薄以“员工工资管理表”为名保存。(3) 将“员工人事档案管理表”工作簿中的Sheetl工作表重命名为“工 资基础信息表”。任务2导入“员工信息”将行政篇中导出的“员工信息”数据 导入到当前工作表中，作为员工“工资基 础信息表的薮据。任务3创建“工资基础信息表(1) 选中“工资基础信息表”工作O(2) 删除“身份证号码”、“学 历”、“职称”、“性别”和“出生 日期”列的数据。人民邮电出版社POSTS & TELECOM PRESS|ABCDE1编号姓名部门入职时间2KY001方

10、成建市场韶1993-7-103KY002人力资源部1981-6-284KY003市场韶1983-3-205KY004M光利行政部1980-7-156KY005钱新财务部1995-7-17KY006曾科财务削2007-7-208KY007李莫莠物流制2003-7-109KY008周苏嘉行政韶2001-6-3010KY009黄雅玲市场韶1985-7-511KY010市场部2005-6-2812KYOU行政部1995-7-213KY012培训韶1984-5-1014KY013财务韶1984-6-2515KY014培训韶1980-7-516KY015物流部1993-8-117KY016市场削1993-

11、7-1018KY017尔阿物流削1986-7-2019KY018审蓝人力资源部1982-7-320KY019皮桂华物业部1987-6-2921KY020培训部1993-7-1822KY021财务部1987-6-3023KY022高並玲物业韶2001-7-1524KY023市场韶1999-4-1525KY024人力资源部1990-7-626KY025揃伟市场韶2002-8-1527图260删除数据后的工作表7(3) 分别在El、Fl、G1单元格中输 入标题字段名称“基本工资”、“绩 效工资”和“工龄工资”。(4) 输入“基本工资数据”。(5) 计算“绩效工资”。这里，绩效工资二基本工资*50%。

12、(6) 计算“工龄工资”。这里，如果“工龄”超过10年，则工龄工资为500元，否则，按每年50元计算。任务4创建“加班费结算表”(1)复制“工资基础信息表”,将晝黔的工作表重命名为“加班费结(2) 删除“入职时间”、“绩效工 资”和“工龄工资”列。(3) 在El、F1单元格中分别输入标 题“加班时间”和“加班费”。人民邮电出版社POSTS & TELECOM PRESSS j卜十十十TT十”卜十十(4) 输入加班时间。(5) 计算机加班费。加班费的计算方法为：加班费二基本工资/30/8*15*加班时间任务5创建“考勤扣款结算表”(1)复制“工资基础信息表”，将 复制后的工作表重命名为“考勤扣款

13、 结算表” O(2)删除“入职时间”、“绩效工资”和“工龄工资”列。倉人民邮电出版社(3)在E1:K1单元格中分别输入标 题“迟到”、“迟到扣款”、“病 假”、“病假扣款”、“事假”、“事假扣款”和“扣款合计” o:人民邮电出版社POSTS & TELECOM PRESS(4) 输入“迟到”、“病假”、 “事假”的数据。(5) 计算“迟到扣款”。这里，假设每迟到一次扣款为50元。（6）计算“病假扣款”。这里，假设每病假一天扣款为当日工 资收入的50%,即“病假扣款二基本工资 /30*05*病假天数”。（7）计算“事假扣款”。FA这里，假设每事假一天扣款为当日工 资收入，即“事假扣款=基本工资/

14、30*事假 天数”。（8）计算“扣款合计”。參人民邮电出版社POSTS & TELECOM PRESS任务6创建“员工工资明细表”(1)将Sheet2工作表重命名为“员:人民邮电出版社POSTS & TELECOM PRESS工工资明细表” O(3)填充“编号”、“姓名”和 “部门”数据。人民邮电出版社POSTS & TELECOM PRESS(4) 导入“基本工资”、“绩效工 资”、“工龄工资”和“加班费” 数据。 选中D3单元格。 选择【插入】菜单中的【函数】命 令，从函数列表中选择“VLOOKUP”函数, 打开【函数参数】对话框，设置参数。 单击【确定】按钮，导入相应的 “基本工资”数据

15、。 选中D3单元格，用鼠标拖动其填充 句柄至D26单元格，将公式复制到D3:D26 单元格区域中，可导入所有员工的基本工 资。(5) 同样地方式，分别导入“绩效 工资”、“工龄工资”和“加班费” 数据。(6) 导入“加班费”数据。(7) 计算“应发工资”。:人民邮电出版社POSTS & TELECOM PRESSS j卜十十十十十十十十十十十十十十十.(8) 填充“养老保险”数据。这里，养老保险的数据为个人缴纳部分，计算方法为：养老保险二(基本工资+绩效工资)*8%S j卜十十十十十十十十十十十十十十十.(9) 填充“医疗保险”数据。这里，医疗保险的数据为个人缴纳部 分，计算方法为：医疗保险二

16、(基本工资+绩效工资)*2%(10) 填充“失业保险”数据。FA这里，失业保险的数据为个人缴纳部 分，计算方法为：失业保险二(基本工资+ 绩效工资)*1%(11) 导入“考勤扣款”数据。(12) 计算“税前工资”。(13) 计算“个人所得税” o应交个人所得税额二应纳税所得额*适 用税率速算扣除数，个人所得税税率见表 21所示。表个人所得税税率表人民邮电出版社POSTS & TELECOM PRESS不超过別0元的Q5%卩超过500元至2000元的部分Q10%P25Q342000元至5Q0Q元的部分小15%1254Q5000元至20000元的部分Q203755Q超过20000元至40000元的

17、部分025%P1375 选中N3单元格。 输入公式：=IF(M32000,IF(M3-2000)500，(M3- 2000)v=2000)，(M32000)*0.125),0) 按L Enter 键确认，可计算出相应 的个人所得税。 选中N3单元格，用鼠标拖动其填充 句柄至N26单元格，将公式复制到N3:N26 单元格区域中，可计算出所有员工的个人 所得税。(13)计算“实发工资” o任务7格式化“员工工资明细表”(1) 设置工作表的标题格式。(2) 将表中所有的数据项格式设置 为“会计专用”格式，保留2位小数, 货币符号为“无”。(3) 为表格设置内细外粗的蓝色边 框。(4) 为“应发工资”

18、和“税前工资” 两列的数据区域添加“淡蓝”色底纹, 为“实发工资”列数据区域添加“天 蓝”色底纹。任务8制作“工资条”(1) 将Sheet3工作表重命名为“工 资条”。(2) 选中“工资条”工作表的A1单 元格。:人民邮电出版社POSTS & TELECOM PRESS(3) 在“编辑栏”中输入下面的公 式，然后按【EnteiJ键确认。=IF(MOD(ROW(),3)=0；nIF(MOD( ROW()，3)=1，员工工资明细 表!A$2,INDEX(员工工资明细 !$A:$OJNT(ROW()- l)/3)+3,COLUMN()人民邮电出版社POSTS & TELECOM PRESS(4) 选中Al单元格，用鼠标拖动其 填充句柄至O1单元格，将公式复制 到81:01单元格区域中。(5) 选中A1:O1单元格区域，用鼠 标拖动其填充句柄至074单元格，将 公式复制到A2:O74单元格区域中。(6) 适当调整单元格的列宽，使单 元格的内容能完整的显示出来。(7) 美化“工资条”工作表。:人民邮电出版社POSTS & TELECO