Trong Excel, các ký tự đại diện đóng vai trò quan trọng giúp bạn tìm kiếm, thay thế và lọc dữ liệu một cách nhanh chóng và chính xác. Fasetto sẽ hướng dẫn bạn cách sử dụng ký tự đại diện hiệu quả, từ việc tìm kiếm các giá trị cụ thể đến việc thay thế và lọc dữ liệu theo những điều kiện phức tạp, giúp nâng cao hiệu suất làm việc và tiết kiệm thời gian.
Các ký tự đại diện trong Excel
Ký tự đại diện là những ký tự đặc biệt có thể thay thế cho bất kỳ ký tự nào trong Excel. Điều này có nghĩa là khi bạn không chắc chắn về một ký tự cụ thể, bạn có thể sử dụng ký tự đại diện để tìm kiếm.
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ã (~)
Dấu hoa thị như ký tự đại diện
Dấu hoa thị (*) là ký tự đại diện thông dụng nhất, có thể thay thế cho bất kỳ số lượng 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”, ví dụ như Charles, séc, cờ vua,… |
| *ch | Khớp với bất kỳ chuỗi nào kết thúc bằng “ch”, ví dụ 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,… |
Dấu chấm hỏi như 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 giúp tìm kiếm chính xác hơn khi bạn muốn điều chỉnh kết quả.
| 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 | Khớp với giá cả, niềm tự hào, giải thưởng và những thứ tương tự. |
Dấu ngã như ký tự đại diện
Dấu ngã (~) được đặt trước một ký tự đại diện sẽ hủy bỏ tác dụng của ký tự đại diện, biến ký tự đó thành ký tự thực 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 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 bất kỳ dữ liệu chứa một dấu hoa thị, ví dụ: * 1, * 11 *, 1-Mar-2020 *,… |
Tìm và thay thế các ký tự đại diện trong Excel
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 để mở bảng Tìm và Thay thế > Sau đó, nhập ký tự tìm kiếm ??-?? > Nhấn chọn Tìm tất cả để xem kết quả tìm kiếm bằng ký tự đại diện.
Cách tìm kiếm bằng ký tự đại diện
Cách thay thế bằng ký tự đại diện
Giả sử bạn có bảng dữ liệu như hình dưới đây.
Bảng ví dụ minh họa.
Bước 1: Nhấn tổ hợp phím Ctrl + H, tại mục Tìm gì > Nhập ký tự đại diện cần tìm để thay thế, tại mục Thay thế bằng > Nhập nội dung thay thế > Nhấn chọn Thay thế tất cả để cập nhật 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 Thay thế tất cả.
Kết quả.
Cách tìm và thay thế các ký tự đại diện
Để tìm một ký tự mà Excel nhận diện là ký tự đại diện, tức là dấu hoa thị hoặc dấu chấm hỏi theo nghĩa đen, bạn cần thêm dấu ngã (~) vào ký tự tìm của bạn. Ví dụ, bạn có thể nhập ký tự ~ * vào ô tìm kiếm để minh họa.
Bạn bấm tổ hợp phím Ctrl + F để mở bảng Tìm và Thay thế > Nhập ký tự ~ * vào mục Tìm gì > Nhấn chọn Tìm tất cả để xem kết quả như hình minh họa.
Cách tìm và thay thế các ký tự đại diện
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 rất hữu ích khi bạn có một cột dữ liệu lớn và muốn lọc theo điều kiện. Cách hiệu quả nhất trong trường hợp này là sử dụng bộ lọc bằng cách nhấn tổ hợp phím Ctrl + Shift + L.
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 tính giá trị trung bình (trung bình cộng) của các ô thỏa mãn điều kiện đã chỉ định. |
| 2 | AVERAGEIFS | Hàm trả về giá trị trung bình của các ô thỏa mãn nhiều điều kiện trong hàm. |
| 3 | COUNTIF | Hàm đếm số lượng ô dựa trên một điều kiện. |
| 4 | COUNTIFS | Hàm đếm số ô dựa trên nhiều điều kiện. |
| 5 | SUMIF | Hàm tính tổng các ô có điều kiện. |
| 6 | SUMIFS | Hàm tính tổng các ô có nhiều điều kiện. Giống như SUMIF nhưng cũng hỗ trợ ký tự đại diện. |
| 7 | VLOOKUP | Hàm thực hiện tra cứu theo chiều dọc với khớp một phần. |
| 8 | HLOOKUP | Hàm thực hiện tra cứu theo chiều ngang với khớp một phần. |
| 9 | XLOOKUP | Hàm 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 tìm một phần đối sánh và trả về vị trí tương đối. |
| 11 | XMATCH | Là phiên bản hiện đại của hàm MATCH cũng hỗ trợ khớp ký tự đại diện. |
Công thức COUNTIF với ký tự đại diện trong Excel
Ví dụ, bạn có bảng dữ liệu minh họa như hình và cần tì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 COUNTIF với ký tự đại diện trong Excel
Bước 2: Nhấn phím Enter để xem kết quả.
Kết quả.
Công thức VLOOKUP với ký tự đại diện trong Excel
Ví dụ, bạn có bảng dữ liệu minh họa như hình và cần tì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 với ký tự đại diện trong Excel
Bước 2: Nhấn phím Enter để xem kết quả.
Kết quả.
Ký tự đại diện Excel cho các số
Với tính năng Tìm và Thay thế cũng như sử dụng Bộ lọc, các ký tự đại diện đều hoạt động tốt cho cả dữ liệu văn bản và số.
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 để mở bảng Tìm và Thay thế > Sau đó, nhập ký tự tìm kiếm *4* > Nhấn chọn Tìm tất cả để xem kết quả tìm kiếm bằng ký tự đại diện.
Tìm và thay thế bằng số ký tự đại diện
Lọc với số ký tự đại diện
Tương tự như trên, bộ lọc tự động của Excel hoạt động tốt với việc lọc các số chứa “4”.
Lọc với số ký tự đại diện
Tại sao ký tự đại diện Excel không hoạt động với các số trong công thức
Sử dụng các ký tự đại diện với các số (dù bạn bao quanh số bằng ký tự đại diện hay nối một tham chiếu ô) sẽ chuyển đổi giá trị số thành chuỗi văn bản. Kết quả là Excel không nhận diện chuỗi trong một dải số.
Ví dụ: Công thức dưới đây có thể đếm số chuỗi 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 để xem kết quả.
Kết quả.
Cách làm cho ký tự đại diện hoạt động với các số
Cách tốt nhất để làm cho 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. Sau đó, bạn có thể áp dụng các hàm đã nêu ở trên. Nếu phương pháp này không khả thi, bạn sẽ cầ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ễ hình dung.
Ví dụ 1. Công thức ký tự đại diện trong Excel cho các số
Giả sử bạn muốn tính số lượng 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 hàm lệnh.
- A3:A7: Là vùng dữ liệu cần tìm.
- “3”: Là giá trị cần tìm.
- —: Là toán tử 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 để xem 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 hoạt động tốt với số nhưng không áp dụng cho ngày tháng. Bởi vì Excel lưu trữ ngày tháng dưới dạng số sê-ri và công thức sẽ xử lý các số đó, không phải ngày tháng hiển thị trong ô.
Để khắc phục, 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 đó, sử dụng các chuỗi này trong hàm SEARCH. Dưới đây là hướng dẫn cụ thể.
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 hàm lệnh.
- C3:C7: Là vùng dữ liệu cần tìm.
- mmddyyyy: Là định dạng ngày tháng trong vùng dữ liệu.
- “4”: Là giá trị cần tìm.
- —: Là toán tử 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 để xem kết quả.
Nhấn phím Enter để hiển thị kết quả.
Hy vọng bài viết đã giúp bạn hiểu rõ hơn về cách sử dụng ký tự đại diện trong Excel để tối ưu hóa công việc của mình. Nếu có bất kỳ thắc mắc nào, đừng ngần ngại để lại bình luận. Cảm ơn bạn đã đọc! Fasetto chúc bạn thành công trong việc khám phá thêm các thủ thuật Excel hữu ích khác.
