Có thể bạn quan tâm: Hàm Lọc Nam Nữ Trong Excel: Cách Tạo, Ứng Dụng Và Mẹo Thực Tế
Mở Đầu
Trong môi trường công việc hiện đại, việc xử lý và phân tích dữ liệu là một kỹ năng không thể thiếu. Excel, với khả năng linh hoạt và mạnh mẽ, luôn là công cụ được ưa chuộng để thực hiện các nhiệm vụ này. Một trong những nhu cầu phổ biến nhưng không kém phần quan trọng là lọc từ khóa trong Excel – giúp người dùng nhanh chóng tìm ra những bản ghi chứa các từ, cụm từ hoặc mẫu dữ liệu nhất định. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ các phương pháp cơ bản đến các kỹ thuật nâng cao, giúp bạn trở thành “thợ săn” từ khóa trong Excel một cách hiệu quả.
Có thể bạn quan tâm: Lọc Mã Hàng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Hiểu Rõ Nhu Cầu Lọc Từ Khóa
1.1. Khi nào cần lọc từ khóa?
- Kiểm tra dữ liệu nhập: Đảm bảo rằng các ô không chứa từ ngữ không mong muốn (ví dụ: “spam”, “test”).
- Phân loại khách hàng: Tìm kiếm khách hàng dựa trên ngành nghề, ví dụ “bán lẻ”, “gia công”.
- Phân tích nội dung: Xác định các bài viết, mô tả sản phẩm có chứa từ khóa quan trọng.
- Kiểm tra lỗi: Phát hiện các ô chứa ký tự đặc biệt hoặc lỗi chính tả.
1.2. Các dạng lọc từ khóa
- Lọc chính xác (Exact match): Tìm ô chứa đúng từ khóa.
- Lọc một phần (Partial match): Tìm ô chứa từ khóa ở bất kỳ vị trí nào.
- Lọc theo mẫu (Wildcard): Sử dụng ký tự đại diện như
và?. - Lọc theo biểu thức chính quy (Regular Expressions): Đối với người dùng nâng cao (qua VBA hoặc Power Query).
Có thể bạn quan tâm: Lọc Kết Quả Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Các Công Cụ Cơ Bản Trong Excel
2.1. Tính năng “Filter” (Lọc tự động)
- Bật Filter: Chọn dải dữ liệu, vào Data → Filter (hoặc nhấn
Ctrl + Shift + L). Các mũi tên xổ xuống sẽ xuất hiện ở tiêu đề cột. - Lọc theo Text Filters:
- Contains: Chọn “Contains” và nhập từ khóa muốn tìm (ví dụ: “sale”).
- Does Not Contain: Loại bỏ các ô có từ khóa.
- Begins With / Ends With: Tìm kiếm theo vị trí đầu/cuối.
- Sử dụng Multiple Criteria:
- Chọn “Custom Filter” để kết hợp nhiều điều kiện (AND/OR).
Ưu điểm: Nhanh, không cần công thức.
Nhược điểm: Không linh hoạt khi cần lọc nhiều cột đồng thời hoặc khi muốn tạo danh sách kết quả riêng.
2.2. Hàm Tìm Kiếm Cơ Bản
| Hàm | Mô tả | Công thức mẫu |
|---|---|---|
SEARCH(find_text, within_text, start_num) | Tìm vị trí của find_text trong within_text (không phân biệt hoa thường). | =SEARCH("sale", A2) |
FIND(find_text, within_text, start_num) | Tương tự SEARCH nhưng phân biệt hoa thường. | =FIND("Sale", A2) |
ISNUMBER(value) | Kiểm tra giá trị có phải là số (thường dùng để kiểm tra kết quả của SEARCH). | =ISNUMBER(SEARCH("sale", A2)) |
COUNTIF(range, criteria) | Đếm số ô thỏa mãn tiêu chí. | =COUNTIF(B:B, "sale") |
FILTER(array, include, if_empty) (Excel 365/2026) | Trả về mảng các dòng thỏa mãn điều kiện. | =FILTER(A2:C100, ISNUMBER(SEARCH("sale", B2:B100))) |
Ví dụ thực tế: Đánh dấu các ô trong cột B chứa từ “sale”:
=IF(ISNUMBER(SEARCH("sale", B2)), "Có", "Không")
Sau đó, bạn có thể lọc cột mới này để chỉ hiển thị “Có”.
3. Kỹ Thuật Lọc Nâng Cao
3.1. Sử Dụng Hàm FILTER (Excel 365/2026)
Giả sử dữ liệu từ A2:C100, cột B chứa mô tả sản phẩm, bạn muốn lấy ra tất cả các dòng có từ “discount”.
=FILTER(A2:C100, ISNUMBER(SEARCH("discount", B2:B100)), "Không tìm thấy")
- Lợi ích: Tạo ra bảng kết quả động, tự động cập nhật khi dữ liệu thay đổi.
- Lưu ý: Đảm bảo máy tính hỗ trợ hàm động mảng.
3.2. Kết Hợp ARRAYFORMULA và TEXTJOIN (Google Sheets, nhưng áp dụng tương tự trong Excel)
Khi muốn liệt kê tất cả các từ khóa xuất hiện trong một cột:
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(TRIM(MID(SUBSTITUTE(A2:A100, " ", REPT(" ", 100)), (ROW(INDIRECT("1:" & LEN(A2:A100))) -1)100 +1, 100)), LEN(TRIM(MID(SUBSTITUTE(A2:A100, " ", REPT(" ", 100)), (ROW(INDIRECT("1:" & LEN(A2:A100))) -1)100 +1, 100)))>0)))
Mặc dù công thức phức tạp, nó giúp tách các từ riêng lẻ và liệt kê chúng duy nhất.
3.3. Power Query (Get & Transform)
Power Query là công cụ mạnh mẽ để làm sạch và lọc dữ liệu:
- Import dữ liệu:
Data → Get Data → From Table/Range. - Thêm cột tùy chỉnh:
Add Column → Custom Column. - Công thức M (ngôn ngữ Power Query) để kiểm tra từ khóa:
= Table.AddColumn(Source, "ContainsSale", each Text.Contains(Description, "sale", Comparer.OrdinalIgnoreCase))
- Lọc: Chọn cột
ContainsSalevà lọc giá trịtrue.
Ưu điểm: Xử lý dữ liệu lớn, ghi nhớ các bước, tái sử dụng.
3.4. VBA – Tự Động Hóa Lọc
Có thể bạn quan tâm: Xóa Nội Dung Trong Ô Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Khi cần lọc nhiều từ khóa cùng lúc hoặc thực hiện các thao tác phức tạp, VBA là lựa chọn:
Sub FilterByKeywords() Dim ws As Worksheet Dim rng As Range Dim kw As Variant Dim criteria As String Set ws = ThisWorkbook.Sheets("Data") Set rng = ws.Range("A1").CurrentRegion kw = Array("sale", "discount", "promo") ' Danh sách từ khóa criteria = "" For Each k In kw If criteria <> "" Then criteria = criteria & " OR " criteria = criteria & "@" & k & "" Next k rng.AutoFilter Field:=2, Criteria1:=criteria, Operator:=xlFilterValues
End Sub
- Giải thích:
Field:=2là cột B (có mô tả).criteriatạo chuỗi điều kiện OR cho các từ khóa.@salelà wildcard, tìm bất kỳ vị trí nào có “sale”.
Lưu ý: Khi dùng VBA, luôn bật macro và lưu file ở định dạng .xlsm.
3.5. Sử Dụng Định Dạng Có Điều Kiện (Conditional Formatting)
Để đánh dấu nhanh các ô chứa từ khóa mà không cần tạo cột phụ:
- Chọn vùng dữ liệu (ví dụ B2:B100).
- Vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Nhập công thức:
=ISNUMBER(SEARCH("sale", B2)). - Chọn định dạng (màu nền, chữ màu đỏ,…).
Kết quả: Các ô có “sale” sẽ tự động được tô màu, giúp người dùng nhận diện nhanh.
4. Thực Hành: 5 Tình Huống Thực Tế
4.1. Tình Huống 1 – Loại Bỏ Dòng Có Từ “spam”
Bạn có danh sách email trong cột A, muốn loại bỏ mọi dòng chứa từ “spam”.
Cách 1 – Filter:
– Áp dụng Filter → Text Filters → Does Not Contain → nhập “spam”.
Cách 2 – Hàm:
=IF(ISNUMBER(SEARCH("spam", A2)), "Xóa", "Giữ")
Sau đó lọc cột mới để chỉ hiển thị “Giữ”.
4.2. Tình Huống 2 – Tìm Kiếm Nhiều Từ Khóa Trong Một Cột
Bạn muốn tìm các dòng có “sale” hoặc “discount” hoặc “promo” trong mô tả (cột C).
Công thức (Excel 365):
=FILTER(A2:D100, ISNUMBER(SEARCH("sale", C2:C100)) + ISNUMBER(SEARCH("discount", C2:C100)) + ISNUMBER(SEARCH("promo", C2:C100)) )
VBA: Tham khảo đoạn mã ở mục 3.4, chỉ cần thay đổi mảng kw.
4.3. Tình Huống 3 – Đếm Số Lần Xuất Hiện Của Từ Khóa
Bạn muốn biết từ “urgent” xuất hiện bao nhiêu lần trong cột B.
=SUMPRODUCT(--ISNUMBER(SEARCH("urgent", B2:B500)))
4.4. Tình Huống 4 – Tách Các Từ Khóa Thành Cột Riêng
Bạn có danh sách mô tả sản phẩm (cột D) và muốn tách ra các từ khóa “new”, “old”, “refurbished” thành các cột riêng.
Công thức (sử dụng IF và SEARCH):
- Cột E (New):
excel =IF(ISNUMBER(SEARCH("new", D2)), "New", "") - Cột F (Old):
excel =IF(ISNUMBER(SEARCH("old", D2)), "Old", "") - Cột G (Refurbished):
excel =IF(ISNUMBER(SEARCH("refurbished", D2)), "Refurbished", "")
Sau đó bạn có thể lọc hoặc tổng hợp dựa trên các cột này.
4.5. Tình Huống 5 – Sử Dụng Power Query Để Lọc Nhiều Từ Khóa
- Import dữ liệu vào Power Query.
- Thêm cột tùy chỉnh:
m = Table.AddColumn(Source, "KeywordMatch", each List.ContainsAny(Text.Split(Description, " "), {"sale","discount","promo"})) - Lọc: Chỉ giữ các dòng có
KeywordMatch = true.
Kết quả: Bảng mới chỉ chứa các bản ghi có ít nhất một từ khóa trong danh sách.
5. Các Mẹo Và Thủ Thuật Khi Lọc Từ Khóa
- Sử Dụng Wildcard:
đại diện cho bất kỳ chuỗi ký tự nào,?đại diện cho một ký tự duy nhất. Ví dụ:salesẽ tìm “sale” ở bất kỳ vị trí nào. - Phân Biệt Hoa Thường:
SEARCHkhông phân biệt,FINDcó. Chọn tùy nhu cầu. - Xử Lý Dữ Liệu Đa Ngôn Ngữ: Khi làm việc với tiếng Việt có dấu, hãy chắc chắn rằng dữ liệu được mã hoá đúng (UTF-8) để tránh lỗi tìm kiếm.
- Tối Ưu Hoá Với Dải Dữ Liệu Động: Dùng
Table1#Alltrong Excel Table để tự động mở rộng khi thêm dòng mới. - Kiểm Tra Lỗi #VALUE!: Khi
SEARCHkhông tìm thấy, nó trả về lỗi. DùngIFERRORhoặcISNUMBERđể tránh lỗi hiển thị.excel =IFERROR(SEARCH("sale", A2), 0)
6. Khi Nào Nên Dùng Công Cụ Nào?
| Nhu Cầu | Công Cụ Gợi Ý |
|---|---|
| Lọc nhanh, không cần công thức | AutoFilter |
| Cần tạo danh sách kết quả riêng, tự động cập nhật | FILTER (Excel 365) |
| Đếm số lần xuất hiện, thống kê | COUNTIF, SUMPRODUCT |
| Xử lý dữ liệu lớn, cần tái sử dụng quy trình | Power Query |
| Tự động hoá, lọc nhiều từ khóa, thao tác phức tạp | VBA |
| Đánh dấu trực quan, không thay đổi dữ liệu | Conditional Formatting |
7. Tổng Kết
Việc lọc từ khóa trong Excel không chỉ là một thao tác đơn giản mà còn là một nghệ thuật kết hợp giữa kiến thức hàm, công cụ lọc và khả năng tự động hoá. Bằng cách nắm vững các phương pháp cơ bản như AutoFilter và các hàm SEARCH, COUNTIF, bạn có thể giải quyết hầu hết các yêu cầu thông thường. Khi dữ liệu ngày càng phức tạp, Power Query và VBA sẽ trở thành “vũ khí” mạnh mẽ giúp bạn tiết kiệm thời gian và giảm thiểu lỗi.
Hãy áp dụng những kỹ thuật đã học vào công việc thực tế, tùy chỉnh chúng sao cho phù hợp với quy trình của bạn. Khi bạn đã thành thạo, việc lọc từ khóa sẽ trở nên nhanh chóng, chính xác và linh hoạt – giúp bạn tập trung vào việc phân tích và ra quyết định thay vì lãng phí thời gian vào công việc thủ công.
Chúc bạn thành công trong việc khai thác dữ liệu và lọc từ khóa hiệu quả với Excel!
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ẻ.