Tiêu Chuẩn Và Cách Sử Dụng Criteria Range Trong Advanced Filter Của Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Có thể bạn quan tâm: Tạo Và Sử Dụng Scripting.dictionary Trong Vba Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Mở đầu: Tại sao Advanced Filter lại quan trọng?

Trong môi trường làm việc hiện đại, việc xử lý và phân tích dữ liệu nhanh chóng, chính xác là yếu tố quyết định thành công của doanh nghiệp. Excel, với hàng chục năm phát triển, đã trở thành công cụ “đắc lực” không thể thiếu cho các nhà quản lý, nhà phân tích và nhân viên văn phòng. Trong số những tính năng mạnh mẽ của Excel, Advanced Filter (Lọc nâng cao) nổi bật vì khả năng lọc dữ liệu phức tạp, cho phép người dùng áp dụng nhiều tiêu chí (criteria) đồng thời, trích xuất dữ liệu duy nhất (unique records) và thậm chí sao chép kết quả sang vị trí khác mà không làm thay đổi dữ liệu gốc.

Tuy nhiên, để khai thác hết tiềm năng của Advanced Filter, người dùng cần nắm vững một khái niệm then chốt: Criteria Range (phạm vi tiêu chí). Đây là phần “đầu vào” quyết định cách Excel sẽ lọc dữ liệu. Bài viết dưới đây sẽ đi sâu vào khái niệm, cấu trúc, các quy tắc viết tiêu chí, và cung cấp hàng loạt ví dụ thực tế, kèm theo các mẹo, thủ thuật và lỗi thường gặp. Mục tiêu là giúp bạn, dù là người mới bắt đầu hay là một “power user”, có thể tự tin áp dụng Advanced Filter trong mọi tình huống công việc.

Có thể bạn quan tâm: Cách Tạo Drop‑down List (select Option) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Khái niệm cơ bản: Advanced Filter và Criteria Range là gì?

1.1 Advanced Filter (Lọc nâng cao)

Advanced Filter là một công cụ lọc dữ liệu nằm trong tab Data → Sort & Filter → Advanced. Khác với AutoFilter (lọc tự động) chỉ cho phép lọc dựa trên một hoặc một vài giá trị trong một cột, Advanced Filter cho phép:

• Kết hợp nhiều tiêu chí (với các phép logic AND/OR) trong một hoặc nhiều cột.
• Lọc dữ liệu duy nhất (loại bỏ các bản ghi trùng lặp).
• Sao chép dữ liệu lọc sang một vị trí khác (cùng sheet hoặc sheet khác) mà không làm thay đổi dữ liệu gốc.
• Sử dụng công thức trong tiêu chí để thực hiện các phép so sánh phức tạp.

1.2 Criteria Range (Phạm vi tiêu chí)

Criteria Range là một vùng ô trong bảng tính chứa các tiêu chí mà bạn muốn áp dụng cho quá trình lọc. Cấu trúc của Criteria Range bao gồm:

1. Header (tiêu đề cột): Phải trùng khớp đúng với tiêu đề của cột trong danh sách dữ liệu (List Range). Excel sẽ dựa vào tiêu đề này để xác định cột nào sẽ được áp dụng tiêu chí.
2. Dòng tiêu chí: Các ô dưới tiêu đề chứa giá trị, ký tự đại diện, hoặc công thức để xác định điều kiện lọc. Bạn có thể có:
3. Một dòng tiêu chí: Các tiêu chí trong cùng một dòng được kết hợp bằng AND (tất cả đều phải đúng).
4. Nhiều dòng tiêu chí: Các tiêu chí trên các dòng khác nhau được kết hợp bằng OR (một trong các dòng phải đúng).

1.3 Mối quan hệ giữa List Range và Criteria Range

• List Range: Vùng dữ liệu gốc bạn muốn lọc (có tiêu đề ở dòng đầu tiên).
• Criteria Range: Vùng chứa tiêu chí. Khi thực hiện Advanced Filter, Excel sẽ so sánh từng bản ghi trong List Range với các tiêu chí trong Criteria Range để quyết định có đưa bản ghi đó vào kết quả hay không.

Nếu tiêu đề trong Criteria Range không khớp với tiêu đề trong List Range, Excel sẽ bỏ qua tiêu chí đó hoặc trả về lỗi “The criteria range must contain at least one column that matches the source list”.

Có thể bạn quan tâm: Cách Tạo Mã Qr Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Cấu trúc chi tiết của Criteria Range

2.1 Header bắt buộc phải trùng khớp

