admin管理员组文章数量:1565810
2024年7月19日发(作者:)
EXCEL电子表格常用函数使用指南
第一部分 简单不要说明的函数
SUM函数——求和
AVERAGE函数——求平均值
COUNT函数——计数函数
MAX函数——求最大值
MIN函数——求最小值
第二部分 较为复杂的函数
ROUND函数
这是四舍五入函数,用于保留几位小数。
语法:ROUND(number,num_digits)
Number 为要进行四舍五入的数字。
num_digits 小数点后要保留的数字位数。
如:ROUND(3.897677,3) 计算3.897677的小数点后保留3位数
字的值为3.898。
RANK函数
这是排位(名)函数,可用于成绩自动排名。
语法:RANK(number,ref,order)
Number 为需要找到排位的数字。
Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值
型参数将被忽略。
Order 为一数字,指明排位的方式。
如果 order 为 0(零)或省略,Microsoft Excel 对数字的排
位是基于 ref 为按照降序排列的列表。 如果 order 不为零,
Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的
列表。
如:RANK(K2,K$2:K$90) 计算K2单元格的数值在 K2至K90单
元格区域 中按从大到小排序的位置,也就是第几名。注意,单元
格区域的语法是K$2:K$90,如果写成K2:K90,则只能用于K2
单元格排位,无法复制到其他单元格。
COUNTIF函数
计算区域中满足给定条件的单元格的个数。
语法:COUNTIF(range,criteria)
Range 为需要计算其中满足条件的单元格数目的单元格区
域。
Criteria 为确定哪些单元格将被计算在内的条件,其形式可
以为数字、表达式或文本。
如:COUNTIF(A2:A90,">=90") 计算A2至A90单元格区域中大
于90分的人数;
同理,COUNTIF(A2:A90,">=80")-COUNTIF(A2:A90,">=90") 为计
算80-89分的人数。
COUNTIF(A2:A90,"本科") 计算学历为本科的人数。
SUMIF函数
根据指定条件对若干单元格求和。
语法:
SUMIF(range,criteria,sum_range)
Range 为用于条件判断的单元格区域。
Criteria 为确定哪些单元格将被相加求和的条件,其形式可以
为数字、表达式或文本。例如,条件可以表示为 32、"32"、">32"
或 "apples"。
Sum_range 是需要求和的实际单元格。
例如:
=SUMIF(D4:D19,"男",N4:N19) 这是计算男性年龄总和的公
式。
其中,D4:D19存放性别,N4:N19存放年龄。
多条件求和的公式
如果要统计“东北区”中“辽宁”的A产品业绩汇总,那么可以
在C10单元格中输入如下公式:
=SUM(IF($A$2:$A$7="东北区",IF($B$2:$B$7="辽宁
",Sheet1!C$2:C$7)))。然后按下“Ctrl+Shift+Enter”键,则
可看到公式最外层加了一对大括号(不可手工输入此括号),同
时,我们所需要的东北区辽宁组的A产品业绩和也在当前单元格
得到了。
SUMPRODUCT函数
该函数在EXCEL定义中描述为在给定的几组数组中,将数组间对
应的元素相乘,并返回乘积之和。这种描述给人的感觉似乎是对
数组进行计算,对乘积汇总。但实际上它对于多条件求和方面的
功能超乎人们的想象,特别是应用于人力资源方面统计更是超
强,不仅能完成多条件的统计功能,而且人数统计和工资汇总统
计都能实现,灵活应用可以取代COUNTIF()和SUMIF(),因此掌
握该这个函数的使用方法,可以说完成任何统计报表的数据统计
工作,都能做到游刃有余。
该函数进行多条件计数统计时,如条件是“或者”关系。必须用
+号连接判断条件,其公式形式如下:
SUMPRODUCT(条件1 +条件2 +条件3…条件N)
该函数进行多条件计数统计时,如条件是“并列”关系,即同时
满足。必须用*号连接判断条件,公式形式如下:
SUMPRODUCT(条件1*条件2*条件3…条件N)
例如
=SUMPRODUCT((D4:D19="
")*(N4:N19>20))
该函数进行多条件求和统计时,如条件同时成立。必须用*号
连接判断条件,其公式形式如下:
SUMPRODUCT((条件1*条件2*条件3…条件N *计算区域)
例如:
=SUMPRODUCT((D4:D19="男")*(E4:E19="科员")*N4:N19)
IF函数
执行真假值判断,根据逻辑计算的真假值,返回不同结果。可以
男")*(E4:E19="科员
使用函数 IF 对数值和公式进行条件检测。
语法
IF(logical_test,value_if_true,value_if_false)
Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表
达式。如,A10=100 就是一个逻辑表达式,如果单元格 A10 中
的值等于 100,表达式即为 TRUE,否则为 FALSE。本参数可使
用任何比较运算符(一个标记或符号,指定表达式内执行的计算
的类型。有数学、比较、逻辑和引用运算符等。)。
Value_if_true logical_test 为 TRUE 时返回的值。如,如果
本参数为文本字符串“预算内”而且 logical_test 参数值为
TRUE,则 IF 函数将显示文本“预算内”。如果 logical_test 为
TRUE 而 value_if_true 为空,则本参数返回 0(零)。如果要
显示 TRUE,则请为本参数使用逻辑值 TRUE。value_if_true 也
可以是其他公式。
函数 IF 可以嵌套七层,用 value_if_false 及
value_if_true 参数可以构造复杂的检测条件。
如对C2单元格的学生成绩区分A、B、C、D四个档次
=IF(C2>=90,"A",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D
","E")))),
AND函数
所有参数的逻辑值为真时,返回 TRUE;只要一个参数的逻辑
值为假,即返回 FLASE。
AND(logical1,logical2, ...)
Logical1, logical2, ... 表示待检测的 1 到 30 个条
件值,各条件值可为 TRUE 或 FALSE。
例如:员工生日提前1周提醒:(C2为当年生日)
=IF(AND(C2-TODAY()<=7,C2-TODAY()>0),"还有"&C2-TODAY()&"
到期","")
Vlookup函数
问题:如下图,已知表sheet1中的数据如下,如何在数据表二
sheet2 中如下引用:当学号随机出现的时候,如何在B列显示
其对应的物理成绩?
首先我们介绍下使用的函数 vlookup 的几个参数,vlookup是
判断引用数据的函数,它总共有四个参数,依次是:
1、判断的条件
2、跟踪数据的区域
3、返回第几列的数据
4、是否精确匹配
根据问题的需求,这个公式应该是:
=vlookup(a2,sheet1!$a$2:$f$100,6,true)
详细说明一下在此vlookup函数例子中各个参数的使用说明:
1、a2 是判断的掉条件,也就是说如果sheet2表中a列对应的
数据和sheet1表中的数据相同方能引用;
2、sheet1!$a$2:$f$100 是数据跟踪的区域,因为需要引用的数
据在f列,所以跟踪的区域至少在f列,$是绝对引用;
3、6 这是返回什么数的列数,如上图的物理是第6列,所以应
该是6,如果要求英语的数值,那么此处应该是5
4、是否绝对引用,如果是就输入 true 如果是近似即可满足条
件 那么输入false (近似值主要用于带小数点的财务、运算等)
5、vlookup是垂直方向的判断,如果是水平方向的判断可使用
Hlookup函数。
Hlookup函数
水平方向的判断。
Mid、Left、Right提取字符函数
可以使用Mid、Left、Right等函数从长字符串内获取一部分字
符。具体语法格式为
LEFT函数:得到字符串左部指定个数的字符。
MID函数:MID(text,start_num,num_chars)其中Text是包含要
提取字符的文本串。Start_num是文本中要提取的第一个字符的
位置。num_chars提取文本串长度。
RIGHT函数:right函数的功能是从字符串右端取指定个数字符。
比如,从字符串"This is an apple."分别取出字符"This"、
"apple"、"is"的具体函数写法为。
LEFT("This is an apple",4)=This
RIGHT("This is an apple",5)=apple
MID("This is an apple",6,2)=is
DATEDIF函数
Excel隐藏函数,在帮助和插入公式里面没有。
简要说明: 返回两个日期之间的年月日间隔数
编辑本段语法
DATEDIF(start_date,end_date,unit)
Start_date 为一个日期,它代表时间段内的第一个日期
或起始日期。
End_date 为一个日期,它代表时间段内的最后一个日期
或结束日期。
Unit 为所需信息的返回类型。
Unit 返回
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" start_date 与 end_date 日期中天数的差。忽略
日期中的月和年。
"YM" start_date 与 end_date 日期中月数的差。忽略
日期中的日和年。
"YD" start_date 与 end_date 日期中天数的差。忽略日
期中的年。
实例1:
题目: 计算出生日期为1973-4-1人的年龄
公式: =DATEDIF("1973-4-1",TODAY(),"Y")
结果: 33
简要说明 当单位代码为"Y"时,计算结果是两个日期间
隔的年数.
实例2:
题目: 计算日期为1973-4-1和当前日期的间隔月份数.
公式: =DATEDIF("1973-4-1",TODAY(),"M")
结果: 403
简要说明 当单位代码为"M"时,计算结果是两个日期间
隔的月份数.
实例3:
题目: 计算日期为1973-4-1和当前日期的间隔天数.
公式: =DATEDIF("1973-4-1",TODAY(),"D")
结果: 12273
简要说明 当单位代码为"D"时,计算结果是两个日期间
隔的天数.
实例4:
题目: 计算日期为1973-4-1和当前日期的不计年数的
间隔天数.
公式: =DATEDIF("1973-4-1",TODAY(),"YD")
结果: 220
简要说明 当单位代码为"YD"时,计算结果是两个日期间
隔的天数.忽略年数差
实例5:
题目: 计算日期为1973-4-1和当前日期的不计月份和
年份的间隔天数.
公式: =DATEDIF("1973-4-1",TODAY(),"MD")
结果: 6
简要说明 当单位代码为"MD"时,计算结果是两个日期间
隔的天数.忽略年数和月份之差
5、实例6:
题目: 计算日期为1973-4-1和当前日期的不计年份的
间隔月份数.
公式: =DATEDIF("1973-4-1",TODAY(),"YM")
结果: 7
简要说明 当单位代码为"YM"时,计算结果是两个日期间
隔的月份数.不计相差年数
实际问题解决办法:
1、员工当年的生日:(B2存放出生日期)
=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))
2、员工生日当天提醒:
=IF(MONTH(B2)=MONTH(NOW()),IF(DAY(B2)=DAY(NOW()),"祝
"&A2&"生日快乐!!!",""),"") A2为员工的姓名!
3、员工生日提前1周提醒:(C2为当年生日)
=IF(AND(C2-TODAY()<=7,C2-TODAY()>0),"还有"&C2-TODAY()&"
到期","")
4、试用期计算
试用期到期时间:
=DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1)
含义:“DATE(YEAR(),MONTH(),DAY())”显示指定日期;
在这里我们假设试用期为3个月,我们需要在Q3单元格中输
入上述公式,其中MONTH(P3)+3表示在此人入职时间月的基础上
增加三个月。而DAY(P3)-1是根据劳动合同签订为整年正月而设
置的。比如2005年11月6日到2006年11月5日为一个劳动合
同签订期。
5、劳动合同到期时间:
=DATE(YEAR(P3)+1,MONTH(P3),DAY(P3)-1)
我们同样采用上述函数的设置方法。这里我们假设劳动合同
期限为1年,则我们需要设置成YEAR(P3)+1,另外这个数值依
然以入职日期为计算根据,所以天数上还要设置成DAY(P3)-1的
格式。
6、续签合同到期时间: =DATE(YEAR(S3)+1,MONTH(S3),DAY(S3))
这里需要注意的是续签合同计算是以前份合同签订到期日期
为根据的,所以只在前一份合同到期时间的基础上增加1年即
可,无需天数上减1。
7、计算退休时间:
如果C3是性别,I3是出生年月(1925年6月2日),R3是退休
时间,
公式为: R3=DATE(YEAR(I3)+IF(C3="男
",60,55),MONTH(I3),DAY(I3))
8、从身份证号自动填充性别、出生月日、年龄
我们先对“性别”“出生年月”“年龄”进行函数设置。当我
们输入某人身份证号码时,系统便会自动生成“性别”,“出生年
月”及“年龄”,这样就减少了我们录入的工作量。请分别选择
性别、出生月日、年龄信息项单元格输入下列公式:
(1)性别: =
IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,"
男","女")
含义:“LEN(E3)=15”表示看E3中是否有15个字符;
“MID(E3,15,1)”表示在E3中从第15位开始提取1位字符;
“MOD(MID(),2)=1”表示提取的字符除以2余数为1;
“IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)”表示看E3
中是否够15个字符,如果够就从第15个字符开始取1个字符,
如果不够15个字符就从第17个字符开始取1个字符。我们的身
份证号码一般是15位或18位。
“IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)
=1,"男","女"”表示所取字符除以2如果余数为1显示男,否则
显示女。
简略的公式:=IF(MOD(MID(A1,15,3),2),”男”,”女”)
(2)出生年月:
=DATE(MID(E3,7,4),MID(E3,11,2),MID(E3,13,2))
含义:DATE(YEAR,MONTH,DAY);
“MID(E3,7,4)”表示在E3中从第7个字符开始连续取4个
字符表示年,用类似的表示方法一个人的出生年月日便可以通过
函数设置表示出来,如果为了看起来方便,我们可以将单元格格
式设置成年、月、日的日期格式,这样显示的结果会非常容易理
解。
如果其中一些身份证是15位的,则用下列公式:
=IF(LEN(C6)=15,"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&
MID(C6,11,2),MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13
,2))
(3)年龄: =DATEDIF(G3,TODAY(),"Y")
含义:“DATEDIF(date1,date2,“Y”)”表示两个日期的差
值;
“TODAY()”表示系统自带的日期即显示当日日期;
“DATEDIF(G3,TODAY(),"Y")”表示今天的日期与G3所表示
的出生月日之间的年份差值,这样一个人的年龄就会容易的显示
出来了。
9、两个字名字的中加空格。
=IF(LEN(D15)=2,MID(D15,1,1)&" "&MID(D15,2,1),D15)
10、判断相同数据有没有
=IF(ISNA(MATCH(H52,$J:$J,0)),"不存在","存在")
11、党龄的计算
1982年9月6日至今,入党时间是通过为预备党员之日(须经
上级党委批准),预备期一年。党员的党龄,从预备期满转为正
式党员之日起算。
excel数据引用
1、excel数据相对引用。
2、excel数据的绝对引用。
使用$来固定那些需要不变的数据,如
=MAX(A$2:A$6)-A2
随你怎样拖动,max计算的区域就不会变化了。如果你想固定的
更牢靠一点,可以在列标号前加$。
3、同文件内excel表间引用。
同文件内不同表之间的数据引用和计算通常是使用英文状态的
引号表名结合而来的,例如本例中:
引用表1当中的a列数据,可以在a2单元格输入
='1'!a2
其中英文单引号中为表的名字,表和单元格名称间用英文的!分
割开来。
4、不同文件间的excel引用。
使用英文的中括号 [ ]来引用文件,如我们将正在操作的文件保
存到d盘,默认名字为 新建一个excel文件,引用
文件中第一列的数据:
=[]1!a2
此例和上面的例子中英文的单引号 ' 可要可不要,不过excel
默认都会给加上,另外本例中,当我们输入完公式,打回车键之
后,excel会自动加上文件地址,如本例中可能会变为:
='d:[]1'!a2
版权声明:本文标题:人力资源常用函数 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dongtai/1721318824a871538.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论