EXCEL教程人力资源篇

Excel人力资源篇项目4制作员工人事档案管理表2.1项目5制作员工培训管理表2.2项目6制作员工工资管理表2.32.1项目4制作员工人事档案管理表 2.1.1项目背景 员工人事档案的整理分类、汇总、分析、查询等是一件比较复杂的工作。 使用Excel可以实现人事管理的自动化，提高人事档案管理的条理化。

本项目通过制作“员工人事档案管理表”，来介绍Excel软件在人力资源管理中的应用。 2.1.2项目效果图2-1员工人事档案筛选效果图图2-2公司各部门人员分配效果图 2.1.3知识与技能 ●工作簿的创建 ●工作表重命名 ●导入外部数据 ●数据链接 ●数据的筛选 ●COUNTIF函数的使用 ●创建图表 2.1.4解决方案 任务1创建工作簿，重命名工作表（1）启动Excel2003，新建一个空白工作簿。（2）将创建的工作薄以“员工人事档案管理表”为名保存。（3）将“员工人事档案管理表”工作簿中的Sheet1工作表重命名为“员工档案信息”。

任务2导入“员工信息” 将行政篇中导出的“员工信息”数据导入到当前的文件中，轻松获取外部人事档案信息。（1）选中“员工人事档案管理表”工作簿中的“员工档案信息”工作表。（2）选择【数据】菜单中【导入外部数据】命令的子命令【导入数据】，打开【选取数据源】对话框。（3）选择数据源文件“员工信息.prn”，（4）从对话框的“原始数据类型”中选择“固定长度”作为最合适的文件类型，在“导入起始行”文本框中保持默认值“1”，在文件原始格式下拉列表中选择“936：简体中文（GB2312）”。（5）按文本导入向导进行操作。在“数据预览”区内设置字段宽度，也就是列间隔；设置每列的数据类型。（6）单击【确定】按钮，完成数据导入。

任务3创建“员工个人详细资料”链接（1）链接到已有文件。（2）链接到新建文件。

任务4查阅“员工个人详细资料” 当需要查阅某个员工的个人详细资料时，我们可通过激活超链接进行访问。

任务5复制“员工档案信息”工作表（1）选中“员工档案信息”工作表。（2）将该工作表复制三份，并分别重命名为“女员工档案信息”、“入职时间最早的5名员工信息”、“2005年-2007年入职的员工”。

任务6通过数据筛选查询女员工档案信息 任务7通过数据筛选查询入职时间最早的5名员工信息 任务8通过数据筛选查询2005年-2007年入职的员工

任务9统计公司各部门员工人数（1）将Sheet2工作表重命名为“公司各部门员工人数”。（2）建立表格框架。（3）选中“公司各部门员工人数”工作表的B3单元格。（4）输入公式“=COUNTIF(员工档案信息!$D$2:$D$26,"行政部")”，按【Enter】键，在B3中显示“行政部”人数。（5）同样地，统计出其他部门的人数。

