新浪博客

【教程】VBA创建数据透视表

2015-02-15 15:10阅读：

http://blog.sina.cn/dpool/blog/u/2180745470

名词解释：
  • PivotTables——数据透视表。
  • PivotCaches——数据透视表缓存。数据缓冲区，充当数据源和透视表之间的传递途径。优点：1）很大程度上控制了从数据源中获得的数据，尤其是与ADO协作时，可对外部数据源实现高级编程控制，有很大的灵活性，2）从同一数据源生成多个数据透视表（也可以使用PivotTable集合,无特别优势，只是提供丰富性选择），这比强制每个数据透视表维护自己的数据源更有效率。

    1. set pvt=Activesheet.PivotTables.add(PivotCache:=pvc,tabledestination:=range('A3'))

PivotFields——数据透视字段。包括源数据所有字段以及已添加的计算字段（源数据表不可见）。两种技术添加：1）PivotTable.AddFields方法；addfields方法可以添加多个行字段/列字段或者页字段，添加的新字段将替换任何现有的字段，除非addtotable=true,不能用来添加或者替换数据字段。2）给PivotField对象的Orientation属性赋值。

    1. With Activesheet.PivotTables(1)
    2. '添加新的行字段state
    3. .AddFields RowFields:='state',AddToTable:=true
    4. '将date字段作为新的页字段
    5. .PivotFields('date').Orientation=xlPageField
    6. End with


  • PivotItems——数据透视项。是PivotField对象的方法而非属性，包含在字段中的唯一值。
  • PivotCharts——数据透视图

  1. Sub creatpivotchart()
  2. Dim shp As Shape
  3. '在shape对象中创建图表
  4. Set shp = activehseet.Shapes.AddChart(xlColumnStacked)
  5. shp.Chart.SetSourceData Source:=ActiveSheet.PivotTables(1).TableRange1, PlotBy:=xlsoumns
  6. '调整图表大小位置与某单元格区域相同
  7. With Range('a11:f28')
  8. shp.Left = .Left
  9. shp.Top = .Top
  10. shp.Width = .Width
  11. shp.Height = .Height
  12. End With
  13. '更改数据透视表和数据透视图的布局
  14. With shp.Chart.PivotLayout.PivotTable
  15. .PivotFields('customer').Orientation = xlColumnField
  16. .PivotFields('product').Orientation = xlRowField
  17. End With
  18. '修改数据透视图的格式
  19. shp.Chart.ChartType = xlCylinderColStacked
  20. End Sub


Excel录制宏代码：

优化过的通用代码：

  1. Sub CreatePivotTable()
  2. Dim wks As Worksheet
  3. Dim pvc As PivotCache
  4. Dim pvt As PivotTable

  6. '添加新工作表
  7. Set wks = Worksheets.Add

  9. '建立数据透视表缓存
  10. Set pvc = ActiveWorkbook.PivotCaches.Create( _
  11. SourceType:=xlDatabase, _
  12. SourceData:=Sheet1.ListObjects('table1').Range) '引用区域可以是range，表或者自定义名称，如名称可以写为：SourceData:='database'

  14. '创建数据透视表，tabledestination为数据透视表的最左上方区域
  15. Set pvt = pvc.CreatePivotTable(tabledestination:=wks.Range('a3'), _
  16. defaultversion:=xlPivotTableVersion12)
  17. 'xlPivotTableVersion12为Excel2007及以上版本的默认模板，见XlPivotTableVersionList 枚举 (Excel)

  19. '定义数据透视表的字段

  21. With pvt
  22. '添加行字段方法1，AddFields方法，可以跟Array函数配合使用，批量设置字段，字段名称必须跟透视表源数据的字段名字完全一样，否则会出错；可以添加页字段，行字段和列字段，不能添加数据项
  23. .AddFields RowFields:=Array('客户名称', '生产单号', '类别', '运营商', '厂家', '品名', _
    '匹配电源', '匹配电源口径', '产品代数', '端口数量', '其它', '调用名称单号记录'), _

我的更多文章

下载客户端阅读体验更佳

APP专享