• Không được có khoảng trắng thừa: “Tên” và “Tên ” (có dấu cách cuối) sẽ được xem là hai tiêu đề khác nhau.
• Không được thay đổi thứ tự: Thứ tự không quan trọng, nhưng tiêu đề phải xuất hiện trong Criteria Range để Excel nhận diện cột.

2.2 Dòng tiêu chí – AND vs OR

2.3 Sử dụng ký tự đại diện (wildcards)

• : đại diện cho bất kỳ chuỗi ký tự nào (có thể là chuỗi rỗng). Ví dụ: Tên = "An" sẽ khớp với “An”, “Annie”, “An Bình”, v.v.
• ? : đại diện cho một ký tự duy nhất. Ví dụ: Mã = "A?C" sẽ khớp “ABC”, “AEC”, nhưng không khớp “ABDC”.

Lưu ý: Khi sử dụng ký tự đại diện, Excel sẽ tự động hiểu là chuỗi văn bản. Nếu cột dữ liệu là kiểu số, bạn cần chuyển sang văn bản (đặt dấu ' trước giá trị) hoặc dùng công thức để so sánh.

2.4 Sử dụng các toán tử so sánh

Bạn có thể viết trực tiếp trong ô tiêu chí, ví dụ: >50000 trong cột “Lương”.

2.5 Sử dụng công thức trong Criteria Range

Công thức trong Criteria Range phải bắt đầu bằng dấu “=” và trả về giá trị TRUE hoặc FALSE. Khi Excel đánh giá công thức, nó sẽ áp dụng cho từng bản ghi dựa trên cùng một hàng trong List Range.

Cú pháp chung:

= <công thức tham chiếu tới cột trong List Range>

Ví dụ 1: Lọc các nhân viên có lương trên mức trung bình

Giả sử cột “Lương” nằm ở cột C. Bạn muốn lọc những nhân viên có lương > trung bình toàn công ty.

1. Tạo một ô tính trung bình, ví dụ: =AVERAGE(C2:C1000) → đặt ở ô Z1.
2. Trong Criteria Range, tạo công thức: =C2>$Z$1 (với C2 là ô đầu tiên của cột Lương trong List Range). Khi Advanced Filter chạy, Excel sẽ thay C2 bằng mỗi ô tương ứng trong cột Lương và so sánh với $Z$1.

Ví dụ 2: Lọc các đơn hàng trong tháng 1 năm 2026

Giả sử cột “Ngày” ở cột D chứa ngày tháng năm. Bạn muốn chỉ lấy các đơn hàng trong tháng 1/2026.

• Criteria Range:
• Header: Ngày
• Công thức: =AND(MONTH(D2)=1, YEAR(D2)=2026)

Excel sẽ áp dụng công thức cho từng ô D2, D3, … và trả về TRUE nếu ngày thuộc tháng 1/2026.

Ví dụ 3: Kết hợp công thức và giá trị tĩnh

Bạn có thể đặt công thức trong một cột và giá trị tĩnh trong cột khác trên cùng một dòng để tạo điều kiện phức tạp. Ví dụ:

Trong thực tế, chỉ cần một cột công thức là đủ vì công thức có thể tham chiếu tới nhiều cột.

3. Các bước thực hiện Advanced Filter với Criteria Range

Bước 1: Chuẩn bị List Range

1. Đảm bảo có tiêu đề ở dòng đầu tiên.
2. Không có dòng trống giữa các bản ghi (Excel sẽ coi là kết thúc dữ liệu).
3. Định dạng dữ liệu chính xác (số, ngày, văn bản). Nếu có dữ liệu hỗn hợp, hãy chuẩn hoá.

Bước 2: Xây dựng Criteria Range

1. Copy tiêu đề từ List Range sang vị trí mới (có thể ở cùng sheet hoặc sheet khác).
2. Nhập tiêu chí dưới mỗi tiêu đề:
3. Giá trị tĩnh (30, >50000, "An", …)
4. Công thức (=C2>$Z$1, =AND(MONTH(D2)=1, YEAR(D2)=2026), …)
5. Kiểm tra: Đảm bảo không có ô trống giữa các tiêu chí trên cùng một cột (nếu có, Excel sẽ coi như OR giữa các dòng, không phải AND).

Bước 3: Mở hộp thoại Advanced Filter

• Data → Sort & Filter → Advanced.
• Action:
• Filter the list, in-place: Lọc trực tiếp trên List Range (ẩn các bản ghi không thỏa).
• Copy to another location: Sao chép kết quả sang một vùng khác (cần chỉ định ô “Copy to”).
• List range: Chọn toàn bộ vùng dữ liệu (bao gồm tiêu đề).
• Criteria range: Chọn vùng tiêu chí (bao gồm tiêu đề và các dòng tiêu chí).
• Unique records only: Đánh dấu nếu muốn loại bỏ các bản ghi trùng lặp.

Bước 4: Kiểm tra kết quả

• Nếu sử dụng “Copy to”, kết quả sẽ xuất hiện ở vùng chỉ định.
• Nếu sử dụng “in-place”, các bản ghi không thỏa sẽ ẩn đi (đánh dấu bằng dấu “” trên thanh row).
• Nếu không có kết quả, kiểm tra lại:
• Tiêu đề có khớp không?
• Công thức có trả về TRUE/FALSE không?
• Ký tự đại diện có đúng không?

4. Các ví dụ thực tế chi tiết

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Tạo Drop‑down List Trong Excel: Từ Cơ Bản Đến Nâng Cao

4.1 Ví dụ 1: Lọc danh sách khách hàng theo khu vực và mức doanh thu

Bảng dữ liệu (List Range):

Yêu cầu: Lấy danh sách các khách hàng ở Khu Vực Bắc hoặc Trung và Doanh Thu ≥ 130,000.

Criteria Range:

• Hai dòng tiêu chí => OR giữa các dòng.
• Trong mỗi dòng, Khu Vực = "Bắc" AND Doanh Thu >= 130000.

Kết quả: Khách hàng Cường (Trung, 150,000) và bất kỳ khách hàng nào ở Bắc với doanh thu ≥130,000.

4.2 Ví dụ 2: Lọc nhân viên có tên bắt đầu bằng “A” và ngày vào làm trong năm 2026

List Range:

Criteria Range:

• A là wildcard, trả về mọi tên bắt đầu bằng “A”.
• Công thức =YEAR(C2)=2026 kiểm tra năm của ngày vào làm.

Kết quả: NV01 và NV03.

4.3 Ví dụ 3: Lọc các đơn hàng có tổng tiền > 10,000 và chưa thanh toán

List Range:

Criteria Range:

• Một dòng tiêu chí => AND.
• Kết quả: DH001, DH003.

4.4 Ví dụ 4: Lọc dữ liệu duy nhất (Unique) – Loại bỏ trùng lặp

Giả sử có danh sách Sản phẩm với cột “Mã SP” có nhiều bản ghi trùng lặp do nhập liệu lỗi.

Criteria Range: Không cần thiết, chỉ cần tick “Unique records only” trong hộp thoại Advanced Filter và Copy to một vùng mới. Excel sẽ tự động loại bỏ các bản ghi có cùng giá trị trong tất cả các cột được chọn.

4.5 Ví dụ 5: Kết hợp OR và AND trong cùng một Criteria Range

Yêu cầu: Lấy các đơn hàng thỏa một trong hai điều kiện:
1. Khách hàng là “An” và Tổng tiền > 5,000.
2. Ngày đặt hàng trong tháng 12/2026.

Criteria Range:

• Dòng 1: Khách Hàng = "An" AND Tổng Tiền > 5000.
• Dòng 2 và 3: Ngày Đặt nằm trong khoảng tháng 12/2026 (hai dòng OR nhau, nhưng vì cùng một cột, Excel sẽ hiểu là OR giữa các dòng; để tạo AND trong cùng một dòng, cần đặt công thức =AND(D2>=DATE(2026,12,1), D2<=DATE(2026,12,31))).

5. Các lỗi thường gặp và cách khắc phục

6. Mẹo nâng cao để tối ưu Criteria Range

6.1 Sử dụng Named Ranges (Tên vùng)

• Đặt tên cho List Range và Criteria Range (ví dụ: tblData, critRegion). Khi mở Advanced Filter, bạn chỉ cần nhập tên thay vì chọn vùng mỗi lần. Điều này giúp tránh lỗi chọn sai và tăng tính tái sử dụng.

6.2 Kết hợp với Dynamic Named Ranges (Vùng tên động)

Sử dụng hàm OFFSET hoặc TABLE để tạo vùng dữ liệu tự mở rộng khi thêm bản ghi mới.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5)

