指定された合計に等しいすべての組み合わせをExcelで見つけるにはどうすればよいですか？

特定の合計になるリスト内の数字のすべての可能な組み合わせを見つけることは、予算編成、計画、またはデータ分析の目的で、多くのExcelユーザーが直面する課題です。

この例では、数値のリストがあり、そのリストから合計が480になる組み合わせを特定することが目的です。スクリーンショットは、300+120+60や250+120+60+50などの組み合わせを含む、この合計を得るための5つの可能なグループがあることを示しています。この記事では、指定された値に合計されるリスト内の数値の特定の組み合わせを見つけるためのさまざまな方法を探ります。

ソルバー関数を使用して指定された合計に等しい数値の組み合わせを見つける

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

• ユーザー定義関数を使用して
• スマートな機能 – Kutools for Excel を使用して

VBAコードを使用して範囲内の合計を持つすべての数値の組み合わせを取得する

ソルバー関数を使用して指定された合計に等しいセルの組み合わせを見つける

特定の数値に合計されるExcel内のセルの組み合わせを見つけるのは難しく思えるかもしれませんが、ソルバーアドインを使えば簡単です。ここでは、ソルバーを設定し、適切なセルの組み合わせを見つけるための簡単な手順を説明します。複雑に見えるタスクもシンプルで実行可能になります。

ステップ 1: ソルバーアドインを有効にする

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

ステップ 2: 数式を入力する

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

=SUMPRODUCT(B2:B10,A2:A10)

ステップ 3: ソルバーを設定し結果を得るために実行する

1. クリック データ > ソルバー 「 ソルバー パラメーター 」ダイアログボックスに移動し、ダイアログ内で次の操作を行ってください：
   • (1.) 「 」ボタンをクリックして、セル B11 を選択してください。これは「 目的の設定 」セクションにあります；
   • (2.) 次に「To」セクションで「Value Of」を選択し、必要に応じて目標値 480を入力します；
   • (3.) 変数セルを変更して セクションで、「 」ボタンをクリックして、セル範囲 B2:B10 を選択してください。これにより対応する数値がマークされます。
   • (4.) 次に、「追加」ボタンをクリックします。
   • 
2. 次に、 制約の追加 ダイアログボックスが表示され、「 」ボタンをクリックして、セル範囲 B2:B10を選択し、「 バイナリ 」をドロップダウンリストから選択します。最後に、「 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))

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

前述の関数の制限を考慮して、迅速かつ包括的な解決策として、Kutools for Excelの「数値補完」機能をお勧めします。この機能はどのバージョンの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のヒントやコツについて知りたい場合は、当サイトには数千のチュートリアルが掲載されています。ご閲覧いただきありがとうございます。今後とも役立つ情報を提供できるよう努めてまいります！

関連記事：