Excel读书笔记26——账龄分析表——IF函数经典应用示例

账龄不仅是分析各类应收应付款项的重要依据，更是各类应收（预付）类科目计提坏账的主要参照标准之一。账龄的理论虽然简单，但是在实际工作中，特别是在样本量较大的情况下，统计各核算项目的账龄就会变成一项枯燥又耗时的工程。

本节中，我们以逸凡公司2013年12月31日的应收账款为例，讨论如何用Excel来设计一套提升账龄统计工作效率的“账龄统计表”（参见示例文件“表5-1账龄统计表”）。

一、基本框架与功能展示

“账龄统计表”由基础数据表（简称“数据表”，见图5-1）、账龄分布表（简称“分布表”，见图5-2）和坏账计提表（简称“计提表”，见图5-3）构成。

图5-1 基础数据表 图5-2 账龄分布表（自动生成） 图5-3 坏账计提表（设置参数后自动生成）

在数据表录入数据时，核算项目和统计日余额一般可以从财务核算系统中引出相关辅助科目余额表后直接粘贴。近三个期间的数据则可以在引出相关报表后，通过VLOOKUP函数进行引用。

注：本案例统计日为2013年12月31日，故1年以内借方累计发生额是指2013年的借方累计发生额，以此类推。

我们的目标是：只需要在基础表中手工录入核算项目名称、坏账计提性质（简称“计提性质”）、统计日余额和前三个账龄期内各期借方累计发生额（注意：若是用于应付账款等负债类科目账龄统计，则应为贷方累计发生额），在计提表中手工设置各类计提性质对应的坏账计提比例和前期已计提坏账金额，就可以自动实现以下功能。

1.主要信息功能

（1）分布表将自动统计各核算项目的账龄分布。

（2）计提表将自动汇总：①各类计提性质的科目余额、账龄分布、坏账计提金额；②计提表将自动计算累计应计提的坏账金额和本期应计提的坏账金额。

2.逻辑校验功能

（1）分布表和计提表将自动提示账龄分布总额与统计日余额是否匹配。

（2）分布表将自动提示是否存在非法数据（例如账龄分布中出现负数）。

二、基本前提及假设

1.“先欠先还”假设

在会计实务中，为了简化统计过程，大多采用“先欠先还”的原则进行账龄统计。归纳起来，表现为以下几点。

（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元）。

即2014年年末A公司应收账款余额为3000.00元，账龄为：1年以内1500.00元，1～2年1500.00元。

（2）同一账龄期间内红字冲销的累计发生额，不超过当期蓝字累计发生额的部分视为对当期数据的调整。

同一账龄期间，是指以统计日为基准，跨度为1年的期间，如图5-5所示。

图5-5 同一账龄期间示例

仍以上述A公司2013年12月31日应收账款为例，如果逸凡公司在2014年新增A公司应收账款1500.00元后，在当年以借方红字方式冲回A公司应收账款1000.00元（未超过同一账龄期间蓝字累计发生额），则视为对同一账龄期间新增额的调整。

即2014年年末A公司应收账款余额为3500.00元，账龄为：1年以内500.00元，1～2年2000.00元，2～3年1000.00元。

（3）同一账龄期间内红字冲销的累计金额，超过当期蓝字累计发生额的部分，视为冲销最早账龄期间金额。

仍以上述A公司2013年12月31日应收账款为例，如果逸凡公司在2014年新增A公司应收账款1500.00元后，在当年以借方红字方式借方冲回A公司应收账款2000.00元（超过同一账龄期间蓝字累计发生额），则在对同一账龄期间新增额进行全额冲销调整后，超出部分（500.00元）视为对最早账龄期间（2013年年末为1～2年账龄，2014年年末为2～3年账龄）金额的冲销调整。

即2014年年末A公司应收账款余额为2500.00元，账龄为：1年以内0.00元，1～2年2000.00元，2～3年500.00元。

2.非负数假设

同一账龄期间内，贷方累计发生额（若是用于应付账款等负债类科目账龄统计，则此处应为借方累计发生额）不得为负数。

由于贷方核算款项回收，一般在公司日常债权核对、资金核对等内部控制活动的监管下，不会出现需要回冲的情况。即使出现因挂账串户需要红字回冲的情况，也极少出现同一账龄期间内贷方累计发生额为负数的情况。故“账龄统计表”不对因此造成的特殊情况进行识别。如果确实出现此种情况，则需要通过手工单独调整。

3.四期账龄假设

本案例中设计的“账龄统计表”，采用的是目前最常见的四期账龄设置，其账龄分布及其正常风险组合下坏账计提比率如图5-6所示。

图5-6 账龄分布区间及坏账计提比率

4.单独计提模式下，只考虑全额计提

单独计提一般用于一些特殊性质的应收账款。如某债务人确实山穷水尽疑无路后没有看到柳暗花明又一村的征兆，按照谨慎性原则，就应该根据评估的无法回收的比例计提坏账。所以该类应收账款的计提比率与账龄没有相关性，为简化处理，本案例中针对此类计提性质只考虑全额计提。