任务10创建公司各部门人员比例图（1）选中“公司各部门员工人数”工作表中的A2:B9单元格区域。（2）创建“三位饼图”。（3）设置图表的标题格式。2.1.义5项目小结本项目郊通过制桨作“员私工人事四档案管哪理表”令，主要绒介绍了掘工作簿塌的创建说、工作绢表重命可名、导星入外部强数据创助建员工煮档案信羽息表、垒利用超军链接查园阅员工塑个人详龙细资料困、使用胶筛选查霜询员工川档案信挨息、使蒸用COU桶NTI穗F函数进行芝各部门人斥数统计。在此基础捞上，利用钻统计数据谷创建各部虎门人员比蜜例图表。2.1蚂.6拓展项奖目1.通过数从据筛选垄查询80后的员串工档案净信息。2.通过数杆据筛选呜查询“市场部”的男员筹工档案东信息。3.统计公司慕员工学历灯情况并制厦作比例图父。2.2项目5制作员工鞋培训管理杏表2.2割.1项目背景企业在对趋员工进行妖培训后，杯通常要根茂据培训项热目进行相拣应的考核恢。人力资嘱源部需葡要针对坐考核成据绩制定督相应的呢培训成栋绩管理盯表。本项目迈通过制岭作“员盟工培训哨管理表伴”，来睡介绍Exce砌l软件在培妈训管理方捷面的应用识。2.2定.2项目效果图2-3员工培尽训管理卷表图2-4员工培披训考核排数据透免视图2.2惜.3知识与技璃能●工作簿缴的创建●工作续表重命键名●数据滨有效性●函数AVER袖AGE、SUM、COUN弄TIF和RAN乐K的应用●美化枪工作表●数据透礼视表●数据厌透视图2.2致.4解决方案任务1创建工应作簿，牺重命名舰工作表（1）启动Exce稿l20巴03，新建一遵个空白工诸作簿。（2）将创建鼻的工作薄配以“员工木培训管理曲表”为名咏保存。（3）将“员释工人事档滩案管理表什”工作簿狡中的Shee殖t1工作表重堵命名为“但员工培训传成绩表”汪。任务2创建“员悠工培训成始绩表”框宋架（1）在“员财工培训成磁绩表”中扮输入工作效表标题。（2）输入表宇格标题字筐段。任务3输入“连序号”利用填充吩句柄，输仇入序号。任务4输入“姓潮名”和“须部门”（1）输入介员工“旨姓名”捡。（2）输入员颗工“部门匠”。任务5设置各培绑训项目成斯绩的数据件有效性在输入数执据的过程气中，为尽典量减小数评据输入的深错误，提壁高数据的精有效性，蔽可对要输昼入数据的降单元格区斜域设置有钢效性规则沸，使输入俯的数据在养规定的范顷围之内。即输入异的成绩穷范围在0～100之间。任务6统计“平贤均成绩”帝和总成绩任务7利用RAN房诚K函数排列痰员工培训盟成绩的名梦次（1）选中I3单元格嚼。（2）输入公赴式“=RA惑NK(闻H3,优$H$捎3:$背H$2给1)”按【Ent她er】键确认。（3）选中I3单元格帜，拖动预填充句拆柄至I21单元格，李将公式复叫制到I4:延H21单元格区匀域中，排少列出所有种名次。任务8统计各培初训项目的六平均成绩（1）在A22单元格中舅输入“各挣培训项目熊平均成绩猾”。（2）选中D22单元格，骑输入公式失“=AVE确RAGE畅(D3:上D21)挣”，按【Ent熟er】键确认欢。（5）选中D22单元格，谅拖动填充棍句柄至F22单元格沈，将公毒式复制蕉到E22:浸F22单元格巷区域中凝。任务9利用COU蛛NTI灯F函数统致计考核君不合格飞的员工也人数这里，假抖定员工的欲所有项目絮平均成绩张低于80分，则寇认定为趴不合格棵。（1）在G22单元格中鲁输入“不磨合格人数淋”。（2）选中I22单元格，撑输入公式第“=COU抵NTIF勒(G3:否G21,朗"<80谋")”。（3）按【Ent只er】键确认围。任务10设置单爹元格数偿据格式将“平钞均成绩幕”和“刃各培训俭项目平策均成绩伪”的数臣据保留诉为1位小数。任务11美化员工课培训成绩止表（1）设置工柱作表标题障格式。（2）合并单炸元格。（3）将表冷格中所字有数据涛区域的垄内容设苏置为居畏中对齐机。（4）适当调号整表格的姥行高和列恒宽，能显停示表格内成容。（5）为表敏格设置该边框。任务12制作员工字培训考核愉数据透视谈图（1）选中“烧员工培训惹成绩表”兄。（2）选择【数据】菜单中的【数据透视锋表和数据州透视图】命令，打床开【数据透视疲表和数据微透视图向辱导对话框泳，按提示卸进行操作指。（3）将“滑姓名”扶作为“另行”字虏段，将挡“计算享机技能芹”作为码“数据服”项。（4）选择【汇总方式】为“最影大值”全。（5）单击【完成】按钮，世系统将勉在工作济簿中插钩入一张帮工作表Shee窑t4和一张工贸作表Cha蜓rt1。在Shee暖t4中生成数扒据透视表豆，将Shee抖t4工作表烟重命名默为“员狂工培训月成绩数踢据透视抄表”，狸在Char初t1中生成期数据透滥视图，澡将Char柿t1工作命名热为“员工著培训考核番数据透视匹图”。2.2枕.5项目小结本项目叶通过制衰作“员蓝工培训令管理表总”，主菌要介绍抄了工作望簿的创岗建、工畅作表重钥命名、收数据有液效性设胁置、函藏数AVER岸AGE、SUM、COU狂NTI秃F和RANK的应用、萝美化工作苦表等。在此基雷础上，提利用培品训成绩询表中的肥数据，牛通过数仰据透视僵表和数斯据透视途图对员公工培训她成绩进艇行汇总故、分析融，使表震格中的滔数据更朋为直观肯、生动闹。2.2崭.6拓展项目1.按培训嚷成绩名赚次进行赏升序排范列。2.制作各驴培训项隔目平均顶成绩比亦较图。3.制作公司蛾各部门培半训成绩数鸡据透视图蜂。2.3项目6制作员饼工工资况管理表2.3桶.1项目背景制作工木资表示板通常需升要综合订大量的洞数据，习如基本掠工资、落绩效工奏资、补继贴、扣川款项等催。如果用失手工做饥账，不扶仅工作滥量非常从大，而看且还往寨往容易椅出错。本项目插通过制暮作“员爬工工资优管理表册”来介承绍Exce辛l软件在菠工资管果理方面后的应用歼。2.3剪.2项目效果图2-5员工工兽资明细但表效果割图图2-6员工工资申条效果图2.3.头3知识与销技能●工作簿股的创建●工作公表重命赤名●导入邻外部数荣据●函数Year、Roun援d、Vlo倚oku肠p、IF、INDE订X、ROW、COLU姥MN、INT的使用●公式的秩使用●数据透偶视表●数据宗透视图2.3.针4解决方案任务1创建工跌作簿，誉重命名蔽工作表（1）启动Exc四el管200猎3，新建攀一个空运白工作旱簿。（2）将创姓建的工赏作薄以除“员工春工资管喷理表”袜为名保虹存。（3）将“测员工人饮事档案躬管理表馅”工作球簿中的Shee爪t1工作表重阶命名为“轨工资基础持信息表”诊。任务2导入“员熊工信息”将行政篇帅中导出的贱“员工信证息”数据采导入到当昨前工作表恰中，作为地员工“工还资基础信蒙息表”的烈数据。任务3创建“工燥资基础信面息表”（1）选中“助工资基础愁信息表”旦工作表。（2）删除勿“身份轰证号码悦”、“没学历”煮、“职示称”、舞“性别圈”和“篮出生日戏期”列蛙的数据叔。图2-6访0删除数贪据后的经工作表（3）分别在E1、F1、G1单元格齐中输入惧标题字顾段名称对“基本晚工资”它、“绩泊效工资转”和“腔工龄工泄资”。（4）输入粒“基本吗工资数祖据”。（5）计算“击绩效工资押”。这里，绩膨效工资=基本工司资*50%。（6）计算“什工龄工资汁”。这里，如解果“工龄屑”超过10年，则终工龄工伪资为500元，否米则，按芒每年50元计算迹。任务4创建“加右班费结算养表”（1）复制致“工资朋基础信薪息表”拜，将复绝制后的项工作表酸重命名吗为“加袍班费结尿算表”钱。（2）删除冈“入职盒时间”呼、“绩肃效工资呼”和“球工龄工替资”列诉。（3）在E1、F1单元格述中分别煎输入标钳题“加舍班时间马”和“亏加班费歉”。（4）输入税加班时怀间。（5）计算机晚加班费。加班费少的计算上方法为榨：加班秘费=基本工资/30/林8*1.拆5*加班时间任务5创建“考桶勤扣款结咳算表”（1）复制“萍工资基础岔信息表”咏，将复制迫后的工作黑表重命名尾为“考勤碑扣款结算蚁表”。（2）删除“逃入职时间睡”、“绩迷效工资”叶和“工龄求工资”列构。（3）在E1:K剖1单元格中殃分别输入赠标题“迟隶到”、“迈迟到扣款畏”、“病暑假”、“期病假扣款姥”、“事歉假”、“羽事假扣款夸”和“扣览款合计”走。（4）输入“悦迟到”、晒“病假”铺、“事假远”的数据属。（5）计算“姥迟到扣款子”。这里，岸假设每羽迟到一良次扣款议为50元。（6）计算“关病假扣款盐”。这里，姻假设每曲病假一旷天扣款蹄为当日扇工资收惧入的50%，即“病远假扣款=基本工降资/30*炸0.5*病假天译数”。（7）计算峰“事假习扣款”层。这里，假粱设每事假续一天扣款闲为当日工桌资收入，竞即“事假律扣款=基本工资/30*事假天数英”。（8）计算“潮扣款合计降”。任务6创建“员斩工工资明璃细表”（1）将She袍et2工作表客重命名何为“员扭工工资洗明细表离”。（2）参创爹建员工竹工资明间细表的赤框架。（3）填充“移编号”、斥“姓名”躲和“部门厅”数据。（4）导入“喘基本工资聋”、“绩旧效工资”膝、“工龄惨工资”和拒“加班费森”数据。①选中D3单元格。②选择【插入】菜单中的【函数】命令，从片函数列表狡中选择“VLO洋OKU催P”函数，打衫开【函数参蹈数】对话框，蜜设置参数朗。③单击【确定】按钮，导稿入相应的住“基本工粱资”数据额。④选中D3单元格内，用鼠贡标拖动只其填充他句柄至D26单元格昨，将公相式复制译到D3:D但26单元格区饮域中，可曾导入所有啦员工的基投本工资。（5）同样地它方式，分饿别导入“刷绩效工资删”、“工崖龄工资”丙和“加班滩费”数据沉。（6）导入具“加班悄费”数捷据。（7）计算待“应发跨工资”创。（8）填充欠“养老慕保险”劲数据。这里，远养老保沙险的数缓据为个葵人缴纳贸部分，侦计算方耐法为：瓶养老保连险=（基本工立资+绩效工纪资）*8%（9）填充“语医疗保险贪”数据。这里，恐医疗保垂险的数晨据为个症人缴纳仓部分，笑计算方蝴法为：拨医疗保颗险=（基本工掘资+绩效工资测）*2%（10）填充“疾失业保险体”数据。这里，熔失业保忍险的数板据为个迟人缴纳血部分，归计算方园法为：镰失业保黑险=（基本工敞资+绩效工封资）*1%（11）导入如“考勤怨扣款”楚数据。（12）计算“东税前工资今”。（13）计算油“个人厌所得税孤”。应交个娇人所得耳税额=应纳税级所得额龙*适用司税率-速算扣除舅数，个人宽所得税税丽率见表2-1所示。表2-1个人所待得税税幸率表①选中N3单元格。②输入呜公式：=IF(费M3>2锅000,元IF((虹M3-2询000)爹<=50慨0,(M霉3-20亦00)*减0.05咽,IF(腿AND(弃(M3-荡2000汤)>50境0,(M盗3-20民00)<榆=200丹0),(肥M3-2旅000)注*0.1鸣-25)挂),0)③按【Ent缎er】键确认,可计算致出相应笛的个人赖所得税计。④选中N3单元格，浴用鼠标拖抱动其填充榨句柄至N26单元格绩，将公吼式复制火到N3:月N26单元格区坏域中，可馅计算出所尤有员工的氏个人所得催税。（13）计算“栽实发工资鹊”。任务7格式化“他员工工资净明细表”（1）设置晶工作表归的标题勇格式。（2）将表厨中所有旗的数据华项格式烧设置为塘“会计仅专用”套格式，鲜保留2位小数肾，货币漆符号为乏“无”贤。（3）为表栗格设置逮内细外不粗的蓝赴色边框判。（4）为“应背发工资”顺和“税前答工资”两遍列的数据形区域添加威“淡蓝”小色底纹，守为“实发蹲工资”列柱数据区域致添加“天但蓝”色底矩纹。任务8制作“羡工资条定”（1）将Shee她t3工作表丽重命名瓜为“工服资条”其。（2）选中“朽工资条”