Excel/Power BI如何按分组列生成同组值的所有两两组合表

需求为基于「单任务对应多名参与员工」的原始记录表，按照组合数C(n,2)规则，生成同任务下所有不重复的员工两两配对记录，单条任务下n个员工自动生成n*(n-1)/2条无重复、无反向冗余的配对行。以下分别提供Excel和Power BI的可落地实现方式：

Excel 实现方式 #

优先推荐使用内置Power Query（获取和转换数据）功能实现，无数据量限制、支持源数据更新后一键刷新，操作步骤如下：

• 选中原始数据区域，按Ctrl+T将其转换为带表头的超级表，点击确定。
• 选中超级表，点击「数据」选项卡下的「从表格/区域」，进入Power Query编辑器。
• 选中「工作任务」列，点击「分组依据」，选择高级分组模式，新增聚合列命名为「员工分组」，聚合操作选择「所有行」，确认后得到单任务对应所有员工行的分组结果。
• 新增自定义列，输入以下M代码，自动为每个任务生成所有符合规则的员工配对：

= let 员工清单 = List.Distinct([员工分组][员工姓名]) in
List.Generate(
    ()=>[i=0,j=1],
    (x)=>x[i] < List.Count(员工清单),
    (x)=> if x[j] < List.Count(员工清单)-1 then [i=x[i],j=x[j]+1] else [i=x[i]+1,j=x[i]+2],
    (x)=> [工作任务=[工作任务],员工A=员工清单{x[i]},员工B=员工清单{x[j]}]
)

• 点击自定义列右上角的展开按钮，先选择「扩展到新行」，再次展开记录字段，提取出工作任务、员工A、员工B三列，删除多余辅助列后，点击「关闭并上载」即可得到目标配对表。

该方法会自动过滤同任务下重复录入的同名员工，配对结果完全符合组合数规则：4人任务生成6行、16人任务生成120行，不会出现甲乙、乙甲同时存在的冗余配对。

如果使用Excel 365/2021版本且数据量较小，也可以直接用函数实现：先用UNIQUE()提取所有不重复任务，再针对单个任务用FILTER()提取对应员工名单，搭配TOCOL、SEQUENCE等函数生成配对，缺点是数据量超过千行时计算卡顿明显，稳定性弱于Power Query方案。

Power BI 实现方式 #

可根据使用习惯选择Power Query或DAX两种方案实现：

方案1：Power Query 处理 #

操作逻辑与上述Excel的Power Query步骤完全一致：导入原始任务表后按任务分组，新增自定义列生成配对，展开后即可得到结果表，后续源数据刷新时配对结果自动同步更新。

方案2：DAX计算表生成 #

直接新建计算表，输入以下DAX代码即可一键生成目标配对表，无需修改Power Query层逻辑：

员工配对表 = 
VAR 基础去重表 = DISTINCT(SELECTCOLUMNS('原始任务表',"工作任务",'原始任务表'[工作任务],"员工姓名",'原始任务表'[员工姓名]))
VAR 带序号表 = ADDCOLUMNS(基础去重表,"员工序号",RANKX(FILTER(基础去重表,[工作任务]=EARLIER([工作任务])),[员工姓名],,ASC,Dense))
RETURN
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(
            SELECTCOLUMNS(带序号表,"任务1",[工作任务],"员工A",[员工姓名],"序号A",[员工序号]),
            SELECTCOLUMNS(带序号表,"任务2",[工作任务],"员工B",[员工姓名],"序号B",[员工序号])
        ),
        [任务1]=[任务2] && [序号A]<[序号B]
    ),
    "工作任务",[任务1],
    "员工A",[员工A],
    "员工B",[员工B]
)

该DAX逻辑先对同任务下的员工做去重编号，交叉连接后仅保留同任务下A序号小于B序号的配对，完全避免重复和反向冗余记录，结果符合组合数规则。

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