使用动态规划解决有关数字组合的问题

来源：互联网发布：淘宝延长发货编辑：程序博客网时间：2024/05/17 01:35

题目：在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