Bộ lọc nâng cao của Excel là một công cụ mạnh mẽ cung cấp tính linh hoạt ngoài chức năng lọc tiêu chuẩn, cho phép người dùng thực hiện các tác vụ lọc phức tạp một cách hiệu quả. Hướng dẫn này xem xét kỹ lưỡng tính năng Bộ lọc nâng cao của Excel, so sánh nó với các bộ lọc thông thường, cung cấp các ví dụ thực tế và đưa ra những cân nhắc để sử dụng tối ưu. Nó sẽ giúp bạn tự tin sử dụng thành thạo các bộ lọc nâng cao.
Bộ lọc nâng cao so với bộ lọc thông thường
Sự khác biệt chính giữa bộ lọc thông thường của Excel và Bộ lọc nâng cao nằm ở độ phức tạp và chức năng của chúng. Mặc dù bộ lọc thông thường cung cấp tính năng lọc dựa trên tiêu chí một cột, đơn giản trong tập dữ liệu gốc, nhưng Bộ lọc nâng cao vượt quá các giới hạn này bằng cách:
-
Cho phép sử dụng nhiều tiêu chí trên nhiều cột khác nhau.
-
Cung cấp khả năng trích xuất các giá trị duy nhất từ tập dữ liệu.
-
Cho phép sử dụng ký tự đại diện để khớp một phần, linh hoạt hơn.
-
Cho phép trích xuất dữ liệu đã lọc đến một vị trí riêng.
Ví dụ về sử dụng bộ lọc nâng cao
Phần này sẽ cung cấp các ví dụ thực tế khác nhau để chỉ cho bạn cách sử dụng các bộ lọc nâng cao trong Excel để đạt được các hiệu ứng lọc khác nhau.
Trích xuất một danh sách duy nhất
Bộ lọc nâng cao của Excel có thể nhanh chóng tạo danh sách các giá trị duy nhất từ tập dữ liệu, một tác vụ có thể phức tạp với các bộ lọc thông thường. Nếu bạn có danh sách các giao dịch bán hàng có hàng trùng lặp và muốn trích xuất danh sách các hàng duy nhất, tính năng Bộ lọc nâng cao trong Excel có thể đơn giản hóa tác vụ này. Hãy làm theo hướng dẫn dưới đây để thực hiện điều này.
1. Tới tab Date, chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, bạn cần cấu hình như sau.
– Trong Action hãy chọn một tùy chọn bạn cần. Vì muốn định vị danh sách duy nhất ở một nơi khác nên chọn Copy to another location tùy chọn.
– Chỉ định phần List Range:
- Trích xuất giá trị duy nhất từ một cột: Chọn cột chứa các giá trị mà bạn muốn trích xuất các mục duy nhất. Ví dụ: để trích xuất tên khách hàng duy nhất trong trường hợp này, hãy chọn A1:A11.
-
Trích xuất các hàng duy nhất dựa trên nhiều cột: Chọn phạm vi bao gồm tất cả các cột bạn đang xem xét. Trong trường hợp này, vì muốn trích xuất các hàng duy nhất dựa trên Tên Khách hàng, Doanh số và Khu vực, chọn toàn bộ phạm vi A1:C11.
– Trong Copy to hãy chỉ định nơi bạn muốn dán danh sách duy nhất.
– Kiểm tra Unique records only.
– Nhấn vào OK. Xem ảnh chụp màn hình:
Kết quả
Như được hiển thị trong ảnh chụp màn hình bên dưới, các hàng duy nhất được trích xuất từ phạm vi dữ liệu gốc.
Lọc trong một cột với nhiều tiêu chí (khớp với bất kỳ tiêu chí nào)
Lọc dữ liệu trong một cột với nhiều tiêu chí cho phép bạn hiển thị các hàng đáp ứng bất kỳ điều kiện nào bạn chỉ định. Điều này có thể đặc biệt hữu ích khi bạn đang làm việc với các tập dữ liệu lớn và cần thu hẹp thông tin dựa trên một số kết quả phù hợp có thể xảy ra. Đây là cách bạn có thể đạt được điều này bằng tính năng Bộ lọc nâng cao của Excel:
Bước 1: Chuẩn bị dữ liệu phạm vi danh sách ban đầu
Đảm bảo tập dữ liệu phạm vi danh sách của bạn có tiêu đề cột rõ ràng, vì những tiêu đề này sẽ rất quan trọng để thiết lập phạm vi tiêu chí. Ở đây, tôi đang sử dụng bảng điểm của học sinh sau đây làm ví dụ.
Bước 2: Thiết lập phạm vi tiêu chí
1. Trong phạm vi phía trên hoặc ngoài phạm vi danh sách, hãy tạo phạm vi tiêu chí của bạn. Tiêu đề bạn nhập vào phạm vi tiêu chí phải khớp chính xác với tiêu đề trong phạm vi danh sách để hoạt động chính xác. Ở đây phạm vi tiêu chí nằm phía trên phạm vi danh sách.
2. Bên dưới tiêu đề, liệt kê từng tiêu chí bạn muốn khớp. Mỗi tiêu chí phải nằm trong ô riêng của nó, ngay bên dưới tiêu chí trước đó. Thiết lập này yêu cầu Excel phù hợp với bất kỳ tiêu chí nào trong số này.
Trong ví dụ này, đang tìm kiếm những sinh viên có điểm lớn hơn 95 hoặc nhỏ hơn 60 để có thể lọc phạm vi danh sách một cách hiệu quả để bao gồm cả học sinh có điểm cao và điểm thấp. Vì vậy, nhập từng tiêu chí vào các hàng riêng biệt dưới tiêu đề Điểm. Toàn bộ phạm vi tiêu chí được hiển thị dưới đây:
Bước 3: Áp dụng Bộ lọc nâng cao
Bây giờ bạn có thể áp dụng bộ lọc nâng cao để hoàn thành nhiệm vụ như sau.
1. Tới Tab Date và chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, bạn cần cấu hình như sau.
– Trong Action hãy chọn một tùy chọn bạn cần. Ở đây vì muốn định vị kết quả đã lọc đến một nơi khác, chọn Copy to another location tùy chọn.
– Trong List Range, chọn toàn bộ phạm vi danh sách A7: D17.
– Trong Criteria range, chọn toàn bộ phạm vi tiêu chí A2: D4.
– Trong Copy to, chỉ định nơi bạn muốn dán kết quả đã lọc (ở đây chọn ô F8).
– Nhấp OK để áp dụng bộ lọc. Xem ảnh chụp màn hình:
Kết quả
Sau đó, bạn có thể thấy rằng chỉ những hàng có cột “Điểm” khớp với bất kỳ tiêu chí nào (>95 hoặc <60) mới được trích xuất.
Lọc trong nhiều cột với nhiều tiêu chí
Sau khi đã đề cập đến việc lọc nhiều tiêu chí trong một cột, giờ đây chuyển sự chú ý sang lọc nhiều cột. Phần này sẽ hướng dẫn bạn áp dụng nhiều tiêu chí trên các cột khác nhau bằng cách sử dụng logic AND, OR và AND/OR kết hợp.
-
Để áp dụng logic AND, hãy đặt tiêu chí trên cùng một hàng.
-
Để áp dụng logic OR, hãy đặt tiêu chí trên các hàng riêng biệt.
Với logic AND (phù hợp với tất cả các tiêu chí)
Lọc dữ liệu trong nhiều cột với nhiều tiêu chí bằng logic AND có nghĩa là mỗi hàng phải đáp ứng tất cả tiêu chí đã chỉ định trên các cột khác nhau mới được hiển thị. Dưới đây là cách thực hiện việc này bằng Bộ lọc nâng cao của Excel:
Bước 1: Chuẩn bị dữ liệu phạm vi danh sách ban đầu
Đảm bảo tập dữ liệu phạm vi danh sách của bạn có tiêu đề cột rõ ràng, vì những tiêu đề này sẽ rất quan trọng để thiết lập phạm vi tiêu chí. Ở đây, đang sử dụng bảng điểm của học sinh sau đây làm ví dụ.
Bước 2: Thiết lập phạm vi tiêu chí
1. Tạo phạm vi tiêu chí của bạn ở trên hoặc tách khỏi phạm vi danh sách bằng cách nhập các tiêu đề khớp chính xác với các tiêu đề trong phạm vi danh sách. Ở đây phạm vi tiêu chí nằm phía trên phạm vi danh sách.
2. Trong logic AND, liệt kê tất cả các tiêu chí trong cùng một hàng dưới tiêu đề tương ứng của chúng. Ví dụ: Nếu muốn lọc những học sinh ‘Lớp A’ có điểm trên 85 thì phạm vi tiêu chí phải được đặt như sau:
Bước 3: Áp dụng Bộ lọc nâng cao
Bây giờ bạn có thể áp dụng bộ lọc nâng cao để hoàn thành nhiệm vụ như sau.
1. Tới Tab Date và chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, bạn cần cấu hình như sau.
– Trong Action hãy chọn một tùy chọn bạn cần. Ở đây vì muốn định vị kết quả đã lọc đến một nơi khác, chọn Copy to another location tùy chọn.
– Trong List Range, chọn toàn bộ phạm vi danh sách A7: D16.
– Trong Criteria range, chọn toàn bộ phạm vi tiêu chí A2: D3.
– Trong Copy to, chỉ định nơi bạn muốn dán kết quả đã lọc (ở đây chọn ô F6).
– Nhấp OK để áp dụng bộ lọc. Xem ảnh chụp màn hình:
Kết quả
Trong kết quả, chỉ những hàng khớp với tất cả tiêu chí trên các cột được chỉ định mới được hiển thị hoặc sao chép. Trong ví dụ, chỉ những học sinh lớp A có điểm trên 85 mới được trích xuất.
Với logic OR (phù hợp với bất kỳ tiêu chí nào)
Để lọc dữ liệu trong nhiều cột bằng logic OR (khớp với bất kỳ tiêu chí nào) trong Bộ lọc nâng cao của Excel, hãy làm theo các bước sau:
Bước 1: Chuẩn bị dữ liệu phạm vi danh sách ban đầu
Đảm bảo tập dữ liệu phạm vi danh sách của bạn có tiêu đề cột rõ ràng, vì những tiêu đề này sẽ rất quan trọng để thiết lập phạm vi tiêu chí. Ở đây, đang sử dụng bảng điểm của học sinh sau đây làm ví dụ.
Bước 2: Thiết lập phạm vi tiêu chí
1. Tạo phạm vi tiêu chí của bạn ở trên hoặc tách khỏi phạm vi danh sách bằng cách nhập các tiêu đề khớp chính xác với các tiêu đề trong phạm vi danh sách. Ở đây phạm vi tiêu chí nằm phía trên phạm vi danh sách.
2. Với logic OR, đặt từng bộ tiêu chí cho cùng một cột trên các hàng riêng biệt hoặc liệt kê từng tiêu chí trên các hàng riêng biệt bên dưới tiêu đề tương ứng của nó. Ví dụ: Nếu muốn lọc những học sinh có điểm cao hơn 90 hoặc điểm F, thì phạm vi tiêu chí phải được đặt như sau:
Bước 3: Áp dụng Bộ lọc nâng cao
Bây giờ bạn có thể áp dụng bộ lọc nâng cao để hoàn thành nhiệm vụ như sau.
1. Tới Tab Date và chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, bạn cần cấu hình như sau.
– Trong Action hãy chọn một tùy chọn bạn cần. Ở đây vì muốn định vị kết quả đã lọc đến một nơi khác, chọn Copy to another location tùy chọn.
– Trong List Range, chọn toàn bộ phạm vi danh sách A7: D17.
– Trong Criteria range, chọn toàn bộ phạm vi tiêu chí A2: D4.
– Trong Copy to, chỉ định nơi bạn muốn dán kết quả đã lọc (ở đây chọn ô F8).
– Nhấp OK để áp dụng bộ lọc. Xem ảnh chụp màn hình:
Kết quả
Điều này sẽ lọc dữ liệu của bạn dựa trên các tiêu chí được chỉ định, khớp với bất kỳ tiêu chí nào được liệt kê. Nếu một hàng khớp với bất kỳ tiêu chí nào trong các cột bạn đã chỉ định thì hàng đó sẽ được đưa vào kết quả được lọc.
Trong trường hợp này, bộ lọc sẽ chỉ trả về những học sinh có điểm cao hơn 90 hoặc có điểm F.
Với logic AND cũng như OR
Để lọc dữ liệu theo nhiều cột bằng sự kết hợp của VÀ cũng như OR logic bằng Bộ lọc nâng cao của Excel, bạn có thể làm theo các bước sau.
Bước 1: Chuẩn bị dữ liệu phạm vi danh sách ban đầu
Đảm bảo tập dữ liệu phạm vi danh sách của bạn có tiêu đề cột rõ ràng, vì những tiêu đề này sẽ rất quan trọng để thiết lập phạm vi tiêu chí. Ở đây, đang sử dụng bảng điểm của học sinh sau đây làm ví dụ.
Bước 2: Thiết lập phạm vi tiêu chí
1. Tạo phạm vi tiêu chí của bạn ở trên hoặc bên cạnh phạm vi danh sách của bạn. Bao gồm các tiêu đề cột khớp chính xác với các tiêu đề trong phạm vi danh sách. Ở đây phạm vi tiêu chí nằm phía trên phạm vi danh sách.
2. Bên dưới các tiêu đề, hãy nhập tiêu chí bằng cách sử dụng kết hợp logic AND và OR.
-
Trong logic AND, tiêu chí từ các cột khác nhau phải được đặt trên cùng một hàng.
-
Trong logic OR, tiêu chí nên được đặt trên các hàng riêng biệt.
-
Trong logic AND-OR kết hợp, sắp xếp từng bộ điều kiện OR thành các khối hàng riêng biệt. Trong mỗi khối, đặt tiêu chí AND trên cùng một hàng.
Ví dụ: để lọc học sinh Lớp A có điểm lớn hơn 90 hoặc Lớp B có điểm B, hãy đặt phạm vi tiêu chí như sau:
Bước 3: Áp dụng Bộ lọc nâng cao
Bây giờ bạn có thể áp dụng bộ lọc nâng cao để hoàn thành nhiệm vụ như sau.
1. Tới Tab Date và chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, bạn cần cấu hình như sau.
– Trong Action hãy chọn một tùy chọn bạn cần. Ở đây vì muốn định vị kết quả đã lọc đến một nơi khác, chọn Copy to another location tùy chọn.
– Trong List Range, chọn toàn bộ phạm vi danh sách A7: D17.
– Trong Criteria range, chọn toàn bộ phạm vi tiêu chí A2: D4.
– Trong Copy to, chỉ định nơi bạn muốn dán kết quả đã lọc (ở đây chọn ô F8).
– Nhấp OK để áp dụng bộ lọc. Xem ảnh chụp màn hình:
Kết quả
Excel sẽ chỉ hiển thị các hàng đáp ứng sự kết hợp tiêu chí phức tạp của bạn.
Trong ví dụ này, bộ lọc nâng cao sẽ chỉ trả về những học sinh có điểm lớn hơn 90 ở Lớp A hoặc những học sinh có điểm B ở Lớp B.
Bộ lọc nâng cao với ký tự đại diện
Sử dụng ký tự đại diện với bộ lọc nâng cao của Excel cho phép tìm kiếm dữ liệu linh hoạt và mạnh mẽ hơn. Ký tự đại diện là các ký tự đặc biệt đại diện cho một hoặc nhiều ký tự trong một chuỗi, giúp lọc các mẫu văn bản dễ dàng hơn. Dưới đây là hướng dẫn chi tiết cách sử dụng bộ lọc nâng cao với ký tự đại diện trong Excel.
Bước 1: Chuẩn bị dữ liệu phạm vi danh sách ban đầu
Đảm bảo tập dữ liệu phạm vi danh sách của bạn có tiêu đề cột rõ ràng, vì những tiêu đề này sẽ rất quan trọng để thiết lập phạm vi tiêu chí. Trong ví dụ này, giả sử bạn có một danh sách các tên và một số tên bạn đang tìm kiếm tuân theo một mẫu đặt tên cụ thể.
Bước 2: Thiết lập phạm vi tiêu chí
1. Tạo phạm vi tiêu chí của bạn ở trên hoặc bên cạnh phạm vi danh sách của bạn. Bao gồm các tiêu đề cột khớp chính xác với các tiêu đề trong phạm vi danh sách. Ở đây phạm vi tiêu chí nằm phía trên phạm vi danh sách.
2. Bên dưới tiêu đề, nhập tiêu chí bằng ký tự đại diện.
-
*: Đại diện cho bất kỳ số lượng ký tự nào và có thể được sử dụng trước, sau hoặc trong một chuỗi.
-
?: Đại diện cho một ký tự đơn tại một vị trí cụ thể.
Trong ví dụ này, muốn lọc những tên bắt đầu bằng ký tự “J”, vì vậy nhập J* dưới tiêu đề Tên của phạm vi tiêu chí. Xem ảnh chụp màn hình:
Bước 3: Áp dụng Bộ lọc nâng cao
Bây giờ bạn có thể áp dụng bộ lọc nâng cao để lọc tất cả các tên bắt đầu bằng ký tự J.
1. Tới Tab Date và chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, cấu hình như sau.
– Trong Action hãy chọn một tùy chọn bạn cần. Ở đây vì muốn định vị kết quả đã lọc đến một nơi khác, chọn Copy to another location tùy chọn.
– Trong List Range, chọn toàn bộ phạm vi danh sách A6: B11.
– Trong Criteria range, chọn toàn bộ phạm vi tiêu chí A2: B3.
– Trong Copy to, chỉ định nơi bạn muốn dán kết quả đã lọc (ở đây chọn ô D7).
– Nhấp OK để áp dụng bộ lọc. Xem ảnh chụp màn hình:
Kết quả
Bộ lọc nâng cao sẽ chỉ hiển thị những hàng từ cột Tên trong đó tên bắt đầu bằng chữ cái ‘J’, tuân theo mẫu được chỉ định bởi ký tự đại diện trong phạm vi tiêu chí.
Chỉ trích xuất một số cột nhất định
Việc sử dụng bộ lọc nâng cao của Excel để chỉ trích xuất một số cột nhất định đặc biệt hữu ích khi phân tích các tập dữ liệu lớn mà bạn chỉ cần tập trung vào một số thông tin nhất định.
Giả sử tập dữ liệu của bạn nằm trong phạm vi A7:D17 và bạn muốn lọc dữ liệu này dựa trên các tiêu chí được chỉ định trong B2:D4 và chỉ trích xuất Họ tên, Điểm số và Lớp cột. Đây là cách để làm điều đó.
Bước 1: Chỉ định các cột cần trích xuất
Bên dưới hoặc bên cạnh tập dữ liệu của bạn, hãy viết tiêu đề của các cột bạn muốn trích xuất. Điều này xác định phạm vi “Sao chép vào” nơi dữ liệu đã lọc sẽ xuất hiện. Trong ví dụ này, gõ Họ tên, Điểm số và Lớp tiêu đề trong phạm vi F7: H7.
Bước 2: Áp dụng Bộ lọc nâng cao
Bây giờ bạn có thể áp dụng bộ lọc nâng cao để chỉ lọc một số cột nhất định dựa trên tiêu chí đã chỉ định.
1. Tới Tab Date và chọn Advanced trong nhóm Soft & Filter.
2. Trong Advanced Filter, cấu hình như sau.
– Trong Action chọn phần Copy to another location tùy chọn.
– Trong List Range, chọn toàn bộ phạm vi danh sách A7: D17.
– Trong Criteria range, chọn toàn bộ phạm vi tiêu chí A2: D4.
– Trong Copy to, chọn phạm vi (F7: H7 trong trường hợp này) nơi bạn đã viết tiêu đề của các cột bạn muốn trích xuất.
– Nhấp OK để áp dụng bộ lọc. Xem ảnh chụp màn hình:
Kết quả
Bạn có thể thấy rằng kết quả trích xuất chỉ bao gồm các cột được chỉ định.
