应收账款账龄分析表怎么做?Excel 三个函数搞定
- 2026-09-23 11:59:02
每到月末结账，应收账款总得盘一遍：哪些客户还欠着、欠了多久、哪几笔该催了、坏账准备该提多少。这些问题的答案，其实都在一张"账龄分析表"里。
不用装插件，Excel 三个函数加一个条件格式就能做出来，设好之后每月只改一个日期，其余自动算。
一、先搞清楚账龄是什么
账龄就是这笔应收款已经挂了多久。它不是按记账日期算，而是按开票日期（或业务发生日期）算到今天的天数。
为什么要做这张表，主要三个用处：
1. 催收有重点——90 天以上的先催，30 天以内的正常放着 2. 坏账准备有依据——账龄越长，计提比例越高，这是最常用的一种计提方法 3. 审计和税局要看——长账龄挂着不动的，容易被问"这笔钱还能不能收回来"
下面一步一步来，第一步先把表格摆对位置（这是后面公式不出错的前提）。
二、第一步：先把表格摆成这样（含基准日格子）
很多人做账龄表上来就用 TODAY() 函数，但这样有个毛病——今天算出来的数，明天打开就变了，月报根本没法复现，也没法跟下个月对比。
正确做法：把"基准日"填在一个固定的格子里，公式全部引用它。以后每月只改这一个格子，整张表自动重算。
先把表的骨架搭好，位置摆对了后面公式才不会错。第 1 行当表头，数据从第 2 行开始往下填：
| 1 | 2026-09-30 | |||||||
| 2 | ||||||||
| 3 |
关键就是 G1 和 H1 这两个格子：
1. 点 G1，输入文字"基准日"（纯文字，只是给表格加个说明） 2. 点 H1，输入本月结账日，比如 2026-09-30（这个才是被公式引用的日期） 3. 输入完 H1 后，如果显示成一串数字（像 46234 这种），选中 H1 → 右键"设置单元格格式" → 选"日期" → 类型挑"2012/3/14"那种短的 → 确定
为什么偏偏放在 H1？
因为 A1 到 F1 已经被表头占用了，G、H 两列空着，正好拿来放基准日——不挤占数据区，也不影响后面的排序和筛选。而且它固定在第 1 行，就算明细加到第 1000 行，H1 也一直待在那儿不动。
如果表特别宽、H 列上也有别的数据，那就换个法子：在表格上方单独插一行专门放基准日，比如在第 1 行 A1 写"基准日"、B1 填日期，然后把原来的表头整行往下挪一行（数据从第 3 行开始）。位置换了没关系，公式里引用的是它所在的格子地址，把 $H$1 换成实际的格子地址就行。
三、第二步：算逾期天数
F 列（逾期天数）的公式最简单，在 F2 里填：
=$H$1-C2
拆开看就是：基准日（H1）减去这一行的到期日（C2）。算出来是正数，说明已过期多少天；是负数或者 0，说明还没到期。
H1 前面的两个 $ 一定要加上。点了 H1 之后按一下 F4 就会变成 $H$1（注意这里只按一下，跟条件格式里按三下不是一回事）：
F2 写好之后，选中 F2，把鼠标移到右下角黑色小方块上，双击（或往下拖）就能整列填充。
如果想让逾期天数只显示正数、没到期就显示 0，公式换成：=MAX(0,$H$1-C2)
四、第三步：划分账龄段（重点）
E 列按开票日期 B 列来分段，这是最核心的一步。IF 嵌套写法（所有版本 Excel 都能用）：
=IF($H$1-B2<=30,"0-30天",IF($H$1-B2<=60,"31-60天",IF($H$1-B2<=90,"61-90天","90天以上")))
IFS 写法（Excel 2019 及 Microsoft 365 可用，写起来清爽很多）：
=IFS($H$1-B2<=30,"0-30天",$H$1-B2<=60,"31-60天",$H$1-B2<=90,"61-90天",TRUE,"90天以上")
给别人发的表，建议用 IF 嵌套，兼容性稳；自己用新版 Excel，IFS 写着舒服。分段标准（30/60/90）按公司要求调整，有的公司分 1 年以内、1-2 年、2-3 年、3 年以上，写法一样，把天数换成 365/730/1095 就行。
五、第四步：按账龄段汇总金额
分段算完，接着就是各个账龄段一共多少钱，用 SUMIF：
=SUMIF($E$2:$E$1000,"0-30天",$D$2:$D$1000)
公式三个参数分别是：条件区域（账龄段列）、条件（哪个段）、求和区域（应收金额列）。四个段写四条，就是一张汇总表。
注意两个区域的行范围必须对齐（都是 2 到 1000），否则金额会串行。如果想把条件里的"0-30天"也做成引用单元格，方便下拉，直接改成引用那格的地址，比如 =SUMIF($E$2:$E$1000,G2,$D$2:$D$1000)，G2 里写着"0-30天"，往下一拉四个段一次算完。
如果明细有几十上百行，用数据透视表更快：选中整个区域 → 插入 → 数据透视表 → 把"账龄段"拖到行、"应收金额"拖到值，一秒出汇总。缺点是不会自动刷新，数据变了要右键点一下"刷新"。
六、第五步：条件格式自动标红长账龄
最后一步做视觉提醒，思路跟之前讲的自动加边框是一样的。
选中 A2:F1000 → 条件格式 → 新建格式规则 → 使用公式：
公式里的 $E2、$F2 都是"锁列不锁行"——单击区域第一格后连按三下 F4 就能拿到，手打容易漏 $。
七、账龄算完，坏账准备怎么提
账龄分析表最大的用途就是计提坏账准备。常见做法是按账龄设不同比例（比例由企业会计政策自定，各家不统一，以本公司政策为准）：
每一段的金额乘比例，加起来就是期末坏账准备应有余额，跟账上已有的余额比，差额才是本月要计提或冲回的金额。
三个常用分录：
1. 计提（差额为正，即需要补提） 借：信用减值损失—计提的坏账准备 贷：坏账准备
2. 实际发生坏账，核销 借：坏账准备 贷：应收账款
3. 已核销的坏账又收回来（做两笔） 借：应收账款 贷：坏账准备 借：银行存款 贷：应收账款
这里有个必考点——税会差异。会计上计提的坏账准备计入了信用减值损失，但企业所得税前不允许扣除未经核定的准备金支出，所以汇算清缴时要纳税调增；等这笔坏账真正发生、并按资产损失的规定留存备查资料后，才能在税前扣除，那时候再做纳税调减。这个差异每年汇算都要处理，别漏了。
八、一张表速记
一句话：账龄分析就三步——算出天数、分好段、汇总金额。把基准日做成一个格子，以后每月改一次日期，整张表自己就更新了。
