使用Excel公式或Power Query将主表容量分配到参照表匹配记录

下面分别提供Excel公式、Power Query两种实现方式，均支持新增记录后自动重算均分容量：

方法1：Excel公式实现（操作简单，实时自动更新） #

前置操作 #

• 选中两张表的所有数据，按Ctrl+T转为Excel超级表，主表命名为主表，参照表命名为分配记录表

Capacity列填充公式 #

如果使用365/2021及以上版本Excel，直接在分配记录表的Capacity列第一行输入以下公式，回车后会自动填充整列：

=XLOOKUP([@[RESOURCE NAME]], 主表[RESOURCE NAME], 主表[CAPACITY], 0) / COUNTIF([RESOURCE NAME], [@[RESOURCE NAME]])

公式说明

• XLOOKUP部分：根据当前行的资源名称，匹配主表拿到该资源的总容量，未匹配到资源时返回0避免报错
• COUNTIF部分：统计分配记录表中当前资源的总记录条数
• 后续新增同资源的分配记录时，COUNTIF统计的条数会自动更新，所有同资源的Capacity值会自动重新计算均分

如果使用低版本Excel没有XLOOKUP函数，替换为以下VLOOKUP版本公式即可：

=IFERROR(VLOOKUP([@[RESOURCE NAME]], 主表, MATCH("CAPACITY",主表[#标题],0),FALSE),0)/COUNTIF([RESOURCE NAME], [@[RESOURCE NAME]])

方法2：Power Query实现（适合万行以上大数据量，批量处理效率更高） #

操作步骤 #

• 导入数据到Power Query编辑器
  • 选中主表 → 点击「数据」选项卡 → 从表格/区域 → 加载到编辑器后，仅保留RESOURCE NAME、CAPACITY两列，将查询重命名为主表_容量
  • 选中分配记录表 → 同样操作导入编辑器，查询重命名为分配记录
• 关联数据并计算均分容量
  • 在分配记录查询中，点击「转换」选项卡 → 分组依据 → 选择高级分组
    • 分组列勾选RESOURCE NAME
    • 新增聚合列1：列名记录数，操作选择「对行进行计数」
    • 新增聚合列2：列名明细行，操作选择「所有行」
  • 点击「主页」选项卡 → 合并查询 → 选择和主表_容量合并，匹配列选两张表的RESOURCE NAME，联接种类选「左外部」
  • 展开合并后的主表字段，仅保留CAPACITY列
  • 展开明细行列，恢复原来的SKILL GROUP、PROJECT、DEMAND字段
  • 点击「添加列」选项卡 → 自定义列，列名填Capacity，公式输入=[CAPACITY]/[记录数]
  • 删除不需要的中间列，调整列顺序和原分配表一致
• 结果加载回Excel
  • 点击「关闭并上载」，选择上载到指定工作表位置即可
  • 后续新增主表容量数据或者分配记录后，右键点击上载后的结果表 → 刷新，即可自动重新计算所有Capacity值

补充说明：如果需要按「RESOURCE NAME+SKILL GROUP」双维度匹配（避免重名资源跨技能组的分配错误），只需要把上述公式/Power Query分组的匹配条件从单字段改为两个字段同时匹配即可。

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