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

粘贴一段以前写的代码

供参考

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

以下代码为：当工作簿打开时，自动替换透视表中源路径为当前工作簿路径

Private Sub Workbook_Open()

Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As String

'定义变量

On Error Resume Next

strCon = ActiveSheet.PivotTables(1).PivotCache.Connection

'将当前活动数据透视表中缓存连接信息赋值给变量strCon

Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符

Case "ODBC" '用于判断缓存连接信息中的数据连接方式,如果是ODBC方式

iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag

Case "OLEDB" '用于判断缓存连接信息中的数据连接方式,如果是OLEDB方式

iFlag = "Source="'将"Source=" 赋值给变量iFlag

Case Else

Exit Sub

End Select

iStr = Split(Split(strCon, iFlag)(1), "")(0) '在变量strCon中截取文件路径信息

iPath = ActiveWorkbook.FullName'获取当前活动工作簿的完全路径

With ActiveSheet.PivotTables(1).PivotCache '替换数据透视表中缓存信息中的文件完全路径

.Connection = VBA.Replace(strCon, iStr, iPath)

.CommandText = VBA.Replace(.CommandText, iStr, iPath)

End With

End Sub

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"Sheet2!R1C1:R19C4", Version:=xlPivotTableVersion14).CreatePivotTable _

TableDestination:="[工作簿1]Sheet1!R4C2", TableName:="数据透视表1", DefaultVersion _

:=xlPivotTableVersion14

主要是这句。

你可以用EXCEL自带的宏录制功能来寻找你要的功能的VBA代码。

Sub 宏2()

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"Sheet2!R1C1:R19C4", Version:=xlPivotTableVersion14).CreatePivotTable _

TableDestination:="[工作簿1]Sheet1!R4C2", TableName:="数据透视表1", DefaultVersion _

:=xlPivotTableVersion14

ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _

).PivotFields("Q"), "求和项:Q", xlSum

ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _

).PivotFields("W"), "求和项:W", xlSum

ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _

).PivotFields("E"), "求和项:E", xlSum

ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _

).PivotFields("R"), "求和项:R", xlSum

End Sub

---