Hướng dẫn cách liên kết bộ lọc Pivot Table với một ô cụ thể trên Microsoft Excel

Trong Excel, bạn thường muốn tạo báo cáo tương tác, trong đó bộ lọc Pivot Table của bạn phản ánh giá trị trong một ô cụ thể. Điều này cho phép người dùng chọn hoặc nhập giá trị bộ lọc tại một vị trí và Pivot Table sẽ tự động cập nhật dựa trên dữ liệu đầu vào đó. Phương pháp này đặc biệt hữu ích khi thiết kế bảng thông tin hoặc giao diện bộ lọc tùy chỉnh để khám phá dữ liệu.

Bài viết này, Tri Thức Software cung cấp một số giải pháp thực tế, bao gồm phương pháp dựa trên VBA và các phương pháp Excel tích hợp khác, để giúp bạn liên kết bộ lọc Pivot Table với giá trị ô hoặc đạt được hiệu ứng báo cáo động tương tự.

1. Liên kết bộ lọc Pivot Table với một ô nhất định bằng VBA code

Nếu bạn cần liên kết trực tiếp nhất giữa một ô và bộ lọc Pivot Table để việc thay đổi giá trị của ô tự động cập nhật bộ lọc Pivot Table. VBA cung cấp một giải pháp thiết thực để đạt được điều này. Phương pháp này phù hợp với các bảng thông tin tương tác hoặc báo cáo mà người dùng muốn nhanh chóng kiểm soát các lát cắt dữ liệu từ một ô duy nhất.

Để kỹ thuật này hoạt động, Pivot Table của bạn phải chứa một trường lọc. Tên của trường lọc rất quan trọng để cấu hình mã VBA chính xác.

Hãy xem xét ví dụ sau: Pivot Table có một trường lọc có tên là Category, với hai giá trị lọc: “Expenses” và “Sales”. Bằng cách liên kết một ô với bộ lọc Pivot Table, bạn có thể kiểm soát dữ liệu hiển thị bằng cách nhập “Expenses” hoặc “Sales” vào ô bạn chọn.

Để thực hiện điều này:

• Chọn ô bạn muốn sử dụng làm bộ điều khiển bộ lọc (ví dụ: ô H6) và nhập trước một trong các giá trị bộ lọc. Đảm bảo giá trị này khớp chính xác với giá trị có sẵn trong trường bộ lọc của Pivot Table.
• Đi đến trang tính chứa Pivot Table của bạn. Nhấp chuột phải vào tab trang tính và chọn View Code từ menu. Thao tác này sẽ mở cửa sổ Visual Basic for Applications.

Trong cửa sổ Microsoft Visual Basic for Applications, dán đoạn code VBA sau vào ngăn code.

Mã VBA: Liên kết bộ lọc Bảng Pivot với một ô nhất định

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

Ghi chú:

Sau khi dán code, nhấn Alt + Q để đóng cửa sổ trình soạn thảo VBA và quay lại Excel.

Bây giờ, trạng thái bộ lọc của PivotTable của bạn được điều khiển bởi nội dung của ô H6. Chỉ cần thay đổi giá trị trong ô H6 (thành Sales hoặc Expenses) sẽ ngay lập tức cập nhật hiển thị của PivotTable. Nếu gặp bất kỳ sự cố nào, vui lòng kiểm tra lại xem giá trị ô được tham chiếu có khớp chính xác với giá trị bộ lọc trong PivotTable không, và các tên trong code của bạn đã được gán chính xác chưa.

Bất cứ khi nào bạn sửa đổi nội dung của ô, PivotTable sẽ làm mới dữ liệu đã lọc theo đó.

Lưu ý: Nếu giá trị trường bộ lọc trong ô không khớp chính xác với các mục có sẵn (bao gồm cả chữ viết hoa và khoảng cách), mã có thể không áp dụng bộ lọc như mong đợi. Luôn kiểm tra xem tên trường và tên bảng của bạn có được viết đúng chính tả trong code VBA hay không. Nếu bạn muốn sử dụng thiết lập này trên nhiều PivotTable, bạn có thể điều chỉnh thêm code hoặc mở rộng nó bằng cách sử dụng Loops.

2. Sử dụng công thức Excel (ví dụ: GETPIVOTDATA) kết hợp với tham chiếu bộ lọc báo cáo hoặc bộ lọc cắt

Mặc dù Excel không cung cấp một phương pháp công thức thuần túy để liên kết bộ lọc của Bảng Pivot trực tiếp với một ô, bạn có thể tạo báo cáo động và hiển thị các giá trị liên quan bằng các công thức như GETPIVOTDATA kết hợp với bộ lọc hoặc bộ lọc báo cáo. Giải pháp này hữu ích khi bạn muốn xây dựng bảng thông tin, nơi các giá trị tóm tắt được cập nhật tức thì dựa trên lựa chọn bộ lọc hoặc dữ liệu đầu vào của một ô khác, giúp việc phân tích dữ liệu trở nên tương tác hơn.

Các tình huống áp dụng bao gồm bảng báo cáo động, bảng thông tin hoặc tóm tắt so sánh, trong đó bạn muốn kết quả hiển thị tuân theo các lựa chọn của bộ lọc hoặc phản ánh dữ liệu liên quan đến nội dung của ô. Ưu điểm chính là phương pháp này hiệu quả khi hiển thị dữ liệu tóm tắt được cập nhật. Tuy nhiên, trạng thái bộ lọc thực tế của bảng xoay không thể được thiết lập theo chương trình chỉ bằng công thức ô.

Ví dụ: Hiển thị tóm tắt Pivot Table dựa trên giá trị ô

Giả sử bạn có Pivot Table tóm tắt doanh số theo Category (Sales, Expenses). Bạn có thể sử dụng GETPIVOTDATA để trích xuất giá trị phù hợp cho danh mục được chỉ định trong một ô.

1. Giả sử ô H6 chứa danh mục bạn muốn hiển thị (ví dụ: “Sales”). Nhập công thức sau vào ô tóm tắt (ví dụ: I6):

=GETPIVOTDATA("Sum of Amount",$B$4,"Category",H6)

2. Sau khi nhập công thức vào I6, nhấn Enter. Bây giờ, bất cứ khi nào bạn thay đổi H6 thành một danh mục hợp lệ (Expenses hoặc Sales), I6 sẽ ngay lập tức cập nhật để hiển thị tổng của danh mục đó, theo Pivot Table hiện tại.

Lưu ý: Mặc dù phương pháp này không thay đổi bộ lọc trong chính Pivot Table, nhưng nó vẫn hiển thị dữ liệu kết quả như thể được lọc theo ô, cung cấp màn hình động được liên kết với dữ liệu đầu vào của ô mục tiêu. Bạn cũng có thể sử dụng phương pháp này để tạo biểu đồ, bảng tóm tắt hoặc bảng thông tin.

Nếu công thức trả về lỗi #REF! hoặc #VALUE!, hãy kiểm tra xem tham chiếu ô của bạn có chính xác không, danh mục đã nhập có tồn tại trong Pivot Table của bạn không và tên trường/tổng có khớp chính xác không.