如何基于独立参考表动态填充Excel表格且不丢失历史数据

核心需求 #

• 报表固定列：年份、月份、国家、经销商、产品、数值、单位，前5列为维度列，后2列为人工数据录入列
• 维度来源：国家、经销商、产品组合存在独立可更新的目录表，年月存在独立的周期表，需要自动生成两类表的全量维度笛卡尔积
• 规则要求：目录更新时，新增的维度组合自动追加到报表底部，不能修改、打乱、覆盖之前已经录入的所有数据
• 排除无效方案：直接用Power Query合并两表生成全量维度后加载覆盖原表的方式，刷新会重排已有行、丢失录入内容，不满足要求
• 可接受实现工具：Excel（含Power Query）、Access均可，避免手动逐年份复制维度组合的重复操作

方案1：Excel Power Query 实现（无需切换工具） #

核心逻辑是维度生成和录入区分离，通过唯一维度键做差集追加，永远不改动已有行

1. 先创建3张独立的结构化表（选中区域按Ctrl+T转表，自定义表名），后续所有维度更新只在这3张基础表操作：
   • dim_ym：存所有需要统计的年份、月份，共2列
   • dim_org：存所有有效的国家、经销商、产品组合，共3列
   • fact_sales：作为最终录入表，初始为空，固定列顺序：年份、月份、国家、经销商、产品、维度键、数值、单位
2. 维度键生成规则：将5个维度字段用特殊分隔符拼接，例如[年份]&"|"&[月份]&"|"&[国家]&"|"&[经销商]&"|"&[产品]，每个维度组合对应全局唯一键，无重复
3. Power Query 处理步骤：
   • 导入3张结构化表作为Power Query数据源
   • 对dim_ym和dim_org做笛卡尔积合并，生成全量5列维度组合，同步计算每一行的维度键
   • 读取fact_sales中已存在的维度键列表，对全量维度做反连接筛选，只保留尚未出现在录入表中的新维度组合
   • 加载时选择「将新数据追加到现有fact_sales表底部」，不要选择覆盖原有表区域
4. 后续操作：更新维度表内容后点击刷新，新增的维度组合会自动补到表尾，已有录入数据的顺序、内容完全不会变动。

方案2：Access 实现（适合大数据量场景） #

逻辑和Power Query方案一致，通过主键去重只追加新维度：

1. 新建3张基础表：
   • dim_ym：字段含自增ID、年份（数字型）、月份（数字型）
   • dim_org：字段含自增ID、国家（文本型）、经销商（文本型）、产品（文本型）
   • fact_sales：字段含dim_key（文本型，设为主键）、年份（数字型）、月份（数字型）、国家（文本型）、经销商（文本型）、产品（文本型）、数值（数字型）、单位（文本型）
2. 新建追加查询，SQL代码如下：

   INSERT INTO fact_sales (dim_key, 年份, 月份, 国家, 经销商, 产品)
   SELECT 
       [年份] & "|" & [月份] & "|" & [国家] & "|" & [经销商] & "|" & [产品] AS dim_key,
       dim_ym.年份,
       dim_ym.月份,
       dim_org.国家,
       dim_org.经销商,
       dim_org.产品
   FROM dim_ym, dim_org
   WHERE 
       ([年份] & "|" & [月份] & "|" & [国家] & "|" & [经销商] & "|" & [产品]) 
       NOT IN (SELECT dim_key FROM fact_sales);
   

3. 后续每次更新完维度表，运行一次该追加查询即可。因为dim_key设为主键，重复维度组合不会被插入，已有录入数据完全不会被改动，新组合会自动追加到表中。

之前Power Query方案失效原因 #

之前的操作是直接将全量笛卡尔积结果加载覆盖原有录入表，没有做「仅追加不存在的新维度」的差集筛选，也没有绑定唯一键做匹配，所以每次刷新都会重排行顺序，覆盖手动录入的内容。

内容的提问来源于stack exchange，提问作者Juan Gabriel Fernández