Excel で指定した合計値に一致するすべての組み合わせを検索するには、どうすればよいでしょうか？

リスト内の数値から、特定の合計値をつくるすべての可能な組み合わせを見つけるのは、予算編成や計画、データ分析などにおいて多くのExcel ユーザーが直面する課題です。

この例では、数値のリストから合計が480 となる組み合わせを特定することが目的です。添付のスクリーンショットには、その合計値を達成する5 つの可能な組み合わせ（例：300＋120＋60、250＋120＋60＋50 など）が示されています。本記事では、Excel でリスト内の数値から指定された合計値となる組み合わせを特定するさまざまな方法をご紹介します。

ソルバー機能で指定された合計値と等しい数値の組み合わせを見つける

指定された合計値に等しいすべての数値の組み合わせを取得する

• ユーザー定義関数を使用する場合
• スマート機能「Kutools for Excel」を使用する場合

VBA コードで合計値が特定の範囲内になるすべての数値の組み合わせを取得

ソルバー機能で指定された合計値と等しいセルの組み合わせを見つける

Excel で特定の数値を実現するセルの組み合わせを見つけるのは難しそうに思えるかもしれませんが、ソルバーアドインを使えば簡単に実現できます。本セクションでは、ソルバーを設定して最適なセルの組み合わせを導き出す簡単な手順をご紹介し、一見複雑なタスクをシンプルかつ確実にこなせるようサポートします。

ステップ1：ソルバーアドインを有効化

1. 次に、ファイル > オプションをクリックします。Excel のオプションダイアログボックスが表示されたら、左側のペインからアドインを選択し、続いて実行ボタンをクリックしてください。スクリーンショットをご覧ください：
   
2. 次に、アドインダイアログが表示されるので、ソルバーアドインにチェックを入れて、OKをクリックし、このアドインを正常にインストールしてください。
   

ステップ2：数式を入力

ソルバーアドインを有効化した後、セル B11 に次の数式を入力する必要があります：

=SUMPRODUCT(B2:B10,A2:A10)

ステップ3：ソルバーを設定・実行して結果を取得

1. 次に、データ>ソルバーをクリックしてソルバーパラメーターダイアログボックスを開き、以下の操作を行ってください：
   • （1。）ボタンをクリックして、B11セルを選択します。このセルは数式が入力されている場所で、目的セルの設定セクション内にあります。
   • （2。）次に、目標値セクションで、指定の値を選択し、必要に応じて目標値を480に設定してください。
   • （3。）変数セルの変更セクションで、ボタンをクリックし、セル範囲 B2:B10を選択してください。この範囲には対応する数値が記録されます。
   • （4。）次に、追加ボタンをクリックしてください。
   • 
2. 制約の追加ダイアログボックスが表示されたら、セル範囲を選択するためのB2:B10をクリックし、ドロップダウンリストからbinを選択してください。最後に、OKボタンをクリックします。スクリーンショットをご覧ください：
   
3. ソルバーパラメーターダイアログで解決ボタンをクリックします。数分後、ソルバーの結果ダイアログボックスが表示され、指定された合計値480 と等しいセルの組み合わせが列 B で「1」としてマークされていることを確認できます。ソルバーの結果ダイアログで、ソルバーの解を保存するオプションを選択し、OKをクリックしてダイアログを閉じます。スクリーンショットをご覧ください：
   

指定された合計値に等しいすべての数値の組み合わせを取得する

Excel の高度な機能を活用すれば、特定の合計値と一致するすべての数値の組み合わせを簡単に見つけることができます。本セクションでは、指定された合計値と完全に一致する数値の組み合わせを効率よく導き出す2 つの方法をご紹介します。

ユーザー定義関数で指定された合計値と等しいすべての数値の組み合わせを取得

特定の数値セットから、合計が指定された値となるすべての可能な組み合わせを明らかにするには、以下に示すカスタム関数が効果的なツールです。

ステップ1：VBA モジュールエディターを開き、コードをコピー

