原创作者: 卢子 转自: Excel不加班

学员的问题,以前的账龄分析都是针对一笔明细,现在有多笔明细,要将这些明细划分成具体的区间。

数据透视表做账龄分析表 Excel账龄分析增强版(1)

以供应商A,结算天数60天,4/30这笔欠款2544为例说明。

逾期天数=TODAY()-F2(表头那里是每月最后一天)-60(月结天数)

刚好是60天,就划分在31-60天这个区间里。

数据透视表做账龄分析表 Excel账龄分析增强版(2)

其他的明细用同样的方法依次类推。

为了方便计算,卢子这里采用了辅助列。

计算今天的日期跟每月最后一天相差的天数。

=TODAY()-C2

数据透视表做账龄分析表 Excel账龄分析增强版(3)

手工写上每个区间的下限,全部转换成天方便判断。

数据透视表做账龄分析表 Excel账龄分析增强版(4)

有了这2个辅助列,问题就变得简单很多。

这个公式的意思就是相差的天数减去结算天数,大于等于区间的下限,小于区间的上限,就进行统计。

=SUMPRODUCT(($C$3:$J$3-$B4>=L$3)*($C$3:$J$3-$B4<M$3)*$C4:$J4)

数据透视表做账龄分析表 Excel账龄分析增强版(5)

3年以上1095天,这个没有上限,只需大于等于这个的下限就行,公式有所差异。

=SUMPRODUCT(($C$3:$J$3-$B4>=R$3)*$C4:$J4)

数据透视表做账龄分析表 Excel账龄分析增强版(6)

SUMPRODUCT函数语法说明:

=SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*求和区域)

其实,问题并不难,都是一些常用函数,难在于思路,有思路就变得很简单。

,