快好知 kuaihz

excel账龄计算:两个经典公式快速制作账龄统...

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

*********

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

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

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

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

公式套路1 IF+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,"")。

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

公式套路2 IF+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函数完成最终的结果即可。

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

****部落窝教育-excel账龄统计公式****

原创:老菜鸟/部落窝教育(未经同意,请勿转载)

本站资源来自互联网,仅供学习,如有侵权,请通知删除,敬请谅解!
搜索建议:公式  公式词条  两个  两个词条  快速  快速词条  计算  计算词条  制作  制作词条  
办公

 PPT做得没别人快?30个超常用...

平时在做PPT的时候,总觉得自己做得没有别人快?那是因为大神都是熟悉使用快捷键在做PPT的,小白还停留在鼠标的拖拽和点击!你是哪一种?所以,有效使用PPT快捷键...(展开)

办公

 PPT图片分条动画,另一种风情

昨天我们学习了如何用文本填充的方式把图片分割成,今天我们继续这个话题,今天我们不把图片分块,而是分成条条,先看效果:图片分块进入分块退出。做法如下:一、首先设置...(展开)