|
- Sub test()
- Dim rng As Range
- Dim A(), B()
- Dim n%, i%, j%, x%, y%, s%, p%, MaxValue%, MaxIndex%
- '1)初值
- Sheets("sheet1").Select
- Set rng = Range("A1").CurrentRegion
- rng.Sort Key1:=Range("c1"), Order1:=xlDescending, Header:=xlYes
- A = rng.Value
- n = 160
- '2)拆分人数>n 的记录
- For i = 1 To UBound(A)
- If i <> 1 And A(i, 3) > n Then
- x = A(i, 3) \ n
- y = A(i, 3) Mod n
- '商
- For j = 1 To x
- s = s + 1
- ReDim Preserve B(1 To 3, 1 To s)
- B(1, s) = A(i, 1) & "|" & j
- B(2, s) = A(i, 2) & "|" & j
- B(3, s) = n
- Next j
- '余数
- If y <> 0 Then
- s = s + 1
- ReDim Preserve B(1 To 3, 1 To s)
- B(1, s) = A(i, 1) & "|" & j
- B(2, s) = A(i, 2) & "|" & j
- B(3, s) = y
- End If
- Else
- s = s + 1
- ReDim Preserve B(1 To 3, 1 To s)
- For j = 1 To UBound(A, 2)
- B(j, s) = A(i, j)
- Next j
- End If
- Next i
- B = Application.Transpose(B)
- '3)重建数据源
- Sheets("sheet2").Select
- Range("a:d").Clear
- [a1].Resize(UBound(B), UBound(B, 2)) = B
- Set rng = Range("a1:d" & UBound(B))
- Erase B
- rng.Sort Key1:=Range("c1"), Order1:=xlDescending, Header:=xlYes
- A = rng.Value
- '4)分批
- s = 0
- p = 0
- For i = 2 To UBound(A) - 1
- '1) 累计本次
- p = p + 1
- s = s + A(i, 3)
- '2) 预测下次
- Select Case s + A(i + 1, 3)
- Case Is < n
- '累计大值
- p = p - 1
- Case Is = n
- '开始清算
- s = 0
- Case Is > n
- '开始清算
- '2)是否精确合适,不精确的话,凑一个最适合的小值
- If s = n Then
- A(i, 4) = p
- s = 0
- Else
- y = n - s
- For j = i + 1 To UBound(A)
- If A(j, 4) = "" Then
- If A(j, 3) <= y Then
- If A(j, 3) > MaxValue Then
- MaxValue = A(j, 3)
- MaxIndex = j
- End If
- End If
- End If
- Next j
- If A(MaxIndex, 4) = "" Then A(MaxIndex, 4) = p
- s = 0
- MaxValue = 0
- End If
- End Select
- If A(i, 4) = "" Then A(i, 4) = p
- Next i
- '5)输出
- A(1, 4) = "第几批"
- A(UBound(A), 4) = p '近似值
- [a1].Resize(UBound(A), UBound(A, 2)) = A
- rng.Sort Key1:=Range("d1"), Order1:=xlAscending, Header:=xlYes
-
- End Sub
复制代码
体检时间安排表2.rar
(29.56 KB, 下载次数: 6)
|
评分
-
查看全部评分
|