|
今天是个礼拜天，对于小陆来说是个特别的日子，因为今天他要去给霉霉补Excel,出门还特意打扮的一番，说时迟那时快，说话就到了霉霉家。霉霉听到敲门，赶紧去开门说：欢迎师傅来家教课！ 小陆：师傅初次上门，也不知道你喜欢什么，就给你带了盒面膜，希望你喜欢！ 霉霉：谢谢师傅，当然喜欢了，赶紧进屋吧。说着把切好的水果都拿出来了，说：师傅，吃水果，咱们边吃边聊。你说日期时间在Excel算什么啊？为什么有的时候，拼接出来的日期时间好用，有时候不好用呢？ 小陆：日期时间确实都是数字，而我们拼接的是部分日期，用=比较的也是的字符串，如果想让拼接的完整的或text函数返回的文本日期，需要将其转化成数字，转化有数字的方法有：加减0或乘除1都可以，还可以用－号，只不过需要增加两个，这样才能保证数据不变，别使用函数转化，比如N(),它的函数功能，判断是不是数字，数字返回，不是返回0，是不是跟我们想像的有点不同，不过可以用运算类函数可以实现文本日期转化成日期。 霉霉：你说的这些函数我都没有学啊，我只学了几个日期函数。 小陆：跟你说的有点多，以后用到了，我再跟你仔细讲吧！日期函数你还记的几个啊？有什么功能啊？ 霉霉：我是按参数多少来划分记忆的： 无参数函数：today(),返回当前的日期， 1个参数函数：year,month,day,分别获取年份，月，日， 2参数函数：edate,第1参数是开始日期，第2参数是相隔几个月份，返回与开始日期相隔几个月的日期；除了日期的还有text函数，第1个大体可以分为文本和数字两种类型，第2个参数为格式编码，返回以格式编码的样式显示数字和文本，floor取整函数，mod取余函数... 小陆打断了霉霉说：咱们现在只说日期函数，其它的函数日后再说。 霉霉：3个参数：date函数，第1参数：年份（2位或4位）数字从0-9999，第2和第3参数都没有太严格的要求，如遇到参数为小数的时候会自动取整，超出正常的月份或日期都会自动按日期规则计算返回一个正常的日期，只要保证的各个参数都在Excel可识别的日期返回之内就ok,否则返回#NUM!; 小陆：你这记忆方法可以啊，怪不得学的那么快呢，哪你知道date还可以怎么用吗？ 霉霉：就是我说的规则用啊，还能怎么用啊？ 小陆笑了笑说：比如我让你返回一个500天之后的日期，你该怎么做呢？答案就在你刚说的知识内。 霉霉：edate只能计算几月之后的日期，500之后，难道可以用date实现吗？ 小陆：你试试啊！ 霉霉：=date(year(today()),month(today()),day(today())+500),是这样吗？ 小陆：真棒，是的，你有没有发现其实date函数也能实现edate函数的功能！ 霉霉：就是一个设定日期的函数也有这么多的玩法，真长见识啊！ 小陆：嗯嗯，哪你今天想学点什么呢？ 霉霉：我前两天看你挺忙，自己做了一个统计工龄的表，我也不知道自己做的对不对？你帮我看看吧！ 小陆：你是怎么计算的啊? 霉霉：咱们公司的要求是整年计算，不到一年不计算，我直接使用year获取现在的年份与员工入职年份相减，再用今年满年工龄日期是否大于今天日期相比，大的则减1，小于等于的则不用减，公式=year(today())-year(d2)+(if(date(year(today(),month(d2),day(d2)>today(),-1,0)。 小陆：挺好的啦，能用已学的东西做出来挺好的，其实你用的就是计算的原理，不管用什么函数，实际的原理都是这个，除非细算到月，不过我告诉用一个函数就能轻松搞定，这个函数就是：datedif函数，它是专门用于计算年，月，日差的函数。 霉霉：这么厉害的，等下，我去拿我的小本本。 小陆：datedif是个特别的存在，它是个隐藏函数，你插入函数窗口是无法查到的，在输入函数时也无法提示参数的情况，它有三个参数，第1个参数是开始日期，第2参数是结束日期，第3个参数为显示格式编码，比如"Y"表示相差的年，"M"表示相差的月数，"D"表示相差的天数，还有几个有意思格式就是"MD"表示一个月内天数做差,"YM"只显示一年内月数差,"YD"只表示一年内的天数差，使用语法结构图如下：  datedif函数使用语法结构图 霉霉：第三个参数的编码还挺多啊，第一个如果用公式解释的话，是不是就是我求工龄的公式=year(today())-year(d2)+(if(date(year(today(),month(d2),day(d2)>today(),-1,0)，其它编码的用其他公式怎么表示呢？ 小陆：其实简单，这车开的有点快，请系好“安全带”。 “M”公式表示=(year(结束日期)-year(开始日期))*12+month(结束日期)-month(开始日期) "D"公式表示=(结束日期-开始日期)*1(可参考上述日期转数字的方法) "MD"公式原理=if(day(结束日期)>day(开始日期),day(结束日期)-day(开始日期), edate(date(year(结束日期),month(结束日期),day(开始日期)),1)-结束日期)； "YM"公式原理=if(day(结束日期)>=day(开始日期),if(month(结束日期)>=month(开始日期),month(结束日期)-month(开始日期),month(结束日期)-month(开始日期)+12,if(month(结束日期)>=month(开始日期),month(结束日期)-month(开始日期)-1,month(结束日期)-month(开始日期)+12-1),提取出公共的部分，然后通过数学法优化为： =(month(结束日期)-month(开始日期))+(month(结束日期)<month(开始日期))*12-(day(结束日期)<day(开始日期) "YD"公式原理=if(month(结束日期)>month(开始日期),date(year(开始日期),month(结束日期),month(结束日期)-开始日期,if(month(结束日期)=month(开始日期),date(year(开始日期)+(day(结束日期)<day(开始日期)),month(结束日期),day(结束日期))-开始日期,date(year(开始日期)+1,month(结束日期),month(结束日期))-开始日期),如果你想透彻的理解datedif函数，需要练一下这些原理公式！ 霉霉：这个内容量确实挺大的，"YM"部分的原理公式本来哪么长一串，咋就用数学简化成哪么短了，好神奇啊，能细讲一下吗？ 小陆：函数功能返回的是整月数， 结束日期的日大于等于开始日期的日，如果结束日期的月份再大于等于开始日期的月份，直接month(结束日期)-month(开始日期)，否则month(结束日期)-month(开始日期)+12，在数学计算中，true可以转化为1，而false则转化为0，提取出公共部分，公式可以写为month(结束日期)-month(开始日期)+(month(结束日期)<month(开始日期)*12; 当结束日期的日小于开始日期的日时，需要重复月份大小判读，和上一步相同，唯一不同的是需要在求出月数的基础上-1，同理提取公共部分，我们发现，-1只与日期日比较有关，按month的方法公式最终就编程=month(结束日期)-month(开始日期)+(month(结束日期)<month(开始日期))*12-(day(结束日日期)<day(开始日期)),这可是跟小度学的数学思想，怎么样！ 霉霉：师傅棒棒的，怪不的计算机需要数学好呢，数学思想竟然能将多层if嵌套简化成这样，真厉害了，知道这些，哪工龄的计算公式=datedif(d2,today(),"Y"),真简单啊！ 小陆：还需要加个严谨性的判断=iferror(dateif(d2,today(),"Y"),"日期格式问题"),这样就完美，不过你在学习中，不要忽略函数的返回的错误值是什么！这点很重，如果公式比较复杂的话，出现异常需要通过错误值可以确定大概是那部分的原因。你也可以试试用数学的方法优化第一个公式！ 霉霉：是这样=IFERROR(TEXT(YEAR(TODAY())-YEAR(C2)-(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))>TODAY())*1,"0"),"日期异常")吗？ 小陆：你真的聪明，还加上严谨了性判断，过不多久，你就嫩达到我现在的水平了呢！ 霉霉：为什么不能超过师傅呢？我可是很从聪明的，不过严谨性判断的里的错误提示是随便写的吗？ 小陆：因为师父也在学习啊，也在进步啊！严谨性判断错误提示当然不是随便写的，它是根据你编写公式后，预估什么回导致错误就加以提示，这样即便你嵌套多少公式，一旦报错，你就能知道是那部分程序出错了！ 霉霉：师父，不知不觉天都快黑，咱们出去吃饭吧，我请客，你想吃什么啊？ 小陆：好啊，你有什么忌口的吗？我知道一家挺好的馆子，味道不错！ 霉霉：有好吃的就行，我可是货真价实的吃货，没有忌口的，不过我请啊，别跟我抢啊！ 小陆：不抢，不抢，你请客，我买单！两人就这么你一句我一句，有说有笑的去吃饭了！ 演员/角色：陆之涵/小陆，郭知霉 /霉霉 本故事纯属虚构，如有雷同纯属巧合  公式工龄计算效果图 |
|
|
来自： 我爱极客 > 《Excel使用手册》
