Trong bài viết này, tôi sẽ thảo luận cách bạn có thể lọc dữ liệu Excel bằng danh sách thả xuống xác thực dữ liệu. Thông thường, trong Microsoft Excel, chúng ta sử dụng tùy chọn Bộ lọc để trích xuất dữ liệu cụ thể. Tuy nhiên, bạn có thể sử dụng danh sách thả xuống để lọc dữ liệu. Để thực hiện nhiệm vụ, ban đầu tôi sẽ tạo danh sách thả xuống bằng Xác thực dữ liệu trong Excel. Sau đó, dựa trên lựa chọn mục từ danh sách thả xuống, tôi sẽ lọc các hàng tương ứng.
Bạn có thể tải xuống sổ làm việc thực hành mà chúng tôi đã sử dụng để chuẩn bị bài viết này.
2 ví dụ áp dụng danh sách thả xuống xác thực dữ liệu Excel với bộ lọc
Giả sử, chúng ta có bộ dữ liệu chứa dữ liệu bán hàng theo khu vực của nhiều loại trái cây. Trong bài viết này, tôi sẽ tạo danh sách thả xuống xác thực dữ liệu của các khu vực được đề cập trong bộ dữ liệu. Sau đó, tôi sẽ sử dụng danh sách thả xuống để lấy dữ liệu bán hàng trái cây.
1. Lọc giá trị từ danh sách thả xuống xác thực dữ liệu bằng cột hỗ trợ
Trong phương pháp này, tôi sẽ thêm 3 cột hỗ trợ vào bộ dữ liệu chính. Sau đó, tôi sẽ lấy dữ liệu dựa trên lựa chọn từ danh sách thả xuống. Trước khi nhập công thức cột hỗ trợ, tôi sẽ tạo danh sách thả xuống chứa các Khu vực duy nhất. Làm theo các bước dưới đây để thực hiện nhiệm vụ.
Các bước:
- Trước khi tạo danh sách thả xuống, liệt kê tất cả các Khu vực duy nhất như bên dưới.
- Sau đó, nhấp vào ô nơi bạn muốn đặt danh sách thả xuống (ở đây là Ô H5).
- Từ Băng công cụ Excel, vào Dữ liệu > Công cụ dữ liệu > Xác thực dữ liệu > Xác thực dữ liệu.
- Kết quả, hộp thoại Xác thực dữ liệu sẽ xuất hiện. Sau đó, vào thẻ Cài đặt, chọn Danh sách từ phần Cho phép và chỉ định Nguồn. Sau đó nhấn OK.
- Sau khi nhấn OK, chúng ta nhận được danh sách thả xuống.
- Bây giờ, đến các cột hỗ trợ. Nhập công thức dưới đây vào cột hỗ trợ đầu tiên (tại Ô D5) sử dụng hàm ROWS. Nhấn Enter và sử dụng công cụ Kéo tay cầm điền (+) để sao chép công thức trên toàn bộ cột.
=ROWS($A5:A$5)
- Kết quả, chúng ta sẽ nhận được đầu ra như bên dưới.
- Tiếp theo, sử dụng công thức hàm IF sau cho cột hỗ trợ thứ 2 (Hỗ trợ 2).
=IF(C5=$H$5,D5,"")
- Và cho cột hỗ trợ thứ 3 (Hỗ trợ 3) sử dụng công thức dưới đây.
=IFERROR(SMALL($E$5:$E$14,D5),"")
Ở đây, hàm SMALL trả về giá trị nhỏ thứ k trong phạm vi E5:E14. Sau đó, hàm IFERROR trả về rỗng nếu kết quả của công thức SMALL là lỗi.
- Bây giờ, giả sử cho khu vực Baltimore, tôi muốn lọc tất cả dữ liệu bán hàng trái cây tương ứng. Để có kết quả mong đợi, nhập công thức dưới đây vào Ô J5 và nhấn Enter.
=IFERROR(INDEX($A$5:$C$14,$F5,COLUMNS($J$5:J5)),"")
Ở đây, hàm INDEX lấy dữ liệu dựa trên số hàng. Sau đó hàm COLUMNS trả về số cột trong phạm vi $J$5:J5. Cuối cùng, hàm IFERROR trả về rỗng nếu kết quả là lỗi.
- Sau khi nhập công thức trên, kết quả sẽ như sau. Kéo Kéo tay cầm điền sang phải để lấy tất cả dữ liệu trong một hàng.
- Sau đó, kéo Kéo tay cầm điền xuống như bên dưới và nhận được dữ liệu bán hàng trái cây cuối cùng cho khu vực Baltimore.
- Bây giờ, nếu bạn chọn khu vực Phoenix từ danh sách thả xuống, các hàng tương ứng với Phoenix sẽ được lọc như bên dưới.
Đọc thêm: Cách tạo danh sách thả xuống Excel cho xác thực dữ liệu (8 cách)
Bài đọc tương tự
- Danh sách thả xuống xác thực dữ liệu với VBA trong Excel (7 ứng dụng)
- Giá trị mặc định trong danh sách xác thực dữ liệu với Excel VBA (Macro và UserForm)
- Cách xóa khoảng trống từ danh sách xác thực dữ liệu trong Excel (5 phương pháp)
- [Đã sửa] Xác thực dữ liệu không hoạt động cho Sao chép Dán trong Excel (có giải pháp)
- Cách sử dụng công thức VLOOKUP tùy chỉnh trong xác thực dữ liệu Excel
2. Hàm FILTER Excel để trích xuất dữ liệu dựa trên danh sách thả xuống xác thực dữ liệu
Nếu bạn đang làm việc trong Excel 365, bạn có thể lọc dữ liệu bằng hàm FILTER. Trước khi bắt đầu quy trình, tôi đã chuyển đổi phạm vi dữ liệu thành bảng Excel bằng cách nhấn Ctrl + T. Điều này là vì, nếu bạn thêm bản ghi mới vào bảng, danh sách thả xuống sẽ được cập nhật theo dữ liệu mới thêm.
- Để dễ dàng thao tác, tôi sẽ đặt tên cho bảng mới tạo (giả sử, Bảng4).
Bây giờ hãy làm theo các bước dưới đây để thực hiện nhiệm vụ chính.
Các bước:
- Trước tiên, chúng ta sẽ tạo danh sách duy nhất các khu vực bằng hàm UNIQUE. Để làm điều đó, nhập công thức sau vào Ô F5 và nhấn Enter.
=SORT(UNIQUE(Table4[Area]))
Ở đây, tôi đã sử dụng hàm SORT cùng với hàm UNIQUE để sắp xếp dữ liệu Khu vực trên.
- Sau khi nhập công thức, đây là kết quả chúng ta nhận được. Công thức trên trả về dữ liệu duy nhất đã sắp xếp dưới dạng mảng (được khoanh vùng bằng màu xanh).
- Bây giờ tạo danh sách thả xuống tại Ô H5. Làm theo đường dẫn dưới đây để mở hộp thoại Xác thực dữ liệu: Dữ liệu > Công cụ dữ liệu > Xác thực dữ liệu > Xác thực dữ liệu. Từ hộp thoại đó, chọn Danh sách từ phần Cho phép và nhập công thức dưới đây vào trường Nguồn. Sau đó nhấn OK.
=F5#
Ở đây, ký hiệu # cho biết chúng ta đang xem xét toàn bộ mảng của Ô F5 làm nguồn cho danh sách thả xuống.
- Sau khi nhấn OK, danh sách thả xuống dưới đây sẽ được tạo.
- Bây giờ, giả sử tôi muốn lấy dữ liệu bán hàng trái cây cho khu vực Long Beach. Để có kết quả mong muốn, nhập công thức dưới đây vào Ô F11 và nhấn Enter.
=FILTER(Table4,Table4[Area]=H5,"Không tìm thấy dữ liệu")
- Cuối cùng, sau khi nhập công thức FILTER, chúng ta sẽ nhận được tất cả dữ liệu bán hàng cho khu vực Long Beach. Bạn có thể thay đổi khu vực từ danh sách thả xuống và do đó lọc các hàng tương ứng dựa trên khu vực được chọn.
Đọc thêm: Xác thực dữ liệu Excel dựa trên giá trị ô khác
Kết luận
Trong bài viết trên, tôi đã cố gắng thảo luận hai phương pháp để lọc dữ liệu bằng danh sách thả xuống xác thực dữ liệu trong Excel chi tiết. Hy vọng các phương pháp và giải thích này sẽ đủ để giải quyết vấn đề của bạn. Vui lòng cho tôi biết nếu bạn có bất kỳ thắc mắc nào.
Bài viết liên quan
- Xác thực dữ liệu Excel chỉ chữ và số (Sử dụng công thức tùy chỉnh)
- Áp dụng xác thực dữ liệu tùy chỉnh cho nhiều tiêu chí trong Excel (4 ví dụ)
- Tự động hoàn thành danh sách thả xuống xác thực dữ liệu trong Excel (2 phương pháp)
- Cách tạo danh sách xác thực dữ liệu từ bảng trong Excel (3 phương pháp)
- Tạo danh sách thả xuống xác thực dữ liệu với nhiều lựa chọn trong Excel
- Cách áp dụng nhiều xác thực dữ liệu trong một ô trong Excel (3 ví dụ)