Hướng Dẫn Sử Dụng Advanced Filter Trong Excel: Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Sử Dụng Dấu Tách Hàng Ngàn (1000 Separator) Trong Excel Để Tối Ưu Hóa Dữ Liệu Và Trình Bày

Mở Đầu

Excel là một trong những công cụ mạnh mẽ nhất cho việc quản lý, phân tích và trình bày dữ liệu. Trong số hàng trăm tính năng mà Excel cung cấp, Advanced Filter (bộ lọc nâng cao) là một công cụ ít người biết tới nhưng lại vô cùng hữu ích trong việc trích xuất, lọc dữ liệu phức tạp, loại bỏ các bản ghi trùng lặp và tạo các danh sách con dựa trên tiêu chí tùy chỉnh. Bài viết này sẽ hướng dẫn chi tiết cách sử dụng Advanced Filter trong Excel, từ những kiến thức cơ bản, các bước thực hiện, tới các kỹ thuật nâng cao và một số lưu ý quan trọng giúp bạn tối ưu hoá quy trình làm việc.

Có thể bạn quan tâm: Cách Chuyển Chữ In Hoa Sang Chữ In Thường Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng Quan Về Advanced Filter

1.1 Định Nghĩa

Advanced Filter là một tính năng của Excel cho phép người dùng lọc dữ liệu dựa trên nhiều tiêu chí đồng thời, bao gồm:

• Lọc theo nhiều cột cùng một lúc.
• Sử dụng các toán tử logic (AND, OR) giữa các tiêu chí.
• Sao chép kết quả sang một vị trí mới trong bảng tính.
• Loại bỏ các bản ghi trùng lặp (Unique records only).

1.2 So Sánh Với AutoFilter

1.3 Khi Nào Nên Dùng Advanced Filter?

• Bạn cần lọc dữ liệu dựa trên nhiều tiêu chí phức tạp (ví dụ: “Tên bắt đầu bằng ‘A’ và Doanh thu > 10.000, hoặc “Khu vực = ‘Miền Bắc’ hoặc ‘Miền Trung’”).
• Bạn muốn tạo danh sách con từ một bảng dữ liệu lớn, đồng thời bảo toàn dữ liệu gốc.
• Bạn cần loại bỏ các bản ghi trùng lặp mà không muốn dùng hàm Remove Duplicates.
• Bạn muốn thực hiện các thao tác lọc mà không làm thay đổi định dạng của bảng dữ liệu gốc.

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Upload Excel Lên Google Sheets: Từ Cơ Bản Đến Nâng Cao

2. Chuẩn Bị Dữ Liệu Trước Khi Áp Dụng Advanced Filter

2.1 Định Dạng Dữ Liệu

Để Advanced Filter hoạt động chính xác, dữ liệu cần tuân thủ một số quy tắc:

1. Bảng dữ liệu phải có tiêu đề (Header) rõ ràng ở hàng đầu tiên. Các tiêu đề phải duy nhất.
2. Không có hàng trống trong vùng dữ liệu (có thể có hàng trống ở cuối bảng, nhưng không được nằm giữa).
3. Không có cột trống trong tiêu đề. Nếu có, Excel sẽ coi cột đó là không hợp lệ.
4. Định dạng dữ liệu trong mỗi cột phải đồng nhất (ví dụ: cột ngày tháng chỉ chứa ngày, cột số chỉ chứa số).

Lưu ý: Nếu dữ liệu của bạn chưa đáp ứng các yêu cầu trên, hãy dành thời gian chuẩn hoá (normalize) trước khi thực hiện Advanced Filter.

2.2 Xây Dựng Vùng Tiêu Chí (Criteria Range)

Advanced Filter yêu cầu một vùng tiêu chí (criteria range) để xác định các điều kiện lọc. Vùng này bao gồm:

• Hàng tiêu đề (phải trùng khớp hoàn toàn với tiêu đề trong bảng dữ liệu).
• Các hàng điều kiện (có thể là một hoặc nhiều hàng, tùy thuộc vào việc bạn muốn AND/OR).

