HR必备EXCEL人力资源管理函数说明.docx
- 文档编号:6993102
- 上传时间:2023-01-15
- 格式:DOCX
- 页数:14
- 大小:23.44KB
HR必备EXCEL人力资源管理函数说明.docx
《HR必备EXCEL人力资源管理函数说明.docx》由会员分享,可在线阅读,更多相关《HR必备EXCEL人力资源管理函数说明.docx(14页珍藏版)》请在冰豆网上搜索。
HR必备EXCEL人力资源管理函数说明
HR必备EXCEL人力资源管理函数说明
培训成绩统计分析表
IF函数
函数用途
此函数又称条件函数,改函数用于对目标区域进行判断,返回真假逻辑值,据此输出相应的结果
函数语法
IF(logical test,value if true,value if false)
IF(条件表达式(看也是一个比较式或逻辑式),当条件表达式的逻辑值为TRUE时的返回值 ,当条件表达式的逻辑值为FALSE是的返回值)
函数说明
利用IF函数可以构造IF函数嵌套来完成复杂的功能,但是IF函数最多只能嵌套7层。
IF函数还可以喝数组相结合,借此能够进行工作所需的计算。
函数简单示例
现假设在A1单元格里输入数字15,在B1单元格里进行如下的操作,观察出现的结果。
公式:
=IF(A1>10,"真","假")
说明(结果):
A1单元格里的数字为“15”,大于“10”,因此逻辑值为真(Y)
公式:
=IF(A1<=SUM(2,3),,"Y","N")
说明(结果):
SUM(2,3)的计算结果为“5”,小于A1单元格里的:
15“,因此逻辑值为假(N)
RANK函数
函数用途
次函数的功能是在某个区域对目标数字进行排位计算
函数语法
RANK(number,ref,order)
RANK(目标数字,要进行排行的区域,何种排位的方式(order为0或者省略,系统会按降序排列目标数字排位;相反,当order不是0,系统会按升序排列对目标数字排位。
)
函数说明
在应用RANK函数的过程中,当存在重复时数会影响后面的排位。
函数简单示例
先在假设A1到A7单元格里分别输入如下数字:
3、1、2、3、5、7、6.在B1单元格里进行如下的操作,观察出现的结果。
公式:
=RANK(A4,A1:
A7,1)
说明(结果):
数字3在列表里排列第3,但因为出现了两次,影响了数字5的排位,因此数字5在A1到A7单元格列表里升序排位为5(5)
公式:
=RANK(A5,A1:
A7,0)
说明就(结果)7在A1到A7单元格的数字列表里降序排位为1
(1)
员工销售奖金计算表
VLOOKUP函数
函数用途
此函数的功能是在给定区域的首列里查找目标数值,然后返回目标数值所在行里某一列的相关数值。
函数语法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP(目标数值,给定区域,制定返回给定区域中某一列的序号,用来规定VLOOKUP函数查找类型)
函数说明
range_lookup为TRUE或省略,VLOOKUP函数将进行近似匹配查找;range_lookup为FALSE或0,VLOOKUP函数将进行精确匹配查找。
range_lookup为TRUE或省略时,给定区域内首列值要以生序排列。
注意要查找的目标数值的大小应该是比查找区域内的最小值大,否则应用VLOOKUP函数时会显示错误值。
函数简单示例
在A1至A5单元格里分别输入了“1”、“4.3”、“2.9”、“5”和”2“,在B1至B5单元格里分别输入了“第一名”、“第四名”、“第三名”、“第五名”和“第二名”,在C1只C5单元格里分别输入了“张”、“李”、“陈”、“王”和“滕”。
接着在D5单元格里输入如下公式,观察出现结果。
公式:
=VLOOKUP(1,A1:
C5,2,FALSE)
说明(结果):
使用精确匹配查找A1:
C5单元格区域中首列,即A列中的数值“1”,然后返回同一行中B列的值(第一名)
公式:
=VLOOKUP(3,A1:
C5,3,TRUE)
说明(结果):
使用近似匹配查找A列中的数值“3”,在A列中找到小于3的最大值2.9.然后返回同一行中C列的值(陈)
LOOKUP函数
函数用途
此函数的功能是事先确定两个单行或单列区域,将其中的一个作为查找区域,然后进行目标数值查找,最后返回目标数值所在列或行与另一个区域香蕉的单元格内容。
LOOKUP函数具有两种语法形式,即向量形式和数组形式。
函数语法
向量形式:
LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP(目标数值,查找区域,输出结果的区域(其构成和查找区域相同,即同为单行或单列))
数组形式:
LOOKUP(lookup_value,array)
LOOKUP(查找的目标数值,查找区域)
函数说明
不同于VLOOKUP函数实现需要规定近似或精确匹配查找,LOOKUP函数默认为精确匹配查找,当查找不到目标数值是LOOKUP函数开始近似匹配查找。
函数简单示例
向量形式。
现假设在A1至A5单元格里分别输入了“1”、“4.3”、“2.9”、“5”和”2“,在B1至B5单元格里分别输入了“第一名”、“第四名”、“第三名”、“第五名”和“第二名”,在C1只C5单元格里分别输入了“张”、“李”、“陈”、“王”和“滕”。
接着在D5单元格里输入如下公式,观察出现结果。
公式:
=LOOKUP(1.1,A1:
C5,B1:
B5)
说明(结果):
由于A列中没有1.1,故找到小于1.1的最大值1,然后返回同一行中B列的值(张)
公式:
=LOOKUP(5,A1:
C5,B1:
B5)
说明(结果):
查找A列中的5,然后返回同一行中B列的值(王)
数组形式。
数组中的值必须以升序顺便放置;…,-2,-1,0,1,2,…;A-Z;FALSE,TRUE.否则LOOKUP函数无法提供正确的值。
现在在D5单元格里输入如下公式,观察出现结果。
公式:
=LOOKUP(“c”,﹛"a","b","c","d";1,2,3,4﹜)
说明(结果):
在数组找到第一行中查找“c”,查找小于或等于它(“c”)的最大值,然后返回同一列内最后一行中的值(3)
公式:
=LOOKUP(“bump”,﹛"a",1;"b",2;"c",3"﹜)
说明(结果):
在数组第一行中查找“c”,查找小于或等于它(“b“)的最大值,然后返回同一行内最后一列中的值
(2)
HLOOKUP函数
函数用途
此函数的功能是在给定区域的首行里查找目标数值,然后返回目标数值所在列某一行的相关值。
显然该函数与VLOOKUP函数相似,无非一个是在首行里查找,一个是在首列里查找。
类似于LOOKUP函数,HLOOKUP函数也具有两种语法形式,即向量形式和数组形式。
下面简答介绍一下向量形式的函数语法,至于数组形式的函数语法,可以参阅LOOKUP函数的数组形式函数语法。
函数语法
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HLOOKUP(要查找的目标数值,给定区域{系统将在给定区域的首行中查找目标数值},返回给定区域中某一行的序号)
函数说明
range_lookup为TURE或省略,HLOOKUP函数将进行近似匹配查找,range_lookup为FALSE或0,HLOOKUP函数将进行精确匹配查找。
注意要查找的目标数值的大小应该是在查找区域数值的范围内,否则应用HLOOKUP函数是会显示错误值。
函数简单示例
现假设在A1至E1单元格里分别输入了“1”、“4.3”、“2.9”、“5”和”2“,在A2至E2单元格里分别输入了“张”、“李”、“陈”、“王”和“滕”。
接着在A3单元格里输入如下公式,观察出现结果。
公式:
=HLOOKUP(1.1,A1:
E2,2,TRUE)
说明(结果):
由于1列中没有1.1,故找到小于1.1的最大值1,然后返回同一列中第2行的值(张)
公式:
=HLOOKUP(1.1,A1:
E2,2,FALSE)
说明(结果):
查找A列中的5,然后返回同一列中第2行的值(王)
公式:
=HLOOKUP(3,{1,2,3,"a","b","c","d","e","f"},2,TRUE)
说明(结果):
该例为HLOOKUP函数在数组形式里的应用。
在数组常量的第一行中查找3,并返回同列中第2行的值(c)
带薪年假天数统计表
DATEDIF函数
函数用途
此函数的功能计算起始日期和截止日期之间的天数、月数或年数。
函数语法
DATEDIF(start_date,end_date,unit)
DATEDIF(起始日期,截止日期,返回类型)
函数简单示例
在B3单元格里输入下列公式,,观察出现结果。
公式:
=DATEDIF(“2001/1/1”,“2003/1/1”,“Y”)
说明(结果):
在起始日期和截止日期中有两个整年
(2)
公式:
=DATEDIF(“2001/1/1”,“2003/1/2”,“M”)
说明(结果):
在起始日期和截止日期中有24个月(24)
公式:
=DATEDIF(“2001/6/1”,“2002/8/15”,“D”)
说明(结果):
在2001年6月1日和2002年8月15日之间有440天(440)
公式:
=DATEDIF(“2001/6/1”,“2002/8/16”,“YD”)
说明(结果):
在6月1日与8月15日之间有75天,忽略日期中的年(75)
公式:
=DATEDIF(“2001/6/1”,“2002/8/17”,“MD”)
说明(结果):
计算开始日期1和结束日期15之间的差,忽略日期中的年和月(14)
加班统计表
TEXT函数
函数用途
此函数的功能是一句需要目标数字转换为指定的文本格式。
函数语法
TEXT(value,format_text)
TEXT(目标数值(可以为数字可以为计算结果),需要设定的文本格式)
函数简单示例
在A2和A3单元格里分别输入“工业”和“服务业”,在B2和B3单元格里分别输入“45%”和“1000亿”。
然后在C4和C5单元格里分别输入下面表格左侧的公式,按
公式:
=A2&"总占GDP比重"&TXET(B2,"0%")
说明(结果):
将A2和B2单元格里的内容以及引号间的内容合并为一句(工业占总GDP比重的45%)
公式:
=A3&"的GDP为"&TEXT(B3,"")
说明(结果):
将A3和B3单元格里的内容以及引号间的内容合并为一句(服务业的GDP为1000亿)
ROUND函数
函数用途
此函数的功能是设置指定位数,按此位数对数字进行四舍五入去整,然后返回相应的结果。
函数语法
ROUND(number,num_digits)
TEXT(目标数字,指定的位数(系统按此位数进行四舍五入))
函数说明
如果num_digits小于、等于或者大于0,都会有不同的结果
函数简单示例
在Excel任意一个单元格输入以下公式,观察出现的结果
公式:
=ROUND(3.1415,1)
说明(结果):
将3.1415四舍五入到一位小数(3.1)
公式:
=ROUND(3.1415,0)
说明(结果):
将3.1415四舍五入到整数(3)
公式:
=ROUND(314.15,-1)
说明(结果):
将3.1415四舍五入到小数点左侧以为(310)
YEAR函数
函数用途
此函数的功能是返回给定日期所属的年份。
函数语法
YEAR(serial_number)
YEAR(给定日期)
函数简单示例
在A1单元格里输入“2007-8-2”,在D5单元格里输入以下公式,观察出现的结果。
公式:
=YEAR(A1)
说明(结果):
A1单元格内日期的年份(2007)
MONTH函数
函数用途
此函数的功能是返回给定日期所属的月份。
函数语法
MONTH(serial_number)
MONTH(给定日期)
函数说明
应用MONTH函数时,应注意给定日期的输入格式。
函数简单示例
在A1单元格里输入“=DATE(2007,8,2)”,在D4单元格里输入以下公式,观察出现的结果。
公式:
=MONTH(A1)
说明(结果):
A1单元格内日期的月份(8)
在A2单元格里输入:
2007-9-2“,在D5单元格里输入以下公式,观察出现的结果。
公式:
=MONTH(A2)
说明(结果):
A2单元格内日期的月份(9)
DAY函数
函数用途
此函数的功能是返回给定日期的具体天数。
函数语法
DAY(serial_number)
DAY(给定日期)
函数简单示例
在A1单元格里输入“2007-9-2”,在D5单元格里输入以下公式,观察出现的结果。
公式:
=DAY(A1)
说明(结果):
A1单元格内日期的天数
(2)
NOW函数
函数用途
此函数的功能是显示系统当前的时间。
函数语法
NOW()
函数简单示例
在Excel任意一个单元格里输入以下公式,观察出现的结果。
公式:
=NOW()
说明(结果):
返回当前的系统日期(2012-12-7 16:
45)
TODAY函数
函数用途
此函数的功能是显示系统当前日期。
函数语法
TODAY()
函数简单示例
在Excel任意一个单元格里输入以下公式,观察出现的结果。
公式:
=TODAY()
说明(结果):
返回当前的系统日期(2012-12-7)
NETWORKDAYS函数
函数用途
此函数的功能是计算两个给定参数间的工作日数值。
函数语法
NETWORKDAYS(start_date,end_date,hoildays)
NETWORKDAYS(起始时间,终止时间,假日)
函数说明
应用NETWORKDAYS函数时应注意给定日期的输入格式。
操作NETWORKDAYS函数时,若该函数不可用,则显示#NAME?
错误值,此时,需分要加载相应的宏,具体方法如下:
依次单击菜单“工具”→“加载宏”淡出一个对话框,勾选“分析工具库”复选框,然后单击“确定”按钮。
函数简单示例
在A1单元格里输入“=DATE(2007,8,2)作为开始日期,在A2单元格里输入“=DATE(2008,3,2)”作为结束日期,在A3单元格里输入“=DATE(2007,10,13)”作为假日在D4单元格里输入以下公式,观察出现的结果。
公式:
=NETWORKDAYS(A1,A2)
说明(结果):
计算开始日期和终止日期之间间隔工作日的数值(152)
公式:
=NETWORKDAYS(A1,,A2,A3)
说明(结果):
计算开始日期和终止日期之间间隔工作日的数值,同时扣除A3单元格里的假日(151)
EOMONTH函数
函数用途
此函数的功能是给定一个起始日期,再辅以一个指定数字,将一个指定数字,将起始日期的月份向前或向后拨动该数字大小距离,最后返回所得月份的最后一天日期。
函数语法
EOMONTH(start_date,months)
EOMONTH(起始日期,制定数字(正数表示向未来移动的月数,负数表示向过去移动的月数))
函数说明
应用EOMONTH函数时应注意给定日期的输入格式。
操作EOMONTH函数一样,若EOMONTH函数第一次不能使用,也要进行加载宏的操作。
函数简单示例
在A1单元格里输入“=DATE(2007,8,2),在D4单元格里输入以下公式,观察出现的结果。
注:
若显示的结果为“39386”,此时则可通过调整单元格格式来获得日期格式。
公式:
=EOMONTH(A1,2)
说明(结果):
计算“2007年8月2日”该日期两个月后所在月份的最后一天的日期(2007-10-31)
WORKDAY函数
函数用途
此函数的功能是返回某一个日期前后相隔制定工作日的日期。
函数语法
WORKDAY(start_date,days,holiday)
WORKDAY(起始日期,自起始日期起推算的天数(当该值为正值时意味着将来的日期,当该值为负值时意味着过去的日期,并且推算中是不包含共假日的),要剔除的日期(可以自定义的日期,也可以定义公休假日))
函数说明
应用WORKDAY函数时应注意给定日期的输入格式。
操作WORKDAY函数一样,若WORKDAY函数第一次不能使用,也要进行加载宏的操作。
函数简单示例
在B1单元格里输入“=DATE(2007,8,2),在B2单元格里输入"179"作为工作天数,在B3单元格里输入“=DATE(2007,10,17)”作为假日;在D4单元格里输入以下公式,观察出现的结果。
注:
若显示的结果为“39547”,此时则可通过调整单元格格式来获得日期格式。
公式:
=WORKDAY(B1,B2)
说明(结果):
从起始日期开始计算179个工作日后的日期(2008-4-9)
公式:
=WORKDAY(B1,B2,B3)
说明(结果):
从起始日期开始计算179个工作日后的日期,期间还要扣除B3单元格里的假日(2008-4-10)
TIME函数
函数用途
此函数的功能是返回某一个特定时间的小数值。
函数语法
TIME(hour,minute,second)
TIME(小时,分钟,秒)【三个数值都在在区间(0,32767)内取值】
函数简单示例
在E1单元格里输入“8”作为小时,在F1单元格里输入“12”作为分钟,在G1单元格里输入“0”作为秒;在G4单元格里输入以下公式,观察出现的结果。
公式:
=TIME(E1,F1,G1)
说明(结果):
将E1、F1和G1单元格里的数字构成时间形式(8:
12AM)
单击任意一个单元格,然后输入表格里的任意一个公式,按
公式:
=YEAR(NOW())
说明(结果):
返回当前年份
公式:
=MONTH(NOW())
说明(结果):
返回当前月份
公式:
=DAY(NOW())
说明(结果):
返回当前日期
公式:
=TODAY()
说明(结果):
返回当前日期
公式:
=NOW()
说明(结果):
返回当前时间
公式:
=NETWORKDAY(TODAY(),EOMONTH(TODAY(),0))
说明(结果):
放回当前时间距离月末还有多少工作人哦
公式:
=WORKDAY(TODAY(),15)
说明(结果):
返回当前时间15天以后的工作时间
公式:
=NOW()+TIME(3,30,0)
说明(结果):
返回当前时间丧再加3个半小时
人事信息数据表
TEXT函数
函数用途
此函数的功能是一句需要目标数字转换为指定的文本格式。
函数语法
TEXT(value,format_text)
TEXT(目标数值(可以为数字可以为计算结果),需要设定的文本格式)
函数简单示例
在A2和A3单元格里分别输入“工业”和“服务业”,在B2和B3单元格里分别输入“45%”和“1000亿”。
然后在C4和C5单元格里分别输入下面表格左侧的公式,按
公式=A2&"总占GDP比重"&TXET(B2,"0%")
说明(结果):
将A2和B2单元格里的内容以及引号间的内容合并为一句(工业占总GDP比重的45%)
公式:
=A3&"的GDP为"&TEXT(B3,"")
说明(结果):
将A3和B3单元格里的内容以及引号间的内容合并为一句(服务业的GDP为1000亿)
MID函数
函数用途
此函数的功能是在目标数字符串中指定一个开始位置,按设定的数值返回该字符串中的相应数目字符内容。
函数语法
MID(text,start_num,num_chars)
MID(目标字符串,字符串开始的位置{通常start_num是从字符串的首个字符编号,依次为1、2、3...start_num有取值范围,不能大于字符串的长度,不能小于1},设定的数目{MID函数将按次数目返回相应的字符个数,显然该数值不为负,否则函数将输出错误值。
})
函数简单示例
在A2输入“wonderful”然后在B3单元格里输入下列公式,观察出现结果。
公式:
=MID(A2,1,5)
说明(结果):
提取A2单元格里字符串中的5个字符,且从第一个字符开始提取(wonde)
公式:
=MID(A2,5,20)
说明(结果):
提取A2单元格里字符串中的20个字符,且从第7个字符开始提取.因为A2单元格中的字符串中总字符数是9,所以最多只能从第7个字符开始,将余下的所有字符都提取(erful)(wonde)
公式:
=MID(A2,10,2)
说明(结果):
因为A2单元格中的字符串中总字符数是9,此时要从第10个字符开始,所以系统返回空格()
公式:
=MID(A2,0,2)
说明(结果):
因为start_num小于1,所以系统返回错误值(#VALUE!
)
LEN函数
函数用途
此函数的功能是计算目标字符中的字符数。
函数语法
LEN(text)
LEN(目标字符串)
函数说明
空格也视为字符,加到字符串的字符数中。
函数简单示例
在A3单元格里输入“wonderful”,在A4单元格里输入“Excel word”然后在B4单元格里输入下列的公式,观察出现结果。
公式=LEN(A3)
说明(结果):
返回A3单元格里的字符串的长度(9)
公式:
=LEN(A4)
说明(结果):
返回A4字符串的长度,包含一个空格的位置(10)
LEFT函数
函数用途
此函数的功能是从字符串的左侧开始,按照指定的数值返回相应的字符内容。
函数语法
LEFT(text,num_chars)
LEFT(目标字符串,指定的数值{该数值必须大于等于0})
函数简单示例
在A3单元格里输入“wonderful”,然后在B4单元格里输入下列的公式,观察出现结果。
公式=LEFT(A3,4)
说明(结果):
对A3单元格里的字符串,按从左到右提取前4位字符(wond)
公式:
=LEFT(A3)
说明(结果):
因为省略了num_chars,因此系统假定其为1,将提取A3单元格里字符串左侧的首字符(w)
公式=LEFT(A3,11)
说明(结果):
因为指定指数大于目标字符串的总数,所以系统提取目标字符串的所有字符(wonderful)
RIGHT函数
函数用途
此函数的功能是从字符串的右侧开始,按照指定的数值返回相应的字符内容。
该函数恰好与LEFT函数的功能相反。
函数语法
RIGHT(text,num_chars)
RIGHT(目标字符串,指定的数值{该数值必须大于等于0})
函数简单示例
在A3单元格里输入“wonderful”,然后在B5单元格里输入下列的公式,观察出现结果。
公式:
=RIGHT(A3
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- HR 必备 EXCEL 人力资源 管理 函数 说明