1. Excel でALT + F11キーを押すと、Microsoft Visual Basic for Applicationsウィンドウが開きます。
2. 次に、挿入>標準モジュールをクリックし、モジュールウィンドウに次のコードを貼り付けます。
   VBA コード：指定された合計値と等しいすべての数字の組み合わせを取得

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

ステップ2：カスタム数式を入力して結果を取得

コードを貼り付けたら、コードウィンドウを閉じてワークシートに戻りましょう。空白セルに次の数式を入力して結果を表示し、Enterキーを押してすべての組み合わせを取得してください。スクリーンショットをご覧ください：

=MakeupANumber(A2:A10,B2)

ヒント：組み合わせの結果を列方向（縦に）表示したい場合は、次の数式を適用してください：

=TRANSPOSE(MakeupANumber(A2:A10,B2))

強力な機能で指定された合計値と等しいすべての数値の組み合わせを取得

前述の関数には制限があるため、迅速かつ包括的なソリューションとして、あらゆるバージョンのExcel でご利用いただける「Kutools for Excel」の数値補完機能をおすすめします。この代替手段は正の数値、小数、負の数値にも対応しており、指定された合計値と一致するすべての組み合わせを素早く取得できます。

1. 次に、Kutools > コンテンツ > 数値の補完をクリックしてください。スクリーンショットをご覧ください：
   
2. 次に、表示された数値の補完ダイアログボックスで、ソース範囲から使用する数値リストを選択するためのボタンをクリックし、その後、合計テキストボックスに目標となる合計値を入力します。最後に、OKボタンをクリックしてください。スクリーンショットをご覧ください：
   
3. すると、結果を表示するセルを選択するよう促すメッセージボックスが表示されますので、OKをクリックしてください。スクリーンショットをご確認ください：
   
4. これで、指定された数値と等しいすべての組み合わせが、以下のスクリーンショットのように表示されました：
   

VBA コードで合計値が特定の範囲内になるすべての数値の組み合わせを取得

場合によっては、合計が特定の範囲内になるすべての可能な数値の組み合わせを特定する必要があることがあります。例えば、合計が470 から480 の間になるすべての可能な数値のグループを見つけたい場合などが該当します。

特定の範囲内の値となるすべての可能な数値の組み合わせを見つけるのは、Excel において非常に興味深く、かつ実用的な課題です。本セクションでは、この課題を解決するための VBA コードをご紹介します。

ステップ1：VBA モジュールエディターを開き、コードをコピー

1. Excel でALT + F11を押すと、Microsoft Visual Basic for Applicationsウィンドウが開きます。
2. 次に、挿入>標準モジュールをクリックし、モジュールウィンドウに次のコードを貼り付けます。
   VBA コード：特定の範囲内の合計値になるすべての数字の組み合わせを取得

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

ステップ2：コードを実行

1. コードを貼り付けたら、F5キーを押してコードを実行してください。最初に表示されるダイアログで、使用する数値の範囲を選択し、OKをクリックします。スクリーンショットをご確認ください：
   
2. 2 番目のプロンプトボックスで、下限値を入力または選択して、OKをクリックしてください。スクリーンショットをご確認ください：
   
3. 3 番目のプロンプトボックスで上限値を入力または選択し、OKをクリックしてください。スクリーンショットをご覧ください：
   
4. 最後のプロンプトボックスで、結果の出力を開始するセル（出力先セル）を選択し、OKをクリックしてください。スクリーンショットをご確認ください：
   

結果

これで、条件を満たす各組み合わせがワークシートの連続する行にリストされ、指定した出力セルから始まります。

Excel には、合計が特定の値となる数値の組み合わせを見つけるためのさまざまな方法があり、それぞれ動作が異なるため、あなたのExcel スキルやプロジェクトの要件に応じて最適なものを選べます。 さらに多くのExcel のヒントやテクニックを知りたい場合は、当社ウェブサイトに数千ものチュートリアルをご用意しています。ご一読いただきありがとうございます。今後も役立つ情報を提供できるよう努めてまいります！

関連記事：