Excel在人力资源管理的应用极其精美实用课件

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

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

3、突出年龄大于30的人等如何取消条件格式？第6页，共45页。认识Excel函数第7页，共45页。2-1.EXCEL函数的结构Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel 函数结构:也有一些函数是没有参数的,如NOW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔第8页，共45页。函数名语法函数结构函数作用例子运行结果1、Left1、LEFT(text,num_chars)其中：Text是包含要提取字符的文本字符串。Num_chars 指定要由 LEFT 所提取的字符数。=LEFT(A1,2)从A1最左边向右取两位2、Right2、RIGH

4、T(text,num_chars) 其中：Text是包含要提取字符的文本字符串。Num_chars指定希望 RIGHT 提取的字符数。=RIGHT(A1,2)从A1最右边向左取两位3、Mid3、MID(text,start_num,num_chars)其中：Text是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。Num_chars 指定希望 MID 从文本中返回字符的个数。=MID(A1,2,3)从A1左边第2位开始取3位4、&（连接符号）XX&XX=A2&B2将A2和B2内容合并在一起常用函数之截取、合并类函数第9页，共45页。应用实例从身份证号码提取出生

5、日期 在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位的字符，即出生月； MID(B2,11,2)表示提取B2单元格中从第13位开始连续2位的字符，即出生日； 在公式中加入&可以用来连接两个文本字符串。第10页，共45页。函数名语法函数结构函数作用FIND=FIND(龙,C6)在c6中查找龙，结果显示龙在c6中的位数，查不到显示#VALUE!常用函数之查找类函数第11页，共45页。常

6、用函数之查找类函数函数VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)(以从8月工资表导入考勤到8月考核表为例） GO参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域col_index_num返回数据在查找区域的第几列数正整数range_lookup精确匹配/模糊匹配0或1第12页，共45页。常用函数之查找类函数函数VOOKUP选择区域里最好不要有合并的单元格（先“取消单元格合并”）如果单元格里有空格，有可能导不出来 （提示：函数TR

7、IM（A1)可以去除字符串首部和尾部的空格）注意重复的名字查找区域首列必须含有查找的内容（第一个参数）介绍vookup函数与IFERROR函数组合使用 (以从8月工资表导入考勤到8月考核表为例）GO =IFERROR(VLOOKUP(A4,8月工资!B:F,5,0),0) 如果VOOKUP函数运行后找不到对应的数据，就会显示为#N/A，IFERROR的作用就是把这些#N/A都变为第2个参数。第13页，共45页。函数名语法/例子函数结构函数作用Value=value(b1)将b1数值化Numberstring=NumberString(7890,1）运行结果：七千八百九十=NumberStrin

8、g(7890,2) 运行结果：柒仟捌佰玖拾=NumberString(7890,3) 运行结果：七八九=numberstring(b1,2)将b1数字转为大写汉字Row=ROW(reference)其中：Reference 为需要得到其行号的单元格或单元格区域。=row(b1)求b1是第几行（值为1)Column=COLUMN(reference)=column(b1)求b1是第几列(值为2)常用函数之文本类函数第14页，共45页。函数名语法函数结构函数作用len=LEN(text)其中：Text 是要查找其长度的文本。空格将作为字符进行计数。=len(C2)计算C2的字符长度rankRANK

