如何在Excel中自动生成组合VBA宏

本文将向您展示如何在Excel中使用VBA宏自动生成不同元素的组合。此宏用于通过从特定元素组中选择指定数量的元素来列出所有组合。例如，您可能希望列出产品特征的所有不同组合，或者您可能希望输出团队成员的所有不同角色组合。

如果要从浏览器而不是 Excel 生成组合，请参阅下面的参考页面。

使用组合功能提前检查组合数量
宏代码及其说明
总结
参考：从浏览器执行等效处理的工具

使用组合功能提前检查组合数量

在Excel中，有一个功能可以预先计算组合总数，以从一系列项目中选择一定数量的项目。这就是“组合”功能。该函数采用两个参数：要组合的对象总数（n）和一次选择的项目数（r）。

COMBIN 函数的用法如下：

首先，选择一个空单元格。这是结果的目的地。
然后键入 = COMBIN（n， r）在该单元格中。其中 n 是总数，r 是一次要选择的项目数。
最后，按输入查看结果。

例如

=COMBIN(7, 4)

计算 7 个项目中 4 个的所有组合的数量。这使您可以提前检查生成的组合数量是否太大（35 件）。

这在生成从大范围内选取许多项目的组合时特别有用。您可以使用 COMBIN 函数来避免由意外的大量组合引起的性能问题。

宏代码及其说明

一旦您确认组合的数量不是意外的，就该实际创建一个组合列表了。

下面是宏的代码： {Alt}以+{F11}等开头的VBE，然后将其粘贴到“标准模块”→“插入”中。设置完成后，关闭 VBE 并执行带有 +{F8} 的宏 “”Combinations 等{Alt}。

Option Explicit

Sub Combinations()
    Dim rng As Range
    Dim outCell As Range
    Dim arr() As Variant
    Dim r As Long
    Dim n As Long
    Dim i As Long
    Dim cmb As Variant
    
    ' アイテム範囲を指定
    Set rng = Application.InputBox("アイテム範囲を選択してください", Type:=8)
    arr = rng.Value
    n = rng.Cells.Count
    
    ' 取り出す数を指定
    r = Application.InputBox("取り出す数を入力してください", Type:=1)
    
    ' 出力先の開始セルを指定
    Set outCell = Application.InputBox("出力先の開始セルを指定してください", Type:=8)
    
    ' 組み合わせを計算し、出力
    For Each cmb In CombinationsArray(arr, r)
        outCell.Resize(, r).Value = cmb
        Set outCell = outCell.Offset(1, 0)
    Next cmb
End Sub

Function CombinationsArray(arr() As Variant, r As Long) As Collection
    Dim cmb As New Collection
    CombinationsArrayRecur cmb, arr, "", r, LBound(arr)
    Set CombinationsArray = cmb
End Function

Sub CombinationsArrayRecur(cmb As Collection, arr() As Variant, prefix As String, r As Long, start As Long)
    Dim i As Long
    For i = start To UBound(arr)
        If r = 1 Then
            cmb.Add Split(IIf(prefix <> "", prefix & " ", "") & arr(i, 1))
        Else
            CombinationsArrayRecur cmb, arr, IIf(prefix <> "", prefix & " ", "") & arr(i, 1), r - 1, i + 1
        End If
    Next i
End Sub

此宏按以下步骤工作：提前在某处列出项目清单。在下面的示例中，在单元格 I2：I11 中创建了一个列表。但是，我打算只使用 7 种类型的 I2：I8。