关于在Excel中搭建Relational Database实现采购订单预算追踪的咨询

开发者阿泽

2026-5-20

嘿，这个需求我之前帮不少财务团队落地过——Excel虽然不是专业的关系型数据库，但用好它的表关联、函数和Power工具，完全能搞定预算追踪+发票自动匹配的问题。下面一步步给你拆解：

第一步：搭建核心关系型数据表结构

首先得把数据拆成独立的关联表（这就是Excel里“关系型”的核心），避免数据冗余，方便后续维护：

采购订单主表：存储所有订单的基础信息，必须包含这些核心字段：
- 订单ID：唯一主键（不能重复，用数据验证限制），用来关联其他表
- 原订单ID：补充订单填对应的初始订单ID，初始订单留空，用来把原订单和补充订单归为一组
- 预算金额：该订单的总预算
- 订单状态：标记「活跃」（预算未耗尽）/「已耗尽」，方便后续匹配发票
- 创建日期：可选，用来辅助匹配时间相近的发票
支出&开票记录表：每一笔支出、开票都单独存这里，字段包括：
- 记录ID：唯一标识
- 关联订单ID：关联到采购订单主表的订单ID
- 支出金额：实际支付的金额
- 开票金额：发票上的金额
- 发票号：唯一发票编号
- 交易日期：支出/开票的日期
预算追踪汇总表：用来实时展示每个订单组（原+补充）的汇总数据，这个表不用手动录入，全靠公式/透视表自动生成。

第二步：用Excel数据模型建立表关联

要实现表之间的联动，得用Excel的「数据模型」设置关系（相当于数据库的外键关联）：

打开「数据」选项卡，点击「数据模型」，把上面三个表都添加进去
设置两组关联：
- 采购订单主表的订单ID ↔ 支出&开票记录表的关联订单ID：建立「一对多」关系（一个订单对应多笔支出/开票）
- 采购订单主表的原订单ID ↔ 采购订单主表的订单ID：建立「自关联」，用来把原订单和它的补充订单归为同一组
注意：主键（订单ID）必须是唯一值，否则关联会出错，一定要用数据验证限制重复。

第三步：实现预算、支出、开票的实时追踪

分两种方案，适合不同数据量的场景：

方案1：用普通函数（适合新手/小数据量）

在预算追踪汇总表里，用这些公式自动计算：

单个订单的已支出金额：

=SUMIFS('支出&开票记录表'!$C:$C, '支出&开票记录表'!$B:$B, '采购订单主表'!$A2)

单个订单的已开票金额：

=SUMIFS('支出&开票记录表'!$D:$D, '支出&开票记录表'!$B:$B, '采购订单主表'!$A2)

单个订单的剩余预算：

='采购订单主表'!$C2 - 已支出金额单元格 - 已开票金额单元格

原订单+补充订单的总预算/总支出：

=SUMIF('采购订单主表'!$B:$B, '采购订单主表'!$A2, '采购订单主表'!$C:$C) // 总预算

如果订单和发票数量超过几百条，普通函数会卡顿，用Power Pivot更高效：

在数据模型里，给采购订单主表添加计算列：

总预算（原+补充）：

CALCULATE(SUM('采购订单主表'[预算金额]), ALLEXCEPT('采购订单主表', '采购订单主表'[原订单ID]))

总已支出：

SUMX(RELATEDTABLE('支出&开票记录表'), '支出&开票记录表'[支出金额])

第四步：自动为新发票匹配正确的采购订单

核心是先设定匹配规则（比如优先用未耗尽预算的订单，先原订单再补充订单），然后用工具实现：

在发票录入区域添加「推荐订单ID」列，用INDEX+MATCH筛选符合条件的订单：

=INDEX('采购订单主表'!$A:$A, MATCH(TRUE, ('采购订单主表'!$D:$D="活跃")*('采购订单主表'!$C:$C > 剩余预算单元格), 0))

解释：筛选出「活跃」状态且剩余预算大于当前发票金额的订单，返回第一个符合条件的ID。如果要优先匹配某组原订单的补充订单，再加个条件*('采购订单主表'!$B:$B=指定原订单ID)。

如果是批量导入发票，用Power Query做合并查询更高效：

额外提醒

内容的提问来源于stack exchange，提问作者maria90