如何在 Excel 中制作应收账款账龄分析表（30、60、90 天）

账龄分析表根据未付发票的逾期天数将其分类到不同的区间（通常为 0–30 天、31–60 天、61–90 天以及 90 天以上）。有两个关键决定将影响你的报表是否准确。第一，你是根据到期日还是发票日期来计算账龄。第二，部分付款的发票是显示其全额还是剩余余额。

这两点一旦弄错，每个区间的总额都会出错，这比根本没有报表还要糟糕。

本指南将介绍为什么报表制作会失败、人们常用的三种方法，以及每种方法在何处失效。这是一个数据工作流，而非会计建议，因此请与负责管理您账簿的人员确认具体处理方式。

为什么账龄分析表会让电子表格崩溃

第一个问题是日期选择。根据发票日期计算账龄可以告诉你单据存在了多久。根据到期日计算账龄则可以告诉你客户逾期了多久，而对于催收工作来说，后者才是你需要的数字。

这两种方法都有其合理性，且会产生不同的报表。最容易出错的情况是，电子表格中没有任何人记录到底使用的是哪一种方法。

第二个问题是部分付款。一张金额为 $10,000 的发票，若已收到 $7,000，则应收账款为 $3,000，且必须在且仅在一个区间中显示为 $3,000。基于发票列表而非未结清项目（open-items）列表构建的账龄分析表，会在无形中夸大所有数据。

第三个问题是报表是一个快照。区间是针对今天进行计算的，因此昨天的文件就已经过时了，每次重新构建都需要重新计算每一行。

此外，还有一些棘手的行。红字发票（贷记单）、预付款、有争议的发票和多币种余额，每一个都需要制定规则。而且，这些规则还必须在下一个人打开文件时依然适用。

单独来看，这些问题都不难解决。它们之所以困难，是因为它们会在每个月临近截止日期时同时出现。

这会给你带来什么代价

无法付诸行动的催收名单。分区的目的是为了知道先给谁打电话。一份夸大余额的报表会让催收人员去追讨已经到账的资金。

每个月都要重复劳动。因为区间是相对于今天计算的，所以账龄分析表永远没有完结的时候。每个周期都在重复相同的数据关联、相同的公式和相同的关联检查。

总额与总账对不上。当各区间总额之和不等于应收账款余额时，报表就失去了权威性。寻找原因通常比最初制作报表花费的时间还要长。

账龄分析表之所以值得信赖，是因为它的总额与总账相符。如果这一点做不到，其他的一切都毫无意义。

人们尝试的临时解决方案

方案 1：在动用公式之前先确定定义

在表格顶部写下四件事：根据哪个日期计算账龄、区间的界限是什么、金额是含付款的总额还是扣除付款后的净额，以及截止日期是什么。

这只需花费十分钟，却能避免最常见的争议。《Journal of Accountancy》在介绍相同的报表构建过程时，也同样强调了先做好设置的重要性。

这也决定了你的数据源。你需要的是包含剩余余额的未结清项目导出数据，而不是一张包含有史以来开具的所有发票的列表。

这种方法的局限性在于，定义本身并不进行任何计算。它们只是防止你计算出错误的结果。

方案 2：构建区间列，然后对总额进行透视

将逾期天数计算为截止日期减去到期日，然后将该数字映射到区间标签。TODAY 可以为你提供实时的截止日期，而 DATEDIF 则返回两个日期之间的天数。

对于标签本身，使用 IFS 比六个月后再去看嵌套的 IF 语句更具可读性。然后使用 SUMIFS 按客户和区间进行汇总，这样可以保持计算过程逐行可审计。

在分发报表时，请使用硬编码的截止日期，而不是 TODAY。如果一个文件在下周自动重新计算账龄，就会与别人收件箱里已有的版本产生冲突。

这种方法的瓶颈在于数据量和边缘情况。公式虽然有效，但红字发票、部分付款和争议仍需手动处理。

方案 3：在数据旁保留一个规则标签页

将棘手的决策集中在一个地方。例如红字发票如何抵消、有争议的发票是被排除还是被标记、外币余额如何折算以及采用何种汇率。

这正是当别人运行该报表时，报表仍能正常使用的关键。但这也是在月末截止日期紧张时最容易被忽略的标签页。

局限性在于，规则标签页只是记录了判断标准，而没有自动执行。在每个周期中，仍然需要有人去手动应用这些规则。我们关于如何在电子表格中对账交易的指南涵盖了为此提供支持的匹配工作。

