Lọc nhiều PivotTables đồng thời

Khi tạo các báo cáo phức tạp và đặc biệt là các bảng điều khiển trong Microsoft Excel, thường rất cần thiết phải lọc đồng thời một số bảng tổng hợp cùng một lúc. Hãy xem làm thế nào điều này có thể được thực hiện.

Phương pháp 1: Trình cắt ghép chung để lọc các trục trên cùng một nguồn dữ liệu

Nếu các trục được xây dựng trên cơ sở một bảng dữ liệu nguồn, thì cách dễ nhất là sử dụng chúng để lọc chúng đồng thời phần là một bộ lọc nút đồ họa được kết nối với tất cả các bảng tổng hợp cùng một lúc.

Để thêm nó, hãy chọn bất kỳ ô nào trong một trong các bản tóm tắt và trên tab nghiên cứu chọn đội Dán lát (Phân tích - Chèn bộ cắt). Trong cửa sổ mở ra, hãy chọn hộp cho các cột mà bạn muốn lọc dữ liệu và nhấp vào OK:

Theo mặc định, bộ cắt đã tạo sẽ chỉ lọc trục mà nó được tạo. Tuy nhiên, bằng cách sử dụng nút Báo cáo kết nối (Báo cáo kết nối) chuyển hướng Slice (Lát) chúng ta có thể dễ dàng thêm các bảng tóm tắt khác vào danh sách các bảng đã lọc:

Phương pháp 2. Phần chung để lọc tóm tắt trên các nguồn khác nhau

Nếu các trục của bạn được tạo không theo một mà theo các bảng dữ liệu nguồn khác nhau, thì phương pháp trên sẽ không hoạt động, bởi vì trong cửa sổ Báo cáo kết nối chỉ những bản tóm tắt được tạo từ cùng một nguồn mới được hiển thị.

Tuy nhiên, bạn có thể dễ dàng vượt qua hạn chế này nếu sử dụng Mô hình Dữ liệu (chúng tôi đã thảo luận chi tiết trong bài viết này). Nếu chúng tôi tải các bảng của mình vào Mô hình và liên kết chúng ở đó, thì việc lọc sẽ áp dụng cho cả hai bảng cùng một lúc.

Giả sử rằng chúng ta có hai bảng cho chi phí bán hàng và vận chuyển làm dữ liệu đầu vào:

Giả sử rằng chúng ta phải đối mặt với nhiệm vụ xây dựng bản tóm tắt của riêng mình cho từng người trong số họ và sau đó lọc chúng đồng thời theo các thành phố với mức cắt chung.

Chúng tôi làm như sau:

1. Biến Bảng gốc của chúng tôi thành Bảng thông minh động với phím tắt Ctrl+T hoặc lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng) và đặt tên cho họ bảngProdaji и tabVận chuyển chuyển hướng xây dựng (Thiết kế).

2. Lần lượt tải cả hai bảng vào Mô hình bằng nút Thêm vào mô hình dữ liệu trên tab Power Pivot.

Sẽ không thể liên kết trực tiếp các bảng này trong Mô hình, vì trong khi Power Pivot chỉ hỗ trợ mối quan hệ một-nhiều, tức là yêu cầu một trong các bảng không có bản sao trong cột mà chúng ta đang liên kết. Chúng tôi có giống nhau trong cả hai bảng trong lĩnh vực này City có sự lặp lại. Vì vậy, chúng ta cần tạo một bảng tra cứu trung gian khác với danh sách các tên thành phố duy nhất từ ​​cả hai bảng. Cách dễ nhất để thực hiện việc này là sử dụng chức năng bổ trợ Power Query, đã được tích hợp sẵn trong Excel từ phiên bản 2016 (và đối với Excel 2010-2013, nó được tải xuống miễn phí từ trang web của Microsoft).

3. Sau khi chọn bất kỳ ô nào bên trong bảng “thông minh”, chúng tôi tải từng ô một trong Power Query bằng nút Từ bảng / phạm vi chuyển hướng Ngày (Dữ liệu - Từ bảng / phạm vi) và sau đó trong cửa sổ Power Query, chọn bật Chính đội Đóng và tải - Đóng và tải vào (Trang chủ - Đóng & Tải - Đóng & Tải vào…) và tùy chọn nhập khẩu Chỉ cần tạo kết nối (Chỉ tạo kết nối):

4. Chúng tôi nối cả hai bảng thành một bằng lệnh Dữ liệu - Kết hợp các truy vấn - Thêm (Dữ liệu - Kết hợp các truy vấn - Nối). Các cột có cùng tên trong tiêu đề sẽ khớp với nhau (giống như một cột City), và những cái không khớp sẽ được đặt trong các cột khác nhau (nhưng điều này không quan trọng đối với chúng tôi).

5. Xóa tất cả các cột ngoại trừ cột Citybằng cách nhấp chuột phải vào tiêu đề của nó và chọn lệnh Xóa các cột khác (Xóa các cột khác) và sau đó loại bỏ tất cả các tên thành phố trùng lặp bằng cách bấm chuột phải vào tiêu đề cột một lần nữa và chọn lệnh Loại bỏ các bản sao (Loại bỏ các bản sao):

6. Danh sách tham chiếu đã tạo được tải lên Mô hình dữ liệu qua Trang chủ - Đóng và tải - Đóng và tải vào (Trang chủ - Đóng & Tải - Đóng & Tải vào…) và chọn tùy chọn Chỉ cần tạo kết nối (Chỉ tạo kết nối) và điều quan trọng nhất! - bật hộp kiểm Thêm dữ liệu này vào mô hình dữ liệu (Thêm dữ liệu này vào Mô hình Dữ liệu):

7. Bây giờ chúng ta có thể quay trở lại cửa sổ Power Pivot (tab powerpivot - cái nút Quản lý), chuyển sang Xem biểu đồ (Xem sơ đồ) và liên kết các bảng bán hàng và chi phí vận chuyển của chúng tôi thông qua thư mục trung gian đã tạo của các thành phố (bằng cách kéo các trường giữa các bảng):

8. Bây giờ bạn có thể tạo tất cả các bảng tổng hợp cần thiết cho mô hình đã tạo bằng cách sử dụng nút bảng tóm tắt (Bảng tổng hợp) on Chính (Trang Chủ) trong cửa sổ Power Pivot và bằng cách chọn bất kỳ ô nào trong bất kỳ tổng hợp nào, trên tab nghiên cứu thêm nút lát Dán lát (Phân tích - Chèn Slicer) và chọn cắt trong hộp danh sách City trong thư mục đã thêm:

Bây giờ, bằng cách nhấp vào nút quen thuộc Báo cáo kết nối on Tab Slice (Slicer - Báo cáo kết nối) chúng ta sẽ thấy tất cả bản tóm tắt của mình, bởi vì chúng hiện được xây dựng trên các bảng nguồn có liên quan. Nó vẫn còn để kích hoạt các hộp kiểm bị thiếu và nhấp vào OK - và bộ cắt của chúng tôi sẽ bắt đầu lọc tất cả các bảng tổng hợp đã chọn cùng một lúc.