- Sử dụng GetPivotData để đạt được giá trị
- Tạo bảng tổng hợp trên trang tính
- Tạo bảng tổng hợp trên trang tính mới
- Thêm trường vào bảng tổng hợp
- Thay đổi bố cục báo cáo của bảng tổng hợp
- Xóa bảng tổng hợp
- Định dạng tất cả Pivot Table trong Workbook
- Loại bỏ các trường của một bảng tổng hợp
- Tạo bộ lọc
- Làm mới bảng tổng hợp của bạn
Hướng dẫn này sẽ trình bày cách làm việc với Pivot Table bằng VBA.
Bảng tổng hợp là công cụ tóm tắt dữ liệu mà bạn có thể sử dụng để rút ra những thông tin chi tiết và tóm tắt chính từ dữ liệu của mình. Hãy xem một ví dụ: chúng tôi có tập dữ liệu nguồn trong các ô A1: D21 chứa thông tin chi tiết về các sản phẩm đã bán, được hiển thị bên dưới:
Sử dụng GetPivotData để đạt được giá trị
Giả sử bạn có PivotTable được gọi là PivotTable1 với Bán hàng trong Trường Giá trị / Dữ liệu, Sản phẩm dưới dạng trường Hàng và Khu vực dưới dạng trường Cột. Bạn có thể sử dụng phương thức PivotTable.GetPivotData để trả về giá trị từ Pivot Table.
Mã sau sẽ trả lại $ 1,130,00 (tổng doanh số cho Khu vực phía Đông) từ PivotTable:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Bán hàng", "Khu vực", "Miền Đông") |
Trong trường hợp này, Bán hàng là “Trường dữ liệu”, “Trường 1” là Khu vực và “Mục1” là Đông.
Mã sau sẽ trả về $ 980 (tổng doanh số cho Sản phẩm ABC ở Khu vực phía Bắc) từ Bảng tổng hợp:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Bán hàng", "Sản phẩm", "ABC", "Khu vực", "Miền Bắc") |
Trong trường hợp này, Bán hàng là “Trường dữ liệu”, “Trường 1” là Sản phẩm, “Mục1” là ABC, “Trường 2” là Khu vực và “Lặp lại 2” là Bắc.
Bạn cũng có thể bao gồm nhiều hơn 2 trường.
Cú pháp cho GetPivotData là:
GetPivotData (Trường dữ liệu, Field1, Mục 1, Field2, Lặp lại 2… ) ở đâu:
| Tham số | Sự miêu tả |
|---|---|
| Trường dữ liệu | Trường dữ liệu như doanh số bán hàng, số lượng, v.v. có chứa số. |
| Trường 1 | Tên của một trường cột hoặc hàng trong bảng. |
| Mục 1 | Tên của một mục trong Trường 1 (Tùy chọn). |
| Trường 2 | Tên của trường cột hoặc hàng trong bảng (Tùy chọn). |
| Mặt hàng 2 | Tên của một mục trong Trường 2 (Tùy chọn). |
Tạo bảng tổng hợp trên trang tính
Để tạo Bảng tổng hợp dựa trên phạm vi dữ liệu ở trên, trên ô J2 trên Trang tính1 của sổ làm việc Hoạt động, chúng tôi sẽ sử dụng mã sau:
| 1234567891011 | Trang tính ("Trang 1"). Ô (1, 1). ChọnActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Phiên bản: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Trang tính ("Trang 1"). Chọn |
Kết quả là:
Tạo bảng tổng hợp trên trang tính mới
Để tạo Bảng Pivot dựa trên phạm vi dữ liệu ở trên, trên trang tính mới, của sổ làm việc đang hoạt động, chúng tôi sẽ sử dụng mã sau:
| 12345678910111213 | Trang tính ("Trang 1"). Ô (1, 1). ChọnSheets.AddActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Phiên bản: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Trang tính ("Trang tính2"). Chọn |
Thêm trường vào bảng tổng hợp
Bạn có thể thêm các trường vào Bảng Pivot mới tạo được gọi là PivotTable1 dựa trên phạm vi dữ liệu ở trên. Lưu ý: Trang tính chứa Bảng tổng hợp của bạn, cần phải là Trang tính Hoạt động.
Để thêm Sản phẩm vào Trường Hàng, bạn sẽ sử dụng mã sau:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sản phẩm"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sản phẩm"). Vị trí = 1 |
Để thêm Vùng vào Trường Cột, bạn sẽ sử dụng mã sau:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). Vị trí = 1 |
Để thêm Bán hàng vào Phần Giá trị với định dạng số đơn vị tiền tệ, bạn sẽ sử dụng mã sau:
| 123456789 | ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Doanh số"), "Tổng Doanh số", xlSumVới ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Tổng doanh số").NumberFormat = "$ #, ## 0,00"Kết thúc với |
Kết quả là:
Thay đổi bố cục báo cáo của bảng tổng hợp
Bạn có thể thay đổi Bố cục Báo cáo của Bảng Pivot của mình. Đoạn mã sau sẽ thay đổi Bố cục Báo cáo của Bảng Pivot của bạn thành Biểu mẫu Bảng:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18" |
Xóa bảng tổng hợp
Bạn có thể xóa Pivot Table bằng VBA. Đoạn mã sau sẽ xóa Bảng Pivot được gọi là PivotTable1 trên Trang tính Hoạt động:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents |
Định dạng tất cả Pivot Table trong Workbook
Bạn có thể định dạng tất cả Pivot Table trong Workbook bằng VBA. Đoạn mã sau sử dụng cấu trúc vòng lặp để lặp qua tất cả các trang tính của sổ làm việc và xóa tất cả các Bảng tổng hợp trong sổ làm việc:
| 12345678910111213 | Sub FormattingAllThePivotTablesInAWorkbook ()Làm mờ tuần làm trang tínhDim wb As WorkbookĐặt wb = ActiveWorkbookLàm mờ pt dưới dạng PivotTableCho mỗi tuần Trong wb. Trang tínhĐối với mỗi pt Trong tuần.pt.TableStyle2 = "PivotStyleLight15"Pt tiếp theoTuần tiếp theoKết thúc Sub |
Để tìm hiểu thêm về cách sử dụng Vòng lặp trong VBA, hãy nhấp vào đây.
Loại bỏ các trường của một bảng tổng hợp
Bạn có thể xóa các trường trong Pivot Table bằng VBA. Mã sau sẽ xóa trường Sản phẩm trong phần Hàng khỏi Bảng Pivot có tên PivotTable1 trong Trang tính Hoạt động:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sản phẩm"). Định hướng = _xlHidden |
Tạo bộ lọc
Bảng Pivot được gọi là PivotTable1 đã được tạo với Sản phẩm trong phần Hàng và Bán hàng trong phần Giá trị. Bạn cũng có thể tạo Bộ lọc cho Pivot Table của mình bằng VBA. Đoạn mã sau sẽ tạo một bộ lọc dựa trên Vùng trong phần Bộ lọc:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). Vị trí = 1 |
Để lọc Bảng tổng hợp của bạn dựa trên một Mục báo cáo trong trường hợp này là khu vực phía Đông, bạn sẽ sử dụng mã sau:
| 12345 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). CurrentPage = _"Phía đông" |
Giả sử bạn muốn lọc Pivot Table của mình dựa trên nhiều vùng, trong trường hợp này là Đông và Bắc, bạn sẽ sử dụng mã sau:
| 1234567891011121314 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). Vị trí = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng"). _EnableMultiplePageItems = TrueVới ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Vùng").PivotItems ("Nam"). Visible = False.PivotItems ("Tây"). Visible = FalseKết thúc với |
Làm mới bảng tổng hợp của bạn
Bạn có thể làm mới Bảng Pivot của mình trong VBA. Bạn sẽ sử dụng mã sau để làm mới một bảng cụ thể được gọi là PivotTable1 trong VBA:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh |
