Bộ lọc danh sách thả xuống về cơ bản là một danh sách các tên duy nhất. Nếu 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 bạn. 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ị của ô trong Excel từng bước.
Bạn có thể tải xuống tệp Excel từ liên kết 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 một danh sách duy nhất. Sau đó, bạn có thể thực hiện phần còn lại dựa trên danh sách.
Vì vậy, trước tiên hãy tạo một danh sách các mục duy nhất.
Để tạo một danh sách các mặt hàng duy nhất miễn phí 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 khỏi Danh mục cột của bảng dữ liệu của tôi.
❷ Chọn dữ liệu để tạo một danh sách duy nhất, sau đó đi tới DỮ LIỆU> Xóa bản sao.
❸ Xóa bản sao hộp thoại sẽ xuất hiện. Đảm bảo rằng mọi thứ đều theo sở thích của bạn. Sau đó nhấn OK lệnh.
Vì vậy, bạn vừa tạo một danh sách các mặt hàng duy nhất. Bây giờ để thêm bộ lọc danh sách thả xuống,
❹ Chọn một ô, sau đó đi tới DATA> Data Validation> Data Validation.
Tiếp theo, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
❺ Từ Cài đặt , chọn Danh sách từ Cho phép hộp.
❻ Bao gồm phạm vi ô mà bạn đã tạo danh sách các mục duy nhất. Tùy chọn này có sẵn trong Nguồn hộp.
❼ Sau đó nhấn OK lệnh.
Cuối cùng, bạn sẽ nhận được bộ lọc danh sách thả xuống trong Excel như trong hình sau.
Đọc thêm: Cách tạo danh sách thả xuống có 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ì vậy, chúng tôi vừa thêm bộ lọc danh sách thả xuống trong Excel. Bây giờ, hãy lọc ra dữ liệu từ bảng dữ liệu hiện có của chúng tôi bằng cách sử dụng bộ lọc danh sách thả xuống mà chúng tôi vừa tạo.
Để làm được đ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 trợ giúp. Tôi đã đặt tên cho các cột đó là Hàng SL , Đã khớp và Đã đặt hàng tương ứng.
Cột Trình trợ giúp Đầu tiên:Hàng SL.
Trong cột này, chúng tôi sẽ lưu trữ số thứ tự của các hàng trong bảng dữ liệu của chúng tôi. Để làm điều đó,
❶ Chèn công thức sau vào ô F5 .
=ROWS($E$5:E5) Đối số cho ROWS hàm là một mảng. Ở đâu,
- $ E $ 5 là ô đầu tiên của SL hàng. Bạn có thể thêm ký hiệu đô la bằng cách nhấn F4 để khóa địa chỉ ô.
- E5 cũng là ô đầu tiên của SL hàng.
Vì vậy, công thức thực sự là gì, nó tính toán sự khác biệt từ ô $ E $ 5 đến E5 . Khi chúng tôi kéo Xử lý điền biểu tượng từ ô F5 đến ô F12 , $ E $ 5 vẫn cố định nhưng E5 thay đổi dần dần. Khoảng cách giữa hai địa chỉ ô không ngừng tăng lên. Do đó, chúng tôi nhận được số thứ tự của hàng trong bảng dữ liệu của chúng tôi.
💡 Lưu ý: Bạn có thể thêm số sê-ri theo cách thủ công nếu muốn.
❷ Sau đó nhấn ENTER để thực thi công thức.
❸ Kéo Tay cầm điền vào biểu tượng từ ô F5 đến ô F12 .
Cột trình trợ giúp thứ hai:Đã khớp
Trong cột này, chúng tôi chỉ muốn trả lại số sê-ri của những hàng khớp với mục đã chọn trong bộ lọc danh sách thả xuống trong Ô, 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 đã 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ị sẽ trả về nếu có sự trùng khớp giữa B5 và $ K $ 4.
- “” được sử dụng để trả về giá trị trống nếu không có kết quả phù hợp nào giữa B5 và $ K $ 4.
❷ Nhấn phím ENTER nút.
❸ Kéo Tay cầm điền vào biểu tượng từ ô G5 tới G12 .
Cột trình trợ giúp thứ ba:Có thứ tự
Trong cột trợ giúp thứ hai, Đã khớp số hàng có thể không xuất hiện trong các ô trở lại. Để đảm bảo rằng các số hàng xuất hiện trong ô này sau ô khác, hãy Có thứ tự cột sẽ cần.
Bây giờ,
❶ Trong ô H5 , hãy chèn công thức sau:
=IFERROR(SMALL($G$5:$G$12,F5),"") - $ G $ 5:$ G $ 12 là phạm vi của các ô trong đó NHỎ hàm sẽ tìm kiếm số nhỏ nhất.
- F5 giúp NHỎ hàm tìm các số nhỏ nhất liên tiếp. Vì nó chứa 1 và là F5 càng lớn thì số mà nó chứa tăng lên mỗi lần thêm 1.
- “” được sử dụng để giữ một ô trống với sự trợ giúp của IFERROR , nếu có bất kỳ lỗi nào xảy ra do giá trị được tìm kiếm bởi SMALL ở phía sau
❷ Để thực thi công thức, nhấn ENTER nút.
❸ Cuối cùng kéo Fill Handle biểu tượng từ ô H5 thành H12 .
Vậy là chúng ta đã hoàn thành với các cột cột trợ giúp.
Đọ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 đang 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 cách sử dụng Xóa nội dung yêu cầu. Bạn cũng có thể nhấn XÓA bằng cách chọn tất cả các ô trong bảng đã sao chép để làm như vậy.
❷ Trong ô đầu tiên của bảng dữ liệu đã sao chép, hãy 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 trợ giúp 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.
- “” được sử dụng để để trống tất cả các ô với sự trợ giúp của IFERROR chức năng, nếu không có dữ liệu cho mụ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 Fill Handle trên toàn bộ bảng dữ liệu đã sao chép để áp dụng công thức trên trong mỗi ô của bảng dữ liệu.
Đọc thêm: Danh sách thả xuống của 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 về 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 nên tải xuống sách bài tập thực hành đính kèm với bài viết này và thực hành tất cả các phương pháp với tài liệu đó. 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 trả lời tất cả các truy vấn có liên quan càng sớm càng tốt. Và vui lòng truy cập trang web của chúng tôi Exceldemy để khám phá thêm.
Các bài viết có 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 tạo 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 nhiều lựa chọn trong Excel