Excel在人力资源管理的应用

1、Excel在人力资源管理中的应用,用好工具，高效工作,数据录入 单元格的引用 数据有效性 条件格式 认识Excel函数 Excel函数的结构 常见的几类函数 应用实例 认识数据透视表 数据透视表是什么 数据透视表的几个简单应用实例,课程大纲,1-1 单元格的引用,1.相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变 2. 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$6 3. 混合引用GO 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用. 例: 行变列不变: $A4 列变行不变:A$4

2、,1-2 数据的有效性,控制单元格输入文本长度 GO 选中区域-数据-数据有效性-允许-文本长度 可防止身份证/银行卡号/手机号码输入多一位或者少一位数 序列 选中区域-数据-数据有效性-序列-在“来源”输入（A,B,C,D) 注意逗号要半角格式 禁止输入重复数据 =countif(区域,起始单元格)=1 选中区域-数据-数据有效性- 允许（自定义) - 公式填写=COUNTIF(A:A,A1)=1,1-2 数据的有效性,如何取消对于“数据有效性”的设置？ 选中区域-数据-数据有效性-允许-任何值 GO,1-3 条件格式,利用突出的格式，经常能起到提醒的功能 如：突出不满勤的人GO 突出年龄大

3、于30的人等,如何取消条件格式？,认识Excel函数,2-1 EXCEL函数的结构,Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,Excel 函数结构:,也有一些函数是没有参数的,如NOW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,应用实例 从身份证号码提取出生日期 在C2单元格中输入 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2) 公式解析：MID(B2,7,4)表示提取B2单元格中从第7位开始连续4位的字符，即出生年；MID(B2,11,2)表示提取B2单元格中从第11位开始连续2位的字符，

4、即出生月； MID(B2,11,2)表示提取B2单元格中从第13位开始连续2位的字符，即出生日； 在公式中加入&可以用来连接两个文本字符串。,常用函数之查找类函数,函数VOOKUP =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) (以从8月工资表导入考勤到8月考核表为例） GO,常用函数之查找类函数,函数VOOKUP 选择区域里最好不要有合并的单元格（先“取消单元格合并”） 如果单元格里有空格，有可能导不出来 （提示：函数TRIM（A1)可以去除字符串首部和尾部的空格） 注意重复的名字 查找区域首列必须含有查找的内容（

5、第一个参数） 介绍vookup函数与IFERROR函数组合使用 (以从8月工资表导入考勤到8月考核表为例）GO =IFERROR(VLOOKUP(A4,8月工资!B:F,5,0),0) 如果VOOKUP函数运行后找不到对应的数据，就会显示为#N/A，IFERROR的作用就是把这些#N/A都变为第2个参数。,应用实例 在C1输入=A1&+&B1 如何根据A1得出B1和C1？ 在B1输入=LEFT(A1,FIND(+,A1)-1) 分析：思路是从左边开始，截取“+”号前的位数。 在C1输入=RIGHT(A1,LEN(A1)-FIND(+,A1) 分析：思路是截取“+”号后的位数。,如何根据A1、B

6、1得出C1？,IF函数的应用实例,区域段自动算出对应等级 =IF(K4=90,A,IF(K4=80,B,IF(K4=70,C,D) 嵌套了两个IF函数,应用实例 从身份证号码中提取性别 （身份证倒数第2位若为偶数为女性，奇数为男性） 在C2单元格中输入=IF(MOD(MID(B2,17,1),2)=1,男,女) 公式分析： MID(B2,17,1)是从身份证第17位起取1位字符；在本例即为数字5，MOD函数就是取余数的函数，在本例中就是MOD(5，2),5除以2的余数，最外面是一个IF函数，当这个余数为1时，即为奇数，奇数就是男性，否则为女性。,常用函数之日期类函数,DATEDIF函数 常用于

7、计算两日期的时间差 =DATEDIF(较早的日期，较晚的日期，第三个参数） 第三个参数： “Y” -两日期间共多少年。 “M” -两日期间共多少个月。 “D” -两日期间共多少天。 “MD” 是两日期中天数的差。忽略日期中的月和年。 “YM” 是两日期中月数的差。忽略日期中的年。 “YD” 是两日期中天数的差。忽略日期中的年。,常用函数之日期类函数,举例:算两个日期间差几年、几月、几日 开始时间是2012-2-21 结束时间是2014-2-19 C2=DATEDIF(A2,B2,Y) D2=DATEDIF(A2,B2,YM) E2=DATEDIF(A2,B2,MD) F2=DATEDIF(A2

8、,B2,D) G2=DATEDIF(A2,B2,M) H2=DATEDIF(A2,B2,“YD) I2=C2&年&D2&个月 I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“个月,常用函数之日期类函数,应用实例3 从出生日得出年龄 在D2单元格中输入=DATEDIF(C2,NOW(),“y”) 因为从身份证提取出生日的C2 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2) 所以若直接从身份证得出年龄 D2= DATEDIF(MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2),NOW

9、(),y),应用实例 培训协议服务期限完成提醒 思路：过期了可以显示“协议期限完成” 没过期的，显示“没过期” =IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 协议期限完成, ),没过期) 分析：DATEDIF(B2,TODAY(),“d”)可以 求出今天到B2的日期共多少天，如果“今天” 在B2之前，就会产生错误值，如果“今天”在 B2之后，这个函数就会算出B2距离“今天”有 多少天。当这个天数0时，就会执行IF函数， 显示“协议期限完成”，当DATEDIF运行是一 个错误值时，这时候就执行IFERROR函数， 显示“没过期”。,如果我想到期前一个月就提醒呢？,思路

