|
本帖最后由 CEL_hjl 于 2013-9-7 15:47 编辑
<FONT style="BACKGROUND-COLOR: #f7f7f7">Option Explicit
Sub 整理数据()
Dim sh As Worksheet, arr, k%, d, m%, crr, drr
On Error Resume Next
arr = Sheets("原始数据").UsedRange
For k = 2 To UBound(arr)
Set sh = Worksheets(Format(arr(k, 1), "m月d日"))
If Err.Number > 0 Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(arr(k, 1), "m月d日")
Err.Clear
End If
Next
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "原始数据" Then
Set d = CreateObject("scripting.dictionary")
For k = 2 To UBound(arr)
If Format(arr(k, 1), "m月d日") = sh.Name Then
d(arr(k, 3) & "|" & arr(k, 2)) = d(arr(k, 3) & "|" & arr(k, 2)) + arr(k, 4)
End If
Next k
With sh
.Range("a1").Offset(0, 0) = arr(1, 3)
.Range("a1").Offset(0, 1) = arr(1, 2)
.Range("a1").Offset(0, 2) = arr(1, 4)
crr = d.keys
drr = d.items
For m = 1 To d.Count
.Range("a1").Offset(m, 0) = Split(crr(m - 1), "|")(0)
.Range("a1").Offset(m, 1) = Split(crr(m - 1), "|")(1)
.Range("a1").Offset(m, 2) = drr(m - 1)
Next m
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A:A"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("A1:C" & sh.Range("A65536").End(xlUp).Row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Set d = Nothing
End If
Next sh
End Sub</FONT>
这段程序是提别人问题后最佳答案,看不懂,问题基本类似但这段程序哪里可以修改(需根据不同表格)那里不能修改?求详解
求助.zip
(10.96 KB, 下载次数: 1)
|
|