DÙNG VBA TRONG EXCEL ĐỂ TẠO VÀ SỬA CHỮA PIVOT TABLE.

Chức năng pivot table là chức năng mạnh của Excel. Chức năng này đầu tiên xuất hiện trong Excel 5. Nó giúp cho bạn tổng kết số liệu nhanh một cách kinh ngạc. 

Trong bài viết này tôi giả sử rằng các bạn đã làm quen với việc tạo vào sửa chữa pivot table một cách thủ công và bài viết này viết cho Excel 2000. Tôi sẽ hướng dẫn cho các bạn dùng VBA để tạo và sửa chữa pivot table một cách linh động.

Giả sử ở sheet1 tôi có khối dữ liệu cần phân tích như hình1. Khối dữ liệu này gồm các trường: SalesRep (đại diện bán hàng), Region (Vùng), Month (Tháng), Sales (doanh số bán).

Trước khi tạo bảng pivot table như hình 2, tôi đã chọn Record New Macro... như hình 3, để xem đoạn mã được ghi lại như thế nào.

Sau đó tôi vào cửa sổ màn hình VBE bằng cách nhấn tổ hợp phím Alt + F11. Tôi vào Module1, tôi sẽ thấy được đoạn mã như sau:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 17/03/2003 by Duyet ' Range("A1:D13").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!R1C1:R13C4").CreatePivotTable TableDestination:=Range("A1"), TableName:="PivotTable1" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="SalesRep", ColumnFields:="Month", PageFields:="Region" ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = xlDataField End Sub

Với chú ý:

Region	là trường page trong pivot table.
SalesRep	là trường row trong pivot table.
Month	là trường column trong pivot table.
Sales	là trường data trong pivot table sử dụng hàm Sum

Khảo sát đoạn mã đã được ghi:

Để khảo sát đoạn mã trên bạn cần phải biết một số đối tượng liên quan. Tất cả các đối tượng này đều được giải thích trên online help.

PivotCaches	là tập họp các đối tượng PivotCache trong đối tượng Workbook
PivotTables	là tập họp các đối tượng PivotTable trong đối tượng Workbook
PivotTableFields	là tập họp các trường trong đối tượng PivotTable
CreatePivotTable	một phương thức của đối tượng PivotCache để tạo một pivot table sử dụng dữ liệu trong một pivot cache

Ta có thể viết lại thủ tục trên như thủ tục CreatePivotTable (chú ý bạn nhập thủ tục này vào trong module1) sau đây, có thể nó hơi dài nhưng sẽ dễ hiểu hơn, và bạn có thể chạy chương trình bất cứ đâu bằng cách nhấn tổ hợp phím Alt + F8, sau đó chọn thủ tục CreatePivotTable và chọn Run như hình 4 sau:

Sub CreatePivotTable() 
   Dim PTCache As PivotCache 
   Dim PT As PivotTable 
   Application.ScreenUpdating = False 
   ' Xoa PivotSheet neu no ton tai
   On Error Resume Next 
   Application.DisplayAlerts = False 
   Sheets("PivotSheet").Delete 
   On Error GoTo 0 
   ' Tao Pivot Cache
   Set PTCache = ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion.Address) 
   ' Tao worksheet moi va dat ten
   Worksheets.Add 
   ActiveSheet.Name = "PivotSheet" 
   ' Tao Pivot Table tu Cache
   Set PT = PTCache.CreatePivotTable (TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="PivotTable1") 
   With PT 
      ' Them cac truong
      .PivotFields("Region").Orientation = xlPageField 
      .PivotFields("Month").Orientation = xlColumnField 
      .PivotFields("SalesRep").Orientation = xlRowField 
      .PivotFields("Sales").Orientation = xlRowField 
      Application.ScreenUpdating = True 
   End With 
End Sub 
Khi bạn chạy xong thủ tục trên thì bạn cũng sẽ được một pivot table như ở sheet2, nhưng trong trường hợp này thì là sheet có tên PivotSheet.(Hình5)