9、(number,ref,order)Number为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order为一数字，指明排位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)计算b1在b1:b10区域中从大到小排第几名；计算b1在b1:b10区域中从小到大排第几名。常用函数之文本类函数第15页，共45页。应用实例 在C1输入=A1&+&B1如何根据A1得出B1和C1？在B1输入=LEFT(A1,FIND(+,A1)-1)分析：思路是从左边开始，截取“+”号前的位数。在C1输入=RIGHT(A1,LEN(A1)-FI

10、ND(+,A1)分析：思路是截取“+”号后的位数。如何根据A1、B1得出C1？第16页，共45页。函数名语法函数结构函数作用IFIF(logical_test,value_if_true,value_if_false)=if(a20,1,0)当a20时，显示1，否则显示0IFREEOR第一个参数一般为一个函数，当这个函数运行是个错误值时（#N/A #DIV/0! #N/A #VALUE! #NAME #NULL! #NUM! #REF!），运行结果等于第二个参数=iferror(vookup(A1,A:B,2,0),0) 如果VLOOKUP函数运行值是个错误值的话（找不到数据），就等于0，否则

11、，就等于VLOOKUP函数运行的值。ANDAND(logical1,logical2, .)Logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。=and(a30,b30,并且b30,b40,或者b4=90,A,IF(K4=80,B,IF(K4=70,C,D)嵌套了两个IF函数分数段等级分数90分A80分分数90分B70分分数80分C分数70分D第18页，共45页。函数名语法函数结构函数作用SUMIFSUMIF(range,criteria,sum_range)其中：Range为用于条件判断的单元格区域。Criteria 为确

12、定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。Sum_range 是需要求和的实际单元格。=sumif(B:B,H1,E:E) 当B列满足了条件（HI），对应的E列的单元格的数值之和GOROW 求余数的函数，当第二个参数为2时，常用于判断奇偶数，第二个参数为2时，运行结果为0是偶数，1是奇数=ROW(5，2)求5除以2的余数是多少，本例的运行结果是1常用函数之求和类函数第19页，共45页。应用实例从身份证号码中提取性别 （身份证倒数第2位若为偶数为女性，奇数为男性） 在C2单元格中输入=IF(MOD(MID(B2,17

13、,1),2)=1,男,女)公式分析： MID(B2,17,1)是从身份证第17位起取1位字符；在本例即为数字5，MOD函数就是取余数的函数，在本例中就是MOD(5，2),5除以2的余数，最外面是一个IF函数，当这个余数为1时，即为奇数，奇数就是男性，否则为女性。 第20页，共45页。函数名语法函数结构函数作用1、NOW=now()显示当前的日期+时间2、TODAY=today()显示今天的日期3、WEEKDAY=weekday(b1,2)判断b1为星期几4、DAY=day(b1)显示b1的号数5、MONTH=month(b1)显示b1的月数6、YEAR=year(b1)显示b1的年数常用函数之

14、日期类函数第21页，共45页。函数名以单元格b1为2013-4-5为例的运行结果函数结构函数作用WEEKDAY6=weekday(b1,2)判断b1为星期几DAY5=day(b1)显示b1的号数MONTH4=month(b1)显示b1的月数YEAR2013=year(b1)显示b1的年数常用函数之日期类函数第22页，共45页。常用函数之日期类函数DATEDIF函数常用于计算两日期的时间差=DATEDIF(较早的日期，较晚的日期，第三个参数）第三个参数：“Y” -两日期间共多少年。“M” -两日期间共多少个月。“D” -两日期间共多少天。“MD” 是两日期中天数的差。忽略日期中的月和年。“YM”

15、 是两日期中月数的差。忽略日期中的年。“YD” 是两日期中天数的差。忽略日期中的年。第23页，共45页。常用函数之日期类函数举例:算两个日期间差几年、几月、几日开始时间是2012-2-21 结束时间是2014-2-19C2=DATEDIF(A2,B2,Y)D2=DATEDIF(A2,B2,YM)E2=DATEDIF(A2,B2,MD)F2=DATEDIF(A2,B2,D)G2=DATEDIF(A2,B2,M)H2=DATEDIF(A2,B2,“YD)I2=C2&年&D2&个月I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“个月第24页，共45页。常

16、用函数之日期类函数应用实例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(),y)第25页，共45页。应用实例培训协议服务期限完成提醒思路：过期了可以显示“协议期限完成” 没过期的，显示“没过期”=IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 协议期限完成, ),没过期)分析：DA

17、TEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就会产生错误值，如果“今天”在B2之后，这个函数就会算出B2距离“今天”有多少天。当这个天数0时，就会执行IF函数，显示“协议期限完成”，当DATEDIF运行是一个错误值时，这时候就执行IFERROR函数，显示“没过期”。如果我想到期前一个月就提醒呢？第26页，共45页。思路：=IFERROR(IF(DATEDIF(TODAY(),B2,D)=50)20岁年龄40岁的人数：=COUNTIF(F:F,=20)-COUNTIF(F:F,=40)（注意：如果要统计的数据是有一个绿色的左角的， 要先选择

18、要统计的数据，点击 符号，选择 “转换为数字”，不然是统计不了个数的）GO第33页，共45页。认识数据透视表第34页，共45页。认识数据透视表数据透视表是一种交互，交叉制作的报表，一组具有多个字段的数据进行多立体的分析汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和比较大量数据。当数据规模比较大时，这种分析的意义就显得尤为突出。说人话一种快速分析数据的工具第35页，共45页。前提这些数据使用数段表进行管理第36页，共45页。统计9月入职的招聘来源分布统计离职原因分布个数统计各部门平均工资数据透视表可以快速解决这些事情统计各部门学历分布情况认识数据透视表第37页，共45页。统计9月入

19、职的招聘来源分布步骤1：打开工作表-选择数据来源区域（A1：i79）-插入-数据透视表-确定统计9月入职的招聘来源分布第38页，共45页。步骤2：把招聘来源拖到下面的“行标签”框，把序号拖到下面的“数值”框，就能快速生成下图。第39页，共45页。统计离职原因分布个数同理，可根据前面介绍的步骤在辞工名单的表格中快速做出离职原因分布的表格GO拖“离职原因”到“行标签”框，拖“序号”到“数值”框注意：要留意“数值”框，是求和，还是计数，还是其他，左图的默认是“求和”，而我们是想“计数”。通过双击B3，出来“值字段对话框”，选择“计数”第40页，共45页。统计出不同部门工资总和、平均工资 行标签-部门

20、 数值-求和项：工资，平均值项(值字段设置可找到）：工资这是原数据 这是生成的数据透视表第41页，共45页。统计各部门学历分布情况行标签-部门，列标签-最高学历 数值-计数项：最高学历想想：如果在列标签先拖入性别，再拖入最高学历，数据透视表会怎样第42页，共45页。Thank You !第43页，共45页。每一次的加油，每一次的努力都是为了下一次更好的自己。8月-228月-22Thursday, August 4, 2022天生我材必有用，千金散尽还复来。05:19:1305:19:1305:198/4/2022 5:19:13 AM安全象只弓，不拉它就松，要想保安全，常把弓弦绷。8月-220

21、5:19:1305:19Aug-2204-Aug-22得道多助失道寡助，掌控人心方位上。05:19:1305:19:1305:19Thursday, August 4, 2022安全在于心细，事故出在麻痹。8月-228月-2205:19:1305:19:13August 4, 2022加强自身建设，增强个人的休养。2022年8月4日5:19 上午8月-228月-22扩展市场，开发未来，实现现在。04 八月 20225:19:13 上午05:19:138月-22做专业的企业，做专业的事情，让自己专业起来。八月 225:19 上午8月-2205:19August 4, 2022时间是人类发展的空间

22、。2022/8/4 5:19:1305:19:1304 August 2022科学，你是国力的灵魂；同时又是社会发展的标志。5:19:13 上午5:19 上午05:19:138月-22每天都是美好的一天，新的一天开启。8月-228月-2205:1905:19:1305:19:13Aug-22人生不是自发的自我发展，而是一长串机缘。事件和决定，这些机缘、事件和决定在它们实现的当时是取决于我们的意志的。2022/8/4 5:19:13Thursday, August 4, 2022感情上的亲密，发展友谊；钱财上的亲密，破坏友谊。8月-222022/8/4 5:19:138月-22谢谢大家！第44页，共45页。每一次的加油，每一次的努力都是为了