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

この例では、数値のリストがあり、目的は、このリストのどの組み合わせの合計が 480 になるかを特定することです。提供されたスクリーンショットは、300+120 のような組み合わせを含め、この合計を達成する組み合わせのグループが 60 つあることを示しています。 +250、120+60+50+XNUMXなど。この記事では、Excel で指定された値を合計するリスト内の数値の特定の組み合わせを特定するさまざまな方法を検討します。

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

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

• ユーザー定義関数を使用する
• スマートな機能によって – Kutools for Excel

VBAコードを使用して、合計が範囲内にある数値のすべての組み合わせを取得します

ソルバー関数を使用して、指定された合計に等しいセルの組み合わせを検索します。

Excel に飛び込み、合計すると特定の数値になるセルの組み合わせを見つけるのは困難に思えるかもしれませんが、ソルバー アドインを使用すると簡単になります。ソルバーをセットアップし、セルの適切な組み合わせを見つけて、複雑に見えるタスクを簡単かつ実行可能にするための簡単な手順を説明します。

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

1. にアクセスしてください File > オプション、で Excelのオプション ダイアログボックスで、をクリックします。 アドイン 左側のペインで、 をクリックします。 Go ボタン。 スクリーンショットを参照してください：
   
2. そうして アドイン ダイアログが表示されるので確認してください ソルバーアドイン オプションをクリックして OK このアドインを正常にインストールするには。
   

ステップ2：数式を入力します

Solver アドインをアクティブ化した後、次の数式をセル B11 に入力する必要があります。

=SUMPRODUCT(B2:B10,A2:A10)

ステップ 3: ソルバーを構成して実行し、結果を取得する

1. 且つ > ソルバーパラメーター ダイアログボックスのダイアログで、次の操作を行ってください。
   • （1.）クリック セルを選択するボタン B11 数式がどこにあるか 目標を設定する セクション;
   • （2.）次に に セクション、選択 の価値、および目標値を入力します 480 必要に応じて;
   • （3.） 変数変換による セクションをクリックしてください セル範囲を選択するボタン B2：B10 どこに対応する番号をマークします。
   • （4.）次に、をクリックします Add
   • 
2. 次に、 制約を追加 ダイアログボックスが表示されるので、クリックします セル範囲を選択するボタン B2：B10、および選択 ビン ドロップダウンリストから。最後にクリックしてください OK ボタン。 スクリーンショットを参照してください：
   
3. ソルバーパラメーター ダイアログで、 解決する ボタン、数分後、 ソルバーの結果 ダイアログ ボックスが表示され、指定された合計 480 に等しいセルの組み合わせが列 B で 1 としてマークされていることがわかります。 ソルバーの結果 ダイアログで、選択してください ソルバーソリューションを維持 オプションをクリックして OK ダイアログを終了します。 スクリーンショットを参照してください：
   

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

Excel のさらに奥深い機能を探索すると、特定の合計に一致するすべての数値の組み合わせを見つけることができます。これは、思っているよりも簡単です。このセクションでは、指定された合計に等しい数値のすべての組み合わせを見つけるための 2 つの方法を説明します。

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

集合的に特定の値に達する、特定のセットから考えられるすべての数値の組み合わせを明らかにするには、以下に概説するカスタム関数が効果的なツールとして機能します。

手順 1: VBA モジュール エディターを開いてコードをコピーする

1. 押したまま Alt + F11 Excelでキーを押すと、 アプリケーション向け Microsoft Visual Basic 窓。
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: カスタム数式を入力して結果を取得します

コードを貼り付けた後、コード ウィンドウを閉じてワークシートに戻ります。空のセルに次の数式を入力して結果を出力し、 を押します。 入力します キーを押してすべての組み合わせを取得します。スクリーンショットを参照してください:

=MakeupANumber(A2:A10,B2)

先端: 組み合わせ結果を縦に列にリストしたい場合は、次の式を適用してください。

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

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

前述の関数の制限を考慮して、迅速かつ包括的なソリューションをお勧めします。Kutools for Excel の Make up a Number 機能は、Excel のあらゆるバージョンと互換性があります。この代替方法では、正の数、小数、負の数を効果的に処理できます。この機能を使用すると、指定された合計に等しいすべての組み合わせをすばやく取得できます。

1. クツール > コンテンツ > 数字を作る、スクリーンショットを参照してください：
   
2. そして、 数字を作る ダイアログボックスをクリックしてください ボタンをクリックして、使用する番号リストをから選択します。 情報元、次に合計数をに入力します 合計 テキストボックス。 最後に、をクリックします OK ボタン、スクリーンショットを参照してください：
   
3. 次に、プロンプト ボックスが表示され、結果を検索するセルを選択するよう通知され、クリックします。 OK、スクリーンショットを参照してください：
   
4. そして現在、以下のスクリーンショットのように、指定された番号に等しいすべての組み合わせが表示されています。
   

VBAコードを使用して、合計が範囲内にある数値のすべての組み合わせを取得します

場合によっては、合計が特定の範囲内の合計になる、考えられる数値の組み合わせをすべて特定する必要がある状況に陥ることがあります。たとえば、合計が 470 ～ 480 の間に収まる、考えられるすべての数値グループを見つけようとしているとします。

Excel では、合計が特定の範囲内の値になる、考えられる数値の組み合わせをすべて見つけることは、魅力的で非常に実用的な課題となります。このセクションでは、このタスクを解決するための VBA コードを紹介します。

手順 1: VBA モジュール エディターを開いてコードをコピーする

1. 押したまま Alt + F11 Excelでキーを押すと、 アプリケーション向け Microsoft Visual Basic 窓。
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 のヒントやテクニックをさらに詳しく知りたい場合は、当社の Web サイトで何千ものチュートリアルをご覧ください。 ここをクリックしてアクセスしてください。 読んでいただきありがとうございます。今後さらに役立つ情報を提供できることを楽しみにしています。

関連記事：