エクセルVBAでの順列パターン生成

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

このマクロの意義

順列とは、ある順番で並べられた要素の組み合わせのことを指します。商業的なシミュレーションや研究のシナリオなど、さまざまな場面で順列のリストが求められます。特に、要素の数が多い場合、すべての組み合わせを手動でリストアップするのは非常に時間がかかります。このマクロを利用すれば、短時間で正確な結果を得ることができます。

利用想定シーン

• 商品の配置順序の最適化
• タスクの実施順序の検討
• 研究データのシナリオ作成
• 教育・学習のための問題生成

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

順列のパターンを自動生成する前に、実際にいくつの組み合わせが存在するのかを知ることは、出力の準備や確認作業をスムーズに進める上で非常に役立ちます。エクセルにはこのための関数として「PERMUT関数」が用意されています。

PERMUT関数は、指定した総数（n）から指定した要素数（r）を選んだときの順列の数を計算します。関数の書式は以下の通りです。

=PERMUT(n, r)

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

=PERMUT(6, 3)

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

マクロのコード

順列の数が想定外に多くないことを確認したら、実際に組み合わせリストを作成してみましょう。

以下にマクロのコードを示します。{Alt}+{F11}などでVBEを起動し、「挿入」→「標準モジュール」へ貼り付けてください。設定後はVBEは閉じ、{Alt}+{F8}などでマクロ「GeneratePermutations」を実行してください。

Option Explicit

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

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

Sub PermutationsRecur(perms As Collection, arr() As Variant, prefix As String, r As Long)
    Dim i As Long
    Dim nextArr() As Variant
    Dim idx As Long
    Dim count As Long
    Dim newSize As Long

    ' 終了条件
    If r = 0 Then
        perms.Add Split(prefix)
        Exit Sub
    End If

    ' 一つの要素のみ取り出す場合、直接追加して終了
    If r = 1 Then
        For i = LBound(arr) To UBound(arr)
            perms.Add Split(prefix & arr(i) & " ")
        Next i
        Exit Sub
    End If

    For i = LBound(arr) To UBound(arr)
        newSize = UBound(arr) - LBound(arr) + 1 ' 残りの要素数
        ReDim nextArr(1 To newSize - 1) ' newSizeの数だけのサイズで初期化
        count = 0
        For idx = LBound(arr) To UBound(arr)
            If idx <> i Then
                count = count + 1
                nextArr(count) = arr(idx)
            End If
        Next idx
        PermutationsRecur perms, nextArr, prefix & arr(i) & " ", r - 1
    Next i
End Sub

使い方

1. まず、VBAエディタ(VBE)を開き、新しいモジュールに上記のコードをコピー＆ペーストします。({Alt}+{F11}などでVBEを起動し、「挿入」→「標準モジュール」へ貼り付ける、エディタを閉じる)
2. エクセルのシートに順列を生成したい要素のリストを作成します。下の例ではセルH1:H12にリストを作成しています。ただし使用するのはH1:H6の6種類だけのつもりです。

   

3. {Alt}+{F8}などでマクロ「GeneratePermutations」を実行すると、最初にアイテム・要素の範囲が求められます。入力済み範囲を指定します。

   

4. 次に取り出す数を求められます。

   

5. 最後に結果を出力するセルの位置を指定するよう求められます。

   

6. 指定に従って情報を入力すると、自動的に順列の一覧が出力されます。サンプルの設定では120パターンが出力されます。

   

まとめ

今回紹介したエクセルVBAのマクロを使用することで、複数の要素から順列を効率的に生成することができます。日常業務や研究でのシミュレーションなど、さまざまなシーンでこのマクロを活用して、より高度な分析や検討を行ってみてください。

参考：同等の処理をブラウザから行うツール

以下のページでは順列全リストの生成をブラウザから実行可能です。

順列（重複取出しなし）の全パターンを生成するツール

順列の全パターンを生成するウェブツール n個の異なる要素からr個を選んで並べる際の、全順列パターンを生成します。このツールでは同じ値を複数回採用することがありません。部分順列(partial permutation)の全パターン生成ツールで...

www.helpaso.net

2023.08.12