都用上Excel了,为什么不用VBA呢?- Sub allocate2() '假设数据区域从大到小排序
- Dim employee(19), m(19)
- For i = 0 To 19
- Set employee(i) = CreateObject("Scripting.Dictionary")
- Next i
- Set lost = CreateObject("Scripting.Dictionary")
- NUM = Application.InputBox("请输入客户数:", "选择运算范围", 580, , , , , 9)
- If NUM < 20 Then MsgBox "请重新选择", , "选择有误!"
- avg = Application.WorksheetFunction.Sum(Range("b1:b" & NUM)) / 20
- guest = Range("a1:b" & NUM)
- i = -1
- For n = 1 To NUM
- flag = False
- t = 1
- Do While t <= 20
- i = (i + 1) Mod 20
- If m(i) + guest(n, 2) <= avg Then
- m(i) = m(i) + guest(n, 2)
- employee(i).Add guest(n, 1), guest(n, 2)
- flag = True
- Exit Do
- End If
- t = t + 1
- Loop
- If flag = False Then
- lost.Add guest(n, 1), guest(n, 2)
- End If
- Next n
- End Sub
复制代码
|