Ví dụ:

Trong ví dụ trên, chúng ta sẽ lọc các bản ghi có tên bắt đầu bằng “A” và doanh thu > 10.000 hoặc tên bắt đầu bằng “B” và doanh thu > 15.000.

2.3 Đặt Tên Cho Các Vùng Dữ Liệu (Optional)

Đặt tên (named range) cho vùng dữ liệu và vùng tiêu chí sẽ giúp công thức và thao tác trở nên dễ đọc và quản lý. Ví dụ:

• DataSet → vùng dữ liệu chính.
• CriteriaSet → vùng tiêu chí.

Bạn có thể tạo tên bằng cách chọn vùng → nhập tên vào hộp tên (Name Box) ở góc trái trên cùng của Excel.

3. Các Bước Thực Hiện Advanced Filter

3.1 Sử Dụng Giao Diện (Menu)

1. Chọn bất kỳ ô nào trong bảng dữ liệu (hoặc chọn toàn bộ vùng dữ liệu nếu muốn).
2. Vào Data → Sort & Filter → Advanced.
3. Trong hộp thoại Advanced Filter:
4. Action: Chọn Filter the list, in-place (lọc trực tiếp) hoặc Copy to another location (sao chép kết quả sang vị trí khác).
5. List range: Xác định vùng dữ liệu (Excel sẽ tự động điền nếu bạn đã chọn ô trong bảng).
6. Criteria range: Chọn vùng tiêu chí đã chuẩn bị.
7. Copy to: (Nếu bạn chọn “Copy to another location”) chỉ định ô đầu tiên của vùng đích.
8. Unique records only: Đánh dấu nếu muốn loại bỏ các bản ghi trùng lặp.
9. Nhấn OK.

3.2 Sử Dụng VBA (Macro) Để Tự Động Hóa

Trong các dự án lớn hoặc khi cần thực hiện lọc thường xuyên, việc sử dụng VBA sẽ tiết kiệm thời gian. Dưới đây là một ví dụ macro đơn giản:

Sub ApplyAdvancedFilter() Dim ws As Worksheet Dim dataRange As Range Dim criteriaRange As Range Dim outputRange As Range Set ws = ThisWorkbook.Sheets("Sheet1") ' Định nghĩa vùng dữ liệu và tiêu chí Set dataRange = ws.Range("A1:D1000") ' Thay đổi theo thực tế Set criteriaRange = ws.Range("F1:F2") ' Tiêu chí nằm ở cột F Set outputRange = ws.Range("H1") ' Kết quả sẽ được sao chép tới cột H ' Xóa kết quả cũ (nếu có) ws.Range(outputRange, outputRange.End(xlDown)).Clear ' Thực hiện Advanced Filter dataRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=criteriaRange, _ CopyToRange:=outputRange, _ Unique:=True
End Sub

Giải thích:
– Action:=xlFilterCopy → sao chép kết quả.
– Unique:=True → chỉ giữ các bản ghi duy nhất.
– Bạn có thể gán macro này cho một nút (Button) trên sheet để người dùng không cần mở hộp thoại.

3.3 Lưu Ý Khi Thiết Lập Tiêu Chí

Mẹo: Khi sử dụng ký tự đại diện (dấu sao) hoặc ? (dấu hỏi) trong tiêu chí, Excel sẽ hiểu chúng là wildcard, giúp lọc các chuỗi phần mềm.

4. Kỹ Thuật Nâng Cao Với Advanced Filter

4.1 Kết Hợp AND & OR

Có thể bạn quan tâm: Useful Vba Codes For Excel: Bộ Sưu Tập Mã Vba Hữu Ích Giúp Tăng Tốc Độ Làm Việc Trong Excel

• AND: Đặt các tiêu chí trên cùng một hàng trong vùng tiêu chí. Khi các tiêu chí nằm trên cùng một hàng, Excel sẽ áp dụng điều kiện và (AND) giữa chúng.
• OR: Đặt các tiêu chí trên các hàng khác nhau. Khi các tiêu chí nằm trên các hàng khác nhau, Excel sẽ áp dụng điều kiện hoặc (OR) giữa các hàng.

