こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

Excelで数値が自動的に入力されるようにしたい

この質問は4カ月ほど前に投稿させていただいたものと同じ内容で、当時解決につながる様なアイデアをいただけなかったのですが、また必要が出てきたため再び投稿させていただくものです。

様々な値段の様々な商品があり、今後も増えていきます。
これらの商品を袋詰めしていくのですが、1つの袋に商品の値段の合計が1万円以上になるように詰めていきます。かつ、使う袋の数は多ければ多いほど良いです。
例えば、全商品の値段の合計が4万5千円だとして、その内訳が1千円・2千円・3千円・・・・9千円の(たまたま千円の倍数の)9個だとしたら、ちょうど1万円の袋が4つ出来、5千円の商品が余ります。その5千円の商品は適当にどれかの袋に加えるとして、とにかく1万円以上の袋が最大で4つ作れるということになります。逆に、3つの袋に分けるような詰め方は、上記の「使う袋の数は多ければ多いほど良い」に反するため、不適当です。

このような条件を満たすような商品の各袋ごとへの分け方をExcelで求めたいです。

A列に商品名、B列に値段を入力します。
C列に袋の番号(ダミー)が自動入力されるようにしたいのです。
上記の例だと
B1セルに1000と入力すればC1セルに1(または0または表示なし)と、
B2セルに2000と入力すればC2セルに1(または0または表示なし)と、
・・・
B4セルに4000と入力すればC1~C4セルに1と、
・・・
B6セルに6000と入力したときに、例えば、C1=C3=C6=1、C2=C4=C5=2、というように、自動的に変更されるようにしたいです。なお、C列のセルに同じ数値が入る組み合わせは複数パターンがあると思いますが、1パターンだけ出れば大丈夫です。
逆に、B6セルに6000と入力してもC列のセルが全て1が自動入力されてしまったら、これは「2つの袋にどのように商品を詰め分けても、必ず1万円未満の袋が出来てしまう」ことを意味するので、不適当となります。

Excelのどのような機能を使えば、以上のようなことができるでしょうか。
宜しくお願いいたします。

投稿日時 - 2014-12-07 14:32:04

QNo.8850139

困ってます

このQ&Aは役に立ちましたか?

7人が「このQ&Aが役に立った」と投票しています

回答(12)

ANo.12

補足ありがとうございます。

手作業で90秒ならソルバー利用するより早いですし、ソルバー案の場合でやっても4袋しか出なかったので「先に10000の組み合わせをすべて探す」という案は撤回です。

> 8800+1300=10100
> 6400+3800=10200
> 5800+4300=10100
> 5300+4900=10200
> 3000+2800+2200+1700+700=10400

これが思考した順だとしたら回答No7の手法と同じですよね。私はNo7の手法は駄目なんだろうと思ってその手法以外であれこれやってました。意外とNo7でいけるんじゃないかなと思いかけたりしてますが、それを証明する手段がありません。

とりあえず、手法を考えても実証ができなかったり、反例に時間を費やしたりになってしまいますが、いかがなものでしょう。

前回質問の時の回答でも書きましたが、問題そのものはエクセルというより数学の問題になると思いますので、数学者の意見を聞いた方が手法が意外と簡単に見つかるかもしれません。それは○○○○法で解けるとかあるかもしれませんし、最適解求めるととんでもなく時間がかかるので近似解でという助言があるかもしれません。

どちらにしても、すでに実証されている手法がわかればエクセルのマクロで実装し稼働することができます。

投稿日時 - 2014-12-10 00:53:00

補足

まあ、そもそもNo.2の案の検証として作ったケースですからね。

この回答を読ませていただいた限りでは、恐らく、一番高い商品から順に足していって最後に安めの商品を1つ追加することで帳尻を合わせる、という方法しか無いのかもしれませんね。私は前回手計算でやっていますが、高い安いは関係なく単に補数で決めていましたから。

ただ、計算方法が分からないからExcelに落とし込みようがない、というのは納得しました。数学カテゴリに移してみようと思います。
この質問は一旦未解決終了とします。

投稿日時 - 2014-12-10 23:24:43

ANo.11

No10の補足

ソルバーの時のデータはB2からB14までを想定してます。

以下のデータをコピペでどうぞ。

700
1300
1700
2200
2800
3000
3800
4300
4900
5300
5800
6400
8800

投稿日時 - 2014-12-09 21:53:30

ANo.10

> やはり、M+X>10000だがM+A+B=10000というケースが出ています。このせいで、袋を1つ少なくする判断になってしまっています

途中でしたら、それから先にZ+A=10000が出てくる可能性もありますよね。最後に出たらまた元の戻ってやり直しということになりますが、どこまで戻るのか戻り先がわかりません。また、M+A+B・・・が10000になる組み合わせを都度確認して先に進むという方法も考えられますが、なんか結局総当たりになりそうです。