Sau khi tạo, Criteria Range cũng có thể là một bảng động, giúp Advanced Filter luôn áp dụng cho toàn bộ dữ liệu mới.

6.3 Tạo “Dropdown” cho tiêu chí

• Dùng Data Validation để tạo danh sách thả xuống trong ô tiêu chí (ví dụ: danh sách khu vực, trạng thái). Điều này giảm thiểu lỗi nhập liệu và cho phép người dùng cuối tự thay đổi tiêu chí mà không cần chỉnh công thức.

6.4 Sử dụng công thức ARRAY trong Criteria

Trong Excel 365/2026, bạn có thể viết công thức mảng trong Criteria Range, ví dụ:

=OR(ISNUMBER(SEARCH("An",B2)), ISNUMBER(SEARCH("Bình",B2)))

Công thức này trả về TRUE nếu tên chứa “An” hoặc “Bình”. Khi áp dụng, Excel sẽ tự động mở rộng công thức cho từng bản ghi.

6.5 Lưu macro cho quá trình lọc lặp lại

Nếu bạn thường xuyên lọc cùng một tiêu chí, hãy ghi lại macro (Developer → Record Macro) và gán cho nút bấm. Macro sẽ:

Sub FilterByCriteria() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") ws.Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=ws.Range("K1:L2"), _ CopyToRange:=ws.Range("N1"), _ Unique:=False
End Sub

