|
本帖最后由 爱疯 于 2013-4-13 12:10 编辑
- Sub test()
- Dim d As Object, A(), B(), C(), x, i&, j&, k%, s%, p%, q%
- '指定每波人数
- x = Application.InputBox("凑够多少人,就过马路:", "输入", 10, , , , , 1)
- If x = 0 Then End
- '初值
- With Sheets("sheet1")
- .Range("C:C").Clear
- A = .Range("A1:C" & .Range("A65536").End(xlUp).Row).Value
- A(1, 2) = "序号"
- A(1, 3) = "第几批"
- End With
- '一人一行,建数组B
- For i = 2 To UBound(A)
- s = s + A(i, 2)
- Next i
- ReDim B(1 To s + 1, 1 To UBound(A, 2))
- For j = 1 To UBound(B, 2)
- B(1, j) = A(1, j)
- Next j
- '序号和第几批
- s = 1
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(A)
- For j = 1 To A(i, 2)
- s = s + 1
- B(s, 1) = A(i, 1)
- d(A(i, 1)) = d(A(i, 1)) + 1: B(s, 2) = d(A(i, 1))
- B(s, 3) = (s - 2) \ x + 1
- Next j
- Next i
- Set d = Nothing
- '>>> 仅为测试效果,可注释
- '样式
- Columns("e:g").Clear
- Range("e1").Resize(UBound(B), UBound(B, 2)) = B
- '填色好查看
- For i = 2 To UBound(B) Step x
- Cells(i, "e").Resize(x, UBound(B, 2)).Interior.Color = _
- RGB(Int(Rnd * 123) + 99, Int(Rnd * 123) + 99, Int(Rnd * 123) + 99)
- Next i
- '<<<
- '把连续的人群作为一个单位
- s = 1: p = 1
- ReDim A(1 To UBound(B), 1 To UBound(B, 2))
- For j = 1 To UBound(A, 2)
- A(1, j) = B(1, j)
- Next j
- For i = 2 To UBound(A) - 1
- '是否往数组A写入新元素
- If B(i, 1) <> B(i + 1, 1) And B(i, 2) + 1 <> B(i + 1, 2) Or _
- B(i, 3) <> B(i + 1, 3) Then
- q = B(i, 2) '当前的终点
- '记录当前的范围
- s = s + 1
- A(s, 1) = B(i, 1)
- A(s, 2) = p & "到" & q
- A(s, 3) = B(i, 3)
- p = B(i + 1, 2) '下一个的起点
- k = B(i + 1, 2) '新起点
- Else
- k = p '旧起点
- End If
- '倒数第2行时,就记录最后一个范围
- If i = UBound(B) - 1 Then
- s = s + 1
- A(s, 1) = B(i + 1, 1)
- A(s, 2) = k & "到" & B(i + 1, 2)
- A(s, 3) = B(i + 1, 3)
- End If
- Next i
- '最终输出
- Columns("J:L").Clear
- Range("J1").Resize(s, UBound(A, 2)) = A
- End Sub
复制代码
中国式过马路2.rar
(12.47 KB, 下载次数: 24)
|
|