如何用公式按历史采购占比自动拆分第三方供应商目标金额至各部门？

基础数据前提 #

假设你有两类核心数据：

• 历史采购占比表：包含供应商ID、商品部门、对应部门历史采购占比（占比建议存为小数格式，比如18%直接输入0.18）
• 供应商总目标表：包含供应商ID、该供应商总目标金额

拆分公式（Excel环境） #

方案1：新版Excel（365/2021+）用XLOOKUP（简洁高效）

如果要在部门行自动计算拆分金额，公式如下：

=XLOOKUP([@供应商ID], 总目标表!E:E, 总目标表!F:F) * XLOOKUP([@供应商ID]&[@部门], 历史占比表!A:A&历史占比表!B:B, 历史占比表!C:C)

逻辑：第一个XLOOKUP匹配对应供应商的总目标金额，第二个XLOOKUP通过「供应商ID+部门」的组合键精准匹配对应历史占比，两者相乘即得该部门的拆分金额。

方案2：旧版Excel用SUMPRODUCT（兼容所有版本）

如果没有XLOOKUP功能，用SUMPRODUCT替代：

=SUMPRODUCT((总目标表!E:E=[@供应商ID])*总目标表!F:F) * SUMPRODUCT((历史占比表!A:A=[@供应商ID])*(历史占比表!B:B=[@部门])*历史占比表!C:C)

逻辑：第一个SUMPRODUCT筛选出对应供应商的总目标，第二个SUMPRODUCT筛选出该供应商对应部门的占比，相乘得到拆分结果。

示例验证（你的供应商5场景） #

供应商5总目标4300万美元，厨房部门占比18%，代入公式后：
43000000 * 0.18 = 7740000，即厨房部门拆分金额为774万美元，其他部门按相同逻辑自动计算。

注意事项 #

• 确保同一供应商下各部门的历史占比总和为1，避免总金额出现偏差
• 供应商ID、部门名称需完全匹配，不要有空格、大小写差异
• 可以在历史占比表新增辅助列（比如=A2&B2），将「供应商ID+部门」合并为唯一标识，能大幅提升公式匹配效率

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