10、： =IFERROR(IF(DATEDIF(TODAY(),B2,D)=31,少于31天后就会过期,多于31天),已过期),DATEDIF函数,能运行，说明B2晚于今天,能执行IF函数,当B2晚于今天31天,显示“少于31天后就会过期”,当B2晚于今天31天,显示“多于31天”,不能运行，B2早于今天（培训期限过期了）,DATEDIF函数会产生错误值,IF函数也会产生错误值,执行IFERROR函数,显示“已过期”,常用函数之计数类函数,COUNTIF函数 COUNTIF(range,criteria) 区域 条件标准 是一个数数的函数，可以用于数出参数1区域内，符合参数2的条件的单元格个数。

11、如：=countif(F3:F10, “女”) 统计F3:F10中女的个数。,COUNTIF函数的应用实例,应用实例 在9月流动表中找出有相同名字的人 步骤1：打开9月流动表，找出9月在职的工作表，在姓名右边新建一列，插入函数，找到COUNTIF函数，选择“确定”。,COUNTIF函数的应用实例,步骤2：点击Range的框格，选中E列，点击Criteria的框格，点击E2单元格，点击“确定”。 步骤3：点击F2单元格，把鼠标移到F2右下角，鼠标箭头变为黑色十字的时候双击。,这个的意思就是在9月在职的人中只有一个叫严邦平的,COUNTIF函数的应用实例,步骤4：选中F列-排序与筛序-筛选-点击F

12、1左上角小箭头-去掉1前的小勾-确定GO,COUNTIF函数的应用实例,统计一定区间的个数 如：统计9月流动表“9月在职”工作表中， 50岁以上的人数： =COUNTIF(F:F,=50) 20岁年龄40岁的人数： =COUNTIF(F:F,=20)-COUNTIF(F:F,=40) （注意：如果要统计的数据是有一个绿色的左角的， 要先选择要统计的数据，点击 符号，选择 “转换为数字”，不然是统计不了个数的）GO,认识数据透视表,认识数据透视表,数据透视表是一种交互，交叉制作的报表，一组具有多个字段的数据进行多立体的分析汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和比较大量数据。

13、当数据规模比较大时，这种分析的意义就显得尤为突出。,说人话,一种快速分析数据的工具,前提 这些数据使用数段表进行管理,统计9月入职的招聘来源分布,统计离职原因分布个数,统计各部门平均工资,数据透视表可以快速解决这些事情,统计各部门学历分布情况,认识数据透视表,统计9月入职的招聘来源分布 步骤1：打开工作表-选择数据来源区域（A1：i79）-插入-数据透视表-确定 统计9月入职的招聘来源分布,步骤2：把招聘来源拖到下面的“行标签”框，把序号拖到下面的“数值”框，就能快速生成下图。,统计离职原因分布个数,同理，可根据前面介绍的步骤在辞工名单的表格中快速做出离职原因分布的表格GO 拖“离职原因”到“行标签”框，拖“序号”到“数值”框 注意：要留意“数值”框，