Excel 透视表与透视图的自动化

Excel 透视表与透视图的自动化

Excel 透视表与透视图是经常使用的部分，本文将介绍自动生成透视表、透视图方法

2offset函数之动态图表

Excel中透视表对象层级模型为:PivotCaches->PivotTable->PivotFields、PivotItems

PivotCache：缓冲区域，用于数据源和透视表中的缓冲，适配器，同一份数据可以创建多个透视表。

PivotTable：透视表

PivotFields：透视表中各透视字段

PivotItems：透视字段对应的透视选项

ChartObject：透视图

假设我们的数据如下：

在Excel文件中创建透视表的代码如下：

Dim pvc As PivotCache

Dim pvt As PivotTable

‘先创建缓冲区域槐季芬再创建透视表

Set pvc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _

SourceData:=region)

Set pvt = pvc.CreatePivotTable(TableDestination:=dws.Range("G2"), TableName:="PivotTable1")

‘态追如果有缓冲区，则可以直接创建

ThisWorkbook.PivotCaches(1).CreatePivotTable(TableDestination:=dws.Range("B35"), TableName:="PivotTable3")

‘增加列字段

With pvt

With .PivotFields("年级")

.Orientation = xlColumnField

.Position = 1

End With

With .PivotFields("班级")

.Orientation = xlRowField

.Position = 1

End With

'增加计数项

.AddDataField .PivotFields("姓名"), "计数项: 姓名", xlCount

End With

‘通过pivotItems来改变某一列或行的属性悦态信息：

‘设置年级字段隐藏二年级属性

dws.PivotTables(1).PivotFields("年级").PivotItems("二年级").Visible = False

‘另外可以设置透视表的属性，如空单元格显示的内容：

dws.PivotTables(1).NullString = "0"

最后形成的透视图如下：

透视图的创建

创建ChartObject对象:

Set ch2 = dws.ChartObjects.Add(400, 150, 400, 250)

这样就创建了一张图表：

接下来设置图表的属性：

With ch2.Chart

‘图表类型

.ChartType = xlColumnStacked

‘基础数据

.SetSourceData Source:=dws.PivotTables("PivotTable1").RowRange

‘每个图形显示数据

.ApplyDataLabels xlDataLabelsShowValue

‘标题

.ChartTitle.Text = "班级信息"

End With

接下来设置图型中每个线条的格式：

With .SeriesCollection(2).Format.Fill

.Visible = msoTrue

.ForeColor.RGB = RGB(255, 255, 0)

.Transparency = 0

.Solid

End With

With .SeriesCollection(1).Format.Fill

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 80)

.Transparency = 0

.Solid

End With

最终透视图效果如下：