excel怎么做统计表，透明表透视表pivot table

Question

如题所述

Answer 1

粘贴一段以前写的代码
供参考
Dim DataRng As Range '定义一个数据范围，用来储存生成数据透视表的数据
Dim MyPivot As Worksheet '定义一个工作表，存放数据透视表"
Dim pt As PivotTable '定义一个数据透视表，用来储存数据透视表对象”
Dim MyTable As Worksheet '定义一个工作表，做为汇总表
Dim ptcache As PivotCache

Set DataRng = Range("A1:D" & zuidazhi)
'
Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=DataRng)
Sheets.Add
Sheets("Sheet1").Select
Set pt = ptcache.CreatePivotTable(tabledestination:=Sheets("Sheet1").Range("b1"), TableName:="pivottable1")

With ActiveSheet.PivotTables("pivottable1").PivotFields("采样时间")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("pivottable1").PivotFields("样品名称")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("pivottable1").PivotFields("分项")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("pivottable1").AddDataField ActiveSheet.PivotTables("pivottable1" _
).PivotFields("值"), "计数项:值", xlCount
With ActiveSheet.PivotTables("pivottable1").PivotFields("计数项:值")
.Caption = "平均值项:值"
.Function = xlAverage
End With