财务月初催回款，台账翻到第3页才发现有个客户逾期12天——不是忘了，是没标红。

上个月光追逾期就追了2周，该催谁、催多少、还剩多少没收，全靠Excel拉一下、数一下。今天就教你4招，让回款跟踪表自动盯：逾期天数实时滚动、5色预警谁该催、剩余欠款自动算、回款率2秒出答案。做完这张表，催回款从"凭记忆"变成"看表行动"。

第1招：数据验证下拉菜单——5级状态一键选，不再手打错字

回款状态如果手打，"已回款"和"已还款"是两个世界。下拉菜单统一入口，5个状态选了就生效。

操作步骤：

① 选中G列（回款状态）→ ② 数据→数据验证→ ③ 序列→ ④ 输入未到期,已逾期,部分回款,已回款,已坏账→ ⑤ 确定

表格示例：

踩坑提醒： 数据验证拦不住复制粘贴！别人Ctrl+V过来，下拉菜单直接失效——条件格式（第3招）是第二道防线。

第2招：TODAY()-到期日 + 合同金额-已回款——逾期天数和剩余欠款，两个公式自动出

逾期几天还要手算？剩余欠款还要一行行减？两个公式搞定，每天自动刷新。

操作步骤：

① E10输入 =IF(C10="","",TODAY()-C10) → ② 双击填充柄 → ③ F10输入 =B10-D10 → ④ 双击填充柄

公式：

• 逾期天数：=IF(C10="","",TODAY()-C10)
• 剩余欠款：=B10-D10
• 

• 到期日必须是日期格式！文本"2026/6/10"做减法报#VALUE!。用=TYPE(C10)检测，返回1才是真日期，返回2是文本需转换。
• 空到期日用IF判断显示空，不用IFERROR(TODAY()-C10,0)——0天≠未填，显示0会误导。
• DATEDIF也能算月数，但到期日<今天会报#NUM!。直接用TODAY()-到期日，正数=逾期，负数=未到期，更直观。

第3招：条件格式5色预警——逾期红/即将到期黄/已回款绿/部分回款蓝/坏账灰

逾期了靠肉眼翻？条件格式让颜色替你喊"该催了"。

操作步骤：

① 选中数据区（A17:G22）→ ② 条件格式→管理规则→ ③ 新建规则→使用公式→ ④ 依次添加5条：

踩坑提醒：

• $E17列锁行不锁！$E$17只判断第17行，整表全红——条件格式最常见的致命错误。
• 规则顺序不能乱：①②是逾期预警（先匹配），③④⑤是状态标色（后匹配）。Excel从上往下执行，第一个匹配就生效。
• 未到期的行（E2<0）不被①②匹配，自动留白——不用额外写规则。

第4招：SUM+SUMIF统计看板——总应收/已回款/回款率/逾期数，2秒出答案

催回款不能只看单条，得看全局。4个公式建统计看板，一眼知道还欠多少、逾期几家。

操作步骤：

① 在表右侧建统计区 → ② 依次输入：

公式： =ROUND(SUM(D7:D12)/SUM(B7:B12),3) （配合单元格自定义格式0.0%显示为20.3%）

踩坑提醒

• 回款率不要用SUMIF("已回款",合同金额)——那只算完全回款的合同金额，漏了部分回款已收的钱。用SUM(已回款列)/SUM(合同金额列)才是真实的回款率。
• SUMIF参数顺序"条件区域,条件,求和区域"——求和区域在第3个参数，口诀"SUMIF求和在后"，SUMIFS求和在前。
• 回款率要ROUND防浮点尾差：0.66666...配0.0%格式显示66.7%，比66.666...%好看一百倍。不要乘100——0.667×100=66.7再配0.0"%"会显示66.7%（实际值66.7不是百分比），造成后续计算混乱。
• COUNTIF条件">7"必须加引号——不加直接报错。

进阶联动：4招串联，打造"回款催收自动化"闭环

4招不是孤立的，串联起来从录入→判断→预警→统计，一条线搞定：

1. 录入：数据验证下拉菜单统一状态（招1）→到期日+已回款金额必须填数字格式
2. 判断：TODAY()-到期日算逾期天数+合同金额-已回款算剩余欠款（招2）→正数逾期/负数未到期
3. 预警：条件格式5色标色（招3）→逾期红/即将到期黄/已回款绿
4. 统计：SUM+SUMIF看板实时更新（招4）→回款率/逾期数/剩余欠款总额

每月更新只需3步：
① 打开表格→② 确认到期日和已回款是否更新→③ 统计区自动刷新

高频场景3个——这张表，不止催回款

场景1：销售月初催款日
打开表→筛选逾期天数>7→红色行全出来→按剩余欠款从大到小排序→先催大额逾期客户

场景2：财务月底回款报表
统计区直接抄数据→回款率+逾期数+剩余欠款总额→填进月报→2分钟搞定3小时的汇总

场景3：老板问"还有多少没收回来"
=SUM(F2:F100) 一秒出答案——剩余欠款总额，不用翻台账不用数行

避坑指南——5个踩坑，全是我趟过的

我整理了完整的客户回款跟踪台账模板，包含自动逾期预警、5 色状态标色、账龄分段统计、回款率看板 4 大模块，所有公式、条件格式都预设完成，填入客户和合同信息即可自动计算。
点击下方「华杰办公助手」小程序卡片即可直接下载，还有更多财务、销售专属办公模板免费用。

⬇️⬇️⬇️⬇️⬇️