excel在人力资源中的应用EXCEl培训课件

1、培训：职场技能 ExcelExcel在人力资源在人力资源 管理中的应用管理中的应用 数据录入数据录入 01 认识认识ExcelExcel函数函数 02 认识数据透视表认识数据透视表 03 CONTENTS 目录 单元格的引用数据有效性条件格式 1-11-1单元格的引用单元格的引用 1.相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如 $A$4,$C$6 混合引用GO 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用. 例: 行变列不变: $A4 列变行不变

2、:A$4 1-21-2数据的有效性数据的有效性 控制单元格输入文本长度 GO 选中区域-数据-数据有效性-允许-文本长度 可防止身份证/银行卡号/手机号码输入多一位或者少一位数 序列 选中区域-数据-数据有效性-序列-在“来源”输入（A,B,C,D)注意逗号要半角格式 禁止输入重复数据 =countif(区域,起始单元格)=1 选中区域-数据-数据有效性- 允许（自定义) - 公式填写=COUNTIF(A:A,A1)=1 1-21-2数据的有效性数据的有效性 如何取消对于如何取消对于“数据有效性数据有效性”的设置？的设置？ l 选中区域-数据-数据有效性-允许-任何值 l GO 1-31-3条

3、件格式条件格式 l 利用突出的格式，经常能起到提醒的 功能 如：突出不满勤的人GO 突出年龄大于30的人等 如何取消条件格式？ Excel函数的结构/常见的几类函数/应用 实例 2-1.EXCEL2-1.EXCEL函数的结构函数的结构 Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。 Excel 函数结构: 函数名语法函数结构函数作用 例子运 行结果 1、Left 1、LEFT(text,num_chars) 其中：Text 是包含要提取字符的文本字 符串。Num_chars 指定要由 LEFT 所 提取的字符数。 =LEFT(A1,2) 从A1最左边向右 取两位 2、R

4、ight 2、RIGHT(text,num_chars) 其中：Text 是包含要提取字符的文本字 符串。Num_chars 指定希望 RIGHT 提取的字符数。 =RIGHT(A1,2) 从A1最右边向左 取两位 3、Mid 3、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内容 合并在一起 常用函

5、数之截取、合并类函数常用函数之截取、合并类函数 1. 应用实例 从身份证号码提取出生日期 在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位的字符，即出生日； 在公式中加入&可以用来连接两个文本字符串。 函数名语法函数结构函数作用 FIND =FIND(龙 ,C6) 在c6中查找龙，结果显 示龙在

6、c6中的位数，查 不到显示#VALUE! 常用函数之查找类函数常用函数之查找类函数 参数简单说明输入数据类型 lookup_val ue 要查找的值 数值、引用或文本字符 串 table_array要查找的区域数据表区域 col_index_ num 返回数据在查找区域的 第几列数 正整数 range_look up 精确匹配/模糊匹配0或1 l 函数VOOKUP =VLOOKUP(lookup_val ue,table_array,col_ind ex_num,range_lookup) 常用函数之查找类函数常用函数之查找类函数 常用函数之查找类函数常用函数之查找类函数 函数函数VOOKUP