三、注意事项

1.充分考虑科目重分类

如果某核算项目的统计日余额为负数，则该核算项目的余额应在财务报表中重分类为“预收款项”项目列报，并在预收账款的“账龄统计表”中予以反映（其他科目也有类似的重分类规则，不赘述）。故无论是何种科目的账龄统计，统计日余额均不得为负数。

2.及时排除无效数据

由于只有统计日余额大于0的项目才列入统计，所以从财务记账系统引出统计日余额表后，应首先删除余额小于等于0的项目，再将相关数据粘贴到基础信息表。这样不仅可以使基础信息表中记录的信息具有高度实用性，而且还能杜绝大量无效数据影响整套“账龄统计表”的运算效率。所以处理无效数据应趁早。

3.高度警惕重名现象

前面我们提到过，在数据表录入信息时，一般需要通过VLOOKUP函数引入最近三个账龄期间的借方累计发生额。由于使用VLOOKUP函数进行数据匹配时要求索引信息的唯一性，所以我们需要考虑核算项目的重名问题（特别是核算项目为职员时，存在同名的概率比买彩票中500万大多了）。否则数据出了差错，你在老板面前用一句“纯属巧合”肯定是摆不平的。规避这个问题的常用方法是，录入核算项目信息时，带上相关的编码。这样，每个项目便有了唯一性。

4.使用数据有效性限制信息填写

在很多表格中，总有那么几个手工填写的参数具有较强的限定性，实际上是在做选择题而非填空题。例如数据表中的计提性质，就只允许填写规定的三种性质之一。如果不用数据有效性加以监管，就很容易出现同物不同名，进而影响数据统计的情况。所以，我们要养成勤用数据有效性的好习惯，来限定某些参数输入内容的范围。

四、知识点装备

在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5-7中的相关知识点。

图5-7 相关知识点

五、主要信息的公式设计方法

在对“账龄统计表”的框架、功能和相关注意事项有了大致的认识后，我们就以逸凡公司应收账款的案例来讨论“账龄统计表”的设计。

【案例5-1】逸凡公司2013年12月31日应收账款核算项目的科目余额表如图5-8所示（单位：元）。其中，逸凡A公司为全资子公司，无须计提坏账，而泥沙工业已经陷入财务困境多年，将全额计提坏账。假设前期已计提坏账准备200元。

图5-8 逸凡公司客户应收账款科目余额表

注：据此，在数据表录入的信息参见图5-1。

结合“账龄统计表”结构，相关设计方法如下。

1.“分布表”公式设计方法

（1）核算项目等同步信息（A4:C14单元格区域）的公式设计。

“分布表”的核算项目、坏账计提性质及统计日余额信息与数据表的同名字段是同步的。但需要提醒的是，当被关联的单元格为空白时主单元格将显示为0。为了使主单元格也为空白，我们需要通过IF函数来配合“=”实施信息的同步关联。

A4单元格的公式为：=IF(数据表!A4="","",数据表!A4)

B4单元格的公式为：=IF(数据表!B4="","",数据表!B4)

C4单元格的公式为：=IF(数据表!C4="","",数据表!C4)

（2）账龄1年以内金额分布的公式设计（D4:D14单元格区域）。

第一步：金额为0的情况。

由于账龄分布中不可能存在负数，且根据“先欠先还”的原则可知，账龄1年以内分布的金额其最小值为0，最大值为1年以内借方累计发生额。

如果1年以内借方累计发生额小于等于0，则账龄1年以内分布的金额只能为0。当然，如果核算项目为空白时，此处也应以空白示人。

