Sub 从首列提取周次到第二列并透视()
For i = 1 To ActiveSheet.Range("A1").CurrentRegion.Rows.Count
c = ActiveSheet.Cells(i, 1).Value
ActiveSheet.Cells(i, 2).Value = Mid(c, 5, 2)
Next
ActiveSheet.Range("B1").Value = "周次"
'按照周次做透视表
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C2:R1048576C2", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R4C10", TableName:="数据透视表2", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("数据透视表2").PivotFields("周次")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("数据透视表2").AddDataField ActiveSheet.PivotTables("数据透视表2").PivotFields("周次"), "计数项:周次", xlCount
With ActiveSheet.PivotTables("数据透视表2").PivotFields("周次")
.Orientation = xlColumnField
.Position = 1
End With
End Sub