で、先に10000の組み合わせをすべて探して排除(この時点で残りのデータではどのような組合わせでも10000にならないことは保障されます)してからNo8の方法というのを考えてみましたが…

ソルバーを利用するのですが、スピード的に…
とりあえず手動でやってみて1回作業したときのスピードを確認してみてください。

データはB2セルから下方向に入れてください。C列は0にしておくとソルバーの設定の途中でエラーメッセージが出ないのでいいと思います。

D1セルに
=SUMPRODUCT(B2:B14,C2:C14)
という数式を入れ
ソルバーで目的セルをD1
目標値を指定値で10000
変化セルの変更C1:C14
制約条件の対象を C1:C14=バイナリ (セル対象の右ボックスの三角を押してbinを選ぶと左記になります)
で解決するとC列に1とでたデータの和が10000になっているので該当セルのデータをどこかにコピーして削除
あとは見つからなくなるまでソルバーを続けます。


ところでNo4のデータ

700
1300
1700
2200
2800
3000
3800
4300
4900
5300
5800
6400
8800

これで5袋になる組み合わせはどのような組み合わせでしょうか。ちょっと手動でやってみたけど頭が痛くなって途中でやめました。

投稿日時 - 2014-12-09 19:32:44

補足

今一度、エクセルに書きこみながら暗算でやってみたところ、90秒掛かりました。あくまでこのケースのことなので、実際には値段は1円単位ですし、商品2個で1万円に達することは(恐らく)ありませんし、それに大抵最後の袋が1万円未満になるのでそこから血の入れ替え作業が始まるわけですが、今回はそれがありませんでした。手作業での時間としては参考にならない気がします。

8800+1300=10100
6400+3800=10200
5800+4300=10100
5300+4900=10200
3000+2800+2200+1700+700=10400

投稿日時 - 2014-12-09 23:13:19

ANo.9

訂正です

3) M+Y<10000ならM+YをMにして最初に戻る または M+Y>10000かつY<Xの場合ならそのまま採用

投稿日時 - 2014-12-08 18:52:31

ANo.8

> 高めの商品1個を安めの商品2個に置き換えた方が後々有利

後々がその時点でわからないのが難題ですが、例えば

10000-最大値(仮にMとします)を求めて、データの中からその値と同じかその値に最も近くかつ大きな値(仮にXとします)
1) M+X=10000ならそのまま採用
2) M+X>10000ならM+最小値(仮にYとします)を考える
3) M+Y<10000ならM+YをMにして最初に戻る M+Y>10000ならそのまま採用

のようにしておけばどうでしょう。

Xが5000の場合、最小値の並びが

1000
2000
3000
4000

のデータなら1000と4000をとることになり、後々2000と3000をとったほうが良かったというのはその時点ではわからないので仕方がないことだと思われます。

投稿日時 - 2014-12-08 18:48:01

補足

うーむ、仕方ないとなると・・・どうすればいいでしょうか・・・

やはり、M+X>10000だがM+A+B=10000というケースが出ています。このせいで、袋を1つ少なくする判断になってしまっています。

投稿日時 - 2014-12-08 22:23:31

ANo.7

小さい順に並べておいて最大値から順に10000-最大値を求めて、データの中からその値と同じかその値に最も近くかつ大きな値を最大値と加算したものを一袋にする。
データのなかになければ最大値の次(上)の値と加算し10000以上になれば次の利用されなかった上のデータを最大値として上記を繰り返す。
加算しても10000以上にならなかったら加算した値を最大値と考えて上記を繰り返す。

というのは如何でしょう

投稿日時 - 2014-12-08 13:42:29

補足

反例を出せていませんが、その方法は「高めの商品1個を安めの商品2個に置き換えた方が後々有利」という場合にうまくいかないと思いました。後で検証します。

投稿日時 - 2014-12-08 15:48:04

ANo.6

回答1のアルゴリズムに追加して、
・1→2→3→4の1回が終了した時点で、1万円を超える袋があったら、次回(逆順)以降ではその袋には商品を入れない
・何回目かで全袋の金額が1万円を超え、まだ商品が残ってたら、個数の少ない袋順に入れる
・全部分配終了した後、1万円を割る袋があったら、袋数を1つ減らして再計算

どうやって自動化するか分りませんが。シミュレーション結果
金額 袋 袋の金額 商品数
8800  1 14600 3
6400  2 12400 3
5800   3 11800 3
5300  4 12200 4
4900   4 12200 4
4300   3 11800 3
3800   2 12400 3
3000   1 14600 3
2800   1 14600 3
2200   2 12400 3
1700   3 11800 3
1300   4 12200 4
700   4 12200 4

投稿日時 - 2014-12-08 01:20:34

補足

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

