二维码 购物车
部落窝在线教育欢迎您!

两个经典公式套路,让你快速制作账龄统计表

 

作者:老菜鸟来源:部落窝教育发布时间:2019-11-29 14:39:38点击:670

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!说到账龄统计表,相信是不少财务小姐姐的噩梦,虽说用数据透视表也能解决一部分问题,但是它始终存在一定的局限性。今天老菜鸟将分享两种公式解题套路,并深挖两种套路背后的解题思路,赶紧来看看吧!

 

财务工作者对于账龄统计表都不陌生,我们之前也分享过很多关于账龄统计表的操作技巧和公式解析,但是今天遇到的这种账龄统计表,还是让财务小姐姐犯了难,到底是个怎样的账龄统计表呢,一起来看看吧:

 

 

如图所示,B2单元格是一个制表日期,AD列是数据源,其中有两列是最关键的,一个就是应收款日期(也可能是其他日期,叫法不一),另一个是金额合计。

 

需要实现蓝色区域的效果,即按照不同的账龄,将金额填写在对应的列中。

 

由于数据表的格式相对固定,不考虑使用操作或者透视表分组的方式来实现,下面分享两个公式套路,来解决这个问题。

 


公式套路1IF+LOOKUP

 


公式为:=IF(LOOKUP($B$1-$B3,{-999,0,30,60,90},$E$2:$I$2)=E$2,$D3,"")

 

 

这个公式的核心还是LOOKUP函数的运用,再来复习一下LOOKUP的基本用法:

 

LOOKUP(查找值,查找区域,结果区域),在这个例子中,查找值是$B$1-$B3,也就是用制表日期减去应收款日期,得到一个天数,由于公式要在整个区域使用,所以需要理解“$”在其中的作用。

 

可以看看这一步的结果,便于理解问题:

 

 

账龄的统计用到的是区间查找的原理,在这个例子中,将具体的天数划分成了五个区间,在区间查找时,要使用每个区间对应的下限值,未到期是指日期差为负数的,因此并没有具体的下限,我们可以指定一个绝对值较大的负数,例如-999,其他四个区域的下限都好确定,因此查找区域就是{-999,0,30,60,90},这里用一个常量数组作为查找区域,大括号是直接输入的。

 

根据账龄天数,我们需要得到对应的账龄描述,也就是$E$2:$I$2中的内容,再来看看LOOKUP的结果:

 

 

有了这个结果,下面只需要比较蓝色区域中的内容是否与对应的账龄描述一致,如果一致,返回金额合计,如果不一致,返回空值。

 

因此最终的公式就是=IF(LOOKUP($B$1-$B3,{-999,0,30,60,90},$E$2:$I$2)=E$2,$D3,"")

 

如果明白了第一个公式的套路,再来看看第二个公式套路。



公式套路2IF+COLUMN+MATCH

 


公式为:=IF(COLUMN(A1)=MATCH($B$1-$B3,{-999,0,30,60,90},1),$D3,"")

 

 

与公式1的不同之处在于,这个公式的核心是MATCH函数,与LOOKUP函数不同,MATCH得到的是一个序列号。在本例中,查找值和查找区域与公式1完全一样,但是结果得到的是一个数字:

 

 

可以这样理解,数字5就表示该行数据的账龄天数对应账龄统计的第5个分类,其他数字的含义相同。

 

接下来需要做的是判断这个数字和分类所在的顺序是否一致,因为分类是横向排列的,所以这里用到了COLUMN函数,一个得到列号的函数,表示分类在第几列。

 

=COLUMN(A1)=MATCH($B$1-$B3,{-999,0,30,60,90},1),通过图示可以直观的看到,TRUE所在的位置就是要显示金额的位置:

 

 

在此基础上,再用IF函数完成最终的结果即可。

 

小结:今天这个示例在财务账龄分析中出现的频率还是蛮高的,涉及的两个公式不算很难,对于核心函数LOOKUPMATCH需要结合以前的教程去理解,如果对这两个函数还比较熟悉的话,就可以结合本例来拓宽自己解决问题的思路,从而达到活学活用的目标,其实就这个问题,还有很多其他的公式解法,有兴趣的朋友不妨自己开动脑筋,如果想到其他思路也欢迎留言和大家分享。

 

本文配套的练习课件请加入QQ群:747953401下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

初识LOOKUP函数VLOOKUP&LOOKUP双雄战(一):VLOOKUP的漂亮开局

初识MATCH函数MATCH:函数哲学家,找巨人做伴。新出道必学!

制作账龄统计表一张应收款账龄统计表逼哭了多少会计人?