如何在Excel中按条件顺序求和并匹配资源分配需求？

问题背景 #

现有资源分配数据如下（假设数据位于A2:C7单元格）：

需求：找出可用工时总和恰好为7小时的员工组合（例如Sarah+John+Emily+Leah，总工时3+3+0.5+0.5=7），需用Excel公式实现，后续计划通过宏自动化。

现有尝试的核心缺陷 #

1. SUM+OFFSET方案：按行顺序累加工时，只能匹配连续位置的员工组合，无法识别非顺序的最优组合（比如跳过Bill，选择两个0.5工时的员工）。
2. XLOOKUP+LARGE方案：按工时从大到小依次扣除，逻辑僵化，遇到扣除后总和超标的情况时，无法自动替换为更小的工时项继续匹配。

可行解决方案 #

方案1：Excel 365/2021 动态数组枚举法 #

由于员工数量仅6人，总组合数为2^6-1=63种，完全可以通过二进制掩码枚举所有可能：

1. 生成所有组合掩码：在D2单元格输入公式，自动生成1到63的序列（每个数字对应一种员工选择组合）：

   =SEQUENCE(2^COUNTA(B2:B7)-1,1,1)
   

2. 转换为二进制字符串：在E2单元格输入，将每个数字转为6位二进制（每一位代表是否选中对应行的员工）：

   =DEC2BIN(D2,COUNTA(B2:B7))
   

3. 计算组合工时总和：在F2单元格输入，对每个二进制掩码对应的员工工时求和：

   =SUMPRODUCT(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),C2:C7)
   

4. 提取组合员工姓名：在G2单元格输入，将选中的员工姓名用逗号拼接：

   =TEXTJOIN(", ",TRUE,IF(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1)=1,B2:B7,""))
   

5. 最后筛选F列等于7的行，G列即为符合要求的员工组合。

方案2：规划求解（兼容全版本Excel） #

如果你的Excel版本不支持动态数组，可以用规划求解功能快速找到一组符合条件的组合：

1. 在D2:D7单元格添加「是否选中」列，默认输入0（0代表不选，1代表选中）。
2. 在D8单元格输入公式，计算选中员工的总工时：

   =SUMPRODUCT(D2:D7,C2:C7)
   

3. 启用「规划求解」：点击「数据」选项卡→「规划求解」（若未找到，需在「文件→选项→加载项」中启用「规划求解加载项」）。
4. 设置参数：
   • 目标单元格：D8，目标值：7
   • 可变单元格：D2:D7
   • 约束条件：添加D2:D7 二进制（强制单元格只能为0或1）
5. 点击「求解」，Excel会自动生成一组符合要求的员工组合。若需要多组结果，可添加约束排除已找到的组合后重复求解。

对原有方案的优化建议 #

• SUM+OFFSET方案：由于其核心逻辑是按位置顺序累加，无法适配非顺序组合的需求，建议直接放弃该思路。
• XLOOKUP+LARGE方案：若坚持使用该思路，需结合递归逻辑或多层辅助列实现分支判断，但复杂度远高于上述两种方案，性价比极低，不推荐。

宏自动化思路（VBA示例） #

如果需要自动枚举所有符合条件的组合，可使用VBA遍历所有可能的员工组合，代码示例如下：

Sub FindValidCombinations()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim lastRow As Integer
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    Dim totalEmployees As Integer
    totalEmployees = lastRow - 1 ' 假设数据从第2行开始
    Dim targetHours As Double
    targetHours = 7
    Dim i As Long
    Dim resultRow As Integer
    resultRow = 10 ' 结果从第10行开始输出
    
    ' 遍历所有组合（1到2^n-1）
    For i = 1 To 2 ^ totalEmployees - 1
        Dim sumHours As Double
        sumHours = 0
        Dim employeeList As String
        employeeList = ""
        Dim j As Integer
        ' 检查每个员工是否被选中
        For j = 0 To totalEmployees - 1
            If (i And 2 ^ j) <> 0 Then
                sumHours = sumHours + ws.Cells(j + 2, "C").Value
                employeeList = employeeList & ", " & ws.Cells(j + 2, "B").Value
            End If
        Next j
        ' 匹配目标工时则输出结果
        If sumHours = targetHours Then
            resultRow = resultRow + 1
            ws.Cells(resultRow, "H").Value = Mid(employeeList, 3) ' 移除开头的", "
            ws.Cells(resultRow, "I").Value = sumHours
        End If
    Next i
End Sub

运行这段代码后，所有符合条件的员工组合会自动输出到H、I列。

内容的提问来源于stack exchange，提问作者learning to code