按应收账款明细账来划分账龄，财务审计必会！收藏备用

函数公式、职场模板、财务应用、分析图表、练习题、软件工具、表格合并、Office 365、Power Query、表格美化、符号作用、条件格式、学会骗、一本不正经、避坑指南、数据整理、筛选技巧、偷懒宝典

👆专题文章👆

👇最新文章👇

·  正  ·  文  ·  来  ·  啦  ·

财务审计朋友们，这篇文章，工作中肯定用得上，建议收藏备用。
还有更多Excel在财务应用的专题文章，请点击下面的链接：

Excel财务应用

应收账款账龄

如果不用软件（财务软件、审计软件），手工来划分往来科目的账龄，是一件非常头痛的事。

今天，龙逸凡来给大家“止痛”。

在《偷懒的技术：打造财务Excel达人》第五章第一节，我们介绍了如何根据科目余额表来简单粗暴地划分账龄，那个方法虽然不够精细，但用来应急，还是可以的，具体方法，请大家翻阅第五章，这里不再重复。

前一段时间有读者在群里问：

如何根据应收账款明细账，按先进先出的原则，来精确划分账龄。

趁周末有空，给大家总结整理一下。

思路：

为简化起见，我们以记账日期为业务发生日。由于每一笔业务都有记账日期，我们只需使用辅助列，将那些需核销的应收账款设为0，留下需划分账龄的借方发生额。然后用公式根据记账日期计算出账龄。再用透视表汇总各单位各账龄的应收账款即可。

本需求的难点在于：

如何将需要核销的应收账款借方发生额设为0，留下未核销的。

本文示例文件请在Excel偷懒的技术微信公众号主页发送关键词“账龄”获取。

不是在本文下留言！

不是在本文下留言！！

第一部分  确定未核销的金额

  • 公式一：从后往前的公式

1、只有一个单位时，如何留下未核销的应收账款

我们先来看只有一个单位时，如何用Excel编制公式来核销应收账款。

先将2019年-2021年的应收账款明细账导出来，筛选本月小计、本年累计，并删除，整理成下表：

需要注意的是，明细帐最开始的年初余额（B3单元格），并没有日期，需要手工补填为2018年12月31日），以便计算账龄。
分析：
要准确地划分应收账款账龄，应该按先进先出法。收到的款项，按时间顺序依次核销借方发生额。
想起来就头大，感觉无从下手。
编制公式时，如果感觉进入了死胡同，没法走出来，那就换一个思路，说不定就会简单的多。
既然一笔笔从前往后依次核销比较复杂，我们就改为：
从后往前，凑够应收账款的最终余额。
完全不用去管收到了多少钱，什么时候收的，该去核销哪一笔、该核销多少。
如图所示：
这样的话公式就简单多了。
为便于理解，我们在G10单元格输入公式：
=MIN(D10,F$22-SUM(D11:D$22))
然后将其拖动填充至F3:F22单元格。
强调：
请注意上面公式的单元格引用类型
=MIN(D10,F$22-SUM(D11:D$22))
关于单元格的引用类型，请阅读：
可以看出D列没有借方发生额的单元格，对应的G列计算出来的金额是错的。D列金额为0（为空）时，G列也应该为0。
得加个IF函数来判断，当D22为空时，让其返回0：
=IF(D10="",0,MIN(D10,F$22-SUM(D11:D$22)))
注：上面的公式如果没有显示完整，可按住公式往左拖动，以显示后面的内容。
上面的公式也可以不使用IF函数：
=MIN(D10,F$22-SUM(D11:D$22))*(D10>0)
注：
true相当于1，False相当于0
G3:G10的单元格不应该出现负数：当出现负数时，应该为0，如果有大于零的值，就取大于0的值。
用MAX函数来修正，G3单元格完整的公式为：
=MAX(MIN(D3,F$22-SUM(D4:D$22))*(D3>0),0)
2、有多家单位时，如何确定未核销的应收账款金额

只有一个单位时，最末行的余额我们可以直接填写固定的值或单元格。
但是，当有多家单位时，最末行是不确定的，得用公式来查找该单位最后一行的余额：
LOOKUP(1,0/($A$3:$A$69=A3),$F$3:$F$69)
原公式的SUM(D4:D$22)部分，也得改为SUMIF按单位来条件求和：
SUMIF(A4:A$69,A3,D4:A$69)
第三参数可以简化为D4：
SUMIF(A4:A$69,A3,D4)
其原理详见：
深入理解SUMIF：如何多表多列多条件求和？
将LOOKUP和SUMIF替换套入原公式，G3单元格的完整公式为：
=MAX(MIN(D3,LOOKUP(1,0/($A$3:$A$69=A3),$F$3:$F$69)-SUMIF(A4:A$69,A3,D4))*(D3>0),0)

  • 公式二：从前往后的公式

在理解了从后往前的公式后，我们就可以编制从前往后计算的公式了。
为了简化公式，我们需要将表格处理一下：
先筛选出年初余额行，然后批量将期初余额填列到D列对应单元格。
然后再编制公式。
公式的思路：
用D列，从期初到本行为止的，累计借方应收金额，减掉所有收款金额。具体细节，再用MAX、MIN来修正。
1、只有一个单位时
G1单元格的公式：
=MIN(D3,MAX(0,SUM(D$3:D3)-SUM($E$3:$E$22)))*(D3<>0)
2、有多家单位时
G1单元格的公式
=MIN(D3,MAX(0,SUMIF($A$3:A3,A3,D$3:D3)-SUMIF($A$3:$A$69,A3,$E$3:$E$69)))*(D3>0)
第二部分  划分并统计各单位账龄
我们再来看如何划分账龄
假设账龄划分为五段：
"3个月内","3-6个月","6-12个月","1-2年","2年以上"
基准日为2021年12月31日，在H1单元格录入，那么划分账龄的公式为：
=IF(G3,LOOKUP($H$1-B3,{0,93,184,366,731},{"3个月内","3-6个月","6-12个月","1-2年","2年以上"}),"")
在H列计算出账龄后，再用透视表，统计各单位的应收账款账龄。
第三部分  如何确定核销金额
工作中有时候不需要确定未核销的金额，而是需要确定核销的金额。那公式又该如何确定呢？
G1单元格的公式：
=IF(D3,MAX(MIN(D3-(SUMIF($A$3:$A3,A3,$D$3)-SUMIF($A$3:$A$69,A3,$E$3)),D3),0),"")
注：
同样的，为简化公式，与将期初余额填列到D列借方栏。
只要理解了前面的公式，理解这个公式就没问题。
理解不了前面的，光解释这个公式也没用，所以就不解释了

。

还有更多Excel在财务应用的专题文章，请点击下面的链接：

Excel财务应用

赞 (0)

相关推荐