|
- Sub 合并数据2()
- '数组+字典解法
- '2012-12-18 hwc2ycy 17:33修改
-
- Dim data
- '读源数据
- With Worksheets("Sheet1")
- data = .Range("a1").CurrentRegion
- End With
- '字典,装数据和列类别
- Dim dic As Object, dicItem As Object
- Set dic = CreateObject("scripting.dictionary")
- Set dicItem = CreateObject("scripting.dictionary")
- Dim arr1
- arr1 = Array("餐费", "早餐", " 午餐", "晚餐", "夜宵", "生活用品", "煤气", "宽带费", _
- "车费", "住宿费", "修理费", "工具费", "招待费", "水电", "项目检查费", "人情费", "合计", "联系人姓名", "联系人电话", "备注信息")
-
- '定义列坐标
- Dim t, i&
- i = 7
- For Each t In arr1
- i = i + 1
- dicItem(t) = i
- Next
- '以A|B列数据为KEY
- Dim StrKey$
- Dim Result(), iPos&, iCount&, k&
- ReDim Result(1 To 27, 1 To 1)
- For i = LBound(data) + 1 To UBound(data) '第一行为标题
- StrKey = data(i, 1) & "|" & data(i, 2)
- If Not dic.exists(StrKey) Then '检测字典中是否存在Strkey
- iCount = iCount + 1
- ReDim Preserve Result(1 To 27, 1 To iCount)
- iPos = dicItem(data(i, 3)) '取列坐标
- Result(iPos, iCount) = data(i, 4) '写入数据
- Result(1, iCount) = data(i, 1) 'A列
- Result(2, iCount) = data(i, 2) 'B列
- 'If iPos > 1 And iPos < 6 Then Result(1) = data(i, 4)
- Result(27, iCount) = data(i, 5) '备注
- dic(StrKey) = iCount '写回字典
- Else
- k = dic(StrKey) '取数据列坐标
- iPos = dicItem(data(i, 3)) '行坐标
- Result(iPos, k) = Result(iPos, k) + data(i, 4) '累加
- 'If iPos > 1 And iPos < 6 Then Result(1) = Result(1) + data(i, 4)
- 'Result(27, k) = data(i, 5) '备注
- End If
- Next
-
- '结果数据写入Sheet3
- With Worksheets("Sheet3")
- i = .Cells(Rows.Count, 1).End(xlUp).Row '取最后一行数据位置
- If i > 3 Then .Range("a4:aa" & i).ClearContents '4为第一行数据位置
-
- '数组经转置后写回.
- .Range("a4").Resize(iCount, 27) = WorksheetFunction.Transpose(Result)
-
- i = .Cells(Rows.Count, 1).End(xlUp).Row
- '添加边框
- With .Range("a4:aa" & i).Borders
- .LineStyle = xlContinuous
- End With
- .Range("b4:b" & i).NumberFormatLocal = "yyyy年m月d日" '设置日期格式
- End With
- End Sub
复制代码 |
|