Bảng điều khiển Excel - PivotTables

Nếu bạn có dữ liệu của mình trong một bảng Excel, bạn có thể tóm tắt dữ liệu theo cách được yêu cầu bằng Excel PivotTables. PivotTable là một công cụ cực kỳ mạnh mẽ mà bạn có thể sử dụng để chia nhỏ dữ liệu. Bạn có thể theo dõi, phân tích hàng trăm nghìn điểm dữ liệu với một bảng nhỏ gọn có thể thay đổi linh hoạt để cho phép bạn tìm các góc nhìn khác nhau của dữ liệu. Nó là một công cụ đơn giản để sử dụng, nhưng mạnh mẽ.

Excel cung cấp cho bạn một cách mạnh mẽ hơn để tạo PivotTable từ nhiều bảng, các nguồn dữ liệu khác nhau và các nguồn dữ liệu bên ngoài. Nó được đặt tên là Power PivotTable hoạt động trên cơ sở dữ liệu được gọi là Mô hình Dữ liệu. Bạn sẽ biết về Power PivotTable và các công cụ điện Excel khác như Power PivotChart và Báo cáo Power View trong các chương khác.

Báo cáo PivotTables, Power PivotTables, Power PivotCharts và Power View rất hữu ích để hiển thị kết quả tóm tắt từ tập dữ liệu lớn trên trang tổng quan của bạn. Bạn có thể thành thạo PivotTable thông thường trước khi tham gia vào các công cụ điện.

Tạo PivotTable

Bạn có thể tạo PivotTable từ một dải dữ liệu hoặc từ bảng Excel. Trong cả hai trường hợp, hàng đầu tiên của dữ liệu phải chứa tiêu đề cho các cột.

Bạn có thể bắt đầu với một PivotTable trống và xây dựng nó từ đầu hoặc sử dụng lệnh PivotTables được khuyến nghị của Excel để xem trước PivotTable có thể tùy chỉnh cho dữ liệu của bạn và chọn một cái phù hợp với mục đích của bạn. Trong cả hai trường hợp, bạn có thể sửa đổi PivotTable ngay lập tức để hiểu rõ hơn về các khía cạnh khác nhau của dữ liệu.

Hãy xem xét phạm vi dữ liệu sau chứa dữ liệu bán hàng cho từng Nhân viên bán hàng, ở từng Khu vực và trong các tháng từ tháng 1, tháng 2 và tháng 3 -

Để tạo PivotTable từ dải dữ liệu này, hãy thực hiện như sau:

Đảm bảo rằng hàng đầu tiên có tiêu đề. Bạn cần tiêu đề vì chúng sẽ là tên trường trong PivotTable của bạn.
Đặt tên cho dải dữ liệu là SalesData_Range.
Nhấp vào phạm vi dữ liệu - SalesData_Range.
Bấm vào tab CHÈN trên ruy-băng.
Bấm vào PivotTable trong nhóm Bảng.

Hộp thoại Tạo PivotTable xuất hiện.

Như bạn có thể quan sát, trong hộp thoại Tạo PivotTable, bên dưới Chọn dữ liệu mà bạn muốn phân tích, bạn có thể chọn Bảng hoặc Phạm vi từ sổ làm việc hiện tại hoặc sử dụng nguồn dữ liệu bên ngoài. Do đó, bạn có thể sử dụng các bước tương tự để tạo biểu mẫu PivotTable hoặc là Phạm vi hoặc Bảng.

Nhấp vào Chọn bảng hoặc phạm vi.
Trong hộp Bảng / Phạm vi, hãy nhập tên phạm vi - SalesData_Range.
Bấm vào Bảng tính Mới bên dưới Chọn nơi bạn muốn đặt báo cáo PivotTable.

Bạn cũng có thể nhận thấy rằng bạn có thể chọn phân tích nhiều bảng, bằng cách thêm dải dữ liệu này vào Mô hình Dữ liệu. Mô hình Dữ liệu là cơ sở dữ liệu Excel Power Pivot.

