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

Chia sẻ: Tran Ngoc Trang | Ngày: | Loại File: PDF | Số trang:18

Thêm vào BST

Báo xấu

197
lượt xem 58
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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ố 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 này sẽ hướng dẫn dùng VBA để tạo và sửa chữa PivotTable một cách linh động. Excel 2000.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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ố 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 này sẽ hướng dẫn dùng VBA để tạo và sửa chữa PivotTable một cách linh động. 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ồ SalesRep (đại diện bán hàng), Region (Vùng), Month (Tháng), Sales (doanh số b
Trước khi tạo bảng PivotTable như Hình 2, tôi đã chọn Record New Macro... như mã được ghi lại như thế nào.
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() Hình 3 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ú: Là tr Region page Pivo Là tr SalesRep row
A Pivo c Là tr t Month colu i Pivo v e L S d h Sales P e sử e h t .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 giải thích trên online help. là tập h các đối tượng PivotCaches PivotC trong đ tượng Workb là tập h PivotTables các đối
tượng PivotT trong đ tượng Workb là tập h các trư PivotTableFields trong đ tượng PivotT một phương Create thức củ PivotTable đối tượ PivotC
để tạo m PivotT sử dụn liệu tro một PivotC Ta có thể viết lại thủ tục trên bằng thủ tục CreatePivotTable (chú ý bạn nhập t 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 đâu bằng cách nhấn tổ hợp phím Alt + F8, sau đó chọn thủ tục CreatePivotTab 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 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ử thức Add để tạoPivotCache thì SourceData là "Sheet1!R1C1:R13C4" còn tron làSheets("Sheet1").Range("A1").CurrentRegion.Address. Ở đây tôi dùng thuộc 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. thủ tục CreatePivotTablevẫn tiếp tục làm việc tốt khi chúng ta thêm vào dữ liệ Bây giờ giả sử tôi có thêm trường Target (chỉ tiêu) trong khối dữ liệu, và trong thêm trường target vào đồng thời cũng thêm trường tính toán Variance. Trườn 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 ....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. 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ổn 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, đến đây các bạn thấy đó, nếu chúng ta biết sử dụng VBA thì công việc p nên đơn giản hơn. Ngoài ra, ta cũng có thể tạo một PivotTable từ nguồn dữ liệ Access chẳng hạn. Để lập trình PivotTable được tốt, các bạn nên đọc phần onl các đối tượng, phương thức, thuộc tính đã đề 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.