财务 Excel 的五个高频场景:从流水到报表
银行流水对账、明细转汇总、多表合并、账龄分析、一页看板——五个场景的具体做法与函数组合。
财务用 Excel,不需要学完所有函数。掌握五个场景的做法,能解决 90% 的日常工作。
下面每个场景给“要解决什么 + 用什么 + 关键步骤”,不堆公式。
场景一:银行流水与账面核对
要解决:找出未达账项,确认差异原因。
用什么:XLOOKUP(或 VLOOKUP)+ 条件格式 + 数据透视。
- 把银行流水与账面明细各整理成一张表,字段统一:日期、摘要、金额、对方名称。
- 用“金额 + 日期”做联合键(如 =金额&"|"&日期),两边各加一列。
- 用 COUNTIF 统计每个键在两边的出现次数,次数不等的就是差异项。
- 对差异项逐笔核对:手续费、利息、代扣、未入账收款。
实务笔记
金额相同的多笔交易会互相干扰,联合键里加上“对方名称前四个字”能显著降低误配。
场景二:明细表转汇总表
要解决:几千行流水按科目、部门、月份汇总。
用什么:数据透视表。
- 源数据必须是一维表:一行一笔,不要有合并单元格、不要有小计行。
- 行放“科目”,列放“月份”,值放“金额求和”。
- 需要按部门拆分,把“部门”拖到筛选器,或用“插入切片器”。
场景三:多表合并(每月一张表)
要解决:12 个月的表格合并成一张年度表。
用什么:Power Query(数据 → 获取数据 → 从文件夹)。
- 1
建文件夹
把 12 个文件放进同一个文件夹,命名统一(如 2026-01.xlsx)。
- 2
从文件夹导入
Power Query 自动识别所有文件,合并为一张表。
- 3
清洗
删除空行、统一日期格式、拆分科目编码与名称。
- 4
加载
加载到工作表或数据模型,后续每月只需“刷新”。
场景四:账龄分析
要解决:应收按 30/60/90/180 天分档,识别风险客户。
用什么:IFS 或 XLOOKUP 分档 + 透视表。
| 账龄区间 | 公式思路 |
|---|---|
| 0—30 天 | TODAY() − 发票日期 ≤ 30 |
| 31—60 天 | 介于 31 与 60 之间 |
| 61—90 天 | 介于 61 与 90 之间 |
| 90 天以上 | 大于 90,重点标注 |
分档后用透视表按客户汇总,再按金额降序,前十个客户就是重点催收对象。
场景五:一页看板
要解决:老板只想看一页,知道这个月怎么样。
用什么:透视表 + 切片器 + 条件格式。
收入
本月、累计、同比、达成率,用四个数字卡。
毛利
毛利率趋势线,按产品线拆。
费用
三大费用占比,环比变化。
现金
期末余额、经营性净现金流、预计下月缺口。
看板的原则是“一屏看完、一眼看懂”。超过 8 个图表,老板就不看了。
三个通用习惯
- 源数据与计算表分开,源数据只增不改。
- 每个文件加一个“说明”工作表,写清字段含义与更新日期。
- 重要表格设置数据验证与条件格式,异常值自动标红。
把这五个场景做成模板,每月只需替换数据、点刷新。省下来的时间,用来分析数字背后的业务,比多做几张表有价值。