Dùng VBA trong Excel để tạo và sửa chữa PivotTable -

Dùng VBA trong Excel để tạo và sửa chữa PivotTable -
Chức năng PivotTable là chức năng mạnh của Excel, nó giúp bạn tổng kết số liệu nhanh một cách kinh ngạc. Chức năng này đầu tiên xuất hiện trong Excel 5. Tôi cho rằng các bạn đã làm quen với việc tạo và sửa chữa PivotTable bằng cách thủ công và bài viết này sẽ hướng dẫn dùng VBA để tạo và sửa chữa PivotTable một cách linh động. Bài viết sử dụng cho Excel 2000. 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 PivotTable 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.


 



Hình 3

Sau đó tôi vào màn hình VBE bằng cách nhấn tổ hợp phím Alt + F11. Tôi vào Module1, 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
 
 
Ghi chú:
 

 
 
Region
 
 
Là trường page trong PivotTable.
 

 
 
SalesRep
 
 
Là trường row trong PivotTable.
 

 
 
Month
 
 
Là trường column trong PivotTable.
 

 
 
Sales
 
 
Là trường data trong PivotTable sử dụng hàm Sum
 

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="SalesRep", _ ColumnFields:="Month", PageFields:="Region" ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _ xlDataField End Sub
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
 

 
Create PivotTable
 
 
một phương thức của đối tượng PivotCache để tạo một PivotTable sử dụng dữ liệu trong một PivotCache
 

Ta có thể viết lại thủ tục trên bằng thủ tục CreatePivotTable (chú ý bạn nhập thủ tục này vào 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.


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 chạy xong thủ tục trên, bạn sẽ được một PivotTable ở sheet2, trong trường hợp này sheet có tên là PivotSheet. (Hình 5) Nếu 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 PivotCache 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 thủ tục CreatePivotTable vẫn tiếp tục làm việc tốt khi chúng ta thêm vào