Nhấp vào nút OK. Một trang tính mới sẽ được chèn vào sổ làm việc của bạn. Trang tính mới chứa một PivotTable trống.
Đặt tên cho trang tính - Range-PivotTable.

Như bạn có thể quan sát, danh sách Trường PivotTable xuất hiện ở phía bên phải của trang tính, chứa tên tiêu đề của các cột trong phạm vi dữ liệu. Hơn nữa, trên Ribbon, Công cụ PivotTable - PHÂN TÍCH và THIẾT KẾ xuất hiện.

Bạn cần chọn các trường PivotTable dựa trên dữ liệu bạn muốn hiển thị. Bằng cách đặt các trường vào các khu vực thích hợp, bạn có thể có được bố cục mong muốn cho dữ liệu. Ví dụ: để tóm tắt số lượng đơn đặt hàng mà nhân viên bán hàng khôn ngoan trong các tháng - tháng 1, tháng 2 và tháng 3, bạn có thể làm như sau:

Bấm vào trường Người bán hàng trong danh sách Trường PivotTable và kéo nó vào vùng ROWS.
Bấm vào trường Tháng trong danh sách Trường PivotTable và kéo trường đó vào vùng ROWS.
Nhấp vào Số lượng đặt hàng và kéo nó đến khu vực ∑ VALUES.

PivotTable của bạn đã sẵn sàng. Bạn có thể thay đổi bố cục của PivotTable bằng cách kéo trường qua các vùng. Bạn có thể chọn / bỏ chọn các trường trong danh sách Trường PivotTable để chọn dữ liệu bạn muốn hiển thị.

Lọc dữ liệu trong PivotTable

Nếu bạn được yêu cầu tập trung vào một tập hợp con dữ liệu PivotTable của mình, bạn có thể lọc dữ liệu trong PivotTable dựa trên một tập hợp con các giá trị của một hoặc nhiều trường. Ví dụ trong ví dụ trên, bạn có thể lọc dữ liệu dựa trên trường Phạm vi để bạn có thể chỉ hiển thị dữ liệu cho (các) Vùng đã chọn.

Có một số cách để lọc dữ liệu trong PivotTable -

Lọc bằng Bộ lọc Báo cáo.
Lọc bằng Slicer.
Lọc dữ liệu theo cách thủ công.
Lọc bằng Bộ lọc Nhãn.
Lọc bằng Bộ lọc giá trị.
Lọc bằng Bộ lọc ngày.
Lọc bằng Bộ lọc 10 hàng đầu.
Lọc bằng Dòng thời gian.

Bạn sẽ biết cách sử dụng các Bộ lọc Báo cáo trong phần này và các Bộ lọc trong phần tiếp theo. Đối với các tùy chọn lọc khác, hãy tham khảo hướng dẫn Excel PivotTables.

Bạn có thể gán Bộ lọc cho một trong các trường để có thể thay đổi động PivotTable dựa trên các giá trị của trường đó.

Kéo vùng trường vào vùng LỌC.
Kéo trường Nhân viên bán hàng vào vùng ROWS.
Kéo trường Tháng vào vùng COLUMNS.
Kéo trường Số tiền đặt hàng vào vùng ∑ VALUES.

Bộ lọc có nhãn là Vùng xuất hiện phía trên PivotTable (trong trường hợp bạn không có hàng trống phía trên PivotTable của mình, PivotTable sẽ bị đẩy xuống để tạo khoảng trống cho Bộ lọc).

Như bạn có thể quan sát,

Giá trị nhân viên bán hàng xuất hiện trong các hàng.
Giá trị tháng xuất hiện trong các cột.
Bộ lọc Vùng xuất hiện ở trên cùng với mặc định được chọn là TẤT CẢ.
Giá trị tổng kết là Tổng số tiền đặt hàng.
- Tổng số tiền đặt hàng mà nhân viên bán hàng khôn ngoan xuất hiện trong cột Tổng số tiền.
- Tổng của Đơn hàng Số tiền Theo tháng xuất hiện trong hàng Tổng Tổng.
Bấm vào mũi tên trong Bộ lọc Vùng.

