求Excel/Sheets中筛选最优4人任务覆盖组合的公式方案

核心思路 #

先通过公式筛选出任务覆盖能力较强的人员缩小候选池，再对候选池内的4人组合计算总覆盖任务数，最终排序找出最优组合——既解决18人全量组合（3060组）计算压力大的问题，又能适配人员能力动态变化的需求。

一、筛选高价值候选人员（缩小范围） #

假设能力表结构：A列为人员姓名，B-R列为18项任务，单元格值1表示胜任，0表示不能。

• 计算单人员任务覆盖数：在S2单元格输入=SUM(B2:R2)，下拉填充至所有人员行，统计每个人能胜任的任务数量。
• 筛选靠前的候选人员：用以下公式提取覆盖数排名前N的人员（示例取前10，可按需调整）：

  =SORT(FILTER(A2:S19, S2:S19>=LARGE(S2:S19,10)), 2, FALSE)
  

  该公式会按覆盖数从高到低排序输出候选人员，将后续组合数从3060大幅压缩至210组（10人选4）。

二、生成候选池内的4人组合 #

假设筛选后的候选人员存于X2:X11区域：

• 在Z2、AA2、AB2、AC2分别输入以下公式，下拉填充至出现重复/错误值，生成所有不重复的4人组合：

  // Z2（第一个人）
  =INDEX(X$2:X$11,INT((ROW(Z2)-2)/COMBIN(9,3))+1)
  // AA2（第二个人）
  =INDEX(X$2:X$11, MOD(INT((ROW(Z2)-2)/COMBIN(8,2)),9)+2)
  // AB2（第三个人）
  =INDEX(X$2:X$11, MOD(INT((ROW(Z2)-2)/COMBIN(7,1)),8)+3)
  // AC2（第四个人）
  =INDEX(X$2:X$11, MOD((ROW(Z2)-2),7)+4)
  

三、计算组合的任务覆盖总数 #

在AD2单元格输入数组公式（Sheets按Ctrl+Shift+Enter触发，Excel支持直接回车），下拉填充至所有组合行：

=SUMPRODUCT(--(MMULT(TRANSPOSE(--(B$2:R$19=1)),--(ISNUMBER(MATCH(A$2:A$19,Z2:AC2,0))))>0))

公式逻辑：匹配组合内的4人，提取他们的能力矩阵，对每项任务判断是否至少有1人能胜任，最终统计覆盖的任务总数。

四、筛选最优组合 #

对组合及对应覆盖数区域排序，直接获取最优结果：

=SORT(Z2:AC211,5,FALSE)

排序后最上方的组合即为覆盖任务数最多的，优先查看覆盖数等于18的组合（即能覆盖全部任务）。

5×5示例适配说明 #

若为5人5任务、选2人组合的场景，逻辑完全一致：

1. 计算每人覆盖数，筛选前3-4名缩小范围；
2. 用组合公式生成所有2人组合；
3. 计算每组覆盖任务数后排序，找出最优。

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