Bảng Excel Pivot - Lọc dữ liệu
Bạn có thể phải thực hiện phân tích chuyên sâu về một tập hợp con dữ liệu PivotTable của mình. Điều này có thể là do bạn có dữ liệu lớn và sự tập trung của bạn được yêu cầu vào một phần nhỏ hơn của dữ liệu hoặc bất kể kích thước của dữ liệu, sự tập trung của bạn là bắt buộc vào một số dữ liệu cụ thể. 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. Có một số cách để làm điều đó như sau:
- Lọc bằng Slicer.
- Lọc bằng Bộ lọc Báo cáo.
- 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ẽ tìm hiểu cách lọc dữ liệu bằng Slicer trong chương tiếp theo. Bạn sẽ hiểu việc lọc bằng các phương pháp khác được đề cập ở trên trong chương này.
Hãy xem xét PivotTable sau, trong đó bạn có vùng dữ liệu bán hàng tóm tắt khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.
Bộ lọc Báo cáo
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 từ Hàng sang Bộ lọc trong Vùng PivotTable.
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ẽ được đẩy xuống để tạo khoảng trống cho Bộ lọc.
Bạn sẽ quan sát thấy
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 hộp bên phải của Vùng lọc.
Một danh sách thả xuống với các giá trị của trường Vùng xuất hiện. Kiểm tra hộpSelect Multiple Items.
Theo mặc định, tất cả các hộp được chọn. Bỏ chọn hộp (All). Tất cả các hộp sẽ được bỏ chọn.
Sau đó đánh dấu vào các hộp - South and West và bấm OK.
Dữ liệu chỉ liên quan đến các khu vực Nam và Tây sẽ được tóm tắt.
Trong ô bên cạnh Vùng lọc - (Nhiều Mục) được hiển thị, cho biết rằng bạn đã chọn nhiều mục. Tuy nhiên, có bao nhiêu mục và / hoặc mục 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.
Lọc thủ công
Bạn cũng có thể lọc PivotTable bằng cách chọn các giá trị của trường theo cách thủ công. Bạn có thể thực hiện việc này bằng cách nhấp vào mũi tên
Giả sử bạn chỉ muốn phân tích dữ liệu tháng Hai. Bạn cần lọc các giá trị theo trường Tháng. Như bạn có thể quan sát, Tháng là một phần của Nhãn Cột.
Bấm vào mũi tên
Như bạn có thể quan sát, có một hộp Tìm kiếm trong danh sách thả xuống và bên dưới hộp, bạn có danh sách các giá trị của trường đã chọn, tức là Tháng. Các hộp của tất cả các giá trị được chọn, cho thấy rằng tất cả các giá trị của trường đó đã được chọn.
Bỏ chọn hộp (Chọn Tất cả) ở đầu danh sách các giá trị.
Chọn các hộp của các giá trị bạn muốn hiển thị trong PivotTable của mình, trong trường hợp này là Tháng Hai và bấm OK.
PivotTable chỉ hiển thị những giá trị có liên quan đến giá trị trường Tháng đã chọn - Tháng Hai. Bạn có thể quan sát thấy mũi tên lọc thay đổi thành biểu tượng
Bạn có thể quan sát thấy hiển thị cho biết rằng Bộ lọc Thủ công được áp dụng trên trường- Tháng.
Nếu bạn muốn thay đổi giá trị lựa chọn bộ lọc, hãy làm như sau:
Nhấp vào
biểu tượng.
Chọn / bỏ chọn hộp của các giá trị.
Nếu tất cả các giá trị của trường không hiển thị trong danh sách, hãy kéo chốt ở góc dưới bên phải của menu thả xuống để phóng to nó. Ngoài ra, nếu bạn biết giá trị, hãy nhập giá trị đó vào hộp Tìm kiếm.
Giả sử bạn muốn áp dụng một bộ lọc khác trên PivotTable đã lọc ở trên. Ví dụ: bạn muốn hiển thị dữ liệu của Walters, Chris cho tháng Hai. Bạn cần tinh chỉnh bộ lọc của mình bằng cách thêm một bộ lọc khác cho trường Nhân viên bán hàng. Như bạn có thể quan sát, Nhân viên bán hàng là một phần của Nhãn hàng.
Bấm vào mũi tên
trong ô Nhãn Hàng.
Danh sách các giá trị của trường - Vùng được hiển thị. Điều này là do, Khu vực nằm ở cấp độ bên ngoài của Nhân viên bán hàng trong thứ tự lồng nhau. Bạn cũng có một tùy chọn bổ sung - Chọn Trường. Bấm vào hộp Chọn Trường.
Nhấp vào Nhân viên bán hàng từ danh sách thả xuống. Danh sách các giá trị của trường - Nhân viên bán hàng sẽ được hiển thị.
Bỏ chọn (Chọn tất cả) và chọn Walters, Chris.
Bấm OK.
PivotTable chỉ hiển thị những giá trị có liên quan đến giá trị trường Tháng đã chọn - Tháng Hai và giá trị trường Người bán hàng - Walters, Chris.
Mũi tên lọc cho Nhãn Hàng cũng thay đổi thành biểu tượng
Một hộp văn bản được hiển thị cho biết rằng Bộ lọc Thủ công được áp dụng trên các trường - Tháng và Người bán hàng.
Do đó, bạn có thể lọc PivotTable theo cách thủ công dựa trên bất kỳ số lượng trường nào và trên bất kỳ số lượng giá trị nào.
Lọc theo văn bản
Nếu bạn có các trường chứa văn bản, bạn có thể lọc PivotTable theo Văn bản, miễn là nhãn trường tương ứng là dựa trên văn bản. Ví dụ: hãy xem xét dữ liệu Nhân viên sau đây.
Dữ liệu có thông tin chi tiết về các nhân viên - EmployeeID, Title, BirthDate, MaritalStatus, Gender và HireDate. Ngoài ra, dữ liệu còn có cấp quản lý của nhân viên (cấp 0 - 4).
Giả sử bạn phải thực hiện một số phân tích về số lượng nhân viên báo cáo cho một nhân viên nhất định theo chức danh. Bạn có thể tạo PivotTable như dưới đây.
Bạn có thể muốn biết có bao nhiêu nhân viên có 'Người quản lý' trong chức danh của họ có nhân viên báo cáo cho họ. Vì Tiêu đề Nhãn là văn bản, bạn có thể áp dụng Bộ lọc Nhãn trên trường Tiêu đề như sau:
Bấm vào mũi tên
trong ô Nhãn Hàng.
Chọn Tiêu đề trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp vào Bộ lọc Nhãn.
Nhấp vào Chứa trong danh sách thả xuống thứ hai.
Hộp thoại Bộ lọc Nhãn (Tiêu đề) xuất hiện. Nhập Trình quản lý vào hộp bên cạnh Chứa. Bấm OK.
PivotTable sẽ được lọc thành các giá trị Tiêu đề có chứa 'Trình quản lý'.
Nhấp vào
biểu tượng.
Bạn có thể thấy điều đó
- Bộ lọc Nhãn được áp dụng trên trường - Tiêu đề và
- Bộ lọc Nhãn được áp dụng là gì.
Lọc theo giá trị
Bạn có thể muốn biết chức danh của những nhân viên có hơn 25 nhân viên báo cáo cho họ. Đối với điều này, bạn có thể áp dụng Bộ lọc Giá trị trên trường Tiêu đề như sau:
Bấm vào mũi tên
trong ô Nhãn Hàng.
Lựa chọn Title trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp vào Bộ lọc giá trị.
Chọn Lớn hơn hoặc bằng từ danh sách thả xuống thứ hai.
Hộp thoại Bộ lọc Giá trị (Tiêu đề) xuất hiện. Nhập 25 vào ô bên phải.
PivotTable sẽ được lọc để hiển thị các chức danh nhân viên có hơn 25 nhân viên báo cáo cho họ.
Lọc theo ngày
Bạn có thể muốn hiển thị dữ liệu của tất cả nhân viên đã được thuê trong năm tài chính 2015-15. Bạn có thể sử dụng Bộ lọc dữ liệu tương tự như sau:
Bao gồm trường HireDate trong PivotTable. Bây giờ, bạn không yêu cầu dữ liệu người quản lý và vì vậy hãy xóa trường ManagerLevel khỏi PivotTable.
Bây giờ bạn có trường Ngày trong PivotTable, bạn có thể sử dụng Bộ lọc Ngày.
Bấm vào mũi tên
trong ô Nhãn Hàng.
Chọn Ngày thuê trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp vào Bộ lọc ngày.
Seelct Between từ danh sách thả xuống thứ hai.
Hộp thoại Bộ lọc Ngày (HireDate) xuất hiện. Nhập 4/1/2014 và 3/31/2015 vào hai hộp Ngày. Bấm OK.
PivotTable sẽ được lọc để chỉ hiển thị các dữ liệu với HireDate giữa 1 st tháng 4 năm 2014 và 31 st tháng 3 năm 2015.
Bạn có thể nhóm các ngày thành Quarters như sau:
Nhấp chuột phải vào bất kỳ ngày nào. CácGrouping hộp thoại xuất hiện.
Nhập 4/1/2014 vào hộp Bắt đầu lúc. Kiểm tra hộp.
Nhập 3/31/2015 vào hộp Kết thúc lúc. Kiểm tra hộp.
Nhấp vào Quarters trong hộp bên dưới By.
Các ngày sẽ được nhóm thành các phần tư trong PivotTable. Bạn có thể làm cho bảng trông nhỏ gọn bằng cách kéo trường HireDate từ vùng ROWS sang vùng COLUMNS.
Bạn sẽ có thể biết có bao nhiêu nhân viên đã được thuê trong năm tài chính, theo quý.
Lọc bằng 10 bộ lọc hàng đầu
Bạn có thể sử dụng Bộ lọc 10 hàng đầu để hiển thị một vài giá trị trên cùng hoặc một vài giá trị dưới cùng của một trường trong PivotTable.
Bấm vào mũi tên
trong ô Nhãn Hàng.
Nhấp vào Bộ lọc giá trị.
Nhấp vào Top 10 trong danh sách thả xuống thứ hai.
Hộp thoại 10 Bộ lọc (Tiêu đề) hàng đầu xuất hiện.
Trong hộp đầu tiên, nhấp vào Trên cùng (Bạn cũng có thể chọn Dưới cùng).
Trong hộp thứ hai, hãy nhập một số, chẳng hạn như, 7.
Trong hộp thứ ba, bạn có ba tùy chọn mà bạn có thể lọc.
Nhấp vào Mục để lọc theo số lượng mục.
Nhấp vào Phần trăm để lọc theo phần trăm.
Bấm vào Sum để lọc theo tổng.
Khi bạn có số lượng EmployeeID, hãy nhấp vào Mục.
Trong hộp thứ tư, bấm vào trường Số lượng ID nhân viên.
Bấm OK.
Bảy giá trị hàng đầu theo số lượng EmployeeID sẽ được hiển thị trong PivotTable.
Như bạn có thể quan sát, số lượng tuyển dụng cao nhất trong năm tài chính là Kỹ thuật viên sản xuất và số lượng chủ yếu trong số này là trong Qtr1.
Lọc bằng Dòng thời gian
Nếu PivotTable của bạn có trường ngày, bạn có thể lọc PivotTable bằng Dòng thời gian.
Tạo PivotTable từ Dữ liệu Nhân viên mà bạn đã sử dụng trước đó và thêm dữ liệu vào Mô hình Dữ liệu trong hộp thoại Tạo PivotTable.
Kéo Tiêu đề trường vào vùng ROWS.
Kéo trường EmployeeID đến vùng ∑ VALUES và chọn Số lượng để tính toán.
Bấm vào PivotTable.
Bấm vào tab CHÈN.
Nhấp vào Dòng thời gian trong nhóm Bộ lọc. Hộp thoại Chèn đường thời gian xuất hiện.
- Đánh dấu vào hộp HireDate.
- Bấm OK. Dòng thời gian xuất hiện trong trang tính.
- Công cụ Dòng thời gian xuất hiện trên Ribbon.
Như bạn có thể quan sát, Tất cả các Khoảng thời gian - tính bằng Tháng được hiển thị trên Dòng thời gian.
Bấm vào mũi tên bên cạnh - THÁNG.
Chọn QUARTERS từ danh sách thả xuống. Màn hình Dòng thời gian thay đổi thành Tất cả các khoảng thời gian - theo Quarters.
Nhấp vào Q1 2014.
Giữ phím Shift và kéo đến 2014 Q4. Khoảng thời gian được chọn là Q1 - Q4 năm 2014.
PivotTable được lọc theo Khoảng thời gian này.
Xóa bộ lọc
Thỉnh thoảng, bạn có thể phải xóa các bộ lọc bạn đã đặt để chuyển đổi giữa các kết hợp và phép chiếu khác nhau của dữ liệu. Bạn có thể làm điều này theo một số cách như sau:
Xóa tất cả các bộ lọc trong PivotTable
Bạn có thể xóa tất cả các bộ lọc được đặt trong PivotTable một lúc như sau:
- Bấm vào tab HOME trên ruy-băng.
- Bấm Sắp xếp & Lọc trong nhóm Chỉnh sửa.
- Chọn Xóa từ danh sách thả xuống.
Xóa nhãn, bộ lọc ngày hoặc giá trị
Để xóa Bộ lọc Nhãn, Ngày hoặc Giá trị, hãy làm như sau:
Bấm vào biểu tượng trong Nhãn Hàng hoặc Nhãn Cột.
Bấm vào
<tên trường> mà bạn muốn xóa bộ lọc trong hộp Chọn Trường trong danh sách thả xuống.
Nhấp vào Xóa bộ lọc khỏi <Tên đã nộp> xuất hiện trong danh sách thả xuống.
Bấm OK. Bộ lọc cụ thể sẽ bị xóa.
