| |
▼まくりん さん：
以下を標準モジュールに張り付けて試してください。
考え方だけなので、入力部、出力部、関数宣言は適当です。
最適解の求め方ではありませんが、Sub test2()で出来た隙間３つ（重複部処理必要）に、どれだけ詰められるのか計算をする、と言うように階層を作って行くと、精度を上げられると思いますよ。
Sub test() '単純に（最適解ではなく）個数を求める場合
'ダンボールケースの内寸
Dim x As Double, y As Double, z As Double
'商品の箱の外寸（余裕含む）
Dim A As Double, B As Double, C As Double
'詰め込み可能な個数
Dim 個数(6) As Integer
Dim 回答 As Integer
'条件設定
x = 500
y = 400
z = 300
A = 110
B = 70
C = 30
'CASE 1
個数(1) = Int(x / A) * Int(y / B) * Int(z / C)
'CASE 2
個数(2) = Int(x / A) * Int(y / C) * Int(z / B)
'CASE 3
個数(3) = Int(x / B) * Int(y / A) * Int(z / C)
'CASE 4
個数(4) = Int(x / B) * Int(y / C) * Int(z / A)
'CASE 5
個数(5) = Int(x / C) * Int(y / A) * Int(z / B)
'CASE 6
個数(6) = Int(x / C) * Int(y / B) * Int(z / A)
'計算結果比較
For i = 1 To 6
If 回答 < 個数(i) Then 回答 = 個数(i)
Cells(i, 1).Value = 個数(i)
Next i
Cells(8, 1).Value = 回答
'容積−体積比較による個数
Cells(9, 1).Value = Int(x * y * z / (A * B * C))
End Sub
Sub test2() '箱詰め状況確認用の場合
'ダンボールケースの内寸
Dim x As Double, y As Double, z As Double
'商品の箱の外寸（余裕含む）
Dim A As Double, B As Double, C As Double
'詰め込み可能な個数
Dim 個数(6) As Integer
Dim 回答 As Integer
'条件設定
x = 500
y = 400
z = 300
A = 110
B = 70
C = 30
'CASE 1
x_tmp = Int(x / A): y_tmp = Int(y / B): z_tmp = Int(z / C)
個数(1) = x_tmp * y_tmp * z_tmp
Call output(1, x, y, z, x_tmp, y_tmp, z_tmp, 個数(1), A, B, C)
'CASE 2
x_tmp = Int(x / A): y_tmp = Int(y / C): z_tmp = Int(z / B)
個数(2) = x_tmp * y_tmp * z_tmp
Call output(2, x, y, z, x_tmp, y_tmp, z_tmp, 個数(2), A, C, B)
'CASE 3
x_tmp = Int(x / B): y_tmp = Int(y / A): z_tmp = Int(z / C)
個数(3) = x_tmp * y_tmp * z_tmp
Call output(3, x, y, z, x_tmp, y_tmp, z_tmp, 個数(3), B, A, C)
'CASE 4
x_tmp = Int(x / B): y_tmp = Int(y / C): z_tmp = Int(z / A)
個数(4) = x_tmp * y_tmp * z_tmp
Call output(4, x, y, z, x_tmp, y_tmp, z_tmp, 個数(4), B, C, A)
'CASE 5
x_tmp = Int(x / C): y_tmp = Int(y / A): z_tmp = Int(z / B)
個数(5) = x_tmp * y_tmp * z_tmp
Call output(5, x, y, z, x_tmp, y_tmp, z_tmp, 個数(5), C, A, B)
'CASE 6
x_tmp = Int(x / C): y_tmp = Int(y / B): z_tmp = Int(z / A)
個数(6) = x_tmp * y_tmp * z_tmp
Call output(6, x, y, z, x_tmp, y_tmp, z_tmp, 個数(6), C, B, A)
'計算結果比較
For i = 1 To 6
If 回答 < 個数(i) Then 回答 = 個数(i)
Next i
Cells(1, 1).Value = x
Cells(1, 2).Value = y
Cells(1, 3).Value = z
Cells(2, 1).Value = A
Cells(2, 2).Value = B
Cells(2, 3).Value = C
Cells(10, 1).Value = 回答
'容積−体積比較による個数
Cells(11, 1).Value = Int(x * y * z / (A * B * C))
End Sub
Sub output(i, x, y, z, x_tmp, y_tmp, z_tmp, 個数x, t1, t2, t3)
'x,y,z方向に詰められる個数
Cells(i + 2, 1).Value = x_tmp
Cells(i + 2, 2).Value = y_tmp
Cells(i + 2, 3).Value = z_tmp
'箱内に詰められる個数
Cells(i + 2, 4).Value = 個数x
'x,y,z方向それぞれの隙間
Cells(i + 2, 6).Value = x - x_tmp * t1
Cells(i + 2, 7).Value = y - y_tmp * t2
Cells(i + 2, 8).Value = z - z_tmp * t3
End Sub
|
|
わいわい
- 07/7/31(火) 7:12 -
