题目:在SHEET2中列出SHEET1表中重量不超过170,体积不超过200的所有组合(http://club.excelhome.net/viewthread.php?tid=382466&page=1#pid2435030)
Sheet1
序号 | 重量 | 体积 |
1 | 25 | 30 |
2 | 26 | 31 |
3 | 27 | 32 |
4 | 28 | 33 |
5 | 29 | 34 |
6 | 30 | 35 |
7 | 31 | 36 |
8 | 32 | 37 |
9 | 33 | 38 |
10 | 34 | 39 |
11 | 35 | 40 |
12 | 36 | 41 |
13 | 37 | 42 |
14 | 38 | 43 |
15 | 39 | 44 |
16 | 40 | 45 |
17 | 41 | 46 |
18 | 42 | 47 |
19 | 43 | 48 |
20 | 44 | 49 |
21 | 45 | 50 |
22 | 46 | 51 |
23 | 47 | 52 |
24 | 48 | 53 |
25 | 49 | 54 |
26 | 50 | 55 |
27 | 51 | 56 |
28 | 52 | 57 |
29 | 53 | 58 |
30 | 54 | 59 |
31 | 55 | 60 |
32 | 56 | 61 |
33 | 57 | 62 |
34 | 58 | 63 |
35 | 59 | 64 |
36 | 60 | 65 |
37 | 61 | 66 |
38 | 62 | 67 |
39 | 63 | 68 |
40 | 64 | 69 |
方法:动态规划,代码如下(可惜我的机子内存太小,无法定义更大的数组空间有效的完成此任务):
Sub getit()
Dim s() As String, i&, j&, k&, l&, n&, t, v, w, temp$, sum1&, sum2&
t = Sheet1.[a2:c41]
sum1 = 170
sum2 = 200
ReDim s(UBound(t), sum1, sum2)
For i = 1 To UBound(t)
If t(i, 2) <= sum1 And t(i, 3) <= sum2 Then s(1, t(i, 2), t(i, 3)) = t(i, 1)
Next
For j = 2 To UBound(t)
For k = 1 To sum1
For l = 1 To sum2
If s(j - 1, k, l) > "" Then
v = Split(s(j - 1, k, l))
For m = 0 To UBound(v)
If Not v(m) Like "*" & UBound(t) Then
w = Split(v(m), ",")
For i = Val(w(UBound(w))) + 1 To UBound(t)
If k + t(i, 2) <= sum1 And l + t(i, 3) <= sum2 Then s(j, k + t(i, 2), l + t(i, 3)) = Trim(s(j, k + t(i, 2), l + t(i, 3)) & " " & v(m) & "," & t(i, 1))
Next
End If
Next
End If
Next
Next
Next
ReDim v(65535, 1 To 3)
v(0, 1) = "序号"
v(0, 2) = "重量"
v(0, 3) = "体积"
For k = 1 To sum1
For l = 1 To sum2
For j = 1 To UBound(t)
If s(j, k, l) > "" Then
w = Split(s(j, k, l))
For m = 0 To UBound(w)
n = n + 1
v(n, 1) = w(m)
v(n, 2) = k
v(n, 3) = l
Next
End If
Next j, l, k
Sheet2.[a1].Resize(n, 3) = v
End Sub
运行结果(返回54414组解):
序号 | 重量 | 体积 |
1 | 25 | 30 |
2 | 26 | 31 |
3 | 27 | 32 |
4 | 28 | 33 |
5 | 29 | 34 |
6 | 30 | 35 |
7 | 31 | 36 |
8 | 32 | 37 |
9 | 33 | 38 |
10 | 34 | 39 |
11 | 35 | 40 |
12 | 36 | 41 |
13 | 37 | 42 |
14 | 38 | 43 |
15 | 39 | 44 |
16 | 40 | 45 |
17 | 41 | 46 |
18 | 42 | 47 |
19 | 43 | 48 |
20 | 44 | 49 |
21 | 45 | 50 |
22 | 46 | 51 |
23 | 47 | 52 |
24 | 48 | 53 |
25 | 49 | 54 |
26 | 50 | 55 |
27 | 51 | 56 |
1,2 | 51 | 61 |
28 | 52 | 57 |
1,3 | 52 | 62 |
29 | 53 | 58 |
1,4 | 53 | 63 |
2,3 | 53 | 63 |
30 | 54 | 59 |
1,5 | 54 | 64 |
2,4 | 54 | 64 |
31 | 55 | 60 |
1,6 | 55 | 65 |
2,5 | 55 | 65 |
3,4 | 55 | 65 |
32 | 56 | 61 |
1,7 | 56 | 66 |
2,6 | 56 | 66 |
3,5 | 56 | 66 |
33 | 57 | 62 |
1,8 | 57 | 67 |
................................
6,8,9,13,14 | 170 | 195 |
3,10,11,12,14 | 170 | 195 |
4,9,11,12,14 | 170 | 195 |
5,8,11,12,14 | 170 | 195 |
6,7,11,12,14 | 170 | 195 |
5,9,10,12,14 | 170 | 195 |
6,8,10,12,14 | 170 | 195 |
7,8,9,12,14 | 170 | 195 |
6,9,10,11,14 | 170 | 195 |
7,8,10,11,14 | 170 | 195 |
4,10,11,12,13 | 170 | 195 |
5,9,11,12,13 | 170 | 195 |
6,8,11,12,13 | 170 | 195 |
6,9,10,12,13 | 170 | 195 |
7,8,10,12,13 | 170 | 195 |
7,9,10,11,13 | 170 | 195 |
8,9,10,11,12 | 170 | 195 |
1,2,3,4,5,11 | 170 | 200 |
1,2,3,4,6,10 | 170 | 200 |
1,2,3,4,7,9 | 170 | 200 |
1,2,3,5,6,9 | 170 | 200 |
1,2,3,5,7,8 | 170 | 200 |
1,2,4,5,6,8 | 170 | 200 |