求解可变维度n*m数组排除零值的所有有效组合方案

核心要求 #

• 输入：n列m行的任意尺寸数组
• 生成规则：每列选取且仅选取1个行对应元素，枚举所有可能组合
• 过滤规则：排除任意位置包含零值的组合

示例：2行3列数组，若第1行第2列（Item2）为零，需排除所有用到该单元格的4种组合，最终保留4种有效组合：AEC、AEF、DEC、DEF

实现代码 #

1. Excel VBA 实现（适配宏使用场景） #

可直接在Excel中运行，支持选中任意输入区域，提前过滤每列零值避免无效计算，结果自动输出到指定区域：

Sub GenerateValidCombinations()
    Dim inputRng As Range, outputCell As Range
    Dim arr As Variant, colVals As Object, colCount As Long, rowCount As Long
    Dim result As Collection, tempComb As Variant, i As Long, j As Long
    
    ' 选中输入数组区域
    Set inputRng = Application.InputBox("请选择输入数组区域（不含表头）", Type:=8)
    arr = inputRng.Value
    colCount = UBound(arr, 2)
    rowCount = UBound(arr, 1)
    
    ' 存储每列非零值
    Set colVals = CreateObject("Scripting.Dictionary")
    For j = 1 To colCount
        colVals.Add j, New Collection
        For i = 1 To rowCount
            If arr(i, j) <> 0 And arr(i, j) <> "" Then
                colVals(j).Add arr(i, j)
            End If
        Next i
        ' 某列无非零值直接退出，无有效组合
        If colVals(j).Count = 0 Then
            MsgBox "第" & j & "列无有效非零值，无符合要求的组合", vbExclamation
            Exit Sub
        End If
    Next j
    
    ' 递归生成组合
    Set result = New Collection
    ReDim tempComb(1 To colCount)
    Call RecurseCombinations(colVals, 1, colCount, tempComb, result)
    
    ' 输出结果
    Set outputCell = Application.InputBox("请选择结果输出起始单元格", Type:=8)
    For i = 1 To result.Count
        outputCell.Offset(i - 1, 0).Resize(1, colCount) = result(i)
    Next i
End Sub

' 递归生成组合子过程
Private Sub RecurseCombinations(colVals As Object, curCol As Long, maxCol As Long, tempComb As Variant, result As Collection)
    Dim val As Variant
    For Each val In colVals(curCol)
        tempComb(curCol) = val
        If curCol = maxCol Then
            ' 已到最后一列，保存组合
            result.Add tempComb
        Else
            ' 递归处理下一列
            Call RecurseCombinations(colVals, curCol + 1, maxCol, tempComb, result)
        End If
    Next val
End Sub

2. Python 实现（通用数据处理场景） #

通过标准库itertools快速实现，适配任意数组格式输入：

from itertools import product

def get_valid_combinations(arr):
    # 提取每列的非零值
    col_non_zero = []
    for col in zip(*arr):
        non_zero = [v for v in col if v != 0]
        if not non_zero:
            return []
        col_non_zero.append(non_zero)
    # 生成所有组合返回，字符串拼接可根据需求调整为列表返回
    return [''.join(comb) for comb in product(*col_non_zero)]

# 示例测试
test_arr = [
    ['A', 0, 'C'],
    ['D', 'E', 'F']
]
print(get_valid_combinations(test_arr))
# 输出：['AEC', 'AEF', 'DEC', 'DEF']

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