• Thứ Ba, 05/09/2006 07:17 (GMT+7)

    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ình5)

    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 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, và trong PivotTable 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.

    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. 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 phân tích dữ liệu sẽ trở 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ệu bên ngoài như Access chẳng hạn. Để lập trình PivotTable được tốt, 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 đã đề 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

    ID: A0608_130