お書きいただいたアルゴリズムでは、袋の番号1→2→3→4→5でシミュレートしたときに成立しませんね。
この分配方法だけで1万円未満の袋が出来たら袋の数を減らしてしまうことに問題があるようです。

投稿日時 - 2014-12-08 10:10:18

ANo.5

すみませんダメなパターンがありました。

1000
2000
3000
4000
5000
6000

の場合、私の考えではだめですね。

投稿日時 - 2014-12-07 23:54:53

ANo.4

> マクロではなく、お書きいただいた解説をもとに適当に作ったデータでシミュレートしてみたのですが、不具合があるようです。確認していただけないでしょうか。


実行結果です、C列の数値が同じものが同一の袋となりますので4袋必要になります。

B列  C列  各袋の合計値
700  1  10800
1300 1  10800
1700 2  10300
2200 2  10300
2800 3  11600
3000 3  11600
3800 4  18300
4300 4  18300
4900 4  18300
5300 4  18300
5800 3  11600
6400 2  10300
8800 1  10800


合計10800の袋1袋
合計10300の袋1袋
合計11600の袋1袋
合計18300の袋1袋
全合計=51000 袋数4袋

いかがでしょうか。

投稿日時 - 2014-12-07 23:02:44

補足

No.4=No.5へのお返事です。

もう気づかれていると思いますが、No.2=No.3の方法で1袋少ない結果が出てしまった、ということです。

なかなかうまくいかないですね。
この質問はもう少し続けようと思います。

投稿日時 - 2014-12-08 00:03:11

ANo.3

No2補足です。

データは1行目からと考えてますので2行目からでしたら

j = 1

For i = BRow To 1 Step -1



j = 2

For i = BRow To 2 Step -1

に変更してください。

投稿日時 - 2014-12-07 21:46:16

ANo.2

確か前回は証明が必要とのことでしたが、正しいことの証明はできませんが、たぶん以下のマクロでいいのではないかと思います。

最下行になる最大値と最上行の最小値を足して1000以上になるまで最小値側を下向かってに足していき10000以上になったところで配番し、最下行から順に上に向かって同じ動作をC列に空白がなくなるまで続けてます。

値段を小さい順に並べかえてからマクロの実行が必要になります。

B列に入力した途端に並び替えと実行をすることができますが、実際のデータ範囲がわからないので割愛します。


結果例1
B列  C列
200  1
300  2
4000  2
5000  3
6000  4
7000  4
8000  3
9000  2
10000 1

結果例2
B列   C列
1000  1
2000  2
4000  3
5000  4
6000  4
7000  4
8000  3
9000  2
10000  1



Sub Example()
Dim i As Long, j As Long, k As Long, BRow As Long
Dim Total As Integer, BagNo As Integer

Range("C:C").ClearContents
BRow = Range("B" & Rows.Count).End(xlUp).Row
Total = 0
BagNo = 1
j = 1

For i = BRow To 1 Step -1
Total = Total + Range("B" & i).Value
If Total > 10000 Then
If Range("C" & i).Value <> "" Then
Exit For
ElseIf Range("B" & i).Value > 1000 Then
Range("C" & i).Value = BagNo
Total = 0
BagNo = BagNo + 1
End If
Else
Call Under(Total, BagNo, j, i)
End If
Next
End Sub

Sub Under(ByRef Total As Integer, ByRef BagNo As Integer, ByRef j As Long, ByRef i As Long)
Total = Total + Range("B" & j).Value
If Range("C" & j).Value = "" Then
If i = j Then
Range("C" & j).Value = BagNo - 1
Else
Range("C" & j).Value = BagNo
Range("C" & i).Value = BagNo
If Total > 10000 Then
Total = 0
BagNo = BagNo + 1
j = j + 1
Else
j = j + 1
Call Under(Total, BagNo, j, i)
End If
End If
End If
End Sub

投稿日時 - 2014-12-07 21:01:54

補足

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

マクロではなく、お書きいただいた解説をもとに適当に作ったデータでシミュレートしてみたのですが、不具合があるようです。確認していただけないでしょうか。

700/1300/1700/2200/2800/3000/3800/4300/4900/5300/5800/6400/8800
データ数=13、合計=51000

投稿日時 - 2014-12-07 22:24:39

ANo.1

記載されたパターンしか存在しないのか
条件が不明確なので何とも言えませんが、

もしやるのであれば、
step1 個々の商品を値段で降順にソート
step2 合計金額を求める
step3 切り捨てで一万円単位にする 袋数の確定
step4 求められた袋数で割当 1,2,3,4,4,3,2,1,1,2,3,4 というような順番
という手順では?

投稿日時 - 2014-12-07 16:25:33

補足

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

お示しいただいた方法だと、例えば8千円の商品5個という場合、合計4万円なので袋数が4つという計算になりますが、当然ながら4つの袋に分ける方法がありません。

投稿日時 - 2014-12-07 22:16:45

あなたにオススメの質問