Hướng dẫn này sẽ trình bày cách sử dụng Bộ lọc Bảng Pivot trong VBA.
Bảng tổng hợp là một công cụ dữ liệu đặc biệt mạnh mẽ của Excel. Bảng tổng hợp cho phép chúng tôi phân tích và diễn giải một lượng lớn dữ liệu bằng cách nhóm và tóm tắt các trường và hàng. Chúng tôi có thể áp dụng các bộ lọc cho bảng tổng hợp của mình để cho phép chúng tôi xem nhanh dữ liệu có liên quan đến chúng tôi.
Trước tiên, chúng ta cần tạo bảng Pivot cho dữ liệu của mình. (Bấm vào đây để xem Hướng dẫn bảng VBA Pivot của chúng tôi).
Tạo bộ lọc dựa trên giá trị ô
Bạn có thể lọc trong Bảng tổng hợp bằng VBA dựa trên dữ liệu có trong giá trị ô - chúng tôi có thể lọc trên trường Trang hoặc trên trường Hàng (ví dụ: trên trường Nhà cung cấp ở trên hoặc trường Người tổ chức trong cột Nhãn hàng ).
Trong một ô trống ở bên phải của bảng Pivot, hãy tạo một ô để giữ bộ lọc, sau đó nhập dữ liệu vào ô mà bạn muốn lọc bảng Pivot.
Tạo macro VBA sau:
| 1234567 | Bộ lọc phụPageValue ()Dim pvFld As PivotFieldDim strFilter As StringĐặt pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Nhà cung cấp")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Phạm vi ("M4"). Giá trịpvFld.CurrentPage = strFilterKết thúc Sub |
Chạy macro để áp dụng bộ lọc.
Để xóa bộ lọc, hãy tạo macro sau:
| 12345 | Sub ClearFilter ()Làm mờ pTbl dưới dạng PivotTableĐặt pTbl = ActiveSheet.PivotTables ("PivotTable1")pTbl.ClearAllFiltersKết thúc Sub |
Bộ lọc sau đó sẽ bị loại bỏ.
Sau đó, chúng tôi có thể sửa đổi tiêu chí bộ lọc để lọc trên một hàng trong bảng Tổng hợp thay vì Trang hiện tại.
Việc nhập macro sau sẽ cho phép chúng tôi lọc trên Hàng (lưu ý rằng Trường Pivot để lọc hiện là Oper chứ không phải Nhà cung cấp).
| 1234567 | Sub FilterRowValue ()Dim pvFld As PivotFieldDim strFilter As StringĐặt pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Phạm vi ("M4"). Giá trịpvFld.PivotFilters.Add2 xlCaptionEquals, strFilterKết thúc Sub |
Chạy macro để áp dụng bộ lọc.
Sử dụng nhiều tiêu chí trong bộ lọc xoay vòng
Chúng tôi có thể thêm vào bộ lọc Giá trị hàng ở trên bằng cách thêm tiêu chí bổ sung.
Tuy nhiên, vì bộ lọc tiêu chuẩn ẩn các hàng không bắt buộc, chúng ta cần lặp lại các tiêu chí và hiển thị các hàng được yêu cầu, trong khi ẩn các hàng không được yêu cầu. Điều này được thực hiện bằng cách tạo một biến Mảng và sử dụng một vài Vòng lặp trong mã.
| 1234567891011121314151617181920212223 | Sub FilterMultipleRowItems ()Dim vArray làm biến thểDim i As Integer, j As IntegerDim pvFld As PivotFieldĐặt pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")vArray = Phạm vi ("M4: M5")pvFld.ClearAllFiltersVới pvFldĐối với i = 1 Đến pvFld.PivotItems.Countj = 1Làm trong khi j <= UBound (vArray, 1) - LBound (vArray, 1) + 1Nếu pvFld.PivotItems (i) .Name = vArray (j, 1) ThìpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Vible = TrueThoát DoKhácpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Vible = FalseKết thúc nếuj = j + 1VòngTiếp theo tôiKết thúc vớiKết thúc Sub |
Tạo bộ lọc dựa trên một biến
Chúng ta có thể sử dụng các khái niệm tương tự để tạo bộ lọc dựa trên các biến trong mã của chúng ta thay vì giá trị trong một ô. Lần này, biến bộ lọc (strFilter) được điền trong chính mã (ví dụ: Được mã hóa cứng vào macro).
| 1234567 | Sub FilterTextValue ()Dim pvFld As PivotFieldDim strFilter As StringĐặt pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Nhà cung cấp")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterKết thúc Sub |
