EXCEL函数详解及应用实例

常用Excel函数详解及应用实例一、日期与时间函数序号函数函数定义页码1Date通过年、月或日返回日期12Datedif计算期间内的天数、月数或年数13Datevalue将以文字表示的日期转换成系列数24Day从日期中返回日25Edate返回数月前或数月后的日期36Eomonth返回数月前或数月后的月末37Hour将序列号转换为小时48Minute将序列号转换为分钟49Month从日期中提取出月410Networkdays返回日期之间的全部工作日(除周六、周日和休息日之外的工作天数)511Now返回计算机系统的当前日期和时间512Second返回时间值的秒数(为0至59之间的一个整数)613Time把分散的日期合并换成AM或PM形式的时间表示方式614Timevalue返回由文本字符串所代表的时间的小数值615Today返回系统当前日期的序列号716Weekday返回某日期对应的星期数717Weeknum返回一个数字，该数字代表一年中的第几周818Workday计算给定日期之前或之后的除节假日和双休日之外的日期819Year返回某日期的年份920Yearfrac返回start_date和end_date之间的天数占全年天数的百分比9整理日期：2013年6月常用Excel函数详解及应用实例123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657ABCDEFGHI一、日期与时间函数1.DATE返回特定日期的序列号函数定义：合并年、月、日三个数为完整的日期格式,从指定的年、月、日来计算日期序列号值.使用格式：DATE(year,month,day)格式简义：DATE(年,月,日)参数定义：year参数year可以为一到四位.Excel将根据所使用的日期系统解释year参数Excel支持1900年和1904年两种日期系统,这两种日期系统使用了不同的日期作为参照基础,00年日期系统规定1900年的1月1日为第一天,其存储的日期系列编号为1,最后天是9999年12月31日.04日期系统规定1904年1月1日为第一天,基存储的日期系列为0,最后一天同上.系统默认为1900日期系统.month以整数形式指定日期的月部分的数值,或者指定单元格引用.若指定数大于12,则被视为下一年的1月之后的数值.如果指定的数值小于0,则被视为指定了前一个月份.day以整数的形式指定日期的日部分的数值,或者指定单元格引用.如果指定数大于月份的最后一天,则被视为下一月份的1日之后的数值.如果指定的数值小于0,则被视为指定了前一个月份.注意事项：此函数也可以将公式指定为参数.当参数中指定了数值范围外的值时,返回错误值#VALUE!.因此,使用函数时要注意确认参数是否正确.例1求日期时间相加(date,year,month,day,time,minute,second)基数日期时间：2013/5/2416:58:28年月日时分秒5461248582018/5/242013/9/242013/5/302013/5/244:582013/5/2417:462013/5/2416:59增加年：=DATE(YEAR(D20)+B22,MONTH(D20),DAY(D20))增加月：=DATE(YEAR(D20),MONTH(D20)+C22,DAY(D20))增加日：=DATE(YEAR(D20),MONTH(D20),DAY(D20)+D22)增加时：=DATE(YEAR(D20),MONTH(D20),DAY(D20))+TIME(HOUR(D20)+E22,MINUTE(D20),SECOND(D20))增加分：=DATE(YEAR(D20),MONTH(D20),DAY(D20))+TIME(HOUR(D20),MINUTE(D20)+F22,SECOND(D20))增加秒：=DATE(YEAR(D20),MONTH(D20),DAY(D20))+TIME(HOUR(D20),MINUTE(D20),SECOND(D20)+G22)例2有关日期时间的判断(day,eomonth,today)31=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))(计算本月总天数)31=DAY(EOMONTH(TODAY(),0))(计算本月总天数)22=DAY(EOMONTH(TODAY(),0))-DAY(TODAY())(计算本月还剩几天）31=DAY(EOMONTH(TODAY(),1))(计算下个月总天数）3=INT((MONTH(TODAY())+2)/3)(计算本月属第几季度)3=INT(MONTH(TODAY())/3.1+1)(计算本月属第几季度)3=MONTH(MONTH(TODAY())&0)(计算本月属第几季度)3=ROUNDUP(MONTH(TODAY())/3,)(计算本月属第几季度)3=CEILING(MONTH(TODAY())/3,1)(计算本月属第几季度)3=LEN(POWER(2,MONTH(TODAY())))(计算本月属第几季度)工作日=IF(WEEKDAY(TODAY(),2)5,双休日,工作日)(计算当天是休息/工作日)28=WEEKNUM(TODAY(),2)(计算当天是本年的第几周)191=TODAY()-DATE(YEAR(TODAY()),1,0)(计算当年已经过的天数)2020/9/17=WORKDAY(TODAY(),50)(第50个工作日后日期)例3从身份证中提取出生日期、性别及计算退休日期身份证号码3426261958102600171958-10-26=TEXT(MID(C48,7,8),#-00-00)(提取出生日期)男=IF(MOD(MID(C48,17,1),2)=1,男,女)(判断性别)2018/10/26=DATE(YEAR(B49)+IF(B50=男,60,55),MONTH(B49),DAY(B49))（退休日期）2018/10/26=DATE(YEAR(TEXT(MID(C48,7,8),#-00-00))+IF(IF(MOD(MID(C48,17,1),2)=1,男,女)=男,60,50),MONTH(TEXT(MID(C48,7,8),#-00-00)),DAY(TEXT(MID(C48,7,8),#-00-00)))(综合公式)2.DATEDIF计算期间内的年数、月数、天数函数定义：以指定的单位进行天数计算,通过更改单位,可以进行6种类型天数的计算.使用格式：DATEDIF(start_date,end_date,y)；DATEDIF(start_date,end_date,m)DATEDIF(start_date,end_date,d)；DATEDIF(start_date,end_date,ym)；第2页，共89页常用Excel函数详解及应用实例585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115ABCDEFGHIDATEDIF(start_date,end_date,yd)；DATEDIF(date1,date2,md)格式简义：DATEDIF(开始日期,结束日期,要计算的单位)参数定义：start_date指定表示日期的数值(序列号值)或单元格引用.start_date的月份被视为0进行计算end_date指定序列号值或单元格引用.y、m、y:计算满年数,返回值为0以上的整数;m:计算满月数,返回值为0以上的整数;d、ym、d:计算满日数,返回值为0以上的整数;ym:计算不满一年的月数,返回值为1~11之yd、md间的整数;yd计算不满一年的天数,返回值为0~365之间的整数;md:计算不满一个月的天数,返回值为0~30之间的整数.要点：不能从插入函数对话框中输入（隐藏函数）.在使用时必须直接键盘输入单元格中.注意事项：当start_date或end_date中指定的值无法识别为日期时返回错误值#VALUE!.当返回值为负数时,或者y、m、d、ym、yd、md参数没有用双引号括住时,返回错误值#NAME!.例4姓名入职日期到现在工作年数到现在总月数到现在总天数年内相差月数月内相差天数张三1971/1/114959318077528李四1976/2/174453216214422王五1993/9/7263229802102赵六2007/10/512153466194=DATEDIF(C71,TODAY(),y)(到当前工作的整年数)=DATEDIF(C71,TODAY(),m)(到当前工作的总月数)=DATEDIF(C71,TODAY(),d)(到当前工作的总天数)=DATEDIF(C71,TODAY(),ym)(月份相差数)=DATEDIF(C71,TODAY(),md)(天数相差数)=DATEDIF(C71,TODAY(),y)&年&DATEDIF(C71,TODAY(),ym)&月&DATEDIF(C71,TODAY(),md)&天(综合)=TEXT(SUM(DATEDIF(C71,TODAY(),{y,ym,md})*{10000,100,1}),#年##月##日)(综合)例5计算工龄工资（工龄足5年的每月加100，足10年的每月加200，足20年的每月加300，20年以上的每月加500）姓名入职日期工龄工龄工资绩效工资实发工资张三2000/1/2020600018007800王五2003/2/2017340018005200洋洋1991/9/10288400180010200李小军2005/4/1615300018004800李阳2012/12/1770018002500=IF(DATEDIF(C85,TODAY(),y)5,0,IF(DATEDIF(C85,TODAY(),y)10,DATEDIF(C85,TODAY(),y)*100,IF(DATEDIF(C85,TODAY(),y)20,DATEDIF(C85,TODAY(),y)*200,DATEDIF(C85,TODAY(),y)*300)))(工龄工资)3.DATEVALUE日期换成数字函数定义：从表示日期的文本中计算出表示日期的数值(序列号值).使用格式：DATEVALUE(date_text)格式简义：DATEVALUE(要计算的单元格)参数定义：date_text以文本的形式指定的日期.要点：此函数引用的单元格内的日期必须是文本格式,即在先写'(单引号),然后输入日期注意事项：当参数中引用了日期形式的文本内容时,返回错误值#VALUE!例6函数的几种写法公式结果公式显示2011/8/22=DATEVALUE(2011-8-22)2011/8/22=DATEVALUE(22-AUG-11)2011/2/23=DATEVALUE(2011-2-23)2020/7/5=DATEVALUE(5-JUL)例7计算员工进入公司的天数调查日：2011-11-2注：这个日期是文本型的姓名进入公司日期工作时间(天)张三2010-3-2610=DATEVALUE(E107)-DATEVALUE(C109)(工作时间)李四2011-6-5150注:将调查日期与进入公司日期的日期转换成数值,再减,王五2009-10-6757差数即为其在公司工作的总天数.4.DAY从日期中提取出日函数定义：将系列数转换为日使用格式：DAY(serial_number)格式简义：DAY(要查的单元格)第3页，共89页常用Excel函数详解及应用实例116117118119120121122123124125126127128129130131