Excelで与えられた合計に等しい数値のすべての組み合わせを見つける

一連の数値から特定の合計に合計するすべての組み合わせを見つける3つの方法。

特定の合計に加わる値の組み合わせを見つけることは、データ分析で一般的な問題です。たとえば、特定の予算で購入できるアイテムのすべての可能な組み合わせ、または特定の要件を満たすためにリソースを割り当てるすべての可能な方法を知りたい場合があります。この記事では、ExcelソルバーとVBAを使用してタスクを達成する方法を検討します。また、大きなデータセットや複雑な制約を処理するときに発生する可能性のある各アプローチの潜在的な落とし穴と制限についても説明します。

• 特定の合計に等しい数値の組み合わせを見つける-Excelソルバー
• 合計するすべての組み合わせを特定の合計 - カスタム関数に取得します
• 特定の値に合計するすべての組み合わせを返します-Macro

Excelソルバーを使用した特定の合計に等しい数字の組み合わせを見つける

残念ながら、組み込まれたExcel関数のどれも、特定の合計に加わる数字を特定するのに役立ちません。幸いなことに、Excelは線形プログラミングの問題を解決するための特別なアドインを提供します。ソルバーアドインは、Excelのすべてのバージョンに含まれていますが、デフォルトでは有効になっていません。このツールに慣れていない場合は、Excelでソルバーを追加および使用する方法に関する優れた記事をご紹介します。

Excelでソルバーアドインをアクティブにした状態で、次の手順を進めます。

1. モデルを作成します。

   このために、1つの列（この場合はA3：A12）に数字のセットを入力し、結果のために数字の右側に空白の列を追加します（B3：B12）。別のセル（B13）で、これに類似した等式式を入力します。

   =SUMPRODUCT(A3:A12, B3:B12)

   

2. ソルバーアドインを実行します。

   [データ]タブで、分析グループの[ソルバー]ボタンをクリックします。

   

3. ソルバーの問題を定義します。

   [ソルバーパラメータ]ダイアログボックスで、目的セルと可変セルを構成します。

   • 設定された目的ボックスに、式セル（B13）のアドレスを入力します。
   • Toセクションで、値の値を選択して、目的の合計値を入力します（この例では50）。
   • Variableセルの変更ボックスで、結果を入力する範囲を選択します（B3：B12）。

   

4. 制約を追加します。

   制約、つまり、満たす必要がある制限または条件を指定するには、 [追加]ボタンをクリックします。 [制約の追加]ダイアログウィンドウで、結果の範囲（B3：B12）を選択し、ドロップダウンリストからビンを選択します。制約は自動的にバイナリに設定されます。完了したら、[OK]をクリックします。

   

5. 問題を解決します。

   [ソルバーパラメーター]ダイアログウィンドウに戻したら、設定を確認し、 [solve]ボタンをクリックします。

   

   数秒（または数分）後、 [ソルバー結果]ダイアログボックスが表示されます。成功した場合は、キープソルバーソリューションオプションを選択し、[OK]をクリックしてダイアログを終了します。

   

その結果、指定された合計に合計する数値の横に1つの挿入があります。ユーザーフレンドリーなソリューションではありませんが、箱から出してExcelができるのは最高です。

視覚化のために、私は照明グリーンで望ましい合計を与えるセルを強調しました。

制限：Excel Solverは、せいぜい、特定の合計に等しい数字の1つの組み合わせだけを見つけることができます。

特定の合計とカスタム関数に等しいすべての組み合わせを見つけます

特定の値になる特定の数値セットからすべての可能な組み合わせを取得するには、以下のカスタム関数を使用できます。 UDFSを初めて使用している場合は、このガイドに多くの有用な情報があります。Excelでカスタムユーザー定義関数を作成する方法です。

特定の合計に等しいすべての組み合わせを見つけるカスタム関数

