|
Sub test()
Application.ScreenUpdating = False
Call test2
Call DelPt(Sheets("step2"))
Call AddPt(Sheets("step1").Range("a1").CurrentRegion, Sheets("step2").Range("a1"))
End Sub
'整理
Sub test2()
Dim A, B(), i, j, r, s
Sheets("源数据").Select
A = Range("b2").CurrentRegion
For i = 1 To UBound(A)
If InStr(A(i, 2), "额") Then r = i '更新字段行的行号
If A(i, 1) <> "" Then
For j = 5 To UBound(A, 2)
If A(r, j) <> "" Then
s = s + 1
ReDim Preserve B(1 To 3, 1 To s)
B(1, s) = A(i, 1)
B(2, s) = A(r, j)
B(3, s) = A(i, j)
End If
Next j
End If
Next i
Sheets("step1").Select
Cells.Clear
[a1].Resize(1, 3) = Array("字段1", "字段2", "字段3")
B = Application.Transpose(B)
[a2].Resize(UBound(B), UBound(B, 2)) = B
End Sub
'清除数据透视表
Sub DelPt(sh As Worksheet)
Dim pt As PivotTable
For Each pt In sh.PivotTables
pt.TableRange2.Clear '包括整个数据透视表(含页字段)的区域
Next
End Sub
'添加数据透视表(透视表的源, 透视表的目标单元格)
Sub AddPt(x As Range, y As Range)
Dim pc As PivotCache
Dim pt As PivotTable
Set pt = y.Parent.PivotTableWizard(xlDatabase, x, y)
With pt
.PivotFields("字段1").Orientation = xlRowField
.PivotFields("字段2").Orientation = xlColumnField
With .PivotFields("字段3")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.000_ "
End With
End With
End Sub
整理数据2.rar
(39.39 KB, 下载次数: 2)
|
评分
-
查看全部评分
|