Bạn chú ý bạn sẽ thấy sự khác biệt của 2 đoạn mã trên. Trong Macro1 khi sử dụng phương thức Add để tạo pivot cache thì SourceData là "Sheet1!R1C1:R13C4" còn trong đoạn mã tôi viết là Sheets("Sheet1").Range("A1").CurrentRegion.Address . Ở đây tôi dùng thuộc tính Current Region, có nghĩa là dữ liệu chúng ta sử dụng dựa trên vùng hiện tại xung quanh ô A1. Điều này để chắc chắn rằng thủ tục CreatePivotTable vẫn tiếp tục làm việc tốt khi chúng ta thêm vào dữ liệu.

Bây giờ giả sử tôi có thêm trường Target (chỉ tiêu) trong khối dữ liệu của tôi, và trong pivot table tôi sẽ đưa thêm trường target vào đồng thời cũng thêm trường tính toán Variance. Trường này (Variance) sẽ bằng Sales - Target. Khối dữ liệu mới của tôi như hình 6.

Đoạn mã trong thủ tục CreatePivotTable trên sẽ được thêm như sau: (Tôi chỉ thêm trong đoạn With PT ....End With)

With PT ' Them cac truong .PivotFields("Region").Orientation = xlPageField .PivotFields("Month").Orientation = xlColumnField .PivotFields("SalesRep").Orientation = xlRowField .PivotFields("Sales").Orientation = xlDataField .PivotFields("Target").Orientation = xlDataField ' Them truong tinh toan .CalculatedFields.Add "Variance", "=Sales - Target" .PivotFields("Variance").Orientation = xlDataField ' Thay doi caption .PivotFields("Sum of Sales").Caption = "Sales ($) " .PivotFields("Sum of Target").Caption = "Target ($) " .PivotFields("Sum of Variance").Caption = "Variance ($) " End With

Sau khi chạy lại thủ tục trên tôi sẽ được như hình 7 sau:

Giả sử bây giờ dữ liệu của tôi gồm 6 tháng (hình 8), tôi muốn đưa thêm cột tổng theo từng 3 tháng. Vậy tôi phải sửa lại đoạn mã của mình như sau:

With PT ' Them cac truong .PivotFields("Region").Orientation = xlPageField .PivotFields("Month").Orientation = xlColumnField .PivotFields("SalesRep").Orientation = xlRowField .PivotFields("Sales").Orientation = xlDataField .PivotFields("Target").Orientation = xlDataField ' Them truong tinh toan .CalculatedFields.Add "Variance", "=Sales - Target" .PivotFields("Variance").Orientation = xlDataField ' Them muc tinh toan .PivotFields("Month").CalculatedItems.Add "Q1", "= thang 1 + thang 2 + thang 3" .PivotFields("Month").CalculatedItems.Add "Q2", "= thang 4 + thang 5 + thang 6" ' Di chuyen cac muc tinh toan .PivotFields("Month").PivotItems("Q1").Position = 4 .PivotFields("Month").PivotItems("Q2").Position = 8 ' Thay doi caption .PivotFields("Sum of Sales").Caption = "Sales ($) " .PivotFields("Sum of Target").Caption = "Target ($) " .PivotFields("Sum of Variance").Caption = "Variance ($) " End With

Sau khi chạy lại thủ tục CreatePivotTable tôi sẽ được kết quả như hình 9.

Vâng, và đến đây các bạn thấy đó, nếu chúng ta biết sử dụng VBA thì công việc phân tích dữ liệu của bạn sẽ trở nên đơn giản hơn chỉ cần vài dòng chỉnh sửa mã. Ngoài ra ta cũng có thể tạo một pivot table từ nguồn dữ liệu bên ngoài như Access chẳng hạn. Để cho việc lập trình về pivot table được tốt, tôi đề nghị các bạn nên đọc phần online help của Excel về các đối tượng, phương thức, thuộc tính mà tôi đã đề cập ở trên. 

Hy vọng rằng bài viết trên sẽ giúp các bạn một phần nào trong công việc.
Mọi góp ý, xin các bạn gởi về levanduyet@pmail.vnn.vn
Lê Văn Duyệt.