7、VOOKUP 选择区域里最好不要有合并的单元格（先“取消单元格合并”） 如果单元格里有空格，有可能导不出来 （提示：函数TRIM（A1)可以去除字符串首部和尾部的空格） 注意重复的名字 查找区域首列必须含有查找的内容（第一个参数） 介绍vookup函数与IFERROR函数组合使用 (以从8月工资表导入考勤到8月考核表为例）GO =IFERROR(VLOOKUP(A4,8月工资!B:F,5,0),0) 如果VOOKUP函数运行后找不到对应的数据，就会显示为#N/A，IFERRORIFERROR的作用就是把这些的作用就是把这些#N/A#N/A都变为第都变为第 2 2个参数。个参数。 函数名语法/例

8、子函数结构函数作用 Value=value(b1)将b1数值化 Numberstr ing =NumberString(7890,1）运行结果： 七千八百九十 =NumberString(7890,2) 运行结果：柒 仟捌佰玖拾 =NumberString(7890,3) 运行结果：七 八九 =numberstring (b1,2) 将b1数字转为大 写汉字 Row =ROW(reference) 其中：Reference 为需要得到其行号的 单元格或单元格区域。 =row(b1) 求b1是第几行 （值为1) Column=COLUMN(reference)=column(b1) 求b1是第几

9、列(值 为2) 常用函数之文本类函数常用函数之文本类函数 函数名语法函数结构函数作用 len =LEN(text) 其中：Text 是要查找其长度的文 本。空格将作为字符进行计数。 =len(C2)计算C2的字符长度 rank RANK(number,ref,order) Number 为需要找到排位的数字 。 Ref 为数字列表数组或对数字列表 的引用。Ref 中的非数值型参数将被 忽略。 Order 为一数字，指明排位的方式 。 =rank(b1,b1:b10, 0) =rank(b1,b1:b10 ,1) 计算b1在b1:b10区 域中从大到小排第几 名； 计算b1在b1:b10区 域中

10、从小到大排第几 名。 常用函数之文本类函数常用函数之文本类函数 l应用实例 在C1输入=A1&+&B1 如何根据A1得出B1和C1？ 在B1输入=LEFT(A1,FIND(+,A1)-1) 分析：思路是从左边开始，截取“+”号前 的位数。 在C1输入=RIGHT(A1,LEN(A1)- FIND(+,A1) 分析：思路是截取“+”号后的位数。 函数名语法函数结构函数作用 IFIF(logical_test,value_if_true,value_if_false)=if(a20,1,0)当a20时，显示1，否则显示0 IFREEOR 第一个参数一般为一个函数，当这个函数运行是个错误值 时（#N

11、/A #DIV/0! #N/A #VALUE! #NAME #NULL! #NUM! #REF!），运行结果等于第二个参 数 =iferror(vookup(A1, A:B,2,0),0) 如果VLOOKUP函数运行值是 个错误值的话（找不到数据）， 就等于0，否则，就等于 VLOOKUP函数运行的值。 AND AND(logical1,logical2, .) Logical1, logical2, . 表示待检测的 1 到 30 个条件 值，各条件值可为 TRUE 或 FALSE。 =and(a30,b30,并且b30,b40,或者b4=90,A,I F(K4=80,B,IF (K4=70

12、,C,D ) 嵌套了两个IF函数 IFIF函数的应用实例函数的应用实例 函数名语法函数结构函数作用 SUMIF SUMIF(range,criteria,sum_range) 其中：Range 为用于条件判断的单元格 区域。 Criteria 为确定哪些单元格将被相加求 和的条件，其形式可以为数字、表达式或文 本。例如，条件可以表示为 32、32、 32 或 apples。 Sum_range 是需要求和的实际单元格。 =sumif(B:B, H1,E:E) 当B列满足了条件 （HI），对应的E 列的单元格的数值 之和 GO ROW 求余数的函数，当第二个参数为2时，常用 于判断奇偶数，第二个

13、参数为2时，运行结 果为0是偶数，1是奇数 =ROW(5，2) 求5除以2的余数是 多少，本例的运行 结果是1 常用函数之求和类函数常用函数之求和类函数 应用实例应用实例 从身份证号码中提取性别 l （身份证倒数第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时，即 为奇数，奇数就是男性，否则为女性。 函数名语法函数结构函数

14、作用 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的年数 常用函数之日期类函数常用函数之日期类函数 函数名 以单元格b1为2013- 4-5为例的运行结果 函数结构函数作用 WEEKDAY6=weekday(b1,2)判断b1为星期几 DAY5=day(b1)显示b1的号数 MONTH4=month(b1)显示b1的月数 YEAR2013=year(b1)显

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

16、 C2=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”)&“个月 常用函数之日期类函数常用函数之日期类函数 l应用实例3 从出生日得出年龄 在D2单元格中输入=DATEDIF(C2,NOW(),“y”) 因为从身份证提取出生日的C2 =MID(B2,7,4)&- &MID(B2,11,2

17、)&-&MID(B2,13,2) 所以若直接从身份证得出年龄 D2= DATEDIF(MID(B2,7,4)&-&MID(B2,11,2)&- &MID(B2,13,2),NOW(),y) 培训协议服务期限完成提醒培训协议服务期限完成提醒 思路：过期了可以显示“协议期限完成” 没过期的，显示“没过期” =IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 协议期限完成, ),没过期) 分析：DATEDIF(B2,TODAY(),“d”)可以 求出今天到B2的日期共多少天，如果“今天” 在B2之前，就会产生错误值，如果“今天”在 B2之后，这个函数就会算出B2距离“今天”有

18、多少天。当这个天数0时，就会执行IF函数， 显示“协议期限完成”，当DATEDIF运行是一 个错误值时，这时候就执行IFERROR函数， 显示“没过期”。 如果我想到期 前一个月就提 醒呢？ 应用实例应用实例 思路： =IFERROR(IF(DATEDIF(TODAY(),B2, D)=50) 20岁年龄40岁的人数： =COUNTIF(F:F,=20)-COUNTIF(F:F,=40) （注意：如果要统计的数据是有一个绿色的左角的，（注意：如果要统计的数据是有一个绿色的左角的， 要先选择要统计的数据，点击要先选择要统计的数据，点击 符号，选择符号，选择 “转换为数字转换为数字”，不然是统计不了个数的），不然是统计不了个数的）GO 课程大纲课程大纲 数据录入 单元格的引用 数据有效性 条件格式 认识Excel函数 Excel函数的结构 常见的几类函数 应用实例 认识数据透视表 数据透视表是什么 数据透视表的几个简单应用实例 数据透视表是什么/数据透视表的几个简单应用实例 认识数据透视表认识数据透视表 一种快速分析数据的工具 l 数据透视表是一种交互，交叉制作的报表，一组具有多个字段的数据进行多立体的分析 汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和比较大量数据。当 数据规模比较大时，这种分析的意义就显得尤为突出。 说人话 这些数据使用数段表进行管理 认识数据透视表