Danh sách thả xuống với các giá trị của trường Vùng xuất hiện.

Chọn hộp Chọn Nhiều Mục. Các hộp kiểm sẽ xuất hiện cho tất cả các giá trị. Theo mặc định, tất cả các hộp được chọn.
Bỏ chọn hộp (Tất cả). Tất cả các hộp sẽ không được chọn.
Chọn các hộp - Nam và Tây.

Nhấp vào nút OK. Dữ liệu chỉ liên quan đến các khu vực miền Nam và miền Tây sẽ được tóm tắt.

Như bạn có thể quan sát, trong ô bên cạnh Bộ lọc Vùng - (Nhiều Mục) được hiển thị, cho biết rằng bạn đã chọn nhiều hơn một giá trị. Nhưng có bao nhiêu giá trị và / hoặc giá trị nào không được biết từ báo cáo được hiển thị. Trong trường hợp này, sử dụng Slicer là một lựa chọn tốt hơn để lọc.

Sử dụng Slicer trong PivotTable

Lọc bằng Slicer có nhiều ưu điểm -

Bạn có thể có nhiều Bộ lọc bằng cách chọn các trường cho các Bộ lọc.
Bạn có thể trực quan hóa các trường mà Bộ lọc được áp dụng (một Bộ cắt cho mỗi trường).
Một Slicer sẽ có các nút biểu thị các giá trị của trường mà nó đại diện. Bạn có thể nhấp vào các nút của Trình cắt để chọn / bỏ chọn các giá trị trong trường.
Bạn có thể hình dung những giá trị nào của một trường được sử dụng trong Bộ lọc (các nút đã chọn được tô sáng trong Bộ cắt).
Bạn có thể sử dụng một Slicer chung cho nhiều PivotTable và / hoặc PivotCharts.
Bạn có thể ẩn / hiện một Slicer.

Để hiểu cách sử dụng Slicer, hãy xem xét PivotTable sau.

Giả sử bạn muốn lọc PivotTable này dựa trên các trường - Vùng và Tháng.

Bấm vào tab PHÂN TÍCH bên dưới CÔNG CỤ CÓ THỂ BỐ TRÍ trên Ruy-băng.
Bấm vào Chèn Slicer trong nhóm Bộ lọc.

Hộp thoại Chèn lát xuất hiện. Nó chứa tất cả các trường từ dữ liệu của bạn.

Chọn các hộp Khu vực và Tháng.

Nhấp vào nút OK. Các lát cắt cho mỗi trường đã chọn sẽ xuất hiện với tất cả các giá trị được chọn theo mặc định. Công cụ Slicer xuất hiện trên Ribbon để làm việc trên cài đặt Slicer, hãy xem và cảm nhận.

Như bạn có thể quan sát, mỗi Slicer có tất cả các giá trị của trường mà nó đại diện và các giá trị được hiển thị dưới dạng các nút. Theo mặc định, tất cả các giá trị của một trường được chọn và do đó tất cả các nút được đánh dấu.

Giả sử bạn muốn hiển thị PivotTable chỉ cho các vùng Nam và Tây và cho các tháng 2 và 3.

Nhấp vào Nam trong Bộ cắt Vùng. Chỉ Nam sẽ được đánh dấu trong Slicer - Region.
Giữ phím Ctrl và nhấp vào West trong Bộ cắt vùng.
Nhấp vào tháng Hai trong Bộ cắt tháng.
Giữ phím Ctrl và nhấp vào March trong Bộ cắt tháng. Các giá trị đã chọn trong Slice được tô sáng. PivotTable sẽ được tóm tắt cho các giá trị đã chọn.

Để thêm / xóa các giá trị của một trường khỏi bộ lọc, hãy nhấn giữ phím Ctrl và nhấp vào các nút đó trong Trình cắt tương ứng.