オプション明示的なパブリック関数findSumcombinations（rngnumbers as range、ltargetsum and on long ） dim arnumbers（）and part（） as long dim indi as long dim cellcurr as long dim cellcurr as mangurr redim arres（0） if rngnumbers.count> 1その後のredim arnumbers （rngnumbers.count -court 0 in rnumbers -1 ） Arnumbers（indi）= clng（cellcurr.value）indi 1 indi 1次のCellcurr call sumupRecursiveCombinations（arnumbers、ltargetsum、part（ ） 、 arres （）） end if redim if if redim preserve arres（ 0からubound（arres） -1 ）findumumbinations = arres private sub subrecursivecominations （ ）パート（） byref arres（） as string ）は長い間、長い間、jと同じくらい長い、長い間、長い程度の微量（）と同じ長さ（）のように長いs = sumarray（）の場合はs = sumarray （）の場合（sumarray = ubound = ubound = ubound （arres） redim preserve arres（ 0 ） arres （ 0 ） arres （ indres ext）数字） > 0 then i = 0からubound （ numbers ）消去​​​​dim n n n = lbound （x） 1からubound （x ） result = result ＆ " 、 " ＆x（n） next n arraytostring = result end function private function sumarray（x（））のn = lbound （x） 1からubound （ x ） for ubound（ n ） as long n nog sumarray = 0 if （ not not x） 0 for n = lbound （x） If end function private sub addtoArray（arr（）の場合、xは長い） if （arrではない） 0 0 0 arr（0からubound （arr）1 ） redim redim preserve arr（ 0〜0 ） end arr （ ubound （arr））= x end sub private sub copyarray（ destination （ ） long 、 source （ ） not not not surce not sourc AddToArray宛先、ソース（n）次のn endの場合はsub

この機能の仕組み

主な機能であるFindSumCombinationsは、小さなサブタスクを実装するいくつかの子会社機能を呼び出します。 SumupRecursiveCombinationsという名前の関数は、指定された範囲ですべての可能な合計を見つけ、ターゲットに到達するものをフィルターするコアアルゴリズムを実行します。 ArrayTostring関数は、出力文字列の形を制御します。さらに3つの関数（ Sumarray 、 AddToArray 、およびCopyArray ）が中間配列の処理を担当します。一時配列を作成するたびに、ソース配列から1つの要素を追加し、ターゲット合計に到達するかどうかを確認します。コアアルゴリズムはこのStackOverFlowスレッドから取得されます。共有してくれてありがとう！

構文

ユーザーの観点から見ると、カスタム関数の構文はこれと同じくらい簡単です。

FindSumCombinations（範囲、合計）

どこ：

• 範囲は、テストする数値の範囲です。
• 合計は目標合計です。

注記！カスタム関数は、Dynamic Array Excel 365および2021でのみ機能します。

FindSumCombinations関数の使用方法：

1. 上記のコードをワークブックのコードモジュールに挿入し、マクロ対応のワークブック（.xlsm）として保存します。ステップバイステップの指示はこちらです。
2. 任意の空白のセルで、 FindSumcombinations式を入力し、 Enterキーを押します。すべての組み合わせを出力するのに十分な空のセルが右にあることを確認してください。そうしないと、式は#spillエラーを返します。

たとえば、A3の合計に等しい範囲A6：A15の数値のすべての可能な組み合わせを見つけるには、式は次のとおりです。

=FindSumCombinations(A6:A15, A3)

他の動的配列関数と同様に、式を入力すると、式は1つのセル（下の画像のC6）であり、結果を必要な数のセルに入力します。デフォルトでは、コンマ分離された文字列は連続して出力されます。

結果を列に返すには、カスタム関数をこのように転置に包みます。

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

巻き装置に囲まれた配列の形で文字列を出力するには、次のように配列機能を変更します。

string dim n and and result as string result = "{" ＆ x（n） for n = lbound（x）1からubound （ x ）result = result ＆ " 、 " ＆x（n）next n result = result＆ " }" arraytoString = result end関数として、string dim nとしてstring dim nのように、プライベート関数arraytostring（x（））

結果はこれに似ています。

制限：このカスタム関数は、動的配列Excel 365およびExcel 2021でのみ機能します。

特定の合計とVBAマクロに等しいすべての組み合わせを取得します

上記のカスタム関数は、数字の組み合わせを文字列として返します。個別のセルに各番号を持っている場合は、このマクロが役立ちます。このコードは、このブログでさまざまなExcelの問題を解決するためにユーザーが積極的に支援している別のExcelの専門家Alexander Trifuntovによって書かれています。

次のコードをワークブックに追加することから始めます。詳細な手順については、ExcelにVBAコードを挿入する方法をご覧ください。

マクロ特定の値に合計するすべての組み合わせを見つける

