使用Excel数据透视表实现多销售代表佣金计算的技术咨询

XLOOKUP函数在遇到单个客户对应多个销售代表的情况时，只会返回第一个匹配的结果。比如客户c3在关联表中有A和B两个销售，主表中c3的行只会取到A的佣金比例7%，完全漏掉了B的8%，导致数据透视表中B的佣金少了70*8%=5.6，最终汇总结果不符合预期。

以下是三种可行的实现方法，按需选择：

方法一：用Power Query合并数据（推荐，适合批量新增数据） #

这种方法能自动处理一对多关联，后续新增运单数据后刷新即可更新结果：

• 把主表（Table）和关联表（Table1）都转为Excel表（选中数据按Ctrl+T，勾选「我的表有标题」）。
• 点击主表任意单元格，进入「数据」选项卡 → 「从表格/区域」，打开Power Query编辑器。
• 点击顶部「合并查询」→「合并查询作为新查询」：
  • 下拉选择主表，选「ClientName（客户名称）」列；
  • 下拉选择Table1，选「ClientName（客户名称）」列；
  • 连接类型选「左外部（所有来自第一个，匹配来自第二个）」，点击确定。
• 点击合并后列的展开按钮，勾选「salesName（销售代表姓名）」和「%（佣金比例）」，取消「使用原始列名作为前缀」，点击确定。
• 添加佣金计算列：点击「添加列」→「自定义列」，输入公式 =[Profit] * [%] / 100，命名为「commission（佣金）」，点击确定。
• 点击「关闭并上载」，将处理后的数据加载到新工作表。
• 基于新表创建数据透视表：把「salesName（销售代表姓名）」拖到行区域，「commission（佣金）」拖到值区域设置为「求和」，即可得到正确的汇总结果。

方法二：用动态数组函数生成完整数据（适合Excel 365/2021） #

利用动态数组函数自动生成所有一对多匹配的行，无需手动处理：
在空白单元格（比如E1）输入表头，然后在E2单元格输入以下公式（公式会自动溢出所有结果）：

=LET(
    main_table, Table[#All],
    rel_table, Table1[#All],
    client_col_main, INDEX(main_table,,2),
    client_col_rel, INDEX(rel_table,,2),
    matched_data, XLOOKUP(client_col_main, client_col_rel, rel_table,,0,2),
    profit_col, INDEX(main_table,,3),
    commission_col, profit_col * INDEX(matched_data,,3)/100,
    final_result, HSTACK(INDEX(main_table,,{1,2,3}), INDEX(matched_data,,1), INDEX(matched_data,,3), commission_col),
    VSTACK(Table[#Headers], final_result)
)

生成完整数据后，基于这个动态区域创建数据透视表即可。

方法三：用数据模型关联创建透视表 #

通过Power Pivot建立表间关系，直接在透视表中计算佣金：

• 把两个表都转为Excel表（Ctrl+T）。
• 进入「数据」选项卡 →「数据模型」，在Power Pivot编辑器中，将Table的「ClientName」字段拖拽到Table1的「ClientName」字段上，建立关联关系。
• 关闭Power Pivot编辑器，创建新的数据透视表，选择「使用此工作簿的数据模型」。
• 在透视表字段面板中：
  • 把Table1的「salesName（销售代表姓名）」拖到「行」区域；
  • 点击「分析」选项卡 →「字段、项目和集」→「计算字段」，输入公式 =Table[Profit] * Table1[%] / 100，命名为「commission（佣金）」，点击确定；
  • 把新建的「commission（佣金）」拖到「值」区域设置为「求和」，即可得到正确汇总。

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