|
发表于 2015-4-8 14:47
|
显示全部楼层
本楼为最佳答案
相同地区品种的累加为一列,原数据中有两列“河北”- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim Filename, wb As Workbook, Sht As Worksheet
- Filename = Dir(ThisWorkbook.Path & "\*.xlsx")
- Set d = CreateObject("scripting.dictionary") '品种+地区
- Set d1 = CreateObject("scripting.dictionary") '品种
- Set d2 = CreateObject("scripting.dictionary") '地区
- Do While Filename <> ""
- If Filename <> ThisWorkbook.Name Then
- fn = ThisWorkbook.Path & "" & Filename
- Set wb = Workbooks.Open(fn)
- Set Sht = wb.Worksheets(1)
- arr = Sht.[a1].CurrentRegion
- For j = 2 To UBound(arr, 2) '地区
- If Len(arr(1, j)) > 0 Then d2(arr(1, j)) = ""
- Next
- For i = 2 To UBound(arr)
- d1(arr(i, 1)) = "" '品种
- For j = 2 To UBound(arr, 2)
- x = arr(i, 1) & arr(1, j)
- d(x) = d(x) + arr(i, j) '品种+地区,数量累加
- Next
- Next
- wb.Close False
- End If
- Filename = Dir
- Loop
- Set Sht = Nothing
- With Sheet1
- .[a1].Resize(1, d2.Count) = d2.keys
- .[a2].Resize(d1.Count) = Application.Transpose(d1.keys)
- arr = .[a1].CurrentRegion
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- x = arr(i, 1) & arr(1, j)
- arr(i, j) = d(x)
- Next
- Next
- .[a1].CurrentRegion = arr
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|