パブリックeffarray1 string as public ds as variant public targetsum as long public targetcol as Integer public targetrow as Integer Sub Combointion​​​​​​​​​​​​​​​​​​​​ブールディムオッドステップas boolean dim sss dim targetarray（） as string kk =ターゲットコルrr =ターゲットロウcol1 =ターゲットコル3 row1 =ターゲットロウオッドステップ=ターゲットロウ= lbound （アイテム）上部= ubound （アイテム）セル（rr -1、kk）= " result" cell（rr -1）= "sum sum" 1） kk）.font.bold = Trueセル（RR -1、kk 1 ） .font.bold = true redimimevector （ lower to upper） 'newsub = ""の場合、すべての0のdoを開始します。 sssアイテム（i）終了の場合は、次のife newsub = "" then newsub = "{}" '空のセットsublist = sublist＆vbcrlf＆newsub if sss = targetsum then cell（rr、kk）.numberformat = "@" cells（rr、kk）= "=" {"＆mid（mid、"} " "、" ）n1 = ubound （targetArray） for E = 0からn1セル（row1、col1 ） =ターゲットアレイ（ e ） row1 = row1 1 codevector（i） 1 i = i 1 loop 'if i = upper：upperの場合： upperの場合、uppendの場合、それは終了します= true else ' not not * next * bit： i 1 codevector（i）= 1 -codevector（i） end if end if end = nod odd step 'gree below step loop greycode = sublist end機能の間のトグル

次に、次の設計とプロパティを使用して、userform（挿入> userform ）を作成します。

フォームのデザインを完了したら、フォームのコードを追加します。この場合、プロジェクトエクスプローラーのフォームを右クリックし、 [コードを表示]を選択します。

ユーザーフォームのコード

private sub commandbutton1_click（） dim b dim b d dim b as integer dim d as integer dim a（） as variant dim i as integer dim e as integer ds = range（refedit1）targetsum = textbox1.value範囲（refedit2）。 SELECT TARGETCOL = SELECTION.COLUMN TARGENROW = SELECTION.ROW C = LBOUND （DS） D = UBOUND （ DS） REDIM B（D -1 ） i = 1からD E = I -1 B （ E） = DS（ I 、 1 ）

ヒント。フォームをゼロから再作成する代わりに、この投稿の最後にサンプルワークブックをダウンロードして、そこからコードモジュールとuserform1をコピーできます（マクロを別のワークブックにコピーする方法を参照）。すべてのワークブックでマクロにアクセスできるようにするには、個人のマクロワークブックにコピーします。

コードとフォームを配置すると、 Alt F8を押して、 FindAllCombinations Macroを実行します。

ポップアップするフォームで、次のものを定義します。

• ソース番号の範囲（A4：A13）
• 目標和（50）
• 宛先範囲の上部左セル（C4）。

出力範囲の上部左セルを指定するときは、少なくとも1つの空白の列（ヘッダー用）と十分な空白のセルがあり、右側にあることを確認してください。空白のセルが不十分な場合、既存のデータが上書きされます。

[OK]をクリックすると、以下のスクリーンショットに示すような結果が生成されます。

• C4：C6では、数値の組み合わせがコンマ分離値としてあります。
• 列f、g、およびhは、数字の同じ組み合わせを保持し、各数値は別のセルに存在します。
• D4には、目標和があります。

この形式の出力により、結果を簡単に確認できます - セルF13に合計式を入力し、さらに2つのセルに右にドラッグすると、数値の各組み合わせが指定された値（50）に追加されることがわかります。

制限：多数の大量のセットの場合、マクロはすべての可能な組み合わせを生成するのに少し時間がかかる場合があります。

利点：Excel 2010 -365のすべてのバージョンで機能します。 2つの形式の出力 - 別々のセル内のコンマ分離値と数字の文字列。

結論として、特定の値に等しい値のすべての組み合わせを見つけることは、幅広いデータ分析の問題を解決するための強力なツールです。うまくいけば、この記事により、このトピックをさらに探索し、自分の仕事で同様の問題を処理するための有用な出発点が提供されることを願っています。読んでくれてありがとう！

練習ワークブック

特定の合計に等しい組み合わせを見つける - 例（.xlsmファイル）

以上がExcelで与えられた合計に等しい数値のすべての組み合わせを見つけるの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。