Power Query 十分钟搞定千行序时账的账龄重算

账龄重算是个典型场景：客户给一份千行序时账，按基准日重算往来款账龄区间。手工做法是 Excel 里一层层套公式，做一次两小时，数据一更新全部重来。用 Power Query 做成流水线之后，新数据拖进来，点一下刷新，十分钟变成十秒。

这篇把这个流程完整拆给你，包括 M 代码和踩过的坑。

整体思路

Power Query 的强项是把数据处理过程固化成可重复的步骤。账龄重算的数据流一共五步：

导入序时账 → 规范字段（类型/列名）→ 计算账龄天数 → 归入账龄区间 → 按客户透视汇总

每一歩都会被 Power Query 记录下来，下次换数据只需替换源文件。

第一步：导入与规范

导入 CSV 或 Excel 后，先做三件事：

提升首行为标题（Power Query 对话框勾选即可）；
改数据类型：日期列设为日期、金额列设为小数。这一步不做，后面计算全是错的；
列名标准化：客户给的表可能叫”往来单位”也可能叫”对方名称”，统一改名为 客户。

第二步：计算账龄天数

新增自定义列”账龄天数”，M 代码：

= Number.From(#date(2026, 6, 30) - [日期])

基准日建议不要硬编码——单独建一个”参数表”（Excel 里一个单元格），查询里引用它。这样明年换基准日，改一个单元格即可：

// 引用参数表中的基准日
基准日 = Excel.CurrentWorkbook(){[Name="基准日"]}[Content]{0}[Column1]

第三步：归入账龄区间（核心）

再新增一列”账龄区间”，用条件逻辑做分桶：

= if [账龄天数] < 365 then "1年以内"
  else if [账龄天数] < 730 then "1-2年"
  else if [账龄天数] < 1095 then "2-3年"
  else "3年以上"

避坑 1：不要用”年”做减法算账龄。Date.Year 相减会在年中基准日时算错（跨年未满整年的都按一年算）。永远用天数判断。

避坑 2：分桶边界想清楚开闭区间。365 天算”1 年以内”还是”1-2 年”？和团队口径统一，不然和上年底稿对不上。

第四步：透视汇总

选中 客户 和 账龄区间 两列 → 透视列 → 值取”金额”的和 → 空值替换为 0 → 加一行”总计”。最后”关闭并上载”到工作表。

到这里，一份可以直接贴底稿的账龄汇总表就出来了：行是客户、列是四个账龄区间、末列合计。

第五步：把它变成”十分钟流水线”的关键操作

上面的流程做完只是”快了一次”。真正的效率来自参数化 + 可刷新：

数据源用文件夹：把查询源从”单个文件”改成”文件夹”，Power Query 会自动合并文件夹下所有同结构 CSV。每月新导出的序时账扔进文件夹，刷新即得最新账龄；
基准日参数化：如上，一个单元格控制全表；
刷新时容错：每个文件出错时忽略（合并查询的高级选项里勾选），某个文件格式坏了不会拖垮整条流水线。

性能与坑

千行数据毫无压力，十万行以内 Power Query 都轻松；但避免在 PQ 里做 fuzzy match，那会慢到怀疑人生——模糊匹配交给 Python 或者事前和客户约定标准名称；
日期列含脏数据（如 2026/13/01）会让类型转换整列报错。方案：转换类型那步改成”更改的错误时不中断”（try ... otherwise null），再用筛选把 null 行揪出来人工处理；
刷新后列顺序变化：透视列的列顺序取决于数据里出现过的值。四个区间如果某月没有”3 年以上”，这列会消失，底稿引用就 #REF 了。方案：透视后加一步”引用列名列表”固定列。

为什么不用公式？

XLOOKUP + IF 嵌套当然能实现同样的结果，但公式的三个致命伤：数据换版要重拖、计算过程不透明、千行数组公式卡到 Excel 未响应。Power Query 的步骤记录天然就是一份”处理底稿”——复核的人点开”应用的步骤”，每一步做了什么清清楚楚。这其实很审计：过程可验证，结果可复现。

顺带一提：如果数据量再大一个量级、或者要做更复杂的勾稽，我会直接上 Python（账龄重算器就是干这个的）。Power Query 适合”办公自动化的 80%“，剩下 20% 交给代码。