Ví dụ:

• Hàng 1: Tên = A AND Khu vực = Miền Bắc
• Hàng 2: Tên = B AND Khu vực = Miền Trung

Kết quả là (Tên bắt đầu bằng A và khu vực Bắc) HOẶC (Tên bắt đầu bằng B và khu vực Trung).

4.2 Lọc Dữ Liệu Dựa Trên Nhiều Điều Kiện Trong Cùng Một Cột

Nếu muốn OR trong cùng một cột (ví dụ: Khu vực = “Miền Bắc” HOẶC “Miền Trung”), bạn cần tạo các hàng tiêu chí riêng cho mỗi giá trị, nhưng không đặt tiêu chí ở các cột khác.

Trong trường hợp có nhiều cột cùng lúc, bạn có thể kết hợp cả AND và OR bằng cách sắp xếp các hàng tiêu chí hợp lý.

4.3 Sử Dụng Toán Tử So Sánh Trong Vùng Tiêu Chí

Bạn có thể nhập công thức trực tiếp vào ô tiêu chí, ví dụ:

• >SUM($B$2:$B$10) → Lọc các giá trị lớn hơn tổng của một dải ô.
• =LEFT(A2,1)="A" → Lọc các tên bắt đầu bằng “A” (cần sử dụng hàm trong ô tiêu chí, không phải trong công thức bảng).

Lưu ý: Khi sử dụng công thức trong tiêu chí, hãy chắc chắn rằng công thức trả về TRUE/FALSE hoặc một giá trị so sánh hợp lệ.

4.4 Lọc Dữ Liệu Động (Dynamic Ranges) Với Table

Khi dữ liệu của bạn được chuyển thành Table (Insert → Table), Excel sẽ tự động mở rộng vùng dữ liệu khi thêm dòng mới. Để Advanced Filter hoạt động với Table:

1. Chuyển Table sang Range tạm thời (Table → Design → Convert to Range) hoặc
2. Sử dụng VBA để lấy vùng dữ liệu động:

Set dataRange = ws.ListObjects("MyTable").DataBodyRange

Khi dữ liệu thay đổi, chỉ cần chạy macro lại để lọc lại.

4.5 Kết Hợp Advanced Filter Với Các Hàm Khác

Bạn có thể kết hợp Advanced Filter với các hàm như SUBTOTAL, AGGREGATE, hoặc POWER QUERY để thực hiện các phân tích sâu hơn.

4.5.1 Đếm số bản ghi thỏa mãn tiêu chí

Sau khi lọc dữ liệu (sao chép sang một vị trí mới), sử dụng:

=SUBTOTAL(103, H2:H1000)

Trong đó 103 là hàm COUNTA trong SUBTOTAL.

4.5.2 Tính tổng doanh thu cho các bản ghi đã lọc

=SUBTOTAL(109, I2:I1000)

109 là hàm SUM trong SUBTOTAL.

Ưu điểm: Khi dữ liệu gốc thay đổi và bạn chạy lại Advanced Filter, các công thức SUBTOTAL sẽ tự động cập nhật.

5. Thực Hành: 5 Bài Tập Thực Tế

Bài 1: Lọc Danh Sách Khách Hàng Theo Nhiều Tiêu Chí

• Dữ liệu: Tên, Giới tính, Tuổi, Khu vực, Doanh thu.
• Tiêu chí: (Giới tính = “Nam” và Tuổi > 30) HOẶC (Khu vực = “Miền Nam” và Doanh thu > 20,000).

Bước thực hiện:
1. Tạo tiêu chí trong cột A và B:
– Hàng 1 (tiêu đề): Giới tính, Tuổi
– Hàng 2: Nam, >30
– Hàng 3: Khu vực, Doanh thu
– Hàng 4: Miền Nam, >20000
2. Chạy Advanced Filter → Copy to another location.

Bài 2: Loại Bỏ Bản Ghi Trùng Lặp

