Tính năng Bộ lọc trong Excel giúp chúng ta trích xuất dữ liệu theo lựa chọn. Nhưng vấn đề với tính năng này là số thứ tự hàng và tham chiếu ô vẫn không thay đổi. Việc tạo một bộ lọc thả xuống đơn giản sẽ giải quyết vấn đề này. Trong bài viết này, chúng tôi sẽ chỉ cho bạn các cách hiệu quả để Tạo Bộ lọc Thả xuống nhằm Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel.
Để minh họa, chúng tôi sẽ sử dụng một bộ dữ liệu mẫu làm ví dụ. Ví dụ, bộ dữ liệu sau đại diện cho Nhân viên bán hàng, Sản phẩm, và Doanh thu thuần của một công ty.
Tải xuống sổ làm việc sau để tự thực hành.
4 Cách tạo Bộ lọc Thả xuống để Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel
1. Tạo Bộ lọc Thả xuống để Trích xuất Dữ liệu Dựa trên Lựa chọn với Cột phụ trợ trong Excel
Trong phương pháp đầu tiên, chúng tôi sẽ giới thiệu 3 cột phụ trợ để tạo Bộ lọc Thả xuống. Do đó, hãy làm theo các bước dưới đây để thực hiện nhiệm vụ.
CÁC BƯỚC:
- Trước tiên, chọn ô D5 và nhập công thức:
=ROWS($B$5:B5)
- Sau đó, nhấn Enter và sử dụng công cụ Tự động điền để hoàn thành chuỗi.
- Sau đó, chọn ô G5 hoặc bất kỳ nơi nào bạn muốn tạo Bộ lọc Thả xuống.
- Tiếp theo, chọn Dữ liệu ➤ Công cụ Dữ liệu ➤ Kiểm tra dữ liệu.
- Kết quả là hộp thoại Kiểm tra dữ liệu sẽ xuất hiện.
- Dưới tab Cài đặt, chọn Danh sách trong Cho phép, và nhập TV, Điều hòa trong hộp Nguồn.
- Sau đó, nhấn OK.
- Do đó, nó sẽ tạo bộ lọc mong muốn.
- Bây giờ, chọn ô E5 và nhập công thức:
=IF(B5=$G$5,D5,"")
- Nhấn Enter.
- Sau đó, điền phần còn lại bằng Tự động điền.
- Tiếp theo, chọn F5. Nhập công thức:
=IFERROR(SMALL($E$5:$E$10,D5),"")
- Nhấn Enter.
- Lại sử dụng công cụ Tự động điền để hoàn thành phần còn lại.
- Tiếp theo, chọn ô I5 và nhập công thức:
=IFERROR(INDEX($A$5:$C$10,$F5,COLUMNS($I$5:I5)),"")
- Nhấn Enter.
Ở đây, hàm COLUMNS trả về số cột trong phạm vi $I$5:I5. Hàm INDEX trả về tham chiếu ô hoặc giá trị ô tại giao điểm của số hàng cho trong F5 và số cột cho trong I5. Hàm IFERROR trả về ô trống nếu tìm thấy lỗi trong biểu thức.
- Cuối cùng, sử dụng công cụ Tự động điền để hoàn thành chuỗi. Do đó, bạn sẽ nhận được dữ liệu mong muốn dựa trên lựa chọn của mình.
- Tương tự, chọn Điều hòa từ Bộ lọc Thả xuống, nó sẽ tự động cập nhật bộ dữ liệu.
Đọ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)
2. Hàm FILTER của Excel để Tạo Bộ lọc Thả xuống Trích xuất Dữ liệu Dựa trên Lựa chọn
Chúng tôi sẽ sử dụng hàm FILTER trong phương pháp này để Tạo Bộ lọc Thả xuống nhằm Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel. Vì vậy, hãy học các bước dưới đây để thực hiện thao tác.
CÁC BƯỚC:
- Trước tiên, chọn phạm vi A4:C10.
- Sau đó, dưới tab Chèn, chọn Bảng.
- Kết quả là một hộp thoại sẽ xuất hiện. Tại đó, chọn OK.
- Nó sẽ tự động tạo một bảng với tên Bảng1.
- Bây giờ, mở một trang trống và chọn ô B2. Nhập công thức:
=UNIQUE(Table1[Product])
- Nhấn OK và nó sẽ tràn ra các tên sản phẩm duy nhất.
- Sau đó, chọn ô E5 hoặc bất kỳ ô nào khác của trang chính.
- Sau đó, đi đến Dữ liệu ➤ Công cụ Dữ liệu ➤ Kiểm tra dữ liệu.
- Kết quả là hộp thoại sẽ bật ra.
- Dưới tab Cài đặt, chọn Danh sách trong Cho phép, và hộp Nguồn, nhập công thức:
=list!$B$2#
Ở đây, list là tên trang tính mới tạo. Nó sẽ tìm giá trị ô B2 trong trang list.
- Sau đó, chọn ô G5. Tại đây, nhập công thức:
=FILTER(Table1,Table1[Product]=E5)
- Nhấn Enter và nó sẽ tràn dữ liệu.
Ở đây, hàm FILTER lọc Bảng1 và trả về bộ dữ liệu khớp với ô E5.
- Bạn có thể thay đổi Bộ lọc Thả xuống thành Điều hòa và lấy dữ liệu dựa trên lựa chọn của bạn.
Đọc thêm: Tạo bộ lọc Excel sử dụng danh sách thả xuống dựa trên giá trị ô
Bài đọc tương tự:
- Cách sao chép danh sách thả xuống bộ lọc trong Excel (5 cách)
- Cách tạo danh sách thả xuống phụ thuộc có khoảng trắng trong Excel
- Tạo danh sách thả xuống có thể tìm kiếm trong Excel (2 phương pháp)
- Cách tạo danh sách thả xuống Excel có màu (2 cách)
- Danh sách thả xuống Excel không hoạt động (8 vấn đề và giải pháp)
3. Trích xuất Dữ liệu Dựa trên Lựa chọn bằng cách Tạo Bộ lọc Thả xuống với hàm INDIRECT của Excel
Chúng ta có thể kéo dữ liệu khi lựa chọn từ nhiều trang tính sử dụng hàm INDIRECT của Excel. Ví dụ, trong bộ dữ liệu sau, chúng ta có 2 trang: Trang1 và Trang2 chứa dữ liệu. Chúng ta sẽ trích xuất Tổng doanh thu dựa trên lựa chọn trang tính trong phương pháp này. Do đó, hãy làm theo quy trình để kéo giá trị Tổng doanh thu chỉ dựa trên lựa chọn của chúng ta.
CÁC BƯỚC:
- Ban đầu, chọn ô C4 trong trang tính nơi chúng ta muốn đặt dữ liệu trích xuất.
- Sau đó, chọn Dữ liệu ➤ Công cụ Dữ liệu ➤ Kiểm tra dữ liệu.
- Trong hộp thoại bật ra, chọn Danh sách trong Cho phép. Trong hộp Nguồn nhập công thức:
=$C$8:$C$9
- Sau đó, nhấn OK.
- Bây giờ, chọn ô C6 và nhập công thức:
=INDIRECT("'"&C4&"'!C11")
- Nhấn Enter và nó sẽ kéo Tổng doanh thu từ trang được đề cập trong ô C4.
- Cuối cùng, thay đổi trang bằng Bộ lọc Thả xuống. Bạn sẽ thấy thay đổi mong muốn trong ô C6.
Đọc thêm: Cách trích xuất dữ liệu dựa trên lựa chọn danh sách thả xuống trong Excel
4. VBA để Tạo Bộ lọc Thả xuống Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel
Chúng tôi sẽ áp dụng mã VBA trong phương pháp cuối cùng để Tạo Bộ lọc Thả xuống nhằm Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel. Do đó, hãy học quy trình để biết cách thực hiện nhiệm vụ.
CÁC BƯỚC:
- Trước tiên, chúng ta có dữ liệu trong trang vba1.
- Và chúng ta có Bộ lọc Thả xuống trong trang vba2. Bây giờ, chúng ta muốn lọc dữ liệu trong trang vba1 theo lựa chọn Thả xuống trong trang vba2.
- Tiếp theo, nhấp chuột phải vào trang vba2 như hình ảnh bên dưới. Tại đó, chọn Xem mã.
- Kết quả là cửa sổ Module sẽ bật ra.
- Sao chép mã dưới đây và dán vào đó.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Range("A2"), Target) Is Nothing Then Application.EnableEvents = False If Range("A2").Value = "" Then Worksheets("vba1").ShowAllData Else Worksheets("vba1").Range("A2").AutoFilter 1, Range("A2").Value End If Application.EnableEvents = True End If End Sub
- Sau đó, nhấn F5, và hộp thoại Macros sẽ bật ra. Tại đây, nhập VBA trong Tên Macro.
- Sau đó, nhấn Tạo.
- Lại nhấn F5 và chọn Chạy.
- Sau đó, đóng cửa sổ. Từ Bộ lọc Thả xuống chọn TV.
- Kết quả, bạn sẽ thấy dữ liệu đã lọc trong trang vba1.
- Tương tự, chọn Điều hòa từ Bộ lọc Thả xuống trong vba2. Nó sẽ trả về dữ liệu trích xuất.
Đọc thêm: VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)
Kết luận
Do đó, bạn sẽ có thể Tạo Bộ lọc Thả xuống để Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel với các phương pháp được mô tả ở trên. Hãy tiếp tục sử dụng chúng và cho chúng tôi biết nếu bạn có thêm cách nào để thực hiện nhiệm vụ. Đừng quên để lại bình luận, góp ý hoặc thắc mắc nếu bạn có bất kỳ điều gì trong phần bình luận bên dưới.
Các bài viết liên quan
- Cách tạo biểu mẫu với danh sách thả xuống trong Excel
- Ẩn hoặc Hiện cột dựa trên lựa chọn danh sách thả xuống trong Excel
- Cách tạo danh sách thả xuống phụ thuộc với nhiều từ trong Excel
- Loại bỏ mục đã sử dụng khỏi danh sách thả xuống trong Excel (2 phương pháp)
- Cách loại bỏ trùng lặp khỏi danh sách thả xuống trong Excel (4 phương pháp)