本帖最后由 芐雨 于 2015-1-21 09:21 编辑
- Sub 按钮2_Click()
- Dim arr, brr, crr, i&, j&, x&, s
- Dim dic As Object, d As Object
- arr = Range("A2:D" & Cells(Rows.Count, 1).End(3).Row) 'D列记为数组arr
- brr = Sheets("目标结果").Range("A1").CurrentRegion '记为数组brr
-
- Set d = CreateObject("scripting.dictionary") '创建字典d
- Set dic = CreateObject("scripting.dictionary") '创建字典dic
- For i = 3 To UBound(brr, 2) '遍历,得到类型
- d(brr(1, i)) = i '放入字典,通过字典返加列数
- Next
-
- ReDim crr(1 To 1000, 1 To UBound(brr, 2))
- For i = 1 To UBound(arr) ' 遍历arr
- s = arr(i, 1) & "|" & arr(i, 2) '单位名称与序号,记为s,区分标准
- If dic.exists(s) Then '判断是否存在
- crr(dic(s), d(arr(i, 3))) = arr(i, 4) + crr(dic(s), d(arr(i, 3))) 'd(arr(i, 3))返列数,dic(s)返回行数
- Else
- x = x + 1 '计数
- dic(s) = x 'dic(s)返回行数
- crr(x, 1) = arr(i, 1) '名称
- crr(x, 2) = arr(i, 2) '序号
- crr(x, d(arr(i, 3))) = arr(i, 4) '数值,d(arr(i, 3))返列数
- End If
- Next
- With Sheets("目标结果")
- .UsedRange.Offset(1).Clear '单元格区域向下偏移一行
- .[B:B].NumberFormat = "@" '文本格式
- .Range("A2").Resize(x, UBound(crr, 2)) = crr '赋值
- .Range("A2").Resize(x, UBound(crr, 2)).Borders.LineStyle = 1 '边框
- .Select
- End With
- MsgBox "完成"
- End Sub
复制代码
如何根据条件将数据转置显示?.zip
(12.81 KB, 下载次数: 5)
|