Excel：組み合わせを上手に選ぶ方法

質問

手元のExcelデータには、Ａ列に商品名、Ｂ列に値段が入力されています。
これらの商品をいくつかのグループに分けて袋詰めするのですが、全ての袋を一定額（例えば１００００円）以上にしつつ、袋の数をなるべく多くしたいです。
そこで、Excel上でその最大の袋の数と組み合わせを瞬時に計算できないかと考えています。

アルゴリズムとしては、例えば、袋の数が「商品数－１」個のときに全ての袋を一定額以上にできるか、もし全ての商品の組み合わせについて一定額以上という条件を達成できなければ袋の数を「商品数－２」個・・・としていくことを考えましたが、とてつもなく面倒そうです。
しかも、入力される商品はまだ増えていくので、その都度簡単に計算し直せる方がいいです。

以上の条件で、Excelを活用できるようなアイデアをいただければと思います。
宜しくお願いいたします。

回答No.2

MackyNo1

>そこから先、例えば残りの商品の合計が20000以上だったとして、しかし実際は8000×３個のため、どのように２袋に分けても10000以上ずつにできなかったとしたら、当初の試行錯誤をやり直した方がいいかもしれない、ということになってしまいます。「ソルバー」というもので、そこまで考慮して計算できるでしょうか？（一発計算できないのであれば、質問時の要望に合いません。）

たとえば、値段の調整しにくい、大きい数字を必ず入れて、その条件での最適値（組み合わせ）を計算させることができますが、ソルバーは１回限りの条件ですので、残りの商品を考慮した計算はできません（おそらくご質問の要望を満たさないと思います）。

>実際のデータを書いた方が、ということですが、現時点では全商品の値段の合計（Sum B列）が10000未満なので、書いたところで意味があるのか分かりませんが、一応書いておきます。

例示のデータでは最終的にどのようにグループ分けするのか結果の具体例がないので、例示の意味がありません。
実際はこのような商品が100種類あってこれらの商品の最適の組み合わせを決めたいということでしょうか？
その場合は、それらの商品を使って１万円を超える金額で最も１万円に近い組み合わせしか見つけることができません。

お礼

申し訳ありませんが、質問内容を改めて再投稿することにしましたので、一旦未解決終了とします。

補足

再びの回答ありがとうございます。

１．「ソルバーは１回限り」とのこと、質問時の要望に合わなさそうです。せっかくアイデアをいただきましたが、申し訳ありません。
２．「最終的にどのようにグループ分けするのか結果の具体例」これは・・・文字通りであれば「それを私はExcelで求めたいのであって具体例も何もない」というお返事しかできないのですが・・・もしかしたらNo.1=No.2様のイメージされていることとずれてしまっているような気がします。別の表現で書いていただくことはできますか？
３．実際の（最終的な）商品の個数は分かりませんし、分かった時点でどのように袋分けすれば全ての袋を10000円以上にできるか求められていなければいけません。なので、質問に書かせていただいたように、商品を入力するたびに再計算できるものが都合よいです。

回答No.1

MackyNo1

質問内容がいまひとつ不明です。

＞手元のExcelデータには、Ａ列に商品名、Ｂ列に値段が入力されています。

確認ですが、商品名と値段だけのリストですね。

＞これらの商品をいくつかのグループに分けて袋詰めするのですが、全ての袋を一定額（例えば１００００円）以上にしつつ、袋の数をなるべく多くしたいです。

「いくつかのグループに分け」とはどういう意味なのでしょうか？

たとえば商品の中から、どれかを１つずつ選択して合計が10000以上で10000に最も近くなるような組み合わせを試行錯誤でExcelに計算させるならソルバーの機能を利用して求めることができます。

また「すべての袋」とは上記の１つ目で使用した組み合わせを除いた商品の組み合わせの袋という意味でしょうか？

基本的にソルバーを利用する場合は、計算式で表せるような条件がないと実行できませんので、それらのデータを除外したデータで再度ソルバーを行うことはできると思いますが、一度に簡単に一括して組み合わせを求めることはできないと思います。

いずれにしろ、このようなご質問では、実際のデータと組み合わせ結果をもう少し具体的に例示したほうが、的確な回答が得られやすいと思います。

補足

回答ありがとうございます。しかし、私もどのようにお返事すればよいのか困っています。

１．商品名と値段だけのリストです。
２．「いくつかのグループに分け」に大意はありません。数多くある商品を数個ずつ（のグループに分けて）袋詰めする、という意味で、Excelを離れた実際的なことも書いておいた方が分かりやすいかと思い書いておきました。
３．「どれかを１つずつ選択して合計が10000以上で10000に最も近くなるような組み合わせを試行錯誤でExcelに計算させるならソルバーの機能を利用して求めることができます」とのことですが、そこから先、例えば残りの商品の合計が20000以上だったとして、しかし実際は8000×３個のため、どのように２袋に分けても10000以上ずつにできなかったとしたら、当初の試行錯誤をやり直した方がいいかもしれない、ということになってしまいます。「ソルバー」というもので、そこまで考慮して計算できるでしょうか？（一発計算できないのであれば、質問時の要望に合いません。）
４．「また「すべての袋」とは上記の１つ目で使用した組み合わせを除いた商品の組み合わせの袋という意味でしょうか？」この質問は、多分、意味が無いです。回答者様のお考えでは、最初の試行錯誤で完成させた袋を１番目の袋、２番目の袋を２番目の袋・・・と番号付で区別されているのだと思いましたが、実際そういう区別は不要です。
５．で、実際のデータを書いた方が、ということですが、現時点では全商品の値段の合計（Sum B列）が10000未満なので、書いたところで意味があるのか分かりませんが、一応書いておきます。
商品Ａ　 500
商品Ｂ　 961
商品Ｃ　 850
商品Ｄ　 330
商品Ｅ　1416

以上ですが、いかがでしょうか。