Đồng bộ hóa nhiều Pivot table bằng Combo Box

Chú ý: Bài viết này dành cho các phiên bản Excel từ 2007 trở về trước, từ Excel 2010 trở đi chúng ta có thể sử dụng chức năng Slicer của Excel.

Khi có rất nhiều bảng Pivot cùng tạo từ 1 nguồn data, làm thế nào để khi cần lọc dữ liệu, thay vì mất thời gian lọc từng bảng Pivot đơn lẻ, chúng ta chỉ cần lọc bằng 1 bộ lọc chung duy nhất. Bài viết sau đây sẽ hướng dẫn các bạn làm điều đó  (download file đính kèm để dễ dàng hơn trong quá trình theo dõi)

Đầu tiên, tạo 1 mẫu data để sử dụng trong bài:

untitled1

Ta có 2 bảng Pivot như ở dưới, 2 bảng này đều sử dụng bộ lọc “Vung”, mục đích của chúng ta là tạo ra 1 bộ lọc chung để khi chọn “Vung” từ bộ lọc, cả 2 bảng Pivot sẽ tự động lọc theo giá trị đó .

Trước hết, nhập các giá trị cho bộ lọc chung: H5: H8 (nhập thêm giá trị “All” để có thể select All)untitled12

Tiếp theo, trên Tab Developer, mục Insert chọn Combo Box (Form Control), chọn vị trí bất kỳ để đặt Combo Boxuntitled3

Click chuột phải vào Combo Box vừa tạo, chọn Format Control, sẽ hiện ra bảng chọn như ở dưới:untitled4

Phần Input range ta chọn vùng H5:H8 vừa tạo, phần Cell link chọn 1 ô bất kỳ, chẳng hạn H2. Cell link sẽ hiện thị vị trí của giá trị bạn chọn trong list, ví dụ chọn vùng là “Trung” thì H2 sẽ hiển thị 3, “Bắc” là 1, “Nam” là 2…untitled11

Tại ô G2, nhập công thức =INDEX(H5:H8,H2,0), ô này có ý nghĩa rất quan trọng, nhằm mục đích trả giá trị đang chọn trong Combo Box về 1 ô trong Excel, sau đó chúng ta sẽ tạo 1 đoạn code macro để Refresh các bảng Pivot khi giá trị G2 thay đổi.

Nhấn tổ hợp phím Alt + F11 để hiện thị cửa sổ VBA, ở tab Insert, chọn Moduleuntitled6

 

Sử dụng 1 đoạn code rất đơn giản như sau:untitled7

Trong đó:

  • Table1, Table2: tên của các bảng Pivot cần đồng bộ hóa (xem tên của bảng Pivot bằng cách click chuột phải vào bảng Pivot, chọn “PivotTable Option”)
  • “Vung”: tên của trường dùng làm bộ lọc chung
  • Range(“G2”).text: các bảng Pivot sẽ được lọc theo giá trị của ô G2

Save lại đoạn macro vừa viết, quay trở lại Excel, click chuột phải vào Combo Box chọn Assign Macro, 1 cửa sổ sẽ hiện ra như hình dưới –> chọn macro vừa tạo (ở đây là “Refresh”) , click OK ta đã có bộ lọc cần tìm.  Trường hợp các bảng Pivot ở các sheet khác nhau, chúng ta chỉ cần thay đoạn “Activesheet” bằng tên Sheet chứa bảng Pivot cần lọc.untitled8

Ngoài ra, chức năng Combo Box còn rất hữu ích trong việc tạo Interactive Dashboard (Dashboard tương tác với người dùng), điều này sẽ được đề cập trong những bài sắp tới.

Download Attachments

Comment ( 1 )

  1. / ReplyCường
    Đúng thật. Chức năng này dùng trên 2013, có cái Slicer là xong.

Gửi nhận xét