|
不知道是不是因为极值的问题.
我用VBA列出所有的可能值,规划求解好像是有点问题。
- 'http://club.excelhome.net/forum.php?mod=viewthread&tid=1466255
- '甲->a,乙->b
- '部门一->A,部门二->B
- Sub Combine_Max()
- Dim a As Integer, b As Integer
- Dim aMG As Integer, bMG As Integer
- Dim A_a As Integer, A_b As Integer, B_a As Integer, B_b As Integer
- Dim FxCost As Integer
- Dim GP As Integer
- Dim A_Vol As Integer
- Dim B_Vol As Integer
- Dim A_Limit As Integer
- Dim B_Limit As Integer
- Dim m As Integer
- Dim status As String
- Dim Arr(1 To 3)
- m = 1
- a = 1
- b = 1
- FxCost = 2000
- MaxGP = 0
- aMG = 80
- bMG = 70
- A_a = 4: A_b = 3
- B_a = 6: B_b = 5
- A_Limit = 500
- B_Limit = 750
- With Sheets("List")
- For a = 1 To 80 '循环甲
- For b = 1 To 100 '循环乙
- m = m + 1 '行号
- GP = a * aMG + b * bMG - FxCost '利润
- A_Vol = a * A_a + b * A_b 'A车间产量
- B_Vol = a * B_a + b * B_b 'A车间产量
- '检查量是否超过产能
- If A_Vol <= A_Limit And B_Vol <= B_Limit Then
- status = "OK"
- If GP > MaxGP Then '最大利润值相关数据
- MaxGP = GP
- Arr(1) = a
- Arr(2) = b
- Arr(3) = GP
- End If
- Else
- status = "NOK"
- End If
- '数据输出至EXCEL
- .Cells(m, 1) = a
- .Cells(m, 2) = b
- .Cells(m, 3) = FxCost
- .Cells(m, 4) = GP
- .Cells(m, 5) = A_Vol
- .Cells(m, 6) = B_Vol
- .Cells(m, 7) = status
- Next b
- Next a
- End With
- For i = 1 To 3
- Debug.Print Arr(i)
- Next i
- End Sub
复制代码 |
|