D4单元格的第一步公式为：=IF(A4="","",IF(数据表!C4<=0,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单元格的第二步公式为：=IF(C4<数据表!D4,C4,数据表!D4))

将上述两个步骤的公式合并，就可得出D4单元格的完整公式。

D4单元格的完整公式为：

=IF(A4="","",IF(数据表!D4<=0,0,IF(C4<数据表!D4,C4,数据表!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。

E4单元格的第一步公式为：=IF(A4="","",IF(数据表!E4<=0,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单元格的第二步公式为：=IF(C4-D4<数据表!E4,C4-D4,数据表!E4)

将上述两个步骤的公式合并，就可得出E4单元格的完整公式。

E4单元格的完整公式为：

=IF(A4="","",IF(数据表!E4<=0,0, IF(C4-D4<数据表!E4,C4-D4,数据表!E4)))

执行列填充后，即可完成账龄1～2年分布的金额的公式设置（见图5-10）。

图5-10 账龄1～2年分布的金额的公式

（4）账龄2～3年金额分布的公式设计（F4:F14单元格区域）。

账龄2～3年金额分布的公式和账龄1～2年金额分布的公式的设计在逻辑上属于一脉相传，只是在考虑统计日余额的扣除数时，需要多扣除一个账龄1～2年分布的金额数据。

F4单元格的公式为：

=IF(A4="","",IF(数据表!F4<=0,0,IF(C4-D4-E4<数据表!F4,C4-D4-E4,数据表!F4)))

执行列填充后，即可完成账龄2～3年分布的金额的公式设置（见图5-11）。

图5-11 账龄2～3年分布的金额的公式

（5）账龄3年以上（G4:G14单元格区域）。

账龄3年以上的公式，我们就不用折腾啦。直接用倒算的方式进行处理了。

G4单元格的公式：=IF(A4="","",C4-SUM(D4:F4))

执行列填充后，即可完成账龄为3年以上分布的金额的公式设置（见图5-12）。

图5-12 账龄3年以上分布的金额的公式

不难看出，只要有了清晰的逻辑思路，看似复杂账龄分布公式几乎凭借IF函数的一己之力就可以实现。当然，公式中在进行较小值的选择时，也可以使用更为便捷的MIN函数来优化。例如账龄1年以内金额分布的公式就可以写为：

=IF(A4="","",IF(数据表!D4<=0,0,MIN(C4,数据表!D4)))

至此，所有账龄分布的计算公式设置完毕。

2.计提表公式设计方法

（1）各类计提性质账龄分布金额汇总（C4:G4、C7:G7以及C10:G10单元格区域）。三类计提性质与对应统计日余额及账龄的统计，自然是通过SUMIF函数完成。

C4单元格的公式为：=SUMIF(分布表!$B$4:$B$14,$A4,分布表! C$4:C$14)

虽然三类计提性质被分成了三个不同的单元格区域，但是它们保持了完全相同的户型。所以我们仍然可以以C4单元格为起点执行区域填充（复制C4:G4单元格区域然后将其粘贴在C7:G7单元格区域和C10:G10单元格区域）。但需要明白的是，能实现快速的区域填充，还要得益于计提表和分布表保持了相同的结构。如果我们在设计分布表时，将计提性质放在统计日余额和账龄分布之间（B列和C列互换），而计提表却将统计日余额和账龄分布连在一起（见图5-13），那么我们将无法通过C4单元格执行快捷的区域填充了。

这个事实告诉我们，Excel的公式填充是很具有方位感的，但是需要我们在布局时让工作表之前尽量保持好队形。

执行区域填充后，即可完成各类计提性质账龄分布金额汇总（见图5-13）。

图5-13 各类计提性质账龄分布金额汇总的公式

（2）坏账计提的计算。

坏账计提的各种计算，就是一些简单的数学运算了。

D6单元格的公式为：=D4*D5

在D6:G6、D9:G9以及D12:G12单元格区域执行区域填充后，即可实现各计提性质在不同账龄下的计提坏账金额计算。

C6单元格公式为：=SUM(D6:G6)

在C9和C12单元格执行填充后，即可实现各计提性质计提坏账金额的合计。

最后别忘了本期计提坏账金额的计算。

F15单元格的公式为：=C14-C15

六、逻辑校验信息的公式设计方法

逻辑关系是检验各类财务报表中财务数据正确性的最基本的标准。给每一张财务工作表设置必要的逻辑校验，不仅是一种职业素养的体现，也是一种提高纠错效率的科学方式。

针对“账龄统计表”，我们还要关注以下情况。

1.账龄分布总额与统计日余额的匹配

校验账龄分布总额与统计日余额是否匹配，其实就是校验其账龄分布的完整性。两者应遵守的逻辑关系是：

各账龄期间分布金额之和=统计日余额

假设我们规定，当校验通过时显示“OK”，校验出错时显示“偏差X（X为偏差的金额）”。

分布表H4单元格的公式为：=IF(A4="","",IF(ROUND(C4-SUM(D4:G4),2)= 0,"OK","偏差"&ROUND(C4-SUM(D4:G4),2)))

执行列填充后，即可完成账龄分布的完整性校验设置（见图5-14）。此外，在计提表中，也可以参照该方式设置账龄分布总额与统计日余额校验。

2.是否存在非法数据

非法数据，是指不符合“账龄统计表”规则或假设前提的数据。下列情况就是需要我们严格进行防范的：

（1）统计日余额小于0。

（2）账龄分布金额小于0。

所以，这次我们要报警的对象不再是数据之间的匹配，而是要揪出不该出现的负数。

图5-14 账龄分布完整性校验

换一个说法，不允许出现负数的本质就是：在相关数据区域内，最小的数只能是0。所以MIN函数压轴出场了。假设我们把非法数据校验放在分布表的第17行。

C17单元格的公式为：=IF(MIN(C4:C14)<0,"有负数!","OK")

执行行填充后，一旦有负数混入阵中，就会显示“有负数！”的提示了（见图5-15）。然后，我们就可以按图索骥找到对应的列字段，通过筛选功能，将非法入境者一网打尽了。

图5-15 非法数据校验