[[20201108232744]]　『複数の合計で任意の数字に一番近い組み合わせを導』（イチゴ）　ページの最後に飛ぶ

『複数の合計で任意の数字に一番近い組み合わせを導き出す』（イチゴ）

イチゴです。
また、お世話になります。
B2～B11に任意の数字
34,532
41,211
10,102
18,386
21,205
22,673
40,106
1,560
17,865
12,807
が有ります。
合計が、99,999円に一番近い組み合わせを導きたいのです。
VBAは全く分かりません。
エクセルの機能で解決できませんでしょうか？
どうぞ、よろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

ソルバーが使えると思います

（マナ） 2020/11/08(日) 23:36

マナ様
御世話になります。
素人のため具体的にどの様にすればいいのか教えていただければと存じます。
（イチゴ） 2020/11/08(日) 23:40

「excel ソルバー合計組み合わせ」で検索してください。
素人であっても検索して調べることはできるはずですが。

（γ） 2020/11/09(月) 00:28

検索しましたが、よく理解できません。
下記のように入力しました。

E2に=SUMPRODUCT(B2:B11,C2:C11)
C2からC11まで1を入力
D2にB2*C2入力しD11までB11*C11

ソルバーを立ち上げ
目的セルの設定で$E$2
目標値は最大値
変数セルの変更は$C$2:$C$11
追加をクリック
制約条件のセル参照で$C$2:$C$11
次に<=
制約条件(N)で100000と入力でOK
ソルバーのパラメーターで解決クリック
ソルバーの結果（OK）をクリック

上手くいきません。

制約条件の追加
セル参照　$C$2:$C$11
BIN
バイナリ
追加
解決クリック

何も変わらずでした。

ご教示下さい。

（イチゴ） 2020/11/09(月) 05:08

D列は不要
E2に=SUMPRODUCT(B2:B11,C2:C11)
F2 =ABS(99999-E2)
F2を最小とする。

制約条件は
$C$2:$C$11をバイナリ
でどうですか？
（ちなみに、バイナリとは0か1かという意味です、この場合）
（γ） 2020/11/09(月) 06:10

 これ私も詳しくないので試したのですが、
 解決の方法でエボリューショナリーを選択しないと、バイナリでできない感じですか？

 こっちで実験した結果です。
     |[A]|[B]  |[C]|[D]|[E]  |[F]
 [1] |   |     |   |   |     |   
 [2] |   |34532|  1|   |99976| 23
 [3] |   |41211|  1|   |     |   
 [4] |   |10102|  0|   |     |   
 [5] |   |18386|  0|   |     |   
 [6] |   |21205|  0|   |     |   
 [7] |   |22673|  1|   |     |   
 [8] |   |40106|  0|   |     |   
 [9] |   | 1560|  1|   |     |   
 [10]|   |17865|  0|   |     |   
 [11]|   |12807|  0|   |     |   
（稲葉） 2020/11/09(月) 14:52

そういうことは無いようです。
GRG非線形でも、バイナリを制約条件にすることもできました。

ただし、目的関数が非線形なので、
シンプレックス法は使用できないですね。

# この場合は、2^10=1024とおりしかないので、
# なんでこんなに時間が掛かるのか、という素朴な疑問はあるわけですが、
# それぞれお家の事情ということもあるんでしょうね。

（γ） 2020/11/09(月) 15:26

 すみません、こっちの勘違いでした。
 さっきはいつまでの解が出なかったので、何かしら重いファイルでも開いていたかもしれません。
（稲葉） 2020/11/09(月) 15:34

数値結果を出してくださったので、
質問者さんには安心が増した事でしょう。
ありがとうございます。

私も無論動作を確認した上で発言していますが、
たしかに発言を見る限り、やってみなはれ、
としか見えないでしょうね。
# 1024とおり計算し検算までしまっていたわけですが、
# そういう舞台裏を逐一書いたら暑苦しいかと。
（γ） 2020/11/09(月) 16:18

 こんばんは！
 私も詳しくないのでちょっとやってみました。

 制約条件の対象を
=$C$2:$C$11<=1
=$C$2:$C$11>=0
=$C$2:$C$11=整数
とするのがミソなのですね？

 勉強になります。
（SoulMan） 2020/11/09(月) 19:45

r様
稲賀様
この度御世話になります。

C列に数字の１が入っています。
E2に=SUMPRODUCT(B2:B11,C2:C11)
F2 =ABS(99999-E2)

ソルバーを立ち上げます。
目的セルの設定は　$E$2
目標値は　　最大値
変数セルの変更　$C$2:$C$11
追加をクリック

制約条件の追加
セル参照　$C$2:$C$11
真ん中のセル　bin
制約条件　バイナリ
追加クリック
キャンセルクリック

制約条件の対象表示が
「$C$2:$C$11　=　バイナリ」
制約のない変数を非負数にするにチェック
解決方法の選択　「GRG非線形」

解決を選択
何も変わらず。

１．セル参照の枠
２．次の枠
３．制約条件の枠
が間違っているのでしょうか？

制約条件の対象枠の表示で、真ん中の枠が「bin」と表示したのですが、
制約条件の対象表示が
「$C$2:$C$11　=　バイナリ」と表示しています。

制約条件に<=　１０００００　を入力し、解決をクリックすると
それなりの表示になります。

お忙しい中とは存じますが、ご教示いただければと存じます。

いちご
（イチゴ） 2020/11/09(月) 19:58

 C列には、1　か　0が入って欲しいので
制約条件を追加していき
1以下
0以上
かつ整数
と三つの条件を加える必要があるのではないでしょうか？？？
今日やったばかりなので詳しいことはわかりませんが、、
それなりの答えは出ております。。。
（SoulMan） 2020/11/09(月) 20:09

設定条件は、γさんから回答ありましたが
なぜ、そのとおり試さないのでしょうか。

（マナ） 2020/11/09(月) 20:26

＞目標値は　　最大値

なぜ、最大値？

（マナ） 2020/11/09(月) 20:31

 なるほど、、
=$C$2:$C$11binバイナリ
で
=$C$2:$C$11=バイナリ
となるのですね？？？

 勉強になりますねぇ。。。
ありがとうございます。。。
（SoulMan） 2020/11/09(月) 20:37

 因みに目標のセルは、F2ですよね？
（SoulMan） 2020/11/09(月) 20:43

皆様

この度は有り難うございました。

無事、解決できました。

感謝致します。

いちご
（イチゴ） 2020/11/09(月) 21:53

コメント返信：

[ 一覧(最新更新順) ]

[[20201108232744]] 『複数の合計で任意の数字に一番近い組み合わせを導』（イチゴ） ページの最後に飛ぶ

[[20201108232744]]　『複数の合計で任意の数字に一番近い組み合わせを導』（イチゴ）　ページの最後に飛ぶ