Buzz
Giải pháp ký tự đại diện trong Excel giúp bạn dễ dàng tìm kiếm thông tin không chắc chắn trong dữ liệu. Theo dõi bài viết để hiểu rõ về ký tự đại diện và cách thực hiện.
Hướng dẫn này áp dụng trên Laptop Windows 10, sử dụng Excel 2016. Bạn cũng có thể thực hiện trên Excel 2007, 2010, 2013, 2016, 2109 và trên MacOS.
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. Nếu bạn không biết ký tự cụ thể, bạn có thể sử dụng ký tự đại diện ở vị trí đó.
Trong Excel, có hai loại 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ị là ký tự đại diện
Dấu hoa thị (*) là ký tự đại diện phổ biến nhất, thay thế 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 làm ký tự đại diện
Dấu chấm hỏi (?) đại diện cho bất kỳ ký tự đơn nào. Giúp tìm kiếm chính xác hơn 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ã (~) đặt trước ký tự đại diện sẽ vô hiệu hóa và biến nó thành dấu sao (~ *), dấu hỏi (~?) hoặc dấu ngã (~~).
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ế ký tự đại diện trong Excel
1. Tìm kiếm bằng ký tự đại diện
Lưu ý: Cách này áp dụng cho mọi trường hợp. Dưới đây là ví dụ sử dụng ký tự đại diện dấu (?).
Trong Excel, nhấn tổ hợp phím Ctrl + F để mở bảng Find and Replace >. Sau đó, nhập ký tự tìm kiếm ??-?? > Nhấn Find all để xem kết quả tìm kiếm bằng ký tự đại diện như ảnh minh họa.
Cách tìm kiếm sử dụng ký tự đại diện
2. Cách thay thế bằng ký tự đại diện
Giả sử, ban đầu bạn có bảng dữ liệu như trong hình.
Bảng minh họa ví dụ.
Bước 1: Nhấn tổ hợp phím Ctrl + H. Tại mục Find what >, nhập biểu thức ký tự đại diện cần thay thế. Ở mục Replace with >, nhập nội dung thay thế và nhấn Replace All để thay đổi nội dung cũ.
Cách thay thế sử dụng ký tự đại diện
Bước 2: Kết quả sau khi nhấn Replace All.
Kết quả hiển thị.
3. Cách tìm và thay thế ký tự đại diện
Để tìm ký tự đại diện trong Excel, thêm dấu ngã (~) trước dấu hoa thị hoặc dấu chấm hỏi. Ví dụ, nhập ~ * để tìm ký tự đại diện. Bạn bấm tổ hợp phím Ctrl + F để mở bảng Find and Replace >. Nhập ký tự ~ * vào ô Find What > Nhấn chọn Find All để xem kết quả.
Bấm Ctrl + F để mở bảng Find and Replace >. Nhập ký tự ~ * vào mục Find What > Nhấn chọn Find All để xem kết quả.
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
Ký tự đại diện trong Excel hữu ích khi bạn muốn lọc dữ liệu trong một cột lớn. Sử dụng bộ lọc bằng cách nhấn Ctrl + Shift + L là cách tốt và nhanh nhất.
Để hiểu thêm về cách tạo và khắc phục lỗi bộ lọc trong Excel, bạn có thể tham khảo bài viết: Cách sửa lỗi bộ lọc trong Excel
IV. Công thức Excel với ký tự đại diện
Chú ý: Một số hàm Excel hỗ trợ ký tự đại diện và đây là danh sách các hàm phổ biế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 COUNTIF với ký tự đại diện trong Excel
Để biết thêm về cách sử dụng hàm COUNTIF trong Excel, bạn có thể đọc bài viết: Hướng dẫn sử dụng hàm COUNTIF trong Excel
Ví dụ, có bảng dữ liệu như trong hình và muốn tìm ô chứa ký tự B.
Ví dụ minh họa.
Bước 1: 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 COUNTIF sử dụng ký tự đại diện trong Excel
Bước 2: Nhấn Enter để xem kết quả.
Kết quả hiển thị.
2. Công thức VLOOKUP với ký tự đại diện trong Excel
Để biết thêm về cách sử dụng hàm VLOOKUP trong Excel, bạn có thể đọc bài viết: Hướng dẫn sử dụng hàm VLOOKUP trong Excel
Một ví dụ thú vị về bảng dữ liệu và cách tìm kiếm ô chứa ký tự A-.
Minh họa cho điều đó.
Bước 1: Sử dụng hàm =VLOOKUP(D9&'*', $A$3:$B$7, 2, FALSE) trong ô tham chiếu kết quả của bảng dữ liệu.
Cách sử dụng ký tự đại diện trong công thức VLOOKUP của Excel.
Bước 2: Nhấn phím Enter để xem kết quả ngay lập tức.
Xem ngay kết quả thu được.
V. Sử dụng ký tự đại diện Excel cho các con số.
Trong quá trình sử dụng Tìm và Thay thế cũng như Bộ lọc, ký tự đại diện là một công cụ mạnh mẽ cho cả dữ liệu văn bản và số.
1. Tìm và thay thế sử dụng ký tự đại diện
Mở bảng Find and Replace trong Excel bằng cách nhấn tổ hợp phím Ctrl + F > Nhập ký tự tìm kiếm *4* > Chọn Find all để xem kết quả tìm kiếm với ký tự đại diện.
Tìm và thay thế với số ký tự đại diện
2. Sử dụng Bộ lọc với số ký tự đại diện
Tương tự như trước, Bộ lọc tự động của Excel không gặp vấn đề nào khi lọc các số 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 tương thích với các số trong công thức
Khi bạn sử dụng ký tự đại diện với các số, dù là bao quanh số bằng ký tự đại diện hay nối một tham chiếu ô, điều này sẽ chuyển đổi giá trị số thành một chuỗi văn bản. Điều này khiến Excel không nhận diện chuỗi trong một dải số.
Ví dụ: Công thức sau có thể đếm số chuỗi chứa '4' nhưng không nhận diện chữ số 4 trong một số.
Bước 1: Sử dụng hàm =COUNTIF(A2:A7,'*'&C8&'*' ) trong ô tham chiếu kết quả của 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 Enter để xem ngay kết quả.
Xem kết quả ngay lập tức.
4. Làm thế nào để ký tự đại diện có thể áp dụng cho các số
Cách tốt nhất để ký tự đại diện hoạt động với các số là chuyển đổi chúng thành dạng văn bản và sau đó sử dụng như các hàm đã được mô tả trước đó.
Nếu phương pháp này không khả thi, bạn phải tạo công thức riêng cho từng trường hợp cụ thể. Dưới đây là 2 ví dụ minh họa để bạn dễ hiểu hơn.
Ví dụ 1. Sử dụng ký tự đại diện trong Excel để xử lý các số
Trong bảng mẫu dưới đây, giả sử bạn muốn đếm số lần xuất hiện của số '3' trong phạm vi A2: A7.
Bước 1: Sử dụng hàm =SUMPRODUCT(--(ISNUMBER(SEARCH('3',A3:A7)))) trong ô tham chiếu kết quả của bảng dữ liệu.
Giải thích hàm:
- SUMPRODUCT, ISNUMBER, SEARCH: Các hàm quan trọng.
- A3:A7: Vùng dữ liệu để tìm kiếm.
- '3': Giá trị cần tìm kiếm.
- --: Toán tử chuyển đổi TRUE, FALSE thành 1 và 0.
Sử dụng ký tự đại diện trong Excel để xử lý các số
Bước 2: Nhấn Enter để xem kết quả ngay lập tức.
Xem kết quả ngay lập tức.
Ví dụ 2. Sử dụng ký tự đại diện cho ngày tháng
Công thức SUMPRODUCT đã thảo luận trước đó hoạt động tốt với số nhưng không hiệu quả với ngày tháng. Excel lưu trữ ngày tháng dưới dạng số chuỗi, và công thức xử lý số chuỗi chứ không phải ngày tháng hiển thị trong ô.
Để vượt qua điều này, bạn có thể sử dụng hàm TEXT để chuyển đổi ngày tháng thành chuỗi văn bản. Sau đó, áp dụng chuỗi vào hàm SEARCH. Mình sẽ giải thích chi tiết bên dưới.
Bước 1: Sử dụng hàm =SUMPRODUCT(--(ISNUMBER(SEARCH('4',TEXT(C3:C7, 'mmddyyyy'))))) trong ô tham chiếu kết quả của bảng dữ liệu.
Giải thích hàm:
- SUMPRODUCT, ISNUMBER, SEARCH, TEXT: Các hàm quan trọng.
- C3:C7: Vùng dữ liệu để tìm kiếm.
- mmddyyyy: Định dạng ngày, tháng, năm trong vùng dữ liệu.
- '4': Giá trị cần tìm kiếm.
- --: Toán tử chuyển đổi TRUE, FALSE thành 1 và 0.
Sử dụng ký tự đại diện cho ngày tháng
Bước 2: Nhấn Enter để xem kết quả ngay lập tức.
Nhấn Enter để xem kết quả.
Dưới đây là bài viết về Sử dụng Ký tự đại diện trong Excel: Công thức tìm, thay thế và lọc. Mong rằng bạn sẽ áp dụng thành công trong công việc và học tập. Nếu có ý kiến, hãy để lại bình luận dưới đây và đừng quên chia sẻ nếu thấy hữu ích nhé.
