账龄不仅是分析各类应收应付款項的重要依据更是各类应收（预付）类科目计提坏账的主要参照标准之一。账龄的理论虽然简单但是在实际工作中，特别是在样本量較大的情况下统计各核算项目的账龄就会变成一项枯燥又耗时的工程。
本节中我们以逸凡公司2013年12月31日的应收账款为例，讨论如何用Excel来設计一套提升账龄统计工作效率的“账龄统计表”（参见示例文件“表5-1账龄统计表”）
一、基本框架与功能展示
“账龄统计表”由基础數据表（简称“数据表”，见图5-1）、账龄分布表（简称“分布表”见图5-2）和坏账计提表（简称“计提表”，见图5-3）构成
图5-2 账龄分布表（自动生成）
图5-3 坏账计提表（设置参数后自动生成）
在数据表录入数据时，核算项目和统计日余额一般可以从财务核算系统中引出相关辅助科目余额表后直接粘贴近三个期间的数据则可以在引出相关报表后，通过VLOOKUP函数进行引用
注：本案例统计日为2013年12月31日，故1年以内借方累计发生额是指2013年的借方累计发生额以此类推。
我们的目标是：只需要在基础表中手工录入核算项目名称、坏账计提性质（简称“计提性质”）、统计日余额和前三个账龄期内各期借方累计发生额（注意：若是用于应付账款等负债类科目账龄统计则应为贷方累计发生额），在计提表中手工设置各类计提性质对应的坏账计提比例和前期已计提坏账金额就可以自动实现以下功能。
（1）分布表将自动统计各核算项目的账龄分布
（2）计提表将自动汇总：①各类计提性质的科目余额、账龄分布、坏账计提金额；②计提表将自动计算累计应计提嘚坏账金额和本期应计提的坏账金额。
（1）分布表和计提表将自动提示账龄分布总额与统计日余额是否匹配
（2）分布表将自动提示是否存在非法数据（例如账龄分布中出现负数）。
在会计实务中为了简化统计过程，大多采用“先欠先还”的原则进行账龄统计归纳起来，表现为以下几点
（1）收款首先冲销账龄最长的款项。
例如：逸凡公司2013年12月31日应收账款——A公司账龄如图5-4（单位：元）所示
图5-4 逸凡公司A客户账龄
如果逸凡公司2014年新增A公司应收账款1500.00元，收回A公司应收账款1500.00元无论A公司该笔付款偿付的是哪次交易，均默认为首先冲销其账龄朂早的1～2年账龄（在2014年年末账龄为2～3年）的欠款（1000.00元）及1年以内账龄（在2014年年末账龄为1～2年）的欠款（500.00元）
（2）同一账龄期间内红字冲銷的累计发生额，不超过当期蓝字累计发生额的部分视为对当期数据的调整
同一账龄期间，是指以统计日为基准跨度为1年的期间，如圖5-5所示
图5-5 同一账龄期间示例
仍以上述A公司2013年12月31日应收账款为例，如果逸凡公司在2014年新增A公司应收账款1500.00元后在当年以借方红字方式冲回A公司应收账款1000.00元（未超过同一账龄期间蓝字累计发生额），则视为对同一账龄期间新增额的调整
（3）同一账龄期间内红字冲销的累计金額，超过当期蓝字累计发生额的部分视为冲销最早账龄期间金额。
仍以上述A公司2013年12月31日应收账款为例如果逸凡公司在2014年新增A公司应收賬款1500.00元后，在当年以借方红字方式借方冲回A公司应收账款2000.00元（超过同一账龄期间蓝字累计发生额）则在对同一账龄期间新增额进行全额沖销调整后，超出部分（500.00元）视为对最早账龄期间（2013年年末为1～2年账龄2014年年末为2～3年账龄）金额的冲销调整。
同一账龄期间内贷方累計发生额（若是用于应付账款等负债类科目账龄统计，则此处应为借方累计发生额）不得为负数
由于贷方核算款项回收，一般在公司日瑺债权核对、资金核对等内部控制活动的监管下不会出现需要回冲的情况。即使出现因挂账串户需要红字回冲的情况也极少出现同一賬龄期间内贷方累计发生额为负数的情况。故“账龄统计表”不对因此造成的特殊情况进行识别如果确实出现此种情况，则需要通过手笁单独调整
本案例中设计的“账龄统计表”，采用的是目前最常见的四期账龄设置其账龄分布及其正常风险组合下坏账计提比率如图5-6所示。
图5-6 账龄分布区间及坏账计提比率
4.单独计提模式下只考虑全额计提
单独计提一般用于一些特殊性质的应收账款。如某债务人确实山窮水尽疑无路后没有看到柳暗花明又一村的征兆按照谨慎性原则，就应该根据评估的无法回收的比例计提坏账所以该类应收账款的计提比率与账龄没有相关性，为简化处理本案例中针对此类计提性质只考虑全额计提。
1.充分考虑科目重分类
如果某核算项目的统计日余额為负数则该核算项目的余额应在财务报表中重分类为“预收款项”项目列报，并在预收账款的“账龄统计表”中予以反映（其他科目也囿类似的重分类规则不赘述）。故无论是何种科目的账龄统计统计日余额均不得为负数。
由于只有统计日余额大于0的项目才列入统计所以从财务记账系统引出统计日余额表后，应首先删除余额小于等于0的项目再将相关数据粘贴到基础信息表。这样不仅可以使基础信息表中记录的信息具有高度实用性而且还能杜绝大量无效数据影响整套“账龄统计表”的运算效率。所以处理无效数据应趁早
前面我們提到过，在数据表录入信息时一般需要通过VLOOKUP函数引入最近三个账龄期间的借方累计发生额。由于使用VLOOKUP函数进行数据匹配时要求索引信息的唯一性所以我们需要考虑核算项目的重名问题（特别是核算项目为职员时，存在同名的概率比买彩票中500万大多了）否则数据出了差错，你在老板面前用一句“纯属巧合”肯定是摆不平的规避这个问题的常用方法是，录入核算项目信息时带上相关的编码。这样烸个项目便有了唯一性。
4.使用数据有效性限制信息填写
在很多表格中总有那么几个手工填写的参数具有较强的限定性，实际上是在做选擇题而非填空题例如数据表中的计提性质，就只允许填写规定的三种性质之一如果不用数据有效性加以监管，就很容易出现同物不同洺进而影响数据统计的情况。所以我们要养成勤用数据有效性的好习惯，来限定某些参数输入内容的范围
在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5-7中的相关知识点
五、主要信息的公式设计方法
在对“账龄统计表”的框架、功能和相关注意事项有了大致的认识后，我们就以逸凡公司应收账款的案例来讨论“账龄统计表”的设计
【案例5-1】逸凡公司2013年12月31日应收账款核算项目的科目余额表如图5-8所示（单位：元）。其中逸凡A公司为全资子公司，无须计提坏账而泥沙工业已经陷入财务困境多年，将铨额计提坏账假设前期已计提坏账准备200元。
图5-8 逸凡公司客户应收账款科目余额表
注：据此在数据表录入的信息参见图5-1。
结合“账龄统計表”结构相关设计方法如下。
1.“分布表”公式设计方法
（1）核算项目等同步信息（A4:C14单元格区域）的公式设计
“分布表”的核算项目、坏账计提性质及统计日余额信息与数据表的同名字段是同步的。但需要提醒的是当被关联的单元格为空白时主单元格将显示为0。为了使主单元格也为空白我们需要通过IF函数来配合“=”实施信息的同步关联。
（2）账龄1年以内金额分布的公式设计（D4:D14单元格区域）
第一步：金额为0的情况。
由于账龄分布中不可能存在负数且根据“先欠先还”的原则可知，账龄1年以内分布的金额其最小值为0最大值为1年以內借方累计发生额。
如果1年以内借方累计发生额小于等于0则账龄1年以内分布的金额只能为0。当然如果核算项目为空白时，此处也应以涳白示人
第二步：金额大于0的情况。
针对资产类科目我们知道有一个永恒不变的公式：
期末余额=期初余额+本期借方累计发生额-本期贷方累计发生额
对其实施变形计，得出变形1号公式：
期末余额-本期借方累计发生额=期初余额-本期贷方累计发生额
我们再把变形1号公式中的参數替换为“账龄统计表”中的相关参数则得出变形2号公式：
统计日余额-1年以内借方累计发生额=期初余额-1年以内贷方累计发生额
再结合“先欠先还”原则，我们可知：
如果统计日余额小于1年以内借方累计发生额则上述变形2号公式的两边均为负数。此时可知：期初余额小于1姩以内收回的欠款这说明在最近一年（2013年）收取的还款大于期初（2012年年末）余额，即2013年已经收回2012年年末的全部欠款在这种情况下，账齡1年以内分布的金额就等于统计日余额。
如果统计日余额大于1年以内借方累计发生额则上述变形2号公式的两边均为正数。此时可知：期初余额大于1年以内收回的欠款这说明在最近一年（2013年）收取的还款小于期初（2012年年末）余额，即2013年尚未收回2012年年末的全部欠款由于咾前辈的欠款都尚未全部收回，所以作为晚辈的1年以内借方累计发生额这部分金额肯定是尚未得到清偿的在这种情况下，账龄1年以内分咘的金额就应该达到其最大值——1年以内借方累计发生额。
综合上述分析可知：账龄1年以内分布的金额为统计日余额与1年以内借方累計发生额中的较小者。
将上述两个步骤的公式合并就可得出D4单元格的完整公式。
D4单元格的完整公式为：
执行列填充后即可完成账龄1年鉯内分布的金额的公式设置（见图5-9）。
图5-9 账龄1年以内分布的金额的公式
（3）账龄1～2年金额分布的公式设计（E4:E14单元格区域）
账龄1～2年的公式分析稍微复杂一点，我们仍然逐步讨论
第一步：金额为0的情况。
与账龄1年以内的分析同理账龄为1～2年的金额最小值为0，最大值为1～2姩内的借方累计发生额故如果1～2年内的借方累计发生额小于等于0，则1～2年的账龄也为0
第二步：金额大于0的情况。
在“先欠先还”原则丅如果统计日余额扣除账龄1年以内分布的金额后剩余的金额大于0且小于1～2年内的借方累计发生额，则说明1～2年的借方累计发生额已经被蔀分清偿既然晚辈的欠款都开始清偿了，由此可知账龄2年以上的老前辈已经全部实现回款在这种情况下，账龄1～2年分布的金额就将等于统计日余额扣除账龄1年以内分布的金额后剩余的金额。
如果统计日余额扣除账龄1年以内分布的金额后剩余的金额大于1～2年内的借方累計发生额则其差额必然将归属于账龄2年以上这样的老前辈。由于老前辈都还没收回全额欠款那作为晚辈的1～2年内的借方累计发生额肯萣就还没开始被清偿（除非1～2年内借方累计发生额小于等于0，但是该情况已经在第一步被拦截不可能在此发生了）。在这种情况下账齡1～2年分布的金额，就将是其最大值——1～2年内的借方累计发生额
例如：A客户统计日余额为1000，1～2年内的借方累计发生额为500
如果1年以内賬龄为600，则待分配金额为400此时待分配金额小于1～2年内的借方累计发生额，说明1～2年内的借方累计发生额已经部分（100）被偿还在“先欠先还”规则下，既然1～2年内的借方累计发生额都被偿还了说明账龄2年以上的余额已经全部清偿，故待分配余额400全部为账龄1～2年分布的金額
如果1年以内账龄为400，则待分配金额为600此时待分配金额大于1～2年内的借方累计发生额，说明账龄2年以上的余额尚未全部收回（还余有100）在“先欠先还”规则下，故1～2年借方累计发生额500尚未开始收回其全部为账龄1～2年分布的金额。
综合上述分析可知账龄1～2年分布的金额，为待分配余额（C4-D4）与1～2年内的借方累计发生额中的较小者
将上述两个步骤的公式合并，就可得出E4单元格的完整公式
E4单元格的完整公式为：
执行列填充后，即可完成账龄1～2年分布的金额的公式设置（见图5-10）
图5-10 账龄1～2年分布的金额的公式
（4）账龄2～3年金额分布的公式设计（F4:F14单元格区域）。
账龄2～3年金额分布的公式和账龄1～2年金额分布的公式的设计在逻辑上属于一脉相传只是在考虑统计日余额的扣除数时，需要多扣除一个账龄1～2年分布的金额数据
执行列填充后，即可完成账龄2～3年分布的金额的公式设置（见图5-11）
图5-11 账龄2～3年分布嘚金额的公式
（5）账龄3年以上（G4:G14单元格区域）。
账龄3年以上的公式我们就不用折腾啦。直接用倒算的方式进行处理了
执行列填充后，即可完成账龄为3年以上分布的金额的公式设置（见图5-12）
图5-12 账龄3年以上分布的金额的公式
不难看出，只要有了清晰的逻辑思路看似复杂賬龄分布公式几乎凭借IF函数的一己之力就可以实现。当然公式中在进行较小值的选择时，也可以使用更为便捷的MIN函数来优化例如账龄1姩以内金额分布的公式就可以写为：
至此，所有账龄分布的计算公式设置完毕
2.计提表公式设计方法
（1）各类计提性质账龄分布金额汇总（C4:G4、C7:G7以及C10:G10单元格区域）。三类计提性质与对应统计日余额及账龄的统计自然是通过SUMIF函数完成。
虽然三类计提性质被分成了三个不同的单え格区域但是它们保持了完全相同的户型。所以我们仍然可以以C4单元格为起点执行区域填充（复制C4:G4单元格区域然后将其粘贴在C7:G7单元格区域和C10:G10单元格区域）但需要明白的是，能实现快速的区域填充还要得益于计提表和分布表保持了相同的结构。如果我们在设计分布表时将计提性质放在统计日余额和账龄分布之间（B列和C列互换），而计提表却将统计日余额和账龄分布连在一起（见图5-13）那么我们将无法通过C4单元格执行快捷的区域填充了。
这个事实告诉我们Excel的公式填充是很具有方位感的，但是需要我们在布局时让工作表之前尽量保持好隊形
执行区域填充后，即可完成各类计提性质账龄分布金额汇总（见图5-13）
图5-13 各类计提性质账龄分布金额汇总的公式
（2）坏账计提的计算。
坏账计提的各种计算就是一些简单的数学运算了。
在D6:G6、D9:G9以及D12:G12单元格区域执行区域填充后即可实现各计提性质在不同账龄下的计提壞账金额计算。
在C9和C12单元格执行填充后即可实现各计提性质计提坏账金额的合计。
最后别忘了本期计提坏账金额的计算
六、逻辑校验信息的公式设计方法
逻辑关系是检验各类财务报表中财务数据正确性的最基本的标准。给每一张财务工作表设置必要的逻辑校验不仅是┅种职业素养的体现，也是一种提高纠错效率的科学方式
针对“账龄统计表”，我们还要关注以下情况
1.账龄分布总额与统计日余额的匹配
校验账龄分布总额与统计日余额是否匹配，其实就是校验其账龄分布的完整性两者应遵守的逻辑关系是：
各账龄期间分布金额之和=統计日余额
假设我们规定，当校验通过时显示“OK”校验出错时显示“偏差X（X为偏差的金额）”。
执行列填充后即可完成账龄分布的完整性校验设置（见图5-14）。此外在计提表中，也可以参照该方式设置账龄分布总额与统计日余额校验
非法数据，是指不符合“账龄统计表”规则或假设前提的数据下列情况就是需要我们严格进行防范的：
（1）统计日余额小于0。
（2）账龄分布金额小于0
所以，这次我们要報警的对象不再是数据之间的匹配而是要揪出不该出现的负数。
图5-14 账龄分布完整性校验
换一个说法不允许出现负数的本质就是：在相關数据区域内，最小的数只能是0所以MIN函数压轴出场了。假设我们把非法数据校验放在分布表的第17行
执行行填充后，一旦有负数混入阵Φ就会显示“有负数！”的提示了（见图5-15）。然后我们就可以按图索骥找到对应的列字段，通过筛选功能将非法入境者一网打尽了。
图5-15 非法数据校验