共同的瓶颈。这三种方法都假设你从一个干净的未结清项目导出数据开始。如果数据源是原始发票导出文件加上一个独立的付款文件，那么在开始任何区间分类之前，真正的工作是先将它们关联起来。

如何使用 Powerdrill Bloom 构建账龄分析表

步骤 1：上传您的发票和付款数据

上传未结清项目导出文件，或者将发票和付款文件一起上传。Powerdrill Bloom 会在数据导入时对列进行分析，因此在计算任何区间之前，缺失的到期日、空白金额和重复的发票号码都会被显现出来。

步骤 2：用自然语言描述区间规则

直接陈述规则，而不是去构建它们。例如，说明你是根据特定截止日期的到期日来计算账龄。给出区间的界限，并说明金额应为扣除已收付款后的净额。

然后在同一次操作中要求进行检查。询问哪些发票的付款金额超过了发票金额，哪些发票的到期日早于发票日期。接着询问区间总额是否与应收账款余额相符。

步骤 3：导出图表、报表或幻灯片

导出每个客户的账龄表、区间分布图表，或者按最久未付余额排序的催收名单。

为什么这比每个月重新制作报表更好

	手动路线	Powerdrill Bloom
关联发票与付款	每个文件使用查找公式	同时上传并提问
更改截止日期	重新计算并重新验证	直接说明新日期
抵消部分付款	手动创建余额列	要求提供扣除付款后的余额
总额与总账核对	每个周期手动检查	询问总额是否相符

表格中间的那些行才是每个月耗费时间的地方。区间分类只是算术问题，而获取一份干净的未结清项目列表才是真正的核心工作。

常见错误

本想根据到期日计算，却误用了发票日期。对于催收而言，到期日几乎总是正确的选择。无论你选择哪一个，请在报表上注明。

显示发票金额而非剩余余额。部分付款的发票应按其未付余额归入相应区间。显示全额会夸大每个区间的总额。

让 TODAY 函数重新计算已分发文件的账龄。在发送报表之前，请冻结截止日期，否则两个人可能会从同一个文件中看到不同的数字。

忽略红字发票。未使用的红字发票（贷记单）记录在客户名下，会减少其欠款。遗漏它会让余额看起来比实际情况更糟糕。

按客户而非按发票进行区间分类。区间分类应针对每张发票进行，然后再按客户进行汇总。对客户的账龄进行平均会掩盖最旧的项目，而那正是你最需要关注的。

从不与总账进行核对。区间总额之和必须等于应收账款控制账户余额。省去这一步检查，报表就成了摆设。

每个周期都从头开始重新制作。规则不会每月都变，改变的只有数据。保留规则并更换导出的数据即可，这与制作预算与实际业绩对比报表的原则相同。

结论

确定账龄计算日期、使用剩余余额、冻结截止日期，并将总额与总账核对。这四点决定了你的报表是一份能让人付诸行动的报告，还是一张让人争论不休的表格。

这种报表之所以耗费成本，是因为整个计算都是相对于今天进行的，因此工作永远没有结束的时候。每个周期都需要重新进行数据关联和核对。

如果你的月末时间都耗费在了这些事情上，不妨针对导出的发票和付款数据尝试使用 Powerdrill Bloom。另请参阅我们关于如何将 PDF 财务报表转换为图表的指南以及 AI 现金流分析页面。

Frequently asked questions

应收账款账龄分析表中的标准区间有哪些？

大多数报表使用 0–30 天、31–60 天、61–90 天和 90 天以上，通常还包含一个“未到期”列。这些区间的界限只是一种惯例而非硬性规定，因此请注明你使用的是哪些界限。

我应该根据发票日期还是到期日来计算发票账龄？

如果你想知道客户逾期了多久（这通常是催收的目标），请使用到期日。如果你想知道单据存在了多久，请使用发票日期。

我该如何处理部分付款？

显示剩余余额，而非原始发票金额，并将该余额归入一个区间。从未结清项目导出数据而非发票列表开始处理，可以自动解决这个问题。

我需要哪些 Excel 函数？

使用 TODAY 或固定日期作为截止日期，使用 DATEDIF 计算逾期天数。IFS 用于分配区间标签，SUMIFS 用于按客户和区间进行汇总。这些函数都不复杂，难点在于定义。

账龄分析表应该多长时间重新制作一次？

至少每月一次，如果催收工作频繁，则应每周一次，因为每个区间都是相对于截止日期计算的。在分发的每个版本中，请冻结该日期。