Ký tự đại diện trong Excel là giải pháp giúp bạn tìm kiếm thứ gì đó không chắc chắn trong file dữ liệu và nếu bạn chưa biết cách thực hiện. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về ký tự đại diện cũng như cách thực hiện nhé.
Ký tự đại diện trong Excel: Công thức sử dụng tìm, thay thế và lọc
I. Các ký tự đại diện trong Excel
Ký tự đại diện là loại ký tự đặc biệt có thể thay thế cho bất cứ ký tự nào trong Excel. Nghĩa là, khi bạn không biết một ký tự chính xác thì bạn có thể sử dụng ký tự đại diện ở vị trí đó.
Trong Excel có hai dạng ký tự phổ biến:
- Dấu hoa thị (*)
- Dấu chấm hỏi (?) hoặc dấu ngã (~)
1. Dấu hoa thị dưới dạng ký tự đại diện
Dấu hoa thị (*) là ký tự đại diện chung nhất có thể đại diện cho bất kỳ số ký tự nào .
| Ký tự | Mô tả nội dung |
| ch* | Khớp với bất kỳ từ nào bắt đầu bằng “ch” chẳng hạn như Charles, séc, cờ vua,… |
| *ch | Thay thế bất kỳ chuỗi văn bản nào kết thúc bằng “ch”, chẳng hạn như March, inch, fetch,… |
| *ch* | Đại diện cho bất kỳ từ nào có chứa “ch” ở bất kỳ vị trí nào như Chad, nhức đầu, vòm,… |
2. Dấu chấm hỏi dưới dạng ký tự đại diện
Dấu chấm hỏi (?) là ký tự đại diện cho bất kỳ ký tự đơn nào . Ký tự này có thể giúp cụ thể hơn khi tìm kiếm kết quả phù hợp từng phần.
| Ký tự | Mô tả nội dung |
| ? | Khớp với bất kỳ mục nhập nào có chứa một ký tự, ví dụ: “a”, “1”, “-“,… |
| ?? | Thay thế hai ký tự bất kỳ, ví dụ:”as”, “12”, “a@”,… |
| ??? – ??? | Đại diện cho bất kỳ chuỗi nào có chứa 2 nhóm 3 ký tự được phân tách bằng dấu gạch ngang như ABC-DEF, ABC-123, 111-222,… |
| pri? e | Phù hợp với giá cả, niềm tự hào, giải thưởng và những thứ tương tự. |
3. Dấu ngã làm ký tự đại diện nullifier
Dấu ngã (~) được đặt trước một ký tự đại diện sẽ hủy tác dụng của ký tự đại diện và biến ký tự đó thành dấu sao theo nghĩa đen (~ *), dấu hỏi nghĩa đen (~?) hoặc dấu ngã theo nghĩa đen (~~).
| Ký tự | Mô tả nội dung |
| * ~? | Tìm bất kỳ dữ liệu nhập nào kết thúc bằng dấu chấm hỏi, ví dụ: Cái gì?, Có ai ở đó không?,… |
| * ~ ** | Tìm thấy bất kỳ dữ liệu chứa một dấu hoa thị, ví dụ: * 1, * 11 *, 1-Mar-2020 *,… |
II. Tìm và thay thế các ký tự đại diện trong Excel
1. Cách tìm kiếm bằng ký tự đại diện
Trong bảng tính Excel bạn nhấn tổ hợp phím Ctrl + F để xuất hiện bảng Find and Replace > Sau đó, nhập ký tự tìm kiếm ??-?? > Nhấn chọn Find all bạn sẽ thấy kết quả tìm kiếm bằng ký tự đại diện như hình minh họa.
Cách tìm kiếm bằng ký tự đại diện
2. Cách thay thế bằng ký tự đại diện
Ví dụ ban đầu mình có bảng dữ liệu như hình.
Bảng ví dụ minh họa.
Bước 1: Nhấn tổ hợp phím Ctrl + H và ở mục Find what > Nhập biểu thức ký tự đại diện cần tìm để thay thế, ở mục Replace with > Nhập nội dung thay thế > Nhấn chọn Replace All để thay đổi nội dung cũ.
Cách thay thế bằng ký tự đại diện
Bước 2: Kết quả sau khi nhấn chọn Replace All.
Kết quả.
3. Cách tìm và thay thế các ký tự đại diện
Để tìm một ký tự mà Excel nhận dạng là ký tự đại diện. Nghĩa là dấu hoa thị hoặc dấu chấm hỏi theo nghĩa đen bạn hãy thêm dấu ngã (~) vào ký tự nhập tìm kiếm của bạn. Ở đây mình sẽ nhập ký tự ~ * vào ô tìm kiếm để bạn dễ hình dung.
Bạn bấm tổ hợp phím Ctrl + F để hiển thị bảng Find and Replace > Nhập ký tự ~ * vào mục Find What > Nhấn chọn Find All kết quả sẽ hiển thị như hình minh họa.
Cách tìm và thay thế các ký tự đại diện
III. Lọc dữ liệu bằng các ký tự đại diện trong Excel
Các ký tự đại diện trong Excel cũng rất hữu ích khi bạn có một cột dữ liệu khổng lồ và muốn lọc dữ liệu đó dựa trên điều kiện. Cách tốt nhất và nhanh nhất với trường hợp này là bạn sử dụng bộ lọc, bằng cách nhấn tổ hợp phím Ctrl + Shift + L.
IV. Công thức Excel với ký tự đại diện
| STT | Tên hàm | Mô tả nội dung |
| 1 | AVERAGEIF | Hàm giúp tìm giá trị trung bình (trung bình cộng) của các ô đáp ứng điều kiện được chỉ định. |
| 2 | AVERAGEIFS | Hàm trả về giá trị trung bình của các ô đáp ứng nhiều điều kiện trong hàm. |
| 3 | COUNTIF | Hàm giúp đếm số lượng ô dựa trên một điều kiện. |
| 4 | COUNTIFS | Hàm giúp đếm số ô dựa trên nhiều điều kiện. |
| 5 | SUMIF | Hàm giúp tính tổng các ô có điều kiện. |
| 6 | SUMIFS | Hàm giúp tính tổng các ô có nhiều điều kiện. Giống như SUMIF trong ví dụ trên chấp nhận các ký tự đại diện. |
| 7 | VLOOKUP | Hàm giúp thực hiện tra cứu theo chiều dọc với khớp một phần. |
| 8 | HLOOKUP | Hàm giúp thực hiện tra cứu theo chiều ngang với khớp một phần. |
| 9 | XLOOKUP | Hàm giúp thực hiện tra cứu đối sánh từng phần cả trong một cột và một hàng. |
| 10 | MATCH | Hàm giúp tìm một phần đối sánh và trả về vị trí tương đối. |
| 11 | XMATCH | Là một kế thừa hiện đại của chức năng MATCH cũng hỗ trợ khớp ký tự đại diện. |
1. Công thức ký tự đại diện COUNTIF trong Excel
Ví dụ mình có bảng dữ liệu minh họa như hình và cần tìm kiếm ô chứa ký tự B.
Ví dụ minh họa.
Bước 1: Bạn nhập hàm =COUNTIF(A2:A7,”*”&C9&”*”) vào ô tham chiếu kết quả trong bảng dữ liệu.
Công thức ký tự đại diện COUNTIF trong Excel
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Kết quả.
2. Công thức VLOOKUP ký tự đại diện trong Excel
Ví dụ mình có bảng dữ liệu minh họa như hình và cần tìm kiếm ô chứa ký tự A-.
Ví dụ minh họa.
Bước 1: Bạn nhập hàm =VLOOKUP(D9&”*”, $A$3:$B$7, 2, FALSE) vào ô tham chiếu kết quả trong bảng dữ liệu.
Công thức VLOOKUP ký tự đại diện trong Excel
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Kết quả.
V. Ký tự đại diện Excel cho các số
Với tính năng Find and Replace (Tìm và thay thế) cũng như sử dụng Bộ lọc thì các ký tự đại diện đều hoạt động tốt cho cả dữ liệu văn bản và số.
1. Tìm và thay thế bằng số ký tự đại diện
Trong bảng tính Excel bạn nhấn tổ hợp phím Ctrl + F để xuất hiện bảng Find and Replace > Sau đó, nhập ký tự tìm kiếm *4* > Nhấn chọn Find all bạn sẽ thấy kết quả tìm kiếm bằng ký tự đại diện như hình minh họa.
Tìm và thay thế bằng số ký tự đại diện
2. Lọc với số ký tự đại diện
Tương tự như trên. Bộ lọc tự động của Excel không có vấn đề gì với việc lọc các số có chứa “4”.
Lọc với số ký tự đại diện
3. Tại sao ký tự đại diện Excel không hoạt động với các số trong công thức
Việc sử dụng các ký tự đại diện cùng với các số (bất kể bạn bao quanh số bằng các ký tự đại diện hay nối một tham chiếu ô) sẽ chuyển đổi một giá trị số thành một chuỗi văn bản. Kết quả là Excel không nhận ra một chuỗi trong một dải số.
Ví dụ: Công thức dưới đây có thể đếm số chuỗi có chứa “4” nhưng không thể xác định chữ số 4 trong một số.
Bước 1: Bạn nhập hàm =COUNTIF(A2:A7,”*”&C8&”*” ) vào ô tham chiếu kết quả trong bảng dữ liệu.
Tại sao ký tự đại diện Excel không hoạt động với các số trong công thức
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Kết quả.
4. Cách làm cho các ký tự đại diện hoạt động cho các số
Cách tốt nhất để làm cho các ký tự đại diện hoạt động cho các số là chuyển đổi chúng thành dạng văn bản. Sau đó, thực hiện tương tự như các hàm đã nêu ở trên.
Tuy nhiên, trong trường hợp phương pháp này không được chấp nhận trên thực tế thì bắt buộc bạn phải đưa ra công thức riêng cho từng trường hợp cụ thể. Ở đây mình sẽ có 2 ví dụ minh họa bên dưới để bạn dễ hình dung.
Ví dụ 1. Công thức ký tự đại diện trong Excel cho các số
Trong bảng mẫu bên dưới, giả sử bạn muốn tính xem có bao nhiêu số trong phạm vi A2: A7 chứa “3”
Bước 1: Bạn nhập hàm =SUMPRODUCT(–(ISNUMBER(SEARCH(“3”,A3:A7)))) vào ô tham chiếu kết quả trong bảng dữ liệu.
Giải thích hàm:
- SUMPRODUCT, ISNUMBER, SEARCH: Là các lệnh hàm.
- A3:A7: Là vùng dữ liệu tìm kiếm.
- “3”: Là giá trị cần tìm.
- —: Là dấu toán tử nhằm chuyển đổi TRUE, FALSE thành số 1 và 0 tương ứng.
Công thức ký tự đại diện trong Excel cho các số
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Kết quả.
Ví dụ 2. Công thức ký tự đại diện cho ngày tháng
Công thức SUMPRODUCT được thảo luận ở trên hoạt động tốt cho các con số nhưng sẽ không thành công cho các ngày. Bởi vì bên trong Excel lưu trữ ngày tháng dưới dạng số sê-ri và công thức sẽ xử lý những số đó chứ không phải ngày tháng được hiển thị trong ô.
Để vượt qua trở ngại này bạn hãy sử dụng hàm TEXT để chuyển đổi ngày tháng thành chuỗi văn bản. Sau đó nạp các chuỗi vào hàm SEARCH. Mình sẽ làm cụ thể để bạn dễ hình dung.
Bước 1: Bạn nhập hàm =SUMPRODUCT(–(ISNUMBER(SEARCH(“4”,TEXT(C3:C7, “mmddyyyy”))))) vào ô tham chiếu kết quả trong bảng dữ liệu.
Giải thích hàm:
- SUMPRODUCT, ISNUMBER, SEARCH, TEXT: Là các lệnh hàm.
- C3:C7: Là vùng dữ liệu tìm kiếm.
- mmddyyyy: Là đếm ngày, tháng năm có trong vùng dữ liệu.
- “4”: Là giá trị cần tìm.
- —: Là dấu toán tử nhằm chuyển đổi TRUE, FALSE thành số 1 và 0 tương ứng.
Công thức ký tự đại diện cho ngày tháng
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Kết quả.
Trên đây là bài viết về Ký tự đại diện trong Excel: Công thức sử dụng tìm, thay thế và lọc . Hy vọng bài viết sẽ giúp bạn thực hiện được trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.
