账龄分析是财务工作中最常见的一个工作,今天共给大家介绍2种账龄计算与3种账龄分析的方法,以便帮助大家在今后的工作中有所提高。
下面是一份账款应收逾期的明细,现根据下列的应收日期计算与当前日期之间的差额,在D列的单元格中写出公式。
01
方法一
在D2单元格中输入公式:
=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"个月"&DATEDIF(B2,TODAY(),"md")&"天",按Enter键后完成向下填充即可。
需要注意的是:双引号均是英文半角。TODAY()函数为返回当前的日期,无任何的参数。公式是分别获取了两个日期相隔的年,月,日然后将其连接起来。
如下图所示:
02
方法二
除了上面的方法还有一个更加简单的方法。在D2单元格中输入公式:
=TEXT(SUM(DATEDIF(B2,TODAY(),{"y","ym","md"})*10^{4,2,0}),"0年00月00天")
然后按Enter键后向直填充即可。
需要注意的是:这里先使用Datedif函数将相隔的年,月,日计算出来,再分别乘以10000,100,10进行相加,组成一个五位数,然后使用Text函数返回相应的格式。
如下图所示:
今天,教大家账龄分析的几种方法。
如下图所示,是一份应收账款的时间表,按0-30天,30-60天,60-90天,90天以上四个分布范围求账龄。
01
IF函数+数据透视表法
通常情况下,使用IF函数判断账龄也是比较见的。如下图所示:
在D2单元格中输入公式:
=IF(TODAY()-B2>=90,"90天以上",IF(AND(TODAY()-B2>=60,TODAY()-B2<90),"60-90天",if(and(today()-b2>=30,TODAY()-B2<60),"30-60天","0-30天"))),按Enter键后向下填充。
注:使用IF函数使公式显得很长,条理性差。AND函数是表示两个条件同时成立时返回TRUE,如果有一个不成立,则返回FALSE。TODAY()函数是返回今天的日期。
然后选中区域,依次单击【插入】-【数据透视表】,然后按以下设置字段的拖放。如下图所示:
02
VLOOKUP函数+数据透视表法
在D2单元格中输入公式:
=VLOOKUP(TODAY()-B2,{0,"0-30天";30,"30-60天";60,"60-90天";90,"90天以上"},2,1),按Enter键后向下填充。如下图所示:
最后同上一个方法一样插入数据透视表即可。
注:使用VLOOKUP函数的最后一个参数为1时为模糊查找的原理进行查询。
03
LOOKUP函数+数据透视表法
在D2单元格中输入公式:
=LOOKUP(TODAY()-B2,{0,"0-30天";30,"30-60天";60,"60-90天";90,"90天以上"}),按Enter键后向下填充。如下图所示:
最后同上一个方法一样插入数据透视表即可。
注:使用LOOKUP函数时,一定要注意只有两个数字,不同于VLOOKUP函数。
作者:我是世杰,财务excel深度玩家,坚持每天分享财务excel干货,微信公众号:24财务excel