Excel在人力资源管理的应用.ppt

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

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

3、 GO 1-3 条件格式条件格式 利用突出的格式，经常能起到提醒的功能 如：突出不满勤的人GO 突出年龄大于30的人等 如何取消条件格式？认识Excel函数 2 2-1 EXCEL1 EXCEL函数的结构函数的结构 Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式特殊公式。Excel 函数结构:也有一些函数也有一些函数是没有参数的是没有参数的,如如NOW()左右括号成左右括号成对出现对出现 单一结构 嵌套结构 参数与参数之间参数与参数之间使用使用半角逗号半角逗号进进行分隔行分隔 函数名 语法 函数结构 函数作用 例子运行结果 1、Left 1、LEFT(text,num_c

4、hars)其中：其中：Text 是包含要提取字符的文本字符是包含要提取字符的文本字符串。串。Num_chars 指定要由指定要由 LEFT 所提取所提取的字符数。的字符数。=LEFT(A1,2)从从A1A1最左边最左边向右取两位向右取两位 2、Right 2、RIGHT(text,num_chars)其中：其中：Text 是包含要提取字符的文本字符是包含要提取字符的文本字符串。串。Num_chars 指定希望指定希望 RIGHT 提取的提取的字符数。字符数。=RIGHT(A1,2)从从A1A1最右边最右边向左取两位向左取两位 3、Mid 3、MID(text,start_num,num_cha

5、rs)其中：其中：Text 是包含要提取字符的文本字符是包含要提取字符的文本字符串。串。Start_num 是文本中要提取的第一是文本中要提取的第一个字符的位置。个字符的位置。Num_chars 指定希望指定希望 MID 从文本中返回字符的个数。从文本中返回字符的个数。=MID(A1,2,3)从从A1A1左边第左边第2 2位开始取位开始取3 3位位 4、&（连接符号）（连接符号）XX&XX =A2&B2 将将A2A2和和B2B2内内容合并容合并在一起在一起 应用实例 从身份证号码提取出生日期从身份证号码提取出生日期 在C2单元格中输入 =MID(B2,7,4)&-&MID(B2,11,2)&-

6、&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)在在c6c6中查找龙，中查找龙，结果显示龙在结果显示龙在c6c6中的位数，中的位数，查不到显示查不到显示#VALUE!#VALUE!常用函数之查找类函数常用函数之查找类函数 函数函

7、数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 常用函数之查找类函数常

8、用函数之查找类函数 函数函数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个参数。个参数

9、。函数名函数名 语法语法/例子例子 函数结构函数结构 函数作用函数作用 Value=value(b1)将将b1数值化数值化 Numberstring=NumberString(7890,1）运行结果：七千八百九十=NumberString(7890,2)运行结果：柒仟捌佰玖拾=NumberString(7890,3)运行结果：七八九=numberstring(b1,2)将将b1数字转为大写数字转为大写汉字汉字 Row=ROW(reference)其中：其中：ReferenceReference 为需为需要得到其行号的单元要得到其行号的单元格或单元格区域。格或单元格区域。=row(b1)求求b1

10、是第几行是第几行（值为值为1)Column=COLUMN(reference)=column(b1)求求b1是第几列是第几列(值值为为2)函数名函数名 语法语法 函数结构函数结构 函数作用函数作用 len=LEN(text)=LEN(text)其中：其中：TextText 是要查找其是要查找其长度的文本。空格将长度的文本。空格将作为字符进行计数。作为字符进行计数。=len(C2)计算计算C2的字符长的字符长度度 rank RANK(number,ref,order)RANK(number,ref,order)NumberNumber 为需要找到排位为需要找到排位的数字。的数字。RefRef 为

11、数字列表数组或为数字列表数组或对数字列表的引用。对数字列表的引用。Ref Ref 中的非数值型参中的非数值型参数将被忽略。数将被忽略。OrderOrder 为一数字，指明排为一数字，指明排位的方式。位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)计算计算b1在在b1:b10区域中从大到区域中从大到小排第几名；小排第几名；计算计算b1在在b1:b10区域中从小到区域中从小到大排第几名。大排第几名。应用实例 在C1输入=A1&+&B1 如何根据如何根据A1得出得出B1和和C1？在B1输入=LEFT(A1,FIND(+,A1)-1)分析：思路是从左边开始，截取“分析

12、：思路是从左边开始，截取“+”号前的位数。”号前的位数。在C1输入=RIGHT(A1,LEN(A1)-FIND(+,A1)分析：思路是截取“分析：思路是截取“+”号后的位数。”号后的位数。如何根据如何根据A1、B1得得出出C1？函数名函数名 语法语法 函数结构函数结构 函数作用函数作用 IF IF(logical_test,value_if_true,value_if_false)=if(a20,1,0)当当a20时，显示时，显示1，否则，否则显示显示0 IFREEOR 第一个参数一般为一个函数，当这个第一个参数一般为一个函数，当这个函数运行是个错误值时（函数运行是个错误值时（#N/A#N/A

13、#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(a3

14、0,b30,并且并且b30,b40,或者或者b4=90,A,IF(K4=80,B,IF(K4=70,C,D)嵌套了两个IF函数 分数段分数段 等级等级 分数90分 A 80分分数90分 B 70分分数80分 C 分数70分 D 函数名函数名 语法语法 函数结构函数结构 函数作用函数作用 SUMIF SUMIF(range,criteria,sum_range)其中：Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。Sum_range 是需要求和的实际单元格。=s

15、umif(B:B,H1,E:E)当当B列满足了条列满足了条件（件（HI），对），对应的应的E列的单元列的单元格的数值之和格的数值之和 GO ROW 求余数的函数，当第二个参数为求余数的函数，当第二个参数为2时，时，常用于判断奇偶数，第二个参数为常用于判断奇偶数，第二个参数为2时，运行结果为时，运行结果为0是偶数，是偶数，1是奇数是奇数 =ROW(5，2)求求5除以除以2的余数是的余数是多少，本例的多少，本例的运行结果是运行结果是1 应用实例 从身份证号码中提取性别从身份证号码中提取性别 （身份证倒数第2位若为偶数为女性，奇数为男性）在C2单元格中输入=IF(MOD(MID(B2,17,1),2

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