人力资源管理工作常用EXCEL函数教程.xls
- 文档编号:1780831
- 上传时间:2023-05-01
- 格式:XLS
- 页数:20
- 大小:131.50KB
人力资源管理工作常用EXCEL函数教程.xls
《人力资源管理工作常用EXCEL函数教程.xls》由会员分享,可在线阅读,更多相关《人力资源管理工作常用EXCEL函数教程.xls(20页珍藏版)》请在冰点文库上搜索。
人人力力资资源源及及行行政政管管理理中中的的EXCELEXCEL函函数数应应用用序序号号函函数数类类别别分分序序号号函函数数名名称称用用途途1数学1.1SUM(number1,number2,)计算某个目标区域中单元格里数字的总和1.2ROUND(number,num_digits)设定指定位数,按此位数对目标数字进行四舍五入取整1.3MOD(number,divisor)对目标值除以指定数后的余数,余数的符号和除数相同。
1.4INT(number)对目标数字进行舍入处理,处理的结果是得到小于目标数的最大整数1.5ROUNDUP(number,num_digits)对目标数字按照指定的条件进行相应的舍入处理1.6SUMPRIDUCT(array1,array2,)对几组给定的数组对应的元素相乘,所得结果再进行加总2查询和引用2.1VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在给定区域的首列里查找目标数值,然后返回目标数值所在行里某一列的相关值。
2.2HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)在给定区域的首行里查找目标数值,然后返回目标数值所在列里某一行的相关值。
2.3向量形式:
LOOKUP(lookup_value,lookup_vector,result_vector)事先确定两个单行或单列区域,将其中的一个作为查找区域,然后进行目标数值查找,最后返回目标数值所在列或行与吃一堑一个区域相关的单元格内容。
数组形式:
lOOKUP(lookup_value,array)2.4COLUMN(reference)返回目标单元格或单元格区域(可返回列序号)2.5ROW(reference)返回目标单元格或单元格区域的行序号2.6数组形式:
INDEX(array,row_num,column_num)按照相关条件返回目标区域里的值引用形式:
INDEX(reference,row_num,column_num,area_num)2.7OFFSET(reference,rows,cols,height,width)将给定的区域作为目标引用区域,同时设定一个偏移量,然后依据该偏移量得到新的引用,最终的结果包括单元格和单元格区域的形式。
3文本3.1TEXT(value,format_text)依据需要将目标数字转换为指定的文本格式3.2MID(text,start_num,num_chars)在目标字符串中指定一个开始位置,按设定的数值返回该字符串中的相应数目字符内容。
3.3LEN(text)计算目标字符中的字符数3.4LEFT(text,num_chars)从字符串的左侧开始,按照指定的数值返回相应数目的字符内容。
3.5RIGHT(text,num_chars)从字符串的右侧开始,按指定的数值返回相应数目的字符内容。
3.6FIND(find_text,within_text,start_num)查找字符串中首字符编号为1,接着对余下的字符按顺序依次进行编号,然后在查找字符串中查找目标字符,并且返回目标字符在查找字符串中的编号。
3.7SUBSTITUTE(text,old_text,new_text,instance_num)将目标字符或字符串替换到需要被替换的字符串中。
4统计4.1RANK(number,ref,order)在某个区域内对目标数字进行排位计算。
4.2COUNTIF(range,criteria)在目标区域中统计满足预设条件的单元格数目。
4.3FREQUENCY(data_array,bins_array)计算目标数值在某个区域内出现的次数,然后返回一个重直组5日期和时间5.1YEAR(serial_number)返回给定日期所属的年份5.2MONTH(serial_number)返回给定日期所属的月份5.3DAY(serial_number)返回给定日期所属的目体天数第1页,共20页5日期和时间5.4NOW()显示系统当前的日期和时间5.5TODAY()显示系统当前的日期5.6NETWORKDAYS(start_date,end_date,holidays)计算两个给定参数间的工作日数值5.7EOMONTH(start_date,months)给定一个起始日期,再辅以一个指定数字,将起始日期的月份向前或向后拨运该数字大小距离,最后返回所得月份的最后一天日期。
5.8WORKDAY(start_date,days,holidays)返回某个日期前后相隔指定工作日的日期值5.9TIME(hour,minute,second)返回某一个特定时间的小数值5.9-1DATEDIF(start_date,end_date,unit)计算起始日期和截止日期之间的天数、月数和年数6逻辑6.1IF(logicaltest,valueiftrue,valueiffalse)用于对目标区域进行判断,返回真假逻辑值,据此输出相应的结果。
6.2NOT(logical)对单元格或单元格区域内的逻辑值求反6.3OR(logical1,logical2,)对指定区域内的逻辑值进行判断,当逻辑值为TRUE时返回TRUE,若逻辑值为FALSE则返回FALSE7信息ISERROR(value)判断测试内容是否为错8数据库DSUM(database,field,criteria)在指定区域查找符合条件2的区域,然后在查找到的区域内计算符合条件1的数值之和。
9行政管理9.1Excel抽奖器9.2公议室使用安排表9.3办公室布局平面图人人力力资资源源及及行行政政管管理理中中的的EXCELEXCEL函函数数应应用用序序号号函函数数类类别别分分序序号号函函数数名名称称用用途途第2页,共20页数数学学函函数数的的应应用用11、SUMSUM函函数数用途:
计算某个目标区域中单元格里数字的总和。
语法:
sum(number1,number2,)number1,number2,为需要计算的参数,number数一共可以有30个。
说明:
对于计算区域里的内容,其表达形式可以是数字也可以是逻辑值。
若计算的参数为数组形式,那么只计算数组中的数字;同样,若计算的参数为引用类型,那也只计算引用类型中的数字。
简单示例:
假设在B2:
B5区域里分别输入了“2”、“55”、“24”和“12”,然后在C5单元格里输入下列公式,观察出现的结果。
公式说明(结果)=sum(12,3)将数字12和数字3相加(15)=sum(B2:
B5)将B2至B5单元格里的数据相加(93)扩展知识点讲解一、快速获得简单合计的另外几种方法)1、单击“格式”工具栏中的“自动求和”按钮,此时在求和目标列的下方单元格自动显示求和公式,按键确认即可获得合计的结果。
2、在求和目标列的下方单元格按组合键,即显示求和公司,按键确认即可获得合计的结果。
3、选中求和目标列,在状态栏里(右下方)已显示合计的结果。
Excel2003在默认状态下此功能打开且为“求和”。
二、SUM函数计算区域的交叉合计、非连续区域合计1、交叉合计操作(指定区域公共部份求和)1234指定区域:
B24:
E26指定区域如方框示2345C23:
C27公共区域如黄色部份3456公式:
=sum(B24:
E26C23:
C27)4567注:
公式里“B24:
E26”和“C23:
C27”之间留有一个空格。
5678结果:
122、非连续区域合计操作(指定区域求和)指定区域:
B31:
B33指定区域如方框示1234D30:
E342345公式:
=sum(B31:
B33,D30:
E34)3456注:
指定的区域“B31:
B33”和“D30:
E34”之间要用逗号隔开。
4567结果:
645678三、SUM函数应用1、SUM函数直接求和2、SUM结合IF、COUNTIF函数编制中国式排名公式例:
P1003、应用内存数组公式计算带薪年假天数例:
P142第3页,共20页22、ROUNDROUND函函数数用途:
设置指定位数,按此位数对目标数字进行四舍五入取整,然后返回相应的结果。
语法:
ROUND(number,num_digits)number为目标数字。
num_digits为指定的位数,系统将按此位数进行四舍五入。
说明:
如果num_digits小于、等于或大于0,都会有不同的结果。
简单示例:
公式说明(结果)=ROUND(3.1415,1)将3.1415四舍五入到一位小数(3.1)=ROUND(3.1415,0)将3.1415四舍五入到整数(3)=ROUND(314.15,-1)将314.15四舍五入到小数点左侧一位(310)ROUND函数的应用1、应用ROUND结合TEXT函数舍入计算加班时间例:
P1092、应用ROUND函数计算应发工资例:
P13433、MODMOD函函数数用途:
求对目标值除以指定数后的余数,余数的符号和除数相同。
语法:
MOD(number,divisor)number为目标数值,作为被除数;divisor为指定数,作为除数。
说明:
当指定数为0时,系统将显示错误值#DIV/0!
。
简单示例:
32-2公式说明(结果)=MOD(B61,C61)3除以2余数为1,又因为除数2的符号为正
(1)=MOD(B61,D61)3除以-2余数为1,又因为除数-2的符号为负(-1)=MOD(B61,0)除数为0(#DIV/0!
)MOD函数的应用1、应用MOD函数编制工资条例:
P16244、INTINT函函数数用途:
对目标数字进行舍入处理,处理的结果是得到小于目标数的最大整数。
语法:
INT(number)number为需要处理的目标数字,也可以是含数字的单元格引用。
说明:
向下舍入简单示例:
公式说明(结果)=INT(3.1415)将数字3.1415向下舍入到最接近的整数(3)=INT(-3.1415)将数字-3.1415向下舍入到最接近的整数(-4)=INT(3.1415-INT(3.1415)先将第二个INT函数内的数字3.1415向下舍入到最接近的整数3,然后用数字3.1415减去该数字3,得到数字0.1415,最后当数字0.1415由第一个INT函数进行向下舍入(0)INT函数的应用1、应用INT结合ROUNDUP、SUM函数计算零钞例:
P170第4页,共20页55、ROUNDUPROUNDUP函函数数用途:
对目标数字接照指定的条件进行相应的舍入处理(不要求四舍五入的数字进位)语法:
ROUNDUP(number,num_digits)number为需要处理的目标数字。
num_digits为指定的条件,将决定目标数字处理后的结果位数。
说明:
num_digits小于、等于或大于0,都会有不同的结果。
简单示例:
公式说明(结果)=ROUNDUP(3.1415,0)向上舍入到最接近的整数(4)=ROUNDUP(3.1415,3)舍入到小数点千位上(3.142)=ROUNDUP(314.15,-2)在小数点左侧舍入两位(400)=ROUNDUP(314.15,-1)在小数点左侧舍入一位(320)=ROUNDUP(-3.1415,0)向上舍入到最接近的整数(-4)ROUNDUP函数的应用1、应用ROUNDUP函数计算零钞例:
P17066、SUMPRODUCTSUMPRODUCT函函数数用途:
将几组给定的数组对应的元素相乘,所得结果再进行加总。
语法:
SUMPRODUCT(array1,array2,array3,)array1,array2为给定数组,可以包含30个数组。
说明:
给定的数组应当是含用相同维数的数组,否则将返回错误值#VALUE!
。
对于不是数值形式,比如文本形式的元素,该函数将其统统视为0。
简单示例:
562332556732公式说明(结果)=SUMPRODUCT(B103:
B105,C103:
C105,D103:
D105)3个数组的所有元素对应相乘,然后将乘积相加,即5X6X2+3X2X5+6X7X3(216)=SUMPRODUCT(B103:
C105,C103:
E105)两组数组的所有元素对应相乘,然后将乘积相加,即5X2+3X5+6X3+6X3+2X5+7X2(85)SUMPRODUCT函数的应用1、应用SUMPRODUCT函数查询某年龄阶段例:
P246第5页,共20页查查询询和和引引用用函函数数的的应应用用11、VLOOKUPVLOOKUP函函数数用途:
在给定区域的首列里查找目标数值,然后返回目标数值所在行里某一列的相关值。
语法:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value为目标数值;table_array为给定区域,即查找区域,系统将在给定区域的首列中查找目标数值;col_index_num为指定返回给定区域中某一列的序号,比如该数值取3时,意思是返回给定区域中第三列的值。
range_lookup规定查找类型。
说明:
range_lookup为TRUE或省略,VLOOKUP函数将进行近似匹配查找;range_lookup为FALSE或0,VLOOKUP函数将进行精确匹配查找。
range_lookup为TRUE或省略时,给定区域内首列值要以升序排列。
注意要查找的目标数值的大小应该是比查找区域内的最小值大,否则应用VLOOKUP函数时会显示错误值。
简单示例:
1第一名张公式运算结果:
第一名第一名4.3第四名李陈2.9第三名陈5第五名王2第二名滕公式说明(结果)=VLOOKUP(1,B11:
D15),2,FLASE使用精确匹配查找B11:
D15单元格区域中首列,即B列中的数值“1”,然后返回同一行中C列的值(第一名)=VLOOKUP(3,B11:
D15),3,TRUE使用近似匹配查找A列中的数值“3”,在A列中找到小于3的最大值2.9,然后返回同一行中D列的值(陈)VLOOKUP函数的应用1、应用VLOOKUP函数查找销售额所符合奖金比例例:
P1262、应用VLOOKUP函数制作工资条例:
P15922、HLOOKUPHLOOKUP函函数数用途:
在给定区域的首行里查找目标数值,然后返回目标数值所在列里某一行的相关值。
(功能相似,VLOOKUP是首列查找,HLOOKUP是首行查找)语法:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)lookup_value为要查找的目标数值;table_array为给定区域,即查找区域,系统将在给定区域的首行中查找目标数值;row_index_num为指定返回给定区域中某一行的行号,比如该数值取2时,意味着返回给定区域中第二行的值。
说明:
range_lookup为TRUE或省略,HLOOKUP函数将进行近似匹配查找,range_lookup为FALSE或0,HLOOKUP函数将进行精确匹配查找。
注意要查找的目标数值的大小应该是在查找区域数值的范围内,否则应用HLOOKUP函数时会显示错误值。
简单示例:
14.32.952公式运算结果:
张王张李陈王滕公式说明(结果)=HLOOKUP(1.1,B35:
F36,2,TRUE)由于行1中没有1.1,故找到小于1.1的最大值1,然后返回同一列中第2行的值(张)第6页,共20页=HLOOKUP(5,B35:
F36,2,FALSE)查找A列中的5,然后返回同一列中第2行的值(王)=HLOOKUP(3,1,2,3;a,b,c,d,e,f,2,TRUE)该列为HLOOKUP函数在数组形式里的应用,在数组常量的第一行中查找3,并返回同列中第2行的值(C)33、LOOKUPLOOKUP函函数数用途:
功能是事先确定两个单行或单列区域,将其中的一个作为查找区域,然后进行目标数值查找,最后返回目标数值所在列或行与另一个区域相关的单元格内容。
语法:
1、向量形式:
LOOKUP(lookup_value,lookup_vector,result_vector)lookup_value为要查找的目标数值;lookup_vector为查找区域;result_vector为输出结果的区域,其构成和查找区域相同,即同为单行或单列。
2、数组形式:
LOOKUP(lookup_value,array)lookup_value为要查找的目标数值;array为查找区域,为数组形式,具体构成内容可以多样化。
说明:
不同于VLOOKUP函数事先需规定近似或精确匹配查找,LOOKUP函数默认为精确匹配查找,当查找不到目标数值时LOOKUP函数开始近似匹配查找。
简单示例:
1、向量形式1张运算结果:
张4.3李王2.9陈5王2滕公式说明(结果)=LOOKUP(1.1,B50:
B54,C50:
C54)由于B列中没有1.1,故找到小于1.1的最大值1,然后返回同一行中C列的值(张)=LOOKUP(5,B50:
B54,C50:
C54)查找B列中的5,然后返回同一行中C列的值(王)2、数组形式(注:
数组中的值必须以升序顺序设置)公式说明(结果)=LOOKUP(c,a,b,c,d;1,2,3,4)在数组的第一行中查找C,查找小于或等于它(C)的最大值,然后返回同一列内最后一行中的值(3)=LOOKUP(bump,a,1;b,2;c,3)在数组的第一行中查找bump,查找小于或等于它(b)的最大值,然后返回同一行内最后一列中的值
(2)44、COLUMNCOLUMN函函数数用途:
返回目标单元格或单元格区域语法:
COLUMN(reference)reference为目标单元格或单元格区域说明:
若reference省略,此时系统返回COLUMN函数所在单元格的列序号。
简单示例:
运算结果:
37公式说明(结果)=COLUMN(C70)C列是第3列(3)=COLUMN()当前列的列序号结果选中区域第7页,共20页COLUMN函数应用:
应用COLUMN函数得到引用的列序号例:
P15955、ROWROW函函数数用途:
返回目标单元格或单元格区域的行序号。
该函数与COLUMN函数的功能恰好相反,前者是返回行序号,后者是返回列序号。
语法:
ROW(reference)reference为目标单元格或单元格区域说明:
若reference省略,此时系统返回ROW函数所在单元格的行序号。
简单示例:
运算结果:
86公式说明(结果)=ROW()返回当前行的行序号=ROW(C86)返回(86)结果选中区域91运算过程及结果:
选中B91:
B94,然后输入公式=ROW(C91:
C94),然后按组合键确认。
929394ROW函数应用:
应用ROW函数编制工资条例:
P16266、INDEXINDEX函函数数用途:
按照相关条件返回目标区域里的值。
分为数组形式和引用形式数组形式返回由行和列序号索引选定的值,引用形式返回特定行和列交叉处单元格的引用。
数组形式语法:
INDEX(array,row_num,column_num)array是目标单元格区域或数组。
Row_num为目标区域中待返回值的行序号,column为目标区域中待返回的列序号。
例:
当row_num为2,column为1时,返回目标区域第二行第一列中的数值。
说明:
若省略row_num,INDEX函数将返回整列的元素。
若省略column_num,INDEX函数将返回整行的元素。
简单示例:
张第一名运算结果:
第一名陈第二名孙孙第三名公式说明(结果)=INDEX(B105:
C107,1,2)系统将返回B105:
B107区域里的第一行与第2列交叉单元格里的元素,即C105单元格里的内容(第一名)=INDEX(B105:
C107,3,1)系统将返回B105:
B107区域里的第三行与第1列交叉单元格里的元素,即B107单元格里的内容(孙)引用形式语法:
INDEX(reference,row_num,column_num,area_num)reference是目标单元格区域里的引用;row_num是要从中返回目标单元格区域中的行序号,column_num是要从中返回目标单元格区域中的列序号。
例:
当row_num为2,column_num为1时,则返回目标区域第二行第一列中的数值。
因为在引用形式下,INDEX函数可以对若干个区域进行引用,所以area_num用来指明从第几个区域进行引用。
说明:
若省略row_num,INDEX函数将返回整列的元素。
第8页,共20页若省略column_num,INDEX函数将返回整行的元素。
将INDEX函数和其他的公式组合使用,比如利用INDEX函数的结果作为另一个函数的控制条件,就可以满足工作中复杂功能的需求。
简单示例:
张第一名12运算结果:
第一名陈第三名125陈孙第二名123公式说明(结果)=INDEX(B122:
C124,B122:
C123),1,2,2)系统将返回第2个区域,即B122:
C123区域里的第1行与第2列交叉单元格里的元素,即C122单元格里的内容(第一名)=INDEX(B122:
C124,B122:
C123),2,1,1)系统将返回第1个区域,即B122:
C124区域里的第2行与第1列交叉单元格里的元素,即B123单元格里的内容(陈)77、OFFSETOFFSET函函数数用途:
将给定的区域作为目标引用区域,同时设定一个偏移量,然后依据该偏移量得到新的引用,最终的结果包括单元格和单元格区域的形式。
语法:
OFFSET(reference,rows,cols,height,width)reference是目标引用区域。
可以是单元格,也可以是单元格区域。
rows是相对于目标引用区域里的左上角单元格上(下)偏移的行数,行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。
cols是相对于目标引用区域里的左上角单元格左(右)偏移的列数,列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。
height为高度,即返回的引用区域的行数。
Height必须为正数。
width为宽度,即返回的引用区域的列数。
Width必须为正数。
说明:
如果行数和列数偏移量超出了工作表边缘,OFFSET函数将返回错误值#REF!
。
如果省略了height或width,则假设其高度或宽度与reference相同。
OFFSET函数实际上并不移动任何一个单元格或更改选取定区域,它只是返回一个引用。
OFFSET函数可用于任何需要将引用作为参数的函数。
简单示例:
161116运算结果:
18271217373813184914195101520公式说明(结果)=OFFSET(C141,3,2,1,1)从C141单元格出发,先向下偏移3个单元格单位,再向右偏移2个单元格单位,因为height和width都为1,所以返回E144单元格里的内容(18)=SUM(OFFSET(C141,3,2,2,1)从C141单元格出发,先向下偏移3个单元格单位,再向右偏移2个单元格单位,因为height为2,width为1,所以返回E4:
E5单元格区域,然后函数SUM对该区域里的数值进行加总(37)OFFSET函数应用:
应用OFFSET函数得到新引用第9页,共20页文文本本函函数数的的应应用用11、TEXTTEXT函函数数用途:
依据需要将目标数字转换为指定的文本格式语法:
TEXT(value,format_text)value为目标数值,可以为数字,也可以为计算结果。
format_text为读者需要设定的文本格式。
该格式设置方式:
按组合键弹出单元格格式对话框,单击数字选项卡,在分类列表框中选中文本形式的数字格式,最后单击确定按钮即可。
简单示例:
工业45%运算结果:
工业占总GDP比重45%服务业1000亿服务业的GDP为1000亿公式说明(结果)=B7&占总GDP比重&TEXT(C7,0%)将B7和C7单元格里的内容以及引号间的内容合并为一句(工业占总GDP比重的45%)=B8&的GDP为&TEXT(C8,)将B8和C8单元格里的内容以及引号间的内容合并为一句(服务业
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 人力资源 管理工作 常用 EXCEL 函数 教程