HR必备EXCEL人力资源管理函数说明Word文件下载.docx
- 文档编号:20014906
- 上传时间:2023-01-15
- 格式:DOCX
- 页数:14
- 大小:23.44KB
HR必备EXCEL人力资源管理函数说明Word文件下载.docx
《HR必备EXCEL人力资源管理函数说明Word文件下载.docx》由会员分享,可在线阅读,更多相关《HR必备EXCEL人力资源管理函数说明Word文件下载.docx(14页珍藏版)》请在冰豆网上搜索。
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单元格里分别输入了“张”、“李”、“陈”、“王”和“滕”。
=LOOKUP(1.1,A1:
C5,B1:
B5)
由于A列中没有1.1,故找到小于1.1的最大值1,然后返回同一行中B列的值(张)
=LOOKUP(5,A1:
查找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”,﹛"
1;
"
2;
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行的值(张)
E2,2,FALSE)
查找A列中的5,然后返回同一列中第2行的值(王)
=HLOOKUP(3,{1,2,3,"
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单元格里分别输入下面表格左侧的公式,按<
Enter>
键确认,观察出现结果。
=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()
=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)
单击任意一个单元格,然后输入表格里的任意一个公式,按<
Ente>
键确认,系统将自动显示对应日期。
=YEAR(NOW())
返回当前年份
=MONTH(NOW())
返回当前月份
=DAY(NOW())
返回当前日期
返回当前时间
=NETWORKDAY(TODAY(),EOMONTH(TODAY(),0))
放回当前时间距离月末还有多少工作人哦
=WORKDAY(TODAY(),15)
返回当前时间15天以后的工作时间
=NOW()+TIME(3,30,0)
返回当前时间丧再加3个半小时
人事信息数据表
公式=A2&
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 人力资源 管理 函数 说明