分享

Excel | 一个公式引发的思考

 我看360书 2016-07-29

Excel | 一个公式引发的思考

有朋友问了我一个计算工龄的公式,看到后感慨万千:工作后要计算工龄,在网上搜索的就是这个公式“=YEAR(TODAY()-A2+2)-1900”(假定A2是入职时间),当时研究了好久,还没研究透为什么这样设置。我选这个公式的原因是它很短,过去了好久回过头来再研究,还是不怎么理解。后来知道了DATEDIF函数,就一直用这个函数来计算,包括有人问我工龄、年龄等如何计算,我也是告诉他用DATEDIF函数。

再次看到这个公式,发现是一个挺烧脑的公式,而且也是比较考验基础的公式。

我们来分析一下这个公式:

1、这个公式用的函数挺简单,YEAR函数是用来求年份的,TODAY函数是返回当前的日期。

2、TODAY()-A2+2,两日期相减(大日期减小日期或两个相同日期相减)返回值是一个大于等于0的数字。至于为什么加2,后面再解释,暂且忽略。

3、我们平时看到的YEAR函数里面的参数通常是日期或引用的填充为日期格式的单元格,为什么在这里确是一个大于等于0的数字呢?

其实日期也可以看作为数字,我们在单元格中任意输入一个大于等于“1900-1-0”的日期,将单元格格式设置为常规格式,我们会发现单元格中出现了数字,如分别输入“1900-1-0”、“1900-1-1”、“2016-7-1”,单元格格式设置为常规后,分别返回0、1、42552。系统默认的日期是从1900-1-1开始的(出现1900-1-0,是0设置为日期格式而显示出来的),任意数字N可以看作是从1900-1-1开始向后推N-1天的日期,如数字15,就代表1900-1-1向后推14(15-1=14)天,结果为1900-1-15。

这样我们就可以理解了,YEAR函数的参数既可以是日期(日期用英文引号引用),也可以是大于等于0的数字。所以“=YEAR(“2016-7-1”)”和“=YEAR(“1900-1-1”+42552-1)”得出的结果都是2016。

回过头来再看,TODAY()-A2+2返回的数值N是相差的天数,同样是在1900-1-1之后N-1天的某个日期,用YEAR函数来计算出具体是哪一年。

4、减去1900,这个就好理解了,相差的年数是在1900基础上开始计算的,所以再减去1900,就是最终相差的年数,即工龄。

5、为什么要加2?这个还是基于对YEAR函数的理解,我们都知道两个日期相差的天数,一般用大日期减去小日期再加上1即是结果。

那么肯定有人认为“TODAY()-A2+1”即可,我们再往后看,YEAR函数的参数,如果正好是全年的天数,它不认为是代表1年的,因为这一年还没有过去。例如YEAR(366),366转换为日期为“1900-12-31”(1900年为闰年,天数为366天),我们将其单元格格式转变为“YYYY-M-D HH:SS:MM”格式,我们会发现结果为“1900-12-31 00:00:00”,如果输入366.1则显示结果为“1900-12-31 00:14:24”。

看到这里大家能明白吧,数字366代表还在1900年内,还没有超过“1900-12-31 24:00:00”,同样推理,后面每一年的这个节点都是这种情况,所以如果两个日期相差天数正好为N年的全年日历天数相加,那么返回的结果为N-1。

公式中加2,我们要分开来看,一个是日期相差的天数需要两者相减再加1,另外一个是N年的日历天数之和,YEAR函数返回N-1年,所以还需要再加1,1+1=2,于是就加了2。

这个公式,涉及了YEAR函数的深入理解、日期和数字的相互转换理解,系统默认的最小日期及应用,小小的一个公式,涉及的知识却不少,如果不是对EXCEL基础知识的深入掌握,我想理解这个公式还是有一定难度的。

上面是我对这个公式的理解和解析,有其它意见的朋友也请发现意见,大家一起交流。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 全屏 打印 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多