excelで条件に合うよう、複数のセルの合計を求めたい

例えば、次のように並んでいるセルの数値があるとします。

1515
2748
540
5509
2195
680
7142
305
5042
530
667
325
9950
4800

その合計が30000以上で、かつ、最小の数字となるよう、複数のセルを選択したいと思いますが、これを実現できる関数はありますか？

No.4 回答者： fujillin 回答日時： 2017/06/12 13:50

こんにちは

すでに、ANo3様が回答を出されていらっしゃいますが、エクセルにはソルバー機能があるので（私も使ったことはありませんが）、試しにやってみました。
https://support.office.com/ja-jp/article/%E3%82% …
（リボンのデータタブのソルバーです。ない場合はアドオンで追加する必要があります）

添付図のB列が0か1の値を取るものとして、E1に目的とする値を
　=SUMPRODUCT(A1:A14,B1:B14)
として計算させています。
ソルバーの条件として、最小値を求めるものとして
　E1 <= 30000
B列を0か1にするために
　B1:B14 = 整数
　B1:B14 <= 1
　B1:B14 >= 0
の条件設定をしています。

解法を「エボリューショナリー」として実行すると、ANo3様の回答と同じものが得られます。（解答が出るまでに何十秒かかかります。）
しかしながら、「GRG 非線形」（←こちらが一般的ではないかと思われますが）として実行すると正しい解答は得られませんでした。（近いものにはなりますが）

エクセル本体の機能なので利用できれば簡単かと考えたのですが、私が使ったことがないのと、内部の計算方法や各種設定もちゃんとわかっていないので、何とも言えませんが正しい使い方をしていないのかもしれません。

こんな方法もあるよというご参考までに。

この回答へのお礼

ありがとうございます
いろいろ方法があるようですね。

お礼日時：2017/06/12 16:04

No.3 ベストアンサー 回答者： neKo_deux 回答日時： 2017/06/12 11:15

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
１行目にデータの数値を横並び。
２行目に0～13の固定値を右から
３行目に2の0乗～2の13乗(8192)の固定値を右から
Ａ列に0～16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして２進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、２進数の各桁のビットとデータの数値の積和

で、全16384通りの計算が行われるので、A4:末尾を選択して並べ替えすると、
合計が30002となる、
1515
2748
2195
680
7142
305
667
9950
4800
が確認できるとか。

--
もっと数値の数が増えると、この方法では厳しいので、

ナップザック問題をExcelで解く
http://www.geocities.co.jp/SiliconValley-Oakland …

みたいなプログラムで解くような事になります。


条件が違うのでプログラムはそのまま使えませんが、似た質問。

エクセルで、「袋詰め問題」を解きたい - Excel（エクセル） 解決済 | 教えて!goo
https://oshiete.goo.ne.jp/qa/1255891.html

この回答へのお礼

例では14個の数値を示しましたが、その時によって個数は変動します。
だいたい20個前後。

それを30000とか、一定の数値以上で最小のもの、との条件に当てはまる計算式を作れないかな、と安易に考えていました。

ナップザック問題というんですね。
ちょっと勉強してみます。

お礼日時：2017/06/12 13:32

No.2 回答者： 銀鱗 回答日時： 2017/06/12 09:53

ちょっと突き放しすぎるのも気が引けるのでアドバイス。

選択するセルの数が最大になる場合と最小になる場合を考慮する必要がある。
1
2
3
4
5
6
7
8
9
の、合計が15になる組み合わせを考えるとき、
１，２，３，４，５で15
６，９で15
７，８で15
８，９で15
のような組み合わせができる。
（ほかにもあるけどね）

こういった点も考慮する必要があるので、演算がめんどくさいのです。

この回答へのお礼

ありがとうございます。
関数でもマクロでも、思っていることが実現できれば有り難いと思ったのですが、難しいようですね。

お礼日時：2017/06/12 11:08

No.1 回答者： 銀鱗 回答日時： 2017/06/12 09:49

ない。

めんどくさい演算を延々と繰り返す必要があるので、マクロ（VBA）のほうが向いている。