エクセルで指定範囲の合計になるランダム組み合わせを自動計算する方法

エクセルでデータの組み合わせを自動的に計算し、指定した範囲の合計を見つける方法は多くの業務や分析で役立ちます。この記事では、簡単な関数やVBAを使って、ランダムにデータを足し合わせつつ条件を満たした組み合わせを見つける手法を紹介します。

エクセル関数だけで行う方法
VBAで自動的に組み合わせを探索する
実例で理解する組み合わせ探索
応用：ワークシートへの出力
まとめ

エクセル関数だけで行う方法

小規模なデータの場合は、エクセルのSUM関数やIF関数を組み合わせて条件に合う組み合わせを確認することが可能です。

例えば、A列にデータが入力されている場合、B列にチェック用のフラグを立てて、SUMPRODUCT関数で合計を計算し、350以上400以下かどうかを判定できます。

ただし、データ数が多くなると手作業での組み合わせ確認は現実的ではなくなるため、VBAの利用がおすすめです。

VBAで自動的に組み合わせを探索する

VBA（Visual Basic for Applications）を使えば、一度使用したデータを再利用せずに、指定範囲内の合計になる組み合わせを自動的に見つけることができます。

基本的な考え方は再帰処理で全ての組み合わせをチェックし、合計が範囲内になったらその組み合わせを記録する方法です。

以下の簡単なサンプルコード例です。

Sub FindCombinations()
    Dim data As Variant
    Dim results As Collection
    data = Array(100, 150, 50, 260, 110)
    Set results = New Collection
    Call Combine data, Array(), 0, 350, 400, results
    Dim r
    For Each r In results
        Debug.Print Join(r, ", ") & " -> " & Application.WorksheetFunction.Sum(r)
    Next r
End Sub

Sub Combine(arr As Variant, current As Variant, index As Integer, minVal As Double, maxVal As Double, results As Collection)
    Dim i As Integer
    For i = index To UBound(arr)
        Dim newCurrent() As Variant
        newCurrent = AppendArray(current, arr(i))
        Dim total As Double
        total = Application.WorksheetFunction.Sum(newCurrent)
        If total >= minVal And total <= maxVal Then
            results.Add newCurrent
        End If
        If total < maxVal Then Combine arr, newCurrent, i + 1, minVal, maxVal, results
    Next i
End Sub

Function AppendArray(arr As Variant, val As Variant) As Variant
    Dim result() As Variant
    Dim i As Integer
    ReDim result(IIf(IsEmpty(arr), 0, UBound(arr)) + 1)
    If Not IsEmpty(arr) Then
        For i = LBound(arr) To UBound(arr)
            result(i) = arr(i)
        Next i
    End If
    result(UBound(result)) = val
    AppendArray = result
End Function

実例で理解する組み合わせ探索

上記のサンプルでは、データ {100, 150, 50, 260, 110} を使い、350以上400以下の組み合わせを探索します。

実行すると、例えば「260, 110 -> 370」や「100, 150, 110 -> 360」といった組み合わせが自動で出力されます。どのデータが使用されたかも確認可能です。

この方法を応用すれば、データ数が増えても同様に探索でき、Excelのワークシート上で結果を一覧化することも可能です。

応用：ワークシートへの出力

VBAで見つけた組み合わせを直接シートに出力することも簡単です。例えば、結果をループで書き出すことで、後からフィルターや条件付き書式で視覚的に整理できます。

コードの最後に以下のように追加します。

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
Dim row As Integer
row = 1
For Each r In results
    ws.Cells(row, 1).Value = Join(r, ", ")
    ws.Cells(row, 2).Value = Application.WorksheetFunction.Sum(r)
    row = row + 1
Next r