Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Trong bài viết này, tôi sẽ thảo luận về cách bạn có thể lọc dữ liệu excel bằng cách sử dụng Xác thực dữ liệu danh sách thả xuống. Thông thường, trong Microsoft Excel , chúng tôi sử dụng Bộ lọc tùy chọn để 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 tác vụ, ban đầu, tôi sẽ tạo danh sách thả xuống bằng cách sử dụng Xác thực dữ liệu trong excel. Sau đó, dựa trên lựa chọn mục thả xuống, tôi sẽ lọc ra các hàng tương ứng.

Bạn có thể tải xuống sách bài tập 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

Hãy xem xét, chúng tôi có một tập dữ liệu chứa dữ liệu bán hàng theo khu vực của một số loại trái cây. Trong bài viết này, tôi sẽ tạo Xác thực dữ liệu danh sách thả xuống của các khu vực được đề cập trong tập dữ liệu. Sau đó, tôi sẽ sử dụng danh sách thả xuống để vẽ dữ liệu bán 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ách sử dụng cột trình trợ giúp

Trong phương pháp này, tôi sẽ thêm 3 cột trợ giúp cho tập dữ liệu chính. Sau đó, tôi sẽ vẽ dữ liệu dựa trên lựa chọn thả xuống. Trước khi nhập công thức trợ giúp, tôi sẽ tạo một 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 tác vụ.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Các bước:

Trước khi tạo danh sách thả xuống, hãy liệt kê tất cả các Khu vực duy nhất như bên dưới.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Sau đó, nhấp vào ô mà bạn muốn tìm danh sách thả xuống (tại đây Ô H5 ).

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Từ Ruy-băng Excel , đi tới Dữ liệu > Công cụ dữ liệu > Xác thực dữ liệu > Xác thực dữ liệu .

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện. Sau đó, đi tới Cài đặt , chọn Danh sách từ Cho phép và chỉ định Nguồn . Sau đó, nhấn OK .

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Khi nhấn OK , tại đây, chúng tôi nhận được danh sách thả xuống.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Bây giờ, hãy đến với các cột trợ giúp. Nhập công thức bên dưới vào cột trợ giúp đầu tiên (trong Ô D5 ) bằng cách sử dụng chức năng ROWS . Nhấn Enter và sử dụng Xử lý điền ( + ) để sao chép công thức trên toàn bộ cột.

=ROWS($A5:A$5)

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Do đó, chúng tôi sẽ nhận được kết quả bên dưới.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Tiếp theo, sử dụng hàm IF sau công thức cho cột trợ giúp thứ 2 ( Người trợ giúp 2 ).

=IF(C5=$H$5,D5,"")

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Và đối với cột người trợ giúp thứ 3 ( Người trợ giúp 3 ) sử dụng công thức dưới đây.

=IFERROR(SMALL($E$5:$E$14,D5),"")

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Đây, chức năng NHỎ trả về giá trị nhỏ nhất thứ k trong phạm vi E5:E14 . Sau đó, hàm IFERROR trả về trống nếu kết quả của NHỎ công thức là một lỗi.

Bây giờ, giả sử cho Baltimore khu vực, tôi muốn lọc tất cả dữ liệu bán trái cây tương ứng. Để nhận được kết quả mong đợi, hãy nhập công thức bên dưới vào Ô J5 và nhấn Enter .

=IFERROR(INDEX($A$5:$C$14,$F5,COLUMNS($J$5:J5)),"")

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Đây, hàm INDEX vẽ dữ liệu dựa trên số wow. Sau đó, hàm COLUMNS trả về số cột trong phạm vi $ J $ 5:J5 . Cuối cùng, IFERROR hàm trả về trống nếu kết quả là lỗi.

Sau khi bạn nhập công thức trên, sau đây sẽ là kết quả. Kéo Xử lý điền ở bên phải để nhận tất cả dữ liệu liên tiếp.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Sau đó, kéo Xử lý điền xuống như bên dưới và nhận dữ liệu bán trái cây cuối cùng cho Baltimore khu vực.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Bây giờ, nếu bạn chọn Phoenix khu vực 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.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Đọc thêm: Cách tạo danh sách thả xuống trong Excel để 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 khỏi Danh sách Xác thực Dữ liệu trong Excel (5 Phương pháp)
[Đã sửa lỗi] Xác thực dữ liệu không hoạt động để sao chép dán trong Excel (với 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 trong 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 chức năng FILTER . Trước khi bắt đầu quá 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à do, 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 được thêm vào.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Để dễ thao tác, tôi sẽ đặt tên cho bảng mới được tạo (giả sử Table4 ).

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Bây giờ, hãy làm theo các bước dưới đây để thực hiện tác vụ chính.

Các bước:

Đầu tiên, chúng tôi sẽ tạo một danh sách các khu vực duy nhất bằng cách sử dụng hàm UNIQUE . Để làm điều đó, hãy nhập công thức sau vào Ô F5 và nhấn Enter .

=SORT(UNIQUE(Table4[Area]))

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Ở đây, tôi đã sử dụng hàm SORT cùng với DUY NHẤT chức năng sắp xếp Khu vực ở trên dữ liệu.

Sau khi nhập công thức, đây là kết quả chúng tôi nhận được. Công thức trên trả về dữ liệu duy nhất được sắp xếp dưới dạng một mảng (được tô màu xanh lam).

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Bây giờ, hãy tạo danh sách thả xuống trong Ô H5 . Thực hiện theo đường dẫn bên dưới để đưa ra Xác thực dữ liệu hộp thoại: 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ừ Cho phép và nhập công thức bên dưới vào Nguồn đồng ruộng. Sau đó nhấn OK .

=F5#

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Đây, # biểu tượng cho biết chúng tôi đang xem xét toàn bộ mảng Ô F5 làm nguồn cho danh sách thả xuống.

Sau khi bạn nhấn OK , danh sách thả xuống bên dưới sẽ được tạo.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Bây giờ, hãy xem xét, tôi muốn vẽ dữ liệu bán trái cây cho Long Beach diện tích. Để nhận được kết quả mong muốn, hãy nhập công thức bên dưới vào Ô F11 và nhấn Enter .

=FILTER(Table4,Table4[Area]=H5,"No Data Found")

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Cuối cùng, khi vào FILTER công thức, chúng tôi sẽ nhận được tất cả dữ liệu bán hàng cho Long Beach diện tích. 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 đã chọn.

Danh sách thả xuống xác thực dữ liệu Excel với bộ lọc (2 ví dụ)

Đọ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 về hai phương pháp để lọc dữ liệu bằng cách sử dụng Xác thực dữ liệu danh sách thả xuống trong excel công phu. Hy vọng rằng, những 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.

Các bài viết có liên quan

Chỉ xác thực dữ liệu Excel bằng 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ụ)
Danh sách thả xuống xác thực dữ liệu tự động hoàn thành 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ụ)