|
楼主 |
发表于 2012-1-11 23:34
|
显示全部楼层
本帖最后由 wcymiss 于 2012-1-12 12:11 编辑
作业三_必做一:
效果一用了三种方法,效果二就只用一种了,其实效果一与效果二差不多,区别在于结果数组的行列号构造。
- Sub 作业三_必做一_效果一_数组()
- '数组,数据源需按仓库名称排序。
- Dim arr, trr, brr()
- Dim i As Long, n1 As Long, n2 As Long, j As Long, n As Long, k As Long, x As Long, y As Long
- With Sheets("必做一")
- arr = .Range("a2:e" & .Cells(Rows.Count, 1).End(3).Row + 1) '数据源
- trr = .Range("a1:e1") '标题
- ReDim brr(1 To UBound(arr), 1 To (UBound(arr) \ 4) * 5 + 5) '定义大数组
- n1 = 1 '仓库名称的起始位置
- k = 1 '结果数组行号
- For i = 2 To UBound(arr)
- If arr(i, 1) <> arr(i - 1, 1) Then
- n2 = i - 1 '仓库名称的结束位置
- n = 0 '仓库名称的序号
- brr(k, 1) = trr(1, 1)
- brr(k, 2) = trr(1, 2)
- brr(k, 3) = trr(1, 3)
- brr(k, 4) = trr(1, 4)
- brr(k, 5) = trr(1, 5)
- For j = n1 To n2
- x = n Mod 4 + 1 + k '结果数组的行号
- y = (n \ 4) * 5 '结果数组的列号
- If ymax < y Then ymax = y '最大列号
- brr(x, y + 1) = arr(j, 1)
- brr(x, y + 2) = arr(j, 2)
- brr(x, y + 3) = arr(j, 3)
- brr(x, y + 4) = arr(j, 4)
- brr(x, y + 5) = arr(j, 5)
- n = n + 1
- Next
- n1 = i
- k = k + 5
- End If
- Next
- .Range("g1", .Cells(Rows.Count, Columns.Count)).ClearContents
- For j = 9 To ymax + 9 Step 5
- .Cells(1, j).Resize(i, 1).NumberFormat = "@"
- Next
- .Range("g1").Resize(i, ymax + 5) = brr
- End With
- End Sub
复制代码
- Sub 作业三_必做一_效果一_字典嵌套()
- '字典嵌套,支持乱序
- Dim d As Object
- Dim arr, trr, brr(), dk, ddk
- Dim i As Long, n As Long, k As Long, x As Long, y As Long, ymax As Long
- Set d = CreateObject("scripting.dictionary")
- With Sheets("必做一")
- arr = .Range("a2:e" & .Cells(Rows.Count, 1).End(3).Row) '数据源
- trr = .Range("a1:e1") '标题
- ReDim brr(1 To UBound(arr), 1 To (UBound(arr) \ 4) * 5 + 5) '定义大数组
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 1)) Then
- d(arr(i, 1))(i) = 1
- Else
- Set d(arr(i, 1)) = CreateObject("scripting.dictionary") '父字典key为仓库名称
- d(arr(i, 1))(i) = 1 '子字典key为行号
- End If
- Next
- dk = d.keys '仓库名称
- For i = 0 To UBound(dk)
- ddk = d(dk(i)).keys '每种仓库名称的行号
- k = i * 5 + 1 '结果数组的起始行
- brr(k, 1) = trr(1, 1)
- brr(k, 2) = trr(1, 2)
- brr(k, 3) = trr(1, 3)
- brr(k, 4) = trr(1, 4)
- brr(k, 5) = trr(1, 5)
- For n = 0 To UBound(ddk)
- x = n Mod 4 + 1 + k '结果数组的行号
- y = (n \ 4) * 5 '结果数组的列号
- If ymax < y Then ymax = y '最大列号
- brr(x, y + 1) = arr(ddk(n), 1)
- brr(x, y + 2) = arr(ddk(n), 2)
- brr(x, y + 3) = arr(ddk(n), 3)
- brr(x, y + 4) = arr(ddk(n), 4)
- brr(x, y + 5) = arr(ddk(n), 5)
- Next
- Next
- .Range("g1", .Cells(Rows.Count, Columns.Count)).ClearContents
- For i = 9 To ymax + 9 Step 5
- .Cells(1, i).Resize(k + 4, 1).NumberFormat = "@"
- Next
- .Range("g1").Resize(k + 4, ymax + 5) = brr
- End With
- Set d = Nothing
- End Sub
复制代码
- Sub 作业三_必做一_效果一_字典()
- '字典,支持乱序
- Dim d As Object
- Dim arr, trr, brr(), dk, di, irr
- Dim i As Long, n As Long, k As Long, x As Long, y As Long, ymax As Long
- Set d = CreateObject("scripting.dictionary")
- With Sheets("必做一")
- arr = .Range("a2:e" & .Cells(Rows.Count, 1).End(3).Row) '数据源
- trr = .Range("a1:e1") '标题
- ReDim brr(1 To UBound(arr), 1 To (UBound(arr) \ 4) * 5 + 5) '定义大数组
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) & "," & i '字典key为仓库名称,item为行号的连接
- Next
- dk = d.keys '仓库名称
- di = d.items
- For i = 0 To UBound(dk)
- irr = Split(di(i), ",")
- k = i * 5 + 1 '结果数组的起始行
- brr(k, 1) = trr(1, 1)
- brr(k, 2) = trr(1, 2)
- brr(k, 3) = trr(1, 3)
- brr(k, 4) = trr(1, 4)
- brr(k, 5) = trr(1, 5)
- For n = 1 To UBound(irr)
- x = (n - 1) Mod 4 + 1 + k '结果数组的行号
- y = ((n - 1) \ 4) * 5 '结果数组的列号
- If ymax < y Then ymax = y '最大列号
- brr(x, y + 1) = arr(irr(n), 1)
- brr(x, y + 2) = arr(irr(n), 2)
- brr(x, y + 3) = arr(irr(n), 3)
- brr(x, y + 4) = arr(irr(n), 4)
- brr(x, y + 5) = arr(irr(n), 5)
- Next
- Next
- .Range("g1", .Cells(Rows.Count, Columns.Count)).ClearContents
- For i = 9 To ymax + 9 Step 5
- .Cells(1, i).Resize(k + 4, 1).NumberFormat = "@"
- Next
- .Range("g1").Resize(k + 4, ymax + 5) = brr
- End With
- Set d = Nothing
- End Sub
复制代码
- Sub 作业三_必做一_效果二()
- Dim d As Object
- Dim arr, trr, brr(), dk, di, irr
- Dim i As Long, n As Long, k As Long, x As Long, y As Long, ymax As Long
- Set d = CreateObject("scripting.dictionary")
- With Sheets("必做一")
- arr = .Range("a2:e" & .Cells(Rows.Count, 1).End(3).Row)
- trr = .Range("a1:e1")
- ReDim brr(1 To UBound(arr), 1 To 15) '与效果一不同
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) & "," & i
- Next
- dk = d.keys
- di = d.items
- k = 1
- For i = 0 To UBound(dk)
- brr(k, 1) = trr(1, 1)
- brr(k, 2) = trr(1, 2)
- brr(k, 3) = trr(1, 3)
- brr(k, 4) = trr(1, 4)
- brr(k, 5) = trr(1, 5)
- irr = Split(di(i), ",")
- For n = 1 To UBound(irr)
- x = (n \ 15) * 5 + n Mod 5 + k '结果数组的行号
- y = ((n Mod 15) \ 5) * 5 '结果数组的列号
- brr(x, y + 1) = arr(irr(n), 1)
- brr(x, y + 2) = arr(irr(n), 2)
- brr(x, y + 3) = arr(irr(n), 3)
- brr(x, y + 4) = arr(irr(n), 4)
- brr(x, y + 5) = arr(irr(n), 5)
- Next
- k = 1 - Int(-x / 5) * 5 '下一个仓库名称的起始位置
- Next
- .Range("g1", .Cells(Rows.Count, Columns.Count)).ClearContents
- .Range("i:i").NumberFormat = "@"
- .Range("n:n").NumberFormat = "@"
- .Range("s:s").NumberFormat = "@"
- .Range("g1").Resize(k, 15) = brr
- End With
- Set d = Nothing
- End Sub
复制代码
|
|