Excel中日期时间函数使用辨析

》首页 》 科技论文 》Excel中日期时间函数使用辨析
作者:无, 字数:7474

  摘要:日期时间函数在Excel中广泛使用,在使用过程中,对应于一些函数的基本知识要充分了解,函数的参数,取值要注意辨析,才能保证结果正确。
  关键词:Excel;函数;辨析
  中图分类号:TP3 文献标识码:A
  文章编号:1009-3044(2020)27-0196-02
  开放科学(资源服务)标识码(OSID):
  1 Excel的日期、时间函数的基本知识
  1.1 Excel日期系统
  Excel提供了两种日期系统,一种是1900日期系统,系统的起始日期是1900年1月1日,应用在Windows操作系统中;另一种是1904日期系统,起始日期为1904年1月1日,使用在Ma-cintosh中,本文主要以1900日期系统为例。
  1.2日期、时间与序列号
  一般来说,Excel中有3种数据类型,分别为文本、数字与日期和时间类型。实际上,日期和时间是特殊的数字。从直观上,我们可以看到在默认状态下,数字与日期和时间自动右对齐,而文本自动左对齐;从本质上看,日期和时间是与数字有着一一对应关系的,即系统的起始日期1900年1月1日对应于数字“1”,从这个日期开始,以“天”为单位,24小时为1天,因而得到如下的对应关系:1天=“1”整天;1小时= 1/24=0.0416667;1分钟=1/24*60 - 1/1440=0.000694;1秒=1/24*60*60=1/86400
  0.00001157;1毫秒=1/24*60*60*1000=1/86400000,Excel精确到的最小单位就是毫秒。如果把实际中不存在的1900年1月0日,记为坐标原点,1900年1月1日的序列号即为1,因而,如图1所示,我们也可以看到2020年4月8日对应的就是43929。
  1.3常用日期和时间函数
  (1)NOWO返回当前Windows系统设定的日期和时间,显示方式为正常的日期格式,如当前日期为4月18日,当使用NOW()函数后,则显示为当前日期和当前时间,并且也不会实时更新,除非重新计算工作表。如果要显示当前日期对应的序列号,则需将单元格格式设置为“常规”,或使用“=text (NOWO,”0”)”,则显示函数在Excel中的常规格式,为整型数。
  (2)TODAYO返回当前Windows系统设定的日期,显示方式为正常的日期格式,并且也不会实时更新,除非重新计算工作表。如果要显示当前日期对应的序列号,则需将单元格格式设置为“常规”,或使用“=text(TODAYO,”0”)”。因为TODAYO函数自动获取系统时间,因而在某些场合可以参与倒计时公式=截止日期-TODAYO。
  (3) DATE(year,month,day)返回指定日期的序列号。在DATE函数中所有参数必须为数值型数据,year值为1900-9999之间,month正常值为1-12,day正常值为1-31,但date有自动更正功能,当月>12或日>31时,自动更正为下一年或下一月;同理当月<1或日<1时,自动更正为上一年或上一月。如图2所示。
  (4)Time(hour,minute,second)返回指定时间的序列号,返回值为小数,值在0-0.99999999之间表示从0:00:00到23:59:59。Hour(提取小时数的时间),返回值为0-23。Minute(提取分钟数的时间),返回值为0-59。Second(提取秒数的时间),返回值为0-59。
  (5)Year(提取年份的日期),返回值在1900-9999之间。注意,待提取年份的日期为标准格式日期,或通过其他日期时间函数计算得到的日期值。Month(提取月份的日期),返回值为1-12。Day(提取天数的日期),返回值为1-31。
  2 Excel日期函数应用中的问题
  由于在日常计算中,涉及小数位数取舍问题,因而对于日期及时间计算很容易出错,需多加注意。
  2.1 使用常规方式解决周年、周岁问题
  例如:某年NCRE考题中,就曾出现“年龄需要按周岁计算,满1年才计1岁”类似题目,就需要注意公式使用情况,如果仅使用公式=(今天日期一出生日期)/365,就可能出错,因为每一年不一定都是365天,即使使用了INT()函数,F2=INT《TO-DAYO-[@出生日期])/365),也是一个大致的数值,一般情况下比较接近。
  2.2 使用隐含函数,精确计算工龄、周岁问题
  DATEDIF(起始日期,截止日期,间隔类型)函数是隐含的函数,不仅没有函数帮助,而且也不出现在公式记忆式录入列表中,DATEDIF中间隔类型比较常用的有:“y”代表年差,“m”代表月差,“d”代表日差;还有3中不常用的间隔类型,忽略年月日中的一部分计算日期之差,分别为“md”“ym”“yd”。在计算年龄,尤其是周岁问题,这种要求精确数据时,使用DATEDIF就非常方便了。在计算单元格中直接输入公式= DATEDIF([@出生日期,TODAYO,”y”)就可以啦。
  2.3 停车场收费问题
  某停车场根据记录数据,利用出场日期、时间与进场日期、时间的关系,计算“停放时间”列,单元格格式为时间类型的“XX时XX分”,而后再計算费用更改前后效益差计算费用更改前后的标准如下:固定的是超过15分钟的按每15分钟计费,其中小型车1.5元,中型车2.0元,大型车2.5元;更改的是以前“不足15分钟按15分钟收费”,现在是“不足15分钟的部分不收费”标准。
  题目给出之后,考虑的思路就是日期和时间都是数值型数据可以直接参与运算,给出公式:J2=H2-F2+12-G2,这样就得到停放时间。
  在计算现行收费标准中“不足15分钟按15分钟收费”,直接使用J2值,转化为分钟数,然后再除15,利用ROUNDUP函数向上舍人,K2=ROUNDUP(J2*24*60/15,0)*E2得到结果。
  同理在拟实施标准中“不足15分钟不收费”,直接使用J2值,转化为分钟数,然后再除15,利用ROUNDDOWN函数向下舍人,得到结果。
  如果,采用另一种计算方法如下:使用公式K2=ROUNDUP《HOUR(J2)*60+MINUTE(J2)/15,0)*E2,看似结果与上面计算一样
  但继续浏览到下面发现问题了在第544和545行,利用该公式计算得到的结果:
  而依据原来的公式K2=ROUNDUP(J2*24*60/15,O)*E2,得到结果如下:
  问题出现的原因在哪里?原来我们计算停车时间虽然按照时间格式得到是**时**分,但还是存在**秒的,如果单单应用HOURO和MINUTEO函数提取了小时和分钟数,相当于舍弃了“秒”值,因而会收费减少。
  日期、时间函数在Excel函数中应用非常灵活,但由于在Excel中应用非常广泛,因而对于日期、时间函数的使用必须注意辨析,防止出现各式错误答案。
  参考文献:
  [1]宋翔.Excel公式与函数大辞典[Ml.北京:人民邮电出版社,2010.
  [2]教育部考试中心.全国计算机等级考试二级教程MS Office高级应用[M].北京:高等教育出版社,2018.
  【通联编辑:代影】
  作者简介:胡杰华(1972-),女,安徽绩溪人,副教授,硕士,主要研究方向为计算机技术与保密安全。

相关

相关

相关

  • 让信息技术在历史课中彰显魅力

  • 小学语文课堂教学氛围营造漫谈

  • 浅议抓好农村小学生综合素质评价工作的方法

  • 浅谈多种评价方式提高小学生的综合素质

  • “喂”羊与“牧”羊的启示

  • 开展丰富多彩的游戏活动来提高学生的学习水平

No comments found.

相关