Bộ lọc danh sách thả xuống về cơ bản là danh sách các tên duy nhất. Khi bạn chọn bất kỳ mục nào từ danh sách thả xuống, bạn sẽ nhận được các mục tương ứng liên quan đến lựa chọn của mình. Trong bài viết này, bạn sẽ học cách tạo bộ lọc danh sách thả xuống dựa trên giá trị ô trong Excel từng bước.
Bạn có thể tải xuống tệp Excel từ đường dẫn sau và thực hành cùng với nó.
Các bước tạo bộ lọc Excel bằng danh sách thả xuống dựa trên giá trị ô
Bước 1: Tạo danh sách duy nhất để tạo bộ lọc danh sách thả xuống dựa trên giá trị ô trong Excel
Để tạo bộ lọc danh sách thả xuống, trước tiên bạn phải tạo danh sách duy nhất. Sau đó, bạn có thể thực hiện các bước còn lại dựa trên danh sách đó.
Vậy hãy tạo danh sách các mục duy nhất trước.
Để tạo danh sách mục không trùng lặp:
❶ Sao chép các mục từ bảng dữ liệu của bạn trước. Ví dụ, tôi đang tách các mục từ cột Danh mục của bảng dữ liệu.
❷ Chọn dữ liệu để tạo danh sách duy nhất, sau đó vào DỮ LIỆU > Xóa trùng lặp.
❸ Hộp thoại Xóa trùng lặp sẽ xuất hiện. Đảm bảo mọi thứ theo ý muốn của bạn. Sau đó nhấn lệnh OK.
Vậy bạn vừa tạo xong danh sách các mục duy nhất. Bây giờ để thêm bộ lọc danh sách thả xuống:
❹ Chọn một ô, sau đó vào DỮ LIỆU > Xác thực dữ liệu > Xác thực dữ liệu.
Tiếp theo, hộp thoại Xác thực dữ liệu sẽ xuất hiện.
❺ Từ thẻ Cài đặt, chọn Danh sách từ hộp Cho phép.
❻ Bao gồm phạm vi ô nơi bạn đã tạo danh sách mục duy nhất. Tùy chọn này nằm trong hộp Nguồn.
❼ Sau đó nhấn lệnh OK.
Cuối cùng, bạn sẽ có bộ lọc danh sách thả xuống trong Excel như hình dưới đây.
Đọc thêm: Cách tạo danh sách thả xuống với giá trị duy nhất trong Excel (4 phương pháp)
Bước 2: Làm cho bộ lọc danh sách thả xuống hoạt động
Vậy, chúng ta vừa thêm bộ lọc danh sách thả xuống trong Excel. Bây giờ hãy lọc dữ liệu từ bảng dữ liệu hiện có bằng bộ lọc danh sách thả xuống vừa tạo.
Để làm điều đó, chúng ta cần thêm 3 cột phụ vào bảng dữ liệu chính. Đây là các cột hỗ trợ. Tôi đặt tên cho các cột đó lần lượt là Số thứ tự hàng, Khớp và Đã sắp xếp.
Cột hỗ trợ thứ nhất: Số thứ tự hàng.
Trong cột này, chúng ta sẽ lưu số thứ tự của các hàng trong bảng dữ liệu. Để làm điều đó:
❶ Chèn công thức sau vào ô F5.
=ROWS($E$5:E5)Tham số cho hàm ROWS là một mảng. Trong đó:
- $E$5 là ô đầu tiên của Số thứ tự hàng. Bạn có thể thêm dấu đô la bằng cách nhấn phím F4 để khóa địa chỉ ô.
- E5 cũng là ô đầu tiên của Số thứ tự hàng.
Công thức thực tế tính sự khác biệt từ ô $E$5 đến E5. Khi kéo biểu tượng Kéo tay cầm điền từ ô F5 đến ô F12, $E$5 giữ nguyên nhưng E5 thay đổi dần dần. Khoảng cách giữa hai địa chỉ ô liên tục tăng. Do đó, chúng ta có số thứ tự hàng của bảng dữ liệu.
💡 Lưu ý: Bạn có thể thêm số thứ tự thủ công nếu muốn.
❷ Sau đó nhấn nút ENTER để thực thi công thức.
❸ Kéo biểu tượng Kéo tay cầm điền từ ô F5 đến ô F12.
Cột hỗ trợ thứ hai: Khớp
Trong cột này, chúng ta muốn trả về số thứ tự của chỉ những hàng khớp với mục được chọn trong bộ lọc danh sách thả xuống tại ô K4.
Để làm điều đó:
❶ Nhập công thức sau vào ô G5.
=IF(B5=$K$4,F5,"")Trong công thức trên:
- B5 là địa chỉ ô của mục đầu tiên để khớp với mục được chọn của bộ lọc danh sách thả xuống.
- $K$4 là địa chỉ ô của bộ lọc danh sách thả xuống.
- F5 là địa chỉ ô của giá trị trả về nếu có sự khớp giữa B5 và $K$4.
- "" dùng để trả về rỗng nếu không có sự khớp giữa B5 và $K$4.
❷ Nhấn nút ENTER.
❸ Kéo biểu tượng Kéo tay cầm điền từ ô G5 đến G12.
Cột hỗ trợ thứ ba: Đã sắp xếp
Ở cột hỗ trợ thứ hai, Khớp, các số hàng có thể không xuất hiện liên tiếp. Để đảm bảo các số hàng xuất hiện liên tiếp nhau, cột Đã sắp xếp là cần thiết.
Bây giờ:
❶ Tại ô H5, chèn công thức sau:
=IFERROR(SMALL($G$5:$G$12,F5),"")- $G$5:$G$12 là phạm vi ô nơi hàm SMALL sẽ tìm số nhỏ nhất.
- F5 giúp hàm SMALL tìm các số nhỏ nhất theo thứ tự. Vì nó chứa 1 và khi F5 lớn hơn, số chứa tăng thêm 1 mỗi lần.
- "" dùng để giữ ô trống với sự trợ giúp của hàm IFERROR, nếu xảy ra lỗi do hết giá trị được tìm kiếm bởi SMALL.
❷ Để thực thi công thức, nhấn nút ENTER.
❸ Cuối cùng kéo biểu tượng Kéo tay cầm điền từ ô H5 đến H12.
Vậy chúng ta đã xong với các cột hỗ trợ.
Đọc thêm: Cách tạo danh sách thả xuống với bộ lọc trong Excel (7 phương pháp)
Bước 3: Bộ lọc danh sách thả xuống hoạt động
Để làm cho bộ lọc danh sách thả xuống hoạt động:
❶ Sao chép bảng dữ liệu sang vị trí khác. Sau đó xóa tất cả nội dung khỏi nó bằng lệnh Xóa nội dung. Bạn cũng có thể nhấn phím DELETE bằng cách chọn tất cả ô trong bảng đã sao chép.
❷ Tại ô đầu tiên của bảng dữ liệu đã sao chép, chèn công thức sau:
=IFERROR(INDEX($B$5:$E$12,$G5,COLUMNS($M$5:M5)),"")- $B$5:$E$12 là phạm vi ô của bảng dữ liệu gốc.
- $G5 là ô đầu tiên của cột hỗ trợ thứ hai.
- $M$5:M5 là phạm vi ô của cột đầu tiên của bảng dữ liệu đã sao chép.
- "" dùng để để trống tất cả ô với sự trợ giúp của hàm IFERROR, nếu không có dữ liệu cho mục được chọn trong bộ lọc danh sách thả xuống.
❸ Nhấn ENTER để thực thi công thức.
❹ Kéo biểu tượng Kéo tay cầm điền khắp bảng dữ liệu đã sao chép để áp dụng công thức trên cho mọi ô của bảng dữ liệu.
Đọc thêm: Danh sách thả xuống Excel không hoạt động (8 vấn đề và giải pháp)
Kết luận
Tóm lại, chúng ta đã thảo luận quy trình từng bước để tạo bộ lọc danh sách thả xuống dựa trên giá trị ô trong Excel. Bạn được khuyên nên tải xuống sổ làm việc thực hành đính kèm bài viết này và thực hành tất cả các phương pháp với nó. Và đừng ngần ngại đặt bất kỳ câu hỏi nào trong phần bình luận bên dưới. Chúng tôi sẽ cố gắng phản hồi tất cả các truy vấn liên quan sớm nhất có thể. Vui lòng truy cập trang web Exceldemy để khám phá thêm.
Bài viết liên quan
- Cách tạo danh sách thả xuống trong Excel với nhiều lựa chọn
- Danh sách thả xuống phụ thuộc nhiều Excel VBA (3 cách)
- Cách thực hiện nhiều lựa chọn từ danh sách thả xuống trong Excel (3 cách)
- Tự động cập nhật danh sách thả xuống trong Excel (3 cách)
- Cách tạo hộp danh sách đa chọn trong Excel