• Dữ liệu: Mã sản phẩm, Tên sản phẩm, Nhà cung cấp.
• Mục tiêu: Lấy danh sách duy nhất các Mã sản phẩm.

Thực hiện: Tích chọn Unique records only trong hộp thoại Advanced Filter và sao chép kết quả.

Bài 3: Lọc Dữ Liệu Động Với Table

• Chuyển dải dữ liệu thành Table (MySales).
• Tạo macro để lọc các bản ghi có Doanh thu > 15,000 và sao chép sang Sheet “Report”.

Sub FilterSales() Dim ws As Worksheet, rpt As Worksheet Set ws = ThisWorkbook.Sheets("Data") Set rpt = ThisWorkbook.Sheets("Report") ws.ListObjects("MySales").Range.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=ws.Range("F1:F2"), _ CopyToRange:=rpt.Range("A1"), _ Unique:=False
End Sub

Bài 4: Sử Dụng Wildcard Để Lọc Sản Phẩm

• Tiêu chí: Tên sản phẩm chứa “USB” hoặc bắt đầu bằng “Cable”.

Kết quả: Tất cả các sản phẩm có “USB” ở bất kỳ vị trí nào hoặc bắt đầu bằng “Cable”.

Bài 5: Kết Hợp Advanced Filter và PivotTable

• Sau khi lọc dữ liệu thành danh sách con, tạo PivotTable từ danh sách này để phân tích nhanh:
• Chạy Advanced Filter → sao chép kết quả sang Sheet2!A1.
• Chọn Insert → PivotTable → Chọn vùng dữ liệu đã lọc.
• Đặt Khu vực vào Rows, Doanh thu vào Values (Sum).

6. Các Vấn Đề Thường Gặp Và Cách Khắc Phục

7. Tips & Tricks Để Tối Ưu Hoá Advanced Filter

1. Sử dụng Named Ranges: Giúp công thức và macro dễ đọc, giảm lỗi khi thay đổi vị trí vùng dữ liệu.
2. Kết hợp với Data Validation: Tạo danh sách thả xuống cho các tiêu chí, giúp người dùng cuối dễ nhập giá trị mà không phải nhớ chính xác.
3. Lưu Macro vào Personal Macro Workbook: Khi bạn thường xuyên dùng Advanced Filter, lưu macro vào PERSONAL.XLSB để sử dụng trên mọi workbook.
4. Sử dụng Power Query: Nếu dữ liệu quá lớn (> 1 triệu hàng), Advanced Filter có thể chậm. Power Query (Get & Transform) cung cấp khả năng lọc mạnh mẽ hơn và có thể lưu lại các bước lọc dưới dạng query.
5. Kiểm tra Kết Quả Bằng Conditional Formatting: Sau khi lọc, dùng Conditional Formatting để tô màu các bản ghi thỏa mãn, giúp kiểm tra nhanh.

8. Kết Luận

Advanced Filter là một công cụ đắc lực nhưng thường bị bỏ qua trong Excel. Khi bạn nắm vững cách thiết lập tiêu chí, hiểu rõ cách hoạt động của AND/OR, và biết cách kết hợp với VBA hoặc các tính năng khác như PivotTable, Power Query, bạn sẽ có thể:

• Tiết kiệm thời gian trong việc trích xuất dữ liệu phức tạp.
• Tăng độ chính xác khi lọc, giảm thiểu sai sót do lọc thủ công.
• Tạo ra các báo cáo động và danh sách con một cách nhanh chóng.
• Xây dựng quy trình tự động thông qua macro, giúp công việc lặp lại trở nên nhẹ nhàng hơn.

Hãy thực hành các ví dụ trong bài viết, tùy chỉnh chúng theo nhu cầu thực tế của bạn, và dần dần biến Advanced Filter thành “vũ khí bí mật” trong bộ công cụ Excel của mình. Khi đã thành thạo, bạn sẽ thấy rằng việc xử lý dữ liệu lớn, phức tạp không còn là nỗi ám ảnh nữa – mọi thứ trở nên đơn giản, nhanh gọn và hiệu quả.

Chúc bạn thành công!

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ẻ.