Excel Power Query：合并表格、计算间接成本并创建透视表

1. 数据预处理与表格合并 #

• 加载主表和分配表到Power Query
• 主表添加自定义列区分BU/CC（若主表无明确类型列，可通过主体名称前缀判断）：

  = if Text.StartsWith([主体], "CC") then "CC" else "BU"
  

• 筛选主表中[类型] = "CC"的行，与分配表做合并查询：
  • 合并依据：主表的[主体]（CC名称）和分配表的[CC]字段
  • 合并类型：左外部（确保所有CC数据匹配到分摊系数）
• 展开分配表中的[BU]和[分摊系数]列

2. 间接成本分摊计算 #

• 添加自定义列计算分摊金额：

  = [金额] * [分摊系数]
  

• 调整科目列，将分摊成本归类到指定层级（比如按业务要求标记为对应间接成本科目）：

  = "间接成本-" & [科目]
  

• 重命名列：将[主体]改为"分摊至BU"，[分摊金额]改为"金额"，保留[BU]、[科目]核心字段

3. 合并BU原始数据与分摊数据 #

• 筛选主表中[类型] = "BU"的行，保留原[BU]、[科目]、[金额]列
• 将筛选后的BU数据与分摊后的CC数据做追加查询，确保所有字段类型、名称完全匹配

4. 创建符合层级的透视表 #

方法1：Power Query内完成透视 #

• 对合并后的数据按[BU]、[科目]分组，求和[金额]
• 使用透视列功能：将[科目]设为列，[金额]设为值，聚合方式选求和
• 手动调整列顺序，匹配指定的科目层级结构

方法2：Excel透视表（更灵活） #

• 将整理好的数据加载到Excel工作表
• 插入透视表：
  • 行区域：依次添加[BU]、[科目]（按层级排序）
  • 值区域：添加[金额]，汇总方式设为求和
  • 右键行标签，选择「显示在大纲形式」或手动调整分组，匹配指定层级

关键注意事项 #

• 分配表需确保每个CC的分摊系数总和符合业务逻辑（比如总和为1，避免重复/遗漏计算）
• 若科目有多级层级，需在主表提前维护[科目层级1]、[科目层级2]等字段，透视时按层级依次添加
• 合并/追加前检查字段类型一致（金额列必须为数值型，避免计算错误）

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