Bạn có thể thay đổi CriteriaRange hoặc CopyToRange bằng các tham chiếu tên để linh hoạt hơn.

7. So sánh Advanced Filter với các công cụ lọc khác

Kết luận: Advanced Filter là công cụ “vừa đủ mạnh” cho các nhu cầu lọc phức tạp mà không muốn học Power Query. Nó cho phép dùng công thức, wildcard, và sao chép kết quả nhanh chóng. Tuy nhiên, khi dữ liệu lên tới hàng chục hoặc trăm nghìn bản ghi, Power Query hoặc PivotTable sẽ hiệu quả hơn.

8. Thực hành: Bài tập tự tạo Criteria Range

Bài 1

Bạn có bảng Bán hàng với các cột: Ngày, Sản phẩm, Khu vực, Doanh thu. Hãy tạo Criteria Range để lấy:
– Các bản ghi khu vực “Miền Bắc” hoặc “Miền Trung”.
– Doanh thu từ 100,000 đến 200,000.
– Ngày trong quý 2 năm 2026.

Gợi ý:
– Dòng 1: Khu vực = "Miền Bắc" AND Doanh thu >=100000 AND Doanh thu <=200000 AND =AND(MONTH(A2)>=4, MONTH(A2)<=6, YEAR(A2)=2026).
– Dòng 2: Khu vực = "Miền Trung" với các tiêu chí còn lại.

Bài 2

Trong danh sách Nhân viên, lọc ra những người:
– Tên chứa “an” (không phân biệt hoa thường).
– Tuổi > 30.
– Phòng không phải “HR”.

Gợi ý:
– Tên = "an" (wildcard, không phân biệt hoa thường nếu dữ liệu đã chuẩn hoá).
– Tuổi = >30.
– Phòng = <>HR.

Bạn có thể thử thực hiện trên Excel và so sánh kết quả với công cụ AutoFilter.

9. Kết luận: Khi nào nên dùng Criteria Range trong Advanced Filter?

• Khi cần lọc dữ liệu phức tạp: nhiều điều kiện, kết hợp AND/OR, hoặc công thức.
• Khi muốn sao chép dữ liệu sang vị trí mới mà không làm thay đổi nguồn.
• Khi muốn loại bỏ các bản ghi trùng lặp mà không cần thực hiện thao tác “Remove Duplicates” riêng.
• Khi không muốn học Power Query nhưng vẫn muốn một công cụ mạnh mẽ hơn AutoFilter.

Tuy không phải là giải pháp duy nhất, Advanced Filter với Criteria Range vẫn là “điểm tựa” vững chắc cho hầu hết các nhu cầu lọc dữ liệu trong Excel. Khi bạn nắm vững cách xây dựng Criteria Range—đúng tiêu đề, đúng cú pháp, và biết cách sử dụng wildcard, toán tử, công thức—bạn sẽ tiết kiệm thời gian, giảm lỗi nhập liệu và nâng cao độ chính xác của báo cáo.

Hy vọng bài viết này đã cung cấp cho bạn một bản đồ chi tiết để thiết kế và sử dụng Criteria Range trong Advanced Filter một cách chuyên nghiệp. Hãy thực hành, thử nghiệm với các ví dụ thực tế trong công việc của mình, và đừng ngại chia sẻ kinh nghiệm, câu hỏi của bạn để cộng đồng Excel ngày càng mạnh mẽ hơn!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.