一文讲透账龄编制方法-excel函数法

创作时间:

作者:

@小白创作中心

一文讲透账龄编制方法-excel函数法

引用

来源

http://www.360doc.com/content/24/0903/06/13664199_1133004789.shtml

本文将详细介绍如何使用Excel函数法编制账龄分析表。通过本文，读者将掌握账龄拆分的原理，并学会使用Excel函数和Power Query等工具自动化处理财务数据。

一、导入场景

在实际工作中，财务和审计人员经常需要基于多期的科目余额表生成账龄分析表。本文将以5年（2019-2023年）的应付账款科目余额表为例，介绍如何快速生成账龄分析表。

二、账龄拆分原理分解

为了更好地建模，我们先梳理一下账龄拆分的原理。假设某供应商的5年应付账款科目余额表如下：

我们基于“先进先出法”（即应付账款的借方发生额先冲销以前年度的应付账款）逻辑将以上科目表建模如下：

建模核心解释：

【分析】列
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代表当前年度期初余额未冲销完，未冲销完部分计入对应账龄中

【账龄】列：我做了一个如下对照表，直接写上去即可
【账龄金额】列
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年科目余额数据合并-使用powerquery合并

我们先需要将5年的科目余额表合并成一张表，然后在合并表上面进行公式设计，我们可以手动把5张excel表合并在一起，也可以使用pq合并(好处就是以后变动自动刷新)
我们讲解pq的合并方法，以2023年数据为例：最初表格如下
1）step1：我们将其变成超级表，并命名为【二三年】：全选该表，然后插入→表格，变成一张超级表：
将其名字修改为【二三年】
2）step2：导入pq中：选中超级表
pq界面如下：
3）step3：重复以上2步骤，把2019-2022年的表格都导入到pq中(超级表名字分别命名为二二年、二一年、二零年和一九年)。效果如下：
4）step4：开始合并，选中任一一种表，假设我们选择二三年
选中刚才合并生成的新表：
这样我们就把5年的科目余额表合并到一张excel中了：