トラックへの荷物の積みかたを算出したいのですが。。

質問

エクセルの２００７を使っております
現在積み荷のバランスを数値から算出したく
試行錯誤しております、良ければアドバイスお願いします。
E F G H I J K ...
1 2 3 4 5 6 7
2750 2980 3650 3980 4600 5980
2750 3650 3980 4600 5980
2750 3650
3650
3650
このように荷物ごとの長さがございます
上記ですと２７５０Ｘ３　２９８０Ｘ１　といった形です
ポイントが何個かありましてそれに対する並び替えを
おこないたいのです
１、左右対称が望ましい（１対ずつ）
２、長さは１２０００まで
３、３段目まで

右側
3 2800 3650 3980
2 2750 2980 5980
1 3650 3650 4600
左側
3 2800 2800 3980
2 2750 2750 5980
1 3650 3650 4600

このような形になるのが望ましいのですが、
完璧とはいかないまでも近い形になればと思っております
よろしくお願いします。

回答No.3

okormazd · ベストアンサー

Solverを使えば完全ではなくても実用的なものができると思います。
異なる長さのものを組み合わせて、12000になればいいので、そのようにVBAでプログラムします。
結果を見て、左右対称を考えるか、はじめに材料の数を２分して計算すればいいでしょう。
A列に寸法、B列に個数を入れて、下記プログラムを実行すれば、C列以下に結果が出ます。
それを見て気に入るように積めばいいでしょう。

Sub solver3()

Dim r1 As Integer, r2 As Integer, c As Integer, n As Integer, lmax As Single
Dim rm As Integer, nrm As Integer, total As Single

r1 = 2
r2 = Cells(r1, 1).End(xlDown).Row

c = 3
n = 0
lmax = 12000

Range(Cells(r1, c - 2), Cells(r2, c - 1)).Sort Key1:=Cells(r1, c - 2), Order1:=xlDescending, _
Key2:=Cells(r1, c - 1), Order2:=xlDescending

Range(Cells(r1, c + 2), Cells(r2 + 1, 256)).ClearContents
Cells(r2 + 1, c - 1).FormulaR1C1 = "=SUM(R[" & -(r2 - 1) & "]C:R[-1]C)"
total = Cells(r2 + 1, c - 1)
SolverReset

While total > 0

Range(Cells(r1, c + 1), Cells(r2, c + 1)).FormulaR1C1 = "=RC1*RC[-1]"
Range(Cells(r1, c), Cells(r2, c)) = 1
Range(Cells(r2 + 1, c - 1), Cells(r2 + 1, c + 1)).FormulaR1C1 = "=SUM(R[" & -(r2 - 1) & "]C:R[-1]C)"

rm = r1
nrm = Cells(rm, c - 1)
While nrm = 0
rm = rm + 1
nrm = Cells(rm, c - 1)
Wend
SolverAdd CellRef:=Cells(rm, c), Relation:=3, FormulaText:="1"

SolverOk SetCell:=Cells(r2 + 1, c + 1), MaxMinVal:=1, ValueOf:=lmax, ByChange:=Range(Cells(r1, c), Cells(r2, c))
SolverAdd CellRef:=Range(Cells(r1, c), Cells(r2, c)), Relation:=1, FormulaText:=Range(Cells(r1, c - 1), Cells(r2, c - 1))
SolverAdd CellRef:=Range(Cells(r1, c), Cells(r2, c)), Relation:=3, FormulaText:="0"
SolverAdd CellRef:=Range(Cells(r1, c), Cells(r2, c)), Relation:=4, FormulaText:="整数"
SolverAdd CellRef:=Cells(r2 + 1, c + 1), Relation:=1, FormulaText:=Format(lmax)

SolverSolve userfinish:=True

Range(Cells(r2 + 1, c - 1), Cells(r2 + 1, c + 1)).Copy
c = c + 3
Cells(r2 + 1, c - 1).PasteSpecial
Range(Cells(r1, c - 1), Cells(r2, c - 1)).FormulaR1C1 = "=RC[-3]-RC[-2]"

total = Cells(r2 + 1, c - 1)

SolverReset

n = n + 1

Columns(n * 3 + 2).EntireColumn.Hidden = True

Wend

Cells(r2 + 1, c) = n

End Sub

お礼

ありがとうございます！！　
ＶＢＡは自動記録のコードを触るぐらいしかしたことがないのですが、
がんばってみます、
残念ながらコンパイルエラーが表示されてしまい
SolverReset　　　SUBまたは　fanctinon が定義されてません。。
という感じででるのです、
少し自分で調べてみます
ありがとうございます。

回答No.5

supermab

拙作のSeek（フリー）で解くと、

9,700 ～ 2本 　5,980 x 1 　3,650 x 1 　残＝70
9,700 ～ 2本 　4,600 x 1 　3,980 x 1 　残＝1,120
9,700 ～ 1本 　3,650 x 1 　2,750 x 2 　残＝550
9,700 ～ 1本 　3,650 x 1 　2,980 x 1 　2,750 x 1 　残＝320

となりますので、
同じ組み合わせのもの（２本のもの）を左右に振り分けて、
残が550の組合せを右、残が320の組合せを左に振り分けると
左右のアンバランスは　550-320＝230　となり、
１％以下に収まりました。

９７００で収まりますので、トレーラーでなく１０ｔでも
運べそうです (^^;

残が小さい組み合わせを下に乗せると、さらに安定しますね。
ご安全に！

参考URL：

http://supermab.com

お礼

ありがとうございます。
ぜひ活用させていただきます＾＾

回答No.4

okormazd

書き忘れましたが、Solverを使うには、VBの編集画面の、
ツール―参照設定
で、
SOLVERにチェックを入れてください。
そうすれば、
>残念ながらコンパイルエラーが表示されてしまい
>SolverReset　　　SUBまたは　fanctinon が定義されてません。
は、
出ないと思います。

回答No.2

Tasuke22

少なくともExcelで計算するのは諦めてください。

この問題は「計画問題」という分野になり、超
難解問題の1つに入ります。もっとも積荷の数に
難解さは決定されますが。

具体的な数値が固定であれば、人間が時間をかけ
て計算するのが一番でしょう。まあ、そんなこと
は無いでしょうが。

Lispでプログラムを作るのが一番早く実現できる
と思います。
C++やPHPでも出来るでしょう。

ただ、この手の問題を理解できる人はプログラマ
でも限られます。私の経験上、100人に1人いるか
どうか。この問題を抱えている人でも、実業務を
していない人は、まず理解できません。

一見単純に思えて泥沼化して、何も進まないうちに
予算を食いつぶして泣きつく、が一般的なパターン
です。

お礼

やはりエクセルで難しいのですね、
特定の条件の振り分け位に限定しての作業をやってみたいと思います。
回答ありがとうございます

回答No.1

precog

とりあえず計算式 (振り分けロジック) が良くわからないのと、どこまで出来て何に困っているのか書いていただかないと、あなたの代わりに仕事することにしかならないと思うのですけど。

例えば、、
「対称」とは何が対称なのか?
12000までの制限とは、何の制限なのか?
左端の3...2...1は何を表してるのか?

補足

ごもっともでございます。
現場の状態が解らないかぎり答えようがありませんでしたね
現状の問題に特化してご質問させていただくことにします、
左端の３２１は高さを表しまして
１段目２段目３段目となっております
１２０００までの制限とは、
縦につなぐ際１２０００までの制限となっているとゆうことです
１段目最大　３０００＋３０００＋３０００＋３０００
対象とは左と右部分で分かれてますので、同じ長さのものを左、右に振り分けるという意味でございました。
なかなか伝わりにくいかとおもいますが、お願いします、
とりあえずしたいことをいいます
まず自動でコピー＆ペーストを行うことは可能でしょうか？
A1にある数値を任意の場所に移動
例　A1にある数値をクリック、、そのつぎクリックするセルにその値を
移動（コピーでない）
以上お願いします