系列文章4——花了5个小时整理的账龄计算与分析的5种方法原创
金蝶云社区-陈世杰身份
陈世杰
2人赞赏了该文章 386次浏览 未经作者许可,禁止转载编辑于2020年07月13日 09:56:57

微信图片_20200710144057.jpg


账龄分析是财务工作中最常见的一个工作,今天共给大家介绍2种账龄计算与3种账龄分析的方法,以便帮助大家在今后的工作中有所提高。



01

账龄计算



下面是一份账款应收逾期的明细,现根据下列的应收日期计算与当前日期之间的差额,在D列的单元格中写出公式。


微信图片_20200711103122.jpg




01



方法一


在D2单元格中输入公式:

=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"个月"&DATEDIF(B2,TODAY(),"md")&"天",按Enter键后完成向下填充即可。


需要注意的是:双引号均是英文半角。TODAY()函数为返回当前的日期,无任何的参数。公式是分别获取了两个日期相隔的年,月,日然后将其连接起来。


如下图所示:


微信图片_20200711103126.jpg



02



方法二



除了上面的方法还有一个更加简单的方法。在D2单元格中输入公式:

=TEXT(SUM(DATEDIF(B2,TODAY(),{"y","ym","md"})*10^{4,2,0}),"0年00月00天")

然后按Enter键后向直填充即可。


需要注意的是:这里先使用Datedif函数将相隔的年,月,日计算出来,再分别乘以10000,100,10进行相加,组成一个五位数,然后使用Text函数返回相应的格式。


如下图所示:


微信图片_20200711103130.jpg





 02  

账龄分析


 今天,教大家账龄分析的几种方法。


如下图所示,是一份应收账款的时间表,按0-30天,30-60天,60-90天,90天以上四个分布范围求账龄。


微信图片_20200711103133.png




01



IF函数+数据透视表法



通常情况下,使用IF函数判断账龄也是比较见的。如下图所示:


微信图片_20200711103136.jpg


在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()函数是返回今天的日期。


然后选中区域,依次单击【插入】-【数据透视表】,然后按以下设置字段的拖放。如下图所示:

微信图片_20200711103139.gif





02



VLOOKUP函数+数据透视表法


在D2单元格中输入公式:

=VLOOKUP(TODAY()-B2,{0,"0-30天";30,"30-60天";60,"60-90天";90,"90天以上"},2,1),按Enter键后向下填充。如下图所示:


微信图片_20200711103143.jpg

最后同上一个方法一样插入数据透视表即可。

:使用VLOOKUP函数的最后一个参数为1时为模糊查找的原理进行查询。



03



LOOKUP函数+数据透视表法


在D2单元格中输入公式:

=LOOKUP(TODAY()-B2,{0,"0-30天";30,"30-60天";60,"60-90天";90,"90天以上"}),按Enter键后向下填充。如下图所示:


微信图片_20200711103146.jpg


最后同上一个方法一样插入数据透视表即可。

:使用LOOKUP函数时,一定要注意只有两个数字,不同于VLOOKUP函数。



作者:我是世杰,财务excel深度玩家,坚持每天分享财务excel干货,微信公众号:24财务excel

赞 2