如何用Excel公式或VBA将千级数组拆分为3个指定和的无重叠组？

你的问题本质是大规模子集和匹配问题，传统递归枚举和顺序分配公式无法应对1000条数据的规模，以下是可行的优化方案：

一、Excel规划求解工具（无需代码，优先推荐） #

这是最直接的解决方案，利用Excel自带的规划求解加载项实现约束匹配：

• 数据准备：假设数字在A1:A1000，三个目标和分别存于D1（1810）、E1（850）、F1（800）。
• 添加辅助列：在B、C、E列设置0/1标记，分别代表是否属于组1、组2、组3。
• 设置约束：
  • 每个数字仅归属一组：B1+C1+E1=1，下拉至A1000；
  • 组1和等于目标：SUMPRODUCT(A:A,B:B)=D1；
  • 组2和等于目标：SUMPRODUCT(A:A,C:C)=E1；
  • 组3和等于目标：SUMPRODUCT(A:A,E:E)=F1；
  • 辅助列值为整数：将B、C、E列设置为整数约束（仅能取0或1）。
• 运行求解：打开「数据」选项卡→「规划求解」（需先在Excel选项中加载「规划求解加载项」），选择「单纯线性规划」求解，完成后辅助列的0/1标记即为分组结果。

二、优化后的VBA方案（高效处理大规模数据） #

递归法因枚举所有可能导致超时，改用贪心预排序+分支定界思路，优先处理大数减少无效分支：

Sub SplitToThreeGroups()
    Dim rawNums As Variant, targets As Variant
    Dim groupRemain(1 To 3) As Double, groupAssign(1 To 1000) As Integer
    Dim sortedWithIndex As Variant, i As Integer, g As Integer
    
    ' 读取数据与目标值
    rawNums = Range("A1:A1000").Value
    targets = Array(Range("D1").Value, Range("E1").Value, Range("F1").Value)
    
    ' 初始化分组剩余容量
    groupRemain(1) = targets(0)
    groupRemain(2) = targets(1)
    groupRemain(3) = targets(2)
    
    ' 对数字从大到小排序，保留原始索引
    sortedWithIndex = SortNumbersWithIndex(rawNums, xlDescending)
    
    ' 贪心分配：优先将大数放入剩余容量充足的组
    For i = 1 To UBound(sortedWithIndex)
        Dim currentNum As Double, bestGroup As Integer
        currentNum = sortedWithIndex(i, 1)
        bestGroup = 0
        
        ' 寻找能容纳当前数字且剩余容量最大的组
        For g = 1 To 3
            If groupRemain(g) >= currentNum Then
                If bestGroup = 0 Or groupRemain(g) > groupRemain(bestGroup) Then
                    bestGroup = g
                End If
            End If
        Next g
        
        ' 分配数字到目标组
        If bestGroup > 0 Then
            groupRemain(bestGroup) = groupRemain(bestGroup) - currentNum
            groupAssign(sortedWithIndex(i, 2)) = bestGroup * 100 ' 标记为100/200/300
        Else
            MsgBox "数字" & currentNum & "无法分配，请检查目标和合理性或添加回溯逻辑"
            Exit Sub
        End If
    Next i
    
    ' 写入分组标记结果
    Range("F1:F1000").Value = Application.Transpose(groupAssign)
    MsgBox "分组完成！"
End Sub

' 辅助函数：带原始索引的快速排序
Function SortNumbersWithIndex(arr As Variant, sortOrder As XlSortOrder) As Variant
    Dim tempArr(1 To 1000, 1 To 2) As Variant, i As Integer
    ' 填充值与原始索引
    For i = 1 To 1000
        tempArr(i, 1) = arr(i, 1)
        tempArr(i, 2) = i
    Next i
    ' 调用快速排序
    QuickSort tempArr, 1, 1000, sortOrder
    SortNumbersWithIndex = tempArr
End Function

Sub QuickSort(arr As Variant, left As Integer, right As Integer, sortOrder As XlSortOrder)
    Dim i As Integer, j As Integer, pivot As Double
    Dim tempVal As Double, tempIdx As Integer
    i = left
    j = right
    pivot = arr((left + right) \ 2, 1)
    
    Do While i <= j
        If sortOrder = xlDescending Then
            Do While arr(i, 1) > pivot And i < right: i = i + 1: Loop
            Do While arr(j, 1) < pivot And j > left: j = j - 1: Loop
        Else
            Do While arr(i, 1) < pivot And i < right: i = i + 1: Loop
            Do While arr(j, 1) > pivot And j > left: j = j - 1: Loop
        End If
        
        If i <= j Then
            ' 交换值与索引
            tempVal = arr(i, 1): tempIdx = arr(i, 2)
            arr(i, 1) = arr(j, 1): arr(i, 2) = arr(j, 2)
            arr(j, 1) = tempVal: arr(j, 2) = tempIdx
            i = i + 1: j = j - 1
        End If
    Loop
    
    If left < j Then QuickSort arr, left, j, sortOrder
    If i < right Then QuickSort arr, i, right, sortOrder
End Sub

代码说明 #

1. 先对数字从大到小排序，优先处理可选分组更少的大数，大幅减少无效尝试；
2. 贪心分配逻辑最大化每组剩余容量，提升后续数字的分配可能性；
3. 若贪心失败，说明目标和可能无法达成，需检查数据与目标的匹配性，或扩展代码添加分支定界回溯逻辑。

三、原有公式的改进思路 #

你的顺序分配公式易出现末尾数字无法匹配的问题，可通过动态跟踪剩余容量优化：

1. 在G1、H1、I1初始化组1-3的剩余容量（即目标和）；
2. 分组标记列（如D2）公式：

=IF(A2<=G1,100,IF(A2<=H1,200,IF(A2<=I1,300,"")))

3. 剩余容量更新列（G2）：=IF(D2=100,G1-A2,G1)，H2、I2同理下拉。

此方法仍为贪心策略，仅适合目标和宽松的场景，严格匹配需用前两种方案。

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