エクセルVBAでの同じものを選べる順列パターン生成

エクセルは日常業務の数式計算だけでなく、VBAを用いることで更なる高度な操作を行えます。今回は、エクセルVBAを使用して「n個の異なる要素からr個を選んで並べ、同じものを選べる順列の一覧」を自動生成する方法をご紹介します。

このマクロの意義
利用想定シーン
PERMUTATIONA関数を使用して順列の数を事前に確認する
マクロのコード
使い方
まとめ

このマクロの意義

先述した通常の順列とは異なり、今回の順列では同じ要素を再度選択することが許可されています。これは、例えば同じ商品を複数選べるディスプレイ配置の最適化や、繰り返しのあるタスク順序の検討など、特定のシチュエーションで非常に役立ちます。要素の数が多い場合や、選択数が多い場合、すべての組み合わせを手動でリストアップするのは大変です。このマクロを利用すれば、短時間で正確な結果を得ることができます。

利用想定シーン

商品のディスプレイ配置の最適化
繰り返しタスクの実施順序の検討
リソースの再利用計画
組み合わせの研究やシミュレーション

PERMUTATIONA関数を使用して順列の数を事前に確認する

通常の順列とは異なり、同じ要素を再度選ぶ順列の数を確認するには「PERMUTATIONA関数」を使用します。
PERMUTATIONA関数は、指定した総数（n）から指定した要素数（r）を選ぶときの、同じ要素を再度選ぶことが許された順列の数を計算します。関数の書式は以下の通りです。

=PERMUTATIONA(n, r)

例えば、6つの要素から3つを選ぶ場合の順列の数を知りたい場合、以下のように関数を使用します。

=PERMUTATIONA(6, 3)

この関数を利用することで、事前にどれだけの組み合わせが出力されるのかを知ることができ、出力結果の妥当性の確認や、処理時間の予測などの目的で使用することができます(上記の式は216を返す)。

マクロのコード

順列の数が想定内であることを確認したら、実際に組み合わせリストを作成してみましょう。
以下にマクロのコードを示します。VBEを{Alt}+{F11}などで起動し、「挿入」→「標準モジュール」へ貼り付けてください。設定後はVBEを閉じ、{Alt}+{F8}などでマクロ「GeneratePermutationsWithRepetition」を実行してください。

Option Explicit

Sub GeneratePermutationsWithRepetition()
    Dim rng As Range
    Dim outCell As Range
    Dim arr() As Variant
    Dim r As Long
    Dim perm As Variant
    
    ' アイテム範囲を指定
    Set rng = Application.InputBox("アイテム範囲を選択してください", Type:=8)
    arr = rng.Value
    
    ' 取り出す数を指定
    r = Application.InputBox("取り出す数を入力してください", Type:=1)
    
    ' 出力先の開始セルを指定
    Set outCell = Application.InputBox("出力先の開始セルを指定してください", Type:=8)
    
    ' 順列を計算し、出力
    For Each perm In PermutationsArrayWithRepetition(arr, r)
        outCell.Resize(, r).Value = perm
        Set outCell = outCell.Offset(1, 0)
    Next perm
End Sub

Function PermutationsArrayWithRepetition(arr() As Variant, r As Long) As Collection
    Dim perms As New Collection
    PermutationsRecurWithRepetition perms, arr, "", r
    Set PermutationsArrayWithRepetition = perms
End Function

Sub PermutationsRecurWithRepetition(perms As Collection, arr() As Variant, prefix As String, r As Long)
    Dim i As Long
    
    If r = 0 Then
        perms.Add Split(prefix)
        Exit Sub
    End If
    
    For i = LBound(arr) To UBound(arr)
        PermutationsRecurWithRepetition perms, arr, prefix & arr(i, 1) & " ", r - 1
    Next i
End Sub

使い方

まず、VBAエディタ(VBE)を開き、新しいモジュールに上記のコードをコピー＆ペーストします。({Alt}+{F11}などでVBEを起動し、「挿入」→「標準モジュール」へ貼り付ける、エディタを閉じる)
エクセルのシートに順列を生成したい要素のリストを作成します。下の例ではセルH1:H12にリストを作成しています。ただし使用するのはH1:H6の6種類だけのつもりです。
{Alt}+{F8}などでマクロ「GeneratePermutationsWithRepetition」を実行すると、最初にアイテム・要素の範囲が求められます。入力済み範囲を指定します。
次に取り出す数を求められます。
最後に結果を出力するセルの位置を指定するよう求められます。
指定に従って情報を入力すると、自動的に順列の一覧が出力されます。サンプルの設定では216パターンが出力されます。