自动生成组合模式列表的 Excel 宏

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

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

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

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

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

COMBIN 函数的用法如下:

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

例如

=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。

  1. 首先,让用户选择一系列项目。

  2. 接下来,让他们指定要检索的项目数。

  3. 之后,让他们选择要输出结果的单元格的位置。

  4. CPU 计算从选定区域中检索指定数量的项目的所有组合,并将其输出到指定的单元格。

总结

以上是如何在Excel中使用VBA宏自动生成组合。 宏使用户可以轻松地指定项目范围、要检索的项目数以及输出结果的单元格的位置。 当您要列出不同元素的所有组合时,或者当您想通过从范围中选择一定数量的元素来查找所有可能的组合时,此宏非常有用。

参考:从浏览器执行等效处理的工具

在下一页上,您可以从浏览器生成组合列表。

組合せ(重複取出しなし)の全パターンを生成するツール
組み合わせ(重複取り出しなし)の全パターンを生成するツール n個の異なる要素からr個を選ぶ際の、全組み合わせパターンを生成します。選んだ要素の順番は考慮しません。 サンプルのように、テキストエリアへ要素を1行ごとに入力してください。 組合せ...

 

コメント