本公众号专属群【财税发烧友2群】中一个群友问我如何基于5年的应付账款的科目余额表快速生成账龄分析表，最终呈现账龄表格如下：
基于的科目余额表为5年(2019-2023年数据）的科目余额表数据，其中1年的数据格式如下：
个人觉得这种场景对财务和审计人员经常场景，于是我特别编写了2个模板以解决这个问题：
1、一个基于excel函数公式的模板-半自动
2、一个基于pp和pq(excelpowerquery和excelpowerpivot）模板-全自动
二、账龄拆分原理分解
为了更好建模，我们梳理一下账龄拆分的原理
我们先拿一个供应商的数据来分析，假设某供应商的5年应付账款科目余额表如下：
我们基于“先进先出法”(即应付账款的借方发生额先冲销以前年度的应付账款)逻辑将以上科目表建模如下：
建模核心解释：
1、【分析】列
L7所在单元格公式为:=SUM(K7:$K$11)-I7
L8所在单元格公式为:=SUM(K8:$K$11)-I8
L9所在单元格公式为:=SUM(K9:$K$11)-I9
L10所在单元格公式为:=SUM(K10:$K$11)-I10
L11所在单元格公式为:=SUM(K11:$K$11)-I11
公式这里K11是绝对引用，其他都是相对引用，我们称之为'锁尾不锁头'！
其中SUM部分代表当前年度及以后年度的应付账款的借方发生额，减去I8就是当前年度的期初余额---总体的意思是当前年度及以后年度的应付账款的借方发生额冲销当前年度的期初余额：
大于0代表当前年度的期初余额没有剩余了，不需要计入账龄表中
小于0代表当前年度期初余额未冲销完，未冲销完部分计入对应账龄中
2、【账龄】列：我做了一个如下对照表，直接写上去即可
3、【账龄金额】列
N7所在单元格公式=IF(L7>0,0,-L7)+IF(H7=5,0,MIN(L6,0))
N8-N11直接往下拉拽公式即可
N12单元格公式=K12-SUM(N7:N11)---这里使用倒挤法挤出来“1年内”的账龄金额！
我们来解释一下N7公式=IF(L7>0,0,-L7)+IF(H7=5,0,MIN(L6,0))：
1）=IF(L7>0,0,-L7)这部分代表【分析】列中未被当年以及以后年度的借方发生额未冲销完的当前期初余额部分，大于0就表示没有未冲销完的部分，计入0，否则就将未冲销部分计入
2）IF(H7=5,0,MIN(L6,0))这是调整部分，因为第5年的话不需要调整，如果有剩余部分就计入“5年上”账龄即可；但是如果4-1年的话就必须减去上1年期初未冲销完部分计入账龄表的部分---这个比较难理解，我们看一下如下情形也许就很容易理解了：
理解了以上原理我们就已经成功了80%，接下来我们就可以进入模板设计了：
三、模板设计
我们先需要将5年的科目余额表合并成一张表，然后在合并表上面进行公式设计，我们可以手动把5张excel表合并在一起，也可以使用pq合并(好处就是以后变动自动刷新)
我们讲解pq的合并方法，以2023年数据为例：最初表格如下
1）step1：我们将其变成超级表，并命名为【二三年】：全选该表，然后插入→表格，变成一张超级表：
将其名字修改为【二三年】
2）step2：导入pq中：选中超级表
pq界面如下：
3）step3：重复以上2步骤，把2019-2022年的表格都导入到pq中(超级表名字分别命名为二二年、二一年、二零年和一九年)。效果如下：
4）step4：开始合并，选中任一一种表，假设我们选择二三年
选中刚才合并生成的新表：
这样我们就把5年的科目余额表合并到一张excel中了：
1）【年份】列公式：
=DATEDIF([@日期],账龄日期[账龄截止日],'Y')+1
公式里面的账龄日期【账龄截止日】是2023-12-31---这个可以根据情况修改，它放置在账龄报表的结果输出页！
2）【对应账龄】列公式：
=VLOOKUP([@年份],账龄对照,2,0)
这个公式基于如下【对照表】
3）【分析】列公式：
=SUMIFS([本期借方],[往来单位名称],[@往来单位名称],[年份],'<='&[@年份])-[@期初金额]
公式逻辑跟“二、原理分析”中的逻辑一致，只不过这里要加入“供应商”这个维度！
4）【账龄金额】列公式：
=IF([@分析]>0,0,-[@分析])+MIN(0,SUMIFS([分析],[年份],[@年份]+1,[往来单位名称],[@往来单位名称]))
公式解释：
1）【应付余额】列公式：
=SUMIFS(应付5年明细!F:F,应付5年明细!B:B,[@供应商],应付5年明细!A:A,账龄日期[账龄截止日])
2）【1年内】列公式：
=[@应付余额]-SUM(表10[@[1-2年]:[5年上]])
3）【1-2年】列公式：
=SUMIFS(应付5年明细!$J:$J,应付5年明细!$B:$B,$B6,应付5年明细!$H:$H,E$5)
4）【2-3年】列公式：
=SUMIFS(应付5年明细!$J:$J,应付5年明细!$B:$B,$B6,应付5年明细!$H:$H,F$5)
5）【3-4年】列公式：
=SUMIFS(应付5年明细!$J:$J,应付5年明细!$B:$B,$B6,应付5年明细!$H:$H,G$5)
6）【4-5年】列公式：
=SUMIFS(应付5年明细!$J:$J,应付5年明细!$B:$B,$B6,应付5年明细!$H:$H,H$5)
7）【5年上】列公式：
=SUMIFS(应付5年明细!$J:$J,应付5年明细!$B:$B,$B6,应付5年明细!$H:$H,I$5)