arrays - 查找 n * m 数组的所有可能组合，不包括某些值

Question

我有一个大小可以变化的数组，有 n 列和 m 行，我需要为每个行/列组合找到一个元素的所有组合，但排除元素为零的任何组合。所以，在实践中，如果我有:


排
项目1
项目2
第 3 项


1
一个
乙
C

2
D
乙
F


我将有 2^3 = 8 种可能的组合:美国广播公司 , ABF , AEC, AEF, 数据库 , DBF ，DEC，DEF。
但是如果不是 B 我在第 1 行 Item2 中有一个零，我想从组合列表中排除该单元格(在 粗体 上面)，所以我最终会得到:AEC、AEF、DEC 和 DEF .
我找到了一些代码，可以为我提供固定数量的列 (Macro to make all possible combinations of data in various columns in excel sheet) 上的所有可能组合，但它没有考虑可以更改维度的数组，也没有考虑上面的排除规则。

Answer 1

我只是要发布简单(无零)案例的代码，这样你就可以看到我要去哪里了(当然我已经意识到 Base 切换到基数 11 以后的字母，所以这可能不是最聪明的方法:) )

Function ListCombos(r As Range)

    Dim s As String, result As String
    Dim arr()
    Dim j As Integer, offset As Integer
    Dim rows As Integer, cols As Integer
    Dim nComb As Long, i As Long
    
    rows = r.rows.Count
    cols = r.Columns.Count
    
    nComb = rows ^ cols
    ReDim arr(1 To nComb)
    
    For i = 1 To nComb
        s = Application.Base(i - 1, rows, cols)

        result = ""
        For j = 1 To cols
            offset = CInt(Mid(s, j, 1))
            result = result & r.Cells(1, 1).offset(offset, j - 1)
        Next j

        arr(i) = result
    Next i
    
    ListCombos = arr
End Function

这是包含零的版本跳过组合。该方法是将非零值移动到保持数组的第一行，如果你从这样的开始

你让它看起来像这样

因此，您不必生成或检查所有包含零的组合。
然后使用混合基数循环组合:

Option Explicit
Option Base 1

Function ListCombosWithZeroes(r As Range)
    Dim s As String, result As String
    Dim arr()
    Dim i As Integer, j As Integer, offset As Integer, count As Integer, carry As Integer, temp As Integer
    Dim rows As Integer, cols As Integer
    Dim nComb As Long, iComb As Long
    Dim holdingArr(20, 20) As String
    Dim countArr(20) As Integer
    Dim countUpArr(20) As Integer
    
    
    rows = r.rows.count
    cols = r.Columns.count
    
    ' Move non-zero cells to first rows of holding array and establish counts per column
    
    For j = 1 To cols
        count = 0
        For i = 1 To rows
            If r.Cells(i, j) <> 0 Then
                count = count + 1
                holdingArr(count, j) = r.Cells(i, j)
            End If
        Next i
        countArr(j) = count
    Next j
                
    
    ' Calculate number of combos
    
    nComb = 1
    For j = 1 To cols
        nComb = nComb * countArr(j)
    Next j
        
    ReDim arr(1 To nComb)
    
    'Loop through combos
    
    For iComb = 1 To nComb

        result = ""
        For j = 1 To cols
            offset = countUpArr(j)
            result = result & holdingArr(offset + 1, j)
        Next j

        arr(iComb) = result
        
        'Increment countup Array - this is the hard part.
        
        j = cols
        
        'Set carry=1 to force increment on right-hand column
        
        carry = 1
        
        Do

            temp = countUpArr(j) + carry
            countUpArr(j) = temp Mod countArr(j)
            carry = temp \ countArr(j)
            j = j - 1
            
        Loop While carry > 0 And j > 0

    Next iComb
    
    ListCombosWithZeroes = arr

End Function

您不必每列有相同数量的字母。