Excelで組み合わせを総当たり！ユーザー定義関数で効率的に組み合わせを列挙

あなたは、Excelで複数の範囲のセルに記載されたアイテムのすべての組み合わせを総当たりで列挙したいことがありますか？それなら、以下のユーザー定義関数があなたを助けてくれるでしょう！この関数を使えば、手間を省き、簡単に総当たりの組み合わせを列挙できます。この記事では、関数の使い方や効果、使用例を紹介します。

作成する関数の概要

ここでは作成する関数を「ALLCOMBINATIONS関数」としています。このALLCOMBINATIONS関数は、複数の範囲からそれぞれ1つずつアイテムを選んで組み合わせを作り、その組み合わせを総当たりで列挙する関数です。例えば、商品の色やサイズなど、異なる属性からなるアイテムのすべてのバリエーションを一覧表示したい場合に役立ちます。

関数の使用方法

まず、次のコードをExcelのVBAエディタで標準モジュールに貼り付けてください。

Function ALLCOMBINATIONS(ParamArray ranges() As Variant) As String
    Dim i As Long
    Dim pos() As Long
    Dim totalCombinations As Long
    Dim rangeCount As Long

    rangeCount = UBound(ranges) - LBound(ranges) + 1
    ReDim pos(rangeCount - 1)

    totalCombinations = 1
    For i = LBound(ranges) To UBound(ranges)
        totalCombinations = totalCombinations * Application.WorksheetFunction.CountA(ranges(i))
    Next i

    For i = 1 To totalCombinations
        For j = LBound(ranges) To UBound(ranges)
            ALLCOMBINATIONS = ALLCOMBINATIONS & ranges(j).Cells(pos(j - LBound(ranges)) + 1).Value
            If j < UBound(ranges) Then
                ALLCOMBINATIONS = ALLCOMBINATIONS & ", "
            End If
        Next j
        If i < totalCombinations Then ALLCOMBINATIONS = ALLCOMBINATIONS & " | " End If pos(UBound(pos)) = pos(UBound(pos)) + 1 For j = UBound(pos) To LBound(pos) Step -1 If pos(j) >= Application.WorksheetFunction.CountA(ranges(j)) Then
                If j > LBound(pos) Then
                    pos(j) = 0
                    pos(j - 1) = pos(j - 1) + 1
                End If
            End If
        Next j
    Next i
End Function

その後、以下のように関数を使用できます。

=ALLCOMBINATIONS(範囲1, 範囲2, 範囲3, ...)

使用例

例として、次のような表を作成しました。

この表の内容を使って、すべての色とサイズの組み合わせを列挙するには、以下のように関数を使います。

=ALLCOMBINATIONS(A1:A3, B1:B3)

この関数を実行すると、次のようにすべての色とサイズの組み合わせが列挙されます。

    赤色, 大 | 赤色, 中 | 赤色, 小 | 青色, 大 | 青色, 中 | 青色, 小 | 緑色, 大 | 緑色, 中 | 緑色, 小

関数の応用

ALLCOMBINATIONS関数は、複数の範囲からアイテムを選択して総当たりの組み合わせを列挙するだけでなく、さらに応用して使うことができます。例えば、異なるカテゴリーの商品を組み合わせておすすめセットを作成する際にも、この関数が役立ちます。

まとめ

この記事では、Excelで複数の範囲のセルの組み合わせを総当たりで列挙するALLCOMBINATIONS関数について紹介しました。関数の使い方や効果、使用例を通じて、Excelで組み合わせを効率的に作成する方法を学びました。この関数を利用すれば、手間をかけずに様々な組み合わせを簡単に作成できますので、是非お試しください。