Trong Microsoft Excel, tùy chọn Bộ lọc nâng cao (Advanced Filter) rất hữu ích khi cần tìm dữ liệu thỏa mãn hai hoặc nhiều tiêu chí. Bài viết này sẽ thảo luận về các ứng dụng của Dải tiêu chí (Criteria Range) trong Bộ lọc nâng cao Excel.
18 Ứng dụng của Dải tiêu chí Bộ lọc nâng cao trong Excel
1. Sử dụng Dải tiêu chí Bộ lọc nâng cao cho Số và Ngày
Trước hết, chúng ta sẽ làm quen với bộ dữ liệu. Cột B đến cột E đại diện cho các dữ liệu liên quan đến bán hàng. Bây giờ chúng ta có thể triển khai Dải tiêu chí Bộ lọc nâng cao. Trong ví dụ này, chúng ta sẽ sử dụng Dải tiêu chí để lọc số và ngày. Chúng ta sẽ trích xuất tất cả dữ liệu nơi số lượng bán hàng lớn hơn 10. Hãy xem quy trình thực hiện.
- Trước tiên, trong tab Dữ liệu (Data), chọn lệnh Nâng cao (Advanced) từ tùy chọn Sắp xếp & Lọc (Sort & Filter). Một hộp thoại tên Bộ lọc nâng cao (Advanced Filter) sẽ xuất hiện.
- Tiếp theo, chọn toàn bộ bảng (B4:E14) cho Dải danh sách (List range).
- Chọn ô (C17:C18) làm Dải tiêu chí (Criteria range).
- Nhấn OK.
Kết quả: Chúng ta chỉ thấy dữ liệu có số lượng lớn hơn 10.
Lưu ý:
1. Chọn tiêu chí với ít nhất hai hàng.
2. Sử dụng tiêu đề cho các cột liên quan nơi tiêu chí lọc sẽ được áp dụng.
2. Lọc giá trị văn bản với Dải tiêu chí Bộ lọc nâng cao
Chúng ta có thể so sánh các giá trị văn bản bằng toán tử logic ngoài số và ngày. Phần này thảo luận cách lọc giá trị văn bản với Dải tiêu chí Bộ lọc nâng cao cho khớp chính xác của Văn bản cũng như có ký tự cụ thể ở đầu.
2.1 Khớp chính xác của Văn bản
Phương pháp này trả về giá trị chính xác của văn bản đầu vào. Giả sử chúng ta có bộ dữ liệu bán hàng dưới đây cùng cột mới Thành phố (City). Chúng ta sẽ trích xuất chỉ dữ liệu cho thành phố 'NEW YORK'. Thực hiện các bước sau:
- Ban đầu, chọn ô C18. Chèn công thức sau:
=EXACT(D5," NEW YORK")- Nhấn Enter.
- Tiếp theo, chọn dải tiêu chí lọc sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C18
- Nhấn OK.
Cuối cùng, chúng ta chỉ nhận được dữ liệu cho thành phố 'NEW YORK'.
2.2 Có ký tự cụ thể ở đầu
Bây giờ chúng ta sẽ lọc giá trị văn bản bắt đầu bằng ký tự cụ thể thay vì khớp chính xác. Ở đây, chúng ta trích xuất chỉ các giá trị của các thành phố bắt đầu bằng từ 'New'. Hãy xem cách thực hiện.
- Trước tiên, chọn các dải tiêu chí trong hộp Bộ lọc nâng cao (Advanced Filter):
Dải danh sách: B4:F14
Dải tiêu chí: C18:C19
- Nhấn OK.
Kết quả: Chúng ta nhận được dữ liệu cho tất cả các thành phố bắt đầu bằng từ 'New'.
3. Sử dụng Ký tự đại diện (Wildcards) với Tùy chọn Bộ lọc nâng cao
Việc sử dụng ký tự đại diện (wildcard characters) là cách khác để áp dụng Dải tiêu chí Bộ lọc nâng cao. Thường có ba loại ký tự đại diện trong Excel:
- ? (Dấu hỏi) – Đại diện cho bất kỳ ký tự đơn nào trong văn bản.
- * (Dấu hoa thị) – Đại diện cho bất kỳ số lượng ký tự nào.
- ~ (Dấu ngã) – Đại diện cho sự hiện diện của ký tự đại diện trong văn bản.
Chúng ta có thể tìm kiếm một chuỗi văn bản cụ thể trong bộ dữ liệu bằng cách sử dụng Dấu hoa thị (*). Trong ví dụ này, chúng ta tìm tên người bán hàng bắt đầu bằng văn bản 'J'.
- Mở cửa sổ Bộ lọc nâng cao. Chọn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C18
- Nhấn OK.
Kết quả: Chúng ta nhận được tên người bán hàng chỉ bắt đầu bằng văn bản 'J'.
Nội dung liên quan: Bộ lọc nâng cao Excel [Nhiều cột & Tiêu chí, Sử dụng Công thức & với Ký tự đại diện]
4. Áp dụng Công thức với Dải tiêu chí Bộ lọc nâng cao
Một cách khác để sử dụng Dải tiêu chí Bộ lọc nâng cao là áp dụng công thức. Trong ví dụ này, chúng ta sẽ trích xuất doanh số bán hàng lớn hơn $350.
- Ban đầu, chọn ô C19. Chèn công thức sau:
=F5>350- Nhấn OK.
- Công thức lặp lại giá trị doanh số bán hàng để xem nó có lớn hơn $350 hay không.
- Tiếp theo, chọn dải tiêu chí sau trong hộp thoại Bộ lọc nâng cao:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C19
- Nhấn OK.
Kết quả: Chúng ta thấy dữ liệu chỉ cho các giá trị bán hàng lớn hơn $350.
5. Bộ lọc nâng cao với Tiêu chí Logic AND
Chúng ta sẽ giới thiệu logic AND trong Dải tiêu chí Bộ lọc nâng cao. Logic này sử dụng hai tiêu chí và trả về kết quả khi dữ liệu thỏa mãn cả hai tiêu chí. Ở đây chúng ta lọc dữ liệu cho thành phố New York cũng như giá trị bán hàng >= 200.
- Mở hộp thoại Bộ lọc nâng cao, chọn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C19
- Nhấn OK.
Kết quả: Bộ dữ liệu chỉ cho thành phố New York có giá trị bán hàng lớn hơn $250.
6. Sử dụng Logic OR với Dải tiêu chí Bộ lọc nâng cao
Giống như logic AND, logic OR cũng sử dụng hai tiêu chí. Logic AND trả về kết quả nếu cả hai tiêu chí đều thỏa mãn, trong khi logic OR trả về nếu chỉ một tiêu chí được thỏa mãn. Ở đây chúng ta lọc dữ liệu cho các thành phố New York và Texas.
- Mở hộp thoại Bộ lọc nâng cao. Nhập dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C20
- Nhấn OK.
Kết quả: Bộ dữ liệu chỉ cho các thành phố New York và Texas.
7. Kết hợp Logic AND & OR làm Dải tiêu chí
Đôi khi chúng ta cần lọc dữ liệu cho nhiều tiêu chí. Trong trường hợp đó, chúng ta có thể sử dụng kết hợp AND & OR logic.
- Mở hộp thoại Bộ lọc nâng cao. Chọn tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C20
- Nhấn OK.
Kết quả: Chúng ta thấy bộ dữ liệu chỉ khớp với tiêu chí của chúng ta.
8. Sử dụng Dải tiêu chí Bộ lọc nâng cao để Trích xuất các Cột cụ thể
Trong ví dụ này, chúng ta sẽ lọc các phần cụ thể của bộ dữ liệu. Sau khi lọc, chúng ta sẽ di chuyển phần đã lọc vào cột khác.
- Từ hộp thoại Bộ lọc nâng cao chọn tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C20
- Chọn tùy chọn sao chép đến vị trí khác (copy to another location).
- Nhập Dải sao chép (Copy to) là H8:I10.
- Nhấn OK.
Kết quả: Chúng ta nhận được dữ liệu đã lọc trong H8:I10 theo tiêu chí của mình.
9. Sao chép Dữ liệu sang Trang tính khác sau khi Lọc
Trong ví dụ này, chúng ta cũng sao chép dữ liệu sang một trang tính khác, trong khi ví dụ trước làm trên cùng một trang tính.
- Đi đến 'Trang tính khác-2' nơi chúng ta sẽ sao chép dữ liệu sau khi lọc.
- Mở hộp thoại 'Bộ lọc nâng cao'.
- Đi đến 'Trang tính khác-1'. Chọn tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C19
- Chọn tùy chọn sao chép đến vị trí khác.
- Đi đến 'Trang tính khác-2'. Chọn Dải Sao chép (Copy to) là B2:C4.
- Nhấn OK.
Kết quả: Dữ liệu đã lọc xuất hiện trong 'Trang tính khác-2'.
10. Trích xuất Bản ghi duy nhất với Tiêu chí Bộ lọc nâng cao
Trong trường hợp này, chúng ta sẽ trích xuất chỉ các giá trị duy nhất từ một cột cụ thể. Từ bộ dữ liệu sau, chúng ta trích xuất các giá trị duy nhất của thành phố sang cột khác.
- Mở cửa sổ Bộ lọc nâng cao. Chọn tiêu chí:
Dải danh sách: D4:D14
- Chọn tùy chọn Sao chép đến vị trí khác.
- Nhập Dải Sao chép (Copy to) là H4:H8.
- Chọn hộp Chỉ bản ghi duy nhất (Unique records only).
- Nhấn OK.
Kết quả: Chúng ta thấy tên các thành phố chỉ với các bản ghi duy nhất trong cột H.
11. Tìm Ngày trong tuần với Dải tiêu chí Bộ lọc nâng cao
Chúng ta có thể tìm Ngày trong tuần với Dải tiêu chí Bộ lọc nâng cao.
- Chọn ô C19. Chèn công thức sau:
=AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7)- Thiết lập dải tiêu chí sau trong hộp thoại Bộ lọc nâng cao:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C19
- Nhấn OK.
Kết quả: Chúng ta nhận được giá trị Ngày chỉ cho các ngày trong tuần.
🔎 Cách công thức hoạt động:
- WEEKDAY(B5)<>1: 1 biểu thị Chủ nhật. Phần này đặt tiêu chí ngày không phải Chủ nhật.
- WEEKDAY(B5)<>7: 7 biểu thị Thứ bảy. Phần này đặt tiêu chí ngày không phải Thứ bảy.
- AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7): Đặt tiêu chí ngày không phải Thứ bảy cũng không phải Chủ nhật.
12. Áp dụng Bộ lọc nâng cao để Tìm Cuối tuần
Chúng ta cũng có thể sử dụng Dải tiêu chí Bộ lọc nâng cao để tìm Cuối tuần từ cột Ngày.
- Chọn ô C19. Chèn công thức sau:
=OR(WEEKDAY(B5)=1,WEEKDAY(B5)=7)- Nhấn Enter.
- Từ hộp thoại Bộ lọc nâng cao chọn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C19
- Nhấn OK.
Kết quả: Chúng ta thấy chỉ các giá trị cuối tuần trong cột Ngày.
13. Sử dụng Bộ lọc nâng cao để Tính giá trị Dưới hoặc Trên Trung bình
Phần này tính giá trị dưới hoặc trên trung bình bằng Dải tiêu chí Bộ lọc nâng cao. Chúng ta chỉ lọc giá trị bán hàng lớn hơn giá trị bán hàng trung bình.
- Chọn ô C19. Chèn công thức sau:
=E5>AVERAGE(E5:E14)- Mở hộp thoại Bộ lọc nâng cao. Nhập dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C18:C19
- Nhấn OK.
Kết quả: Chúng ta nhận được bộ dữ liệu chỉ cho giá trị bán hàng lớn hơn giá trị trung bình.
14. Lọc ô trống với Logic OR
Nếu bộ dữ liệu bao gồm ô trống, chúng ta có thể trích xuất ô trống bằng Bộ lọc nâng cao.
Chúng ta có bộ dữ liệu sau chứa ô trống. Tiêu chí được đặt bằng công thức:
=B5=""- Đi đến hộp thoại Bộ lọc nâng cao. Nhập tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C22
- Nhấn OK.
Kết quả: Chúng ta nhận được bộ dữ liệu chỉ bao gồm ô trống.
15. Áp dụng Bộ lọc nâng cao để Lọc ô không trống sử dụng Logic OR và AND
Ví dụ này loại bỏ ô trống, trong khi ví dụ trước loại bỏ ô không trống.
=B5<>""- Đi đến hộp thoại Bộ lọc nâng cao. Chèn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:G18
- Nhấn OK.
Kết quả: Chúng ta nhận được bộ dữ liệu không có ô trống.
16. Tìm 5 Bản ghi đầu tiên Sử dụng Dải tiêu chí Bộ lọc nâng cao
Bây giờ chúng ta triển khai tùy chọn Bộ lọc nâng cao để trích xuất 5 bản ghi đầu tiên từ bất kỳ loại bộ dữ liệu nào.
=F5>=LARGE($F$5:$F$14,5)- Mở hộp thoại Bộ lọc nâng cao. Chèn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C18
- Nhấn OK.
Kết quả: Chúng ta nhận được 5 bản ghi hàng đầu của cột Doanh số (Sales).
17. Sử dụng Dải tiêu chí Bộ lọc nâng cao để Tìm 5 Bản ghi cuối cùng
Chúng ta cũng có thể dùng Bộ lọc nâng cao để tìm 5 bản ghi cuối cùng.
=F5<=SMALL($F$5:$F$14,5)- Chèn dải tiêu chí sau trong hộp thoại Bộ lọc nâng cao:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C18
- Nhấn OK.
Kết quả: Chúng ta thấy 5 giá trị thấp nhất của cột Doanh số (Sales).
18. Lọc Hàng theo Các mục khớp của Danh sách Sử dụng Dải tiêu chí Bộ lọc nâng cao
Đôi khi chúng ta cần so sánh giữa hai cột hoặc hàng của bộ dữ liệu để loại bỏ hoặc giữ các giá trị cụ thể.
18.1 Khớp với Mục trong Danh sách
Giả sử chúng ta có bộ dữ liệu với hai cột thành phố. Chỉ lấy các mục khớp giữa hai cột này.
=C5=E5- Mở tùy chọn Bộ lọc nâng cao. Chèn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C18
- Nhấn OK.
Kết quả: Chúng ta thấy cùng giá trị trong hai cột thành phố.
18.2 Không khớp với Mục trong Danh sách
Ví dụ trước dành cho mục khớp, ví dụ này lọc mục không khớp.
=C5<>E5- Từ Bộ lọc nâng cao chèn dải tiêu chí sau:
Dải danh sách: B4:F14
Dải tiêu chí: C17:C18
- Nhấn OK.
Kết quả: Chúng ta nhận được giá trị thành phố trong Cột C và Cột E không khớp với nhau.
Kết luận
Trong bài viết này, chúng ta đã cố gắng bao gồm tất cả các phương pháp của tùy chọn Dải tiêu chí Bộ lọc nâng cao. Tải xuống sổ làm việc thực hành đính kèm và tự thực hành. Nếu có thắc mắc hoặc góp ý, hãy để lại bình luận bên dưới, chúng tôi sẽ phản hồi sớm nhất có thể.
Bài viết liên quan
- Bộ lọc nâng cao Excel không hoạt động (2 Nguyên nhân & Giải pháp)
- Bộ lọc nâng cao động Excel (VBA & Macro)
- Cách sử dụng Bộ lọc nâng cao trong VBA (Hướng dẫn từng bước)