Thủ thuật

Ký tự đại diện trong Microsoft Excel: Công thức sử dụng tìm, thay thế và lọc

Trần Đông Trí

17-05-2023, 1:59 pm

Ký tự đại diện trong Microsoft Excel là một cách để tìm kiếm các giá trị không chắc chắn trong bộ dữ liệu, đồng thời cung cấp giải pháp cho những người chưa biết cách thực hiện điều này. Hãy tiếp tục đọc bài viết dưới đây để có cái nhìn chi tiết hơn về ký tự đại diện và cách áp dụng chúng.

Các ký tự đại diện trong Excel

Trong Microsoft Excel, ký tự đại diện là một loại ký tự đặc biệt có khả năng thay thế cho bất kỳ ký tự nào. Điều này có nghĩa là khi bạn không biết chính xác một ký tự cụ thể, bạn có thể sử dụng ký tự đại diện để đại diện cho nó.

Trong Excel, có hai dạng ký tự phổ biến được sử dụng:

Dấu hoa thị (*)
Dấu chấm hỏi (?) hoặc dấu ngã (~)

Dấu hoa thị dưới dạng ký tự đại diện

Dấu hoa thị (*) là ký tự đại diện phổ biến nhất trong Excel, có khả năng đại diện cho bất kỳ chuỗi 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,...

Dấu chấm hỏi dưới dạng ký tự đại diện

Ký tự đại diện dấu chấm hỏi (?) được sử dụng để đại diện cho bất kỳ ký tự đơn nào trong Excel. Đây là một công cụ hữu ích để làm cho quá trình tìm kiếm kết quả phù hợp từng phần trở nên cụ thể hơ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ự.

Dấu ngã làm ký tự đại diện nullifier

Khi sử dụng dấu ngã () đặt trước ký tự đại diện trong Excel, nó sẽ hủy bỏ tác dụng của ký tự đại diện và biến ký tự đó thành dấu sao thực tế (*), dấu hỏi thực tế (~?) hoặc dấu ngã thực tế (~~).

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

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

Hãy đánh dấu rõ lưu ý: Phương pháp này có thể áp dụng cho mọi trường hợp. Ví dụ dưới đây sẽ mô tả việc thay đổi các ký tự đại diện bằng dấu (?).

Trên bảng tính Excel, bạn hãy nhấn tổ hợp phím Ctrl + F để mở bảng Find and Replace. Tiếp theo, nhập ký tự tìm kiếm ??-?? và nhấn vào nút Find all. Bạn sẽ thấy kết quả tìm kiếm bằng cách thay thế các ký tự đại diện, giống như hình minh họa.

Cách tìm kiếm bằng ký tự đại diện

Cách thay thế bằng ký tự đại diện

Để minh họa, hãy giả sử bạn có một bảng dữ liệu như được thể hiện trong hình vẽ.

Bảng ví dụ minh họa.

Bước 1: Nhấn tổ hợp phím Ctrl + H để mở hộp thoại Find and Replace. Trong phần Find what, nhập biểu thức ký tự đại diện mà bạn muốn tìm để thay thế. Trong phần Replace with, nhập nội dung bạn muốn thay thế.

Cuối cùng, nhấn nút Replace All để thay đổi nội dung ban đầu.

Sau khi hoàn thành Bước 1, chúng ta tiến đến

Cách thay thế bằng ký tự đại diện

Bước 2: Bước này sẽ hiển thị kết quả sau khi nhấn nút Replace All.

Kết quả.

Cách tìm và thay thế các ký tự đại diện

Để tìm một ký tự được Excel xem là ký tự đại diện, có nghĩa là ký hiệu dấu hoa thị hoặc dấu chấm hỏi theo nghĩa đen, bạn hãy thêm ký hiệu ngã (~) vào ký tự tìm kiếm của bạn. Để dễ hình dung, chúng ta có thể nhập ký hiệu ~ * vào ô tìm kiếm.

Để hiển thị bảng Tìm kiếm và Thay thế, bạn nhấn tổ hợp phím Ctrl + F. Sau đó, nhập ký hiệu ~ * vào phần Tìm kiếm. Tiếp theo, nhấp chọn Tìm tất cả, và kết quả sẽ được hiển thị 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

Trong Excel, các ký tự đại diện cũng mang lại lợi ích to lớn khi bạn đối mặt với một cột dữ liệu lớn và muốn lọc dữ liệu dựa trên điều kiện. Để xử lý tình huống này một cách tốt nhất và nhanh nhất, bạn có thể sử dụng tính nă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

Xin lưu ý: Có giới hạn về số lượng các hàm Excel hỗ trợ ký tự đại diện và đây là danh sách những hàm phổ biến nhất.

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.

Công thức ký tự đại diện COUNTIF trong Excel

Hãy xem xét một ví dụ với bảng dữ liệu minh họa như hình và nhu cầu tìm kiếm ô chứa ký tự B.

Ví dụ minh họa.

Bước 1: Để thực hiện việc này, bạn hãy nhập công thức =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: Sau đó, nhấn phím Enter để xem kết quả hiển thị.

Kết quả.

Công thức VLOOKUP ký tự đại diện trong Excel

Hãy xem xét một ví dụ với bảng dữ liệu minh họa như hình và nhu cầu tìm kiếm ô chứa ký tự A-.

Ví dụ minh họa.

Bước 1: Để thực hiện điều này, bạn hãy nhập công thức =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: Sau đó, nhấn phím Enter để xem kết quả được hiển thị.

Kết quả.

Ký tự đại diện Excel cho các số

Khi sử dụng tính năng Tìm và thay thế cùng với việc áp dụng Bộ lọc, các ký tự đại diện sẽ hoạt động hiệu quả 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, để tìm kiếm một ký tự đại diện, bạn có thể sử dụng tổ hợp phím Ctrl + F để hiển thị bảng Tìm kiếm và Thay thế. Sau đó, nhập ký tự tìm kiếm 4 và nhấn chọn Tìm tất cả. Kết quả tìm kiếm sẽ được hiển thị như minh họa trong hình.

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, việc sử dụng bộ lọc tự động trong Excel để lọc các số chứa ký tự "4" không gặp bất kỳ vấn đề nào.

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

Việc sử dụng ký tự đại diện cùng với các số (bất kể việc bao quanh số bằng ký tự đại diện hoặc kết hợp với tham chiếu ô) sẽ chuyển đổi một giá trị số thành một chuỗi văn bản. Điều này dẫn đến việc 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 có chứa "4", nhưng không thể xác định chữ số 4 trong một số.

Bước 1: Để thực hiện điều này, bạn nhập công thức =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: Sau đó, nhấn phím Enter để hiển thị kết quả.

Kết quả.

Cách làm cho các ký tự đại diện hoạt động cho các số

Cách hiệu quả nhất để biểu diễn các ký tự đại diện đại diện 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 đã được đề cập ở trên.

Tuy nhiên, trong trường hợp phương pháp này không được chấp nhận trong thực tế, bạn sẽ phải đưa ra một công thức riêng biệt cho từng trường hợp cụ thể. Dưới đây là hai ví dụ minh họa để bạn dễ hình dung.

Ví dụ 1. Công thức đại diện ký tự trong Excel cho các số Trong bảng mẫu dưới đây, giả sử bạn muốn tính số lượng các số từ A2 đến A7 chứa "3".

Bước 1: 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à phạm vi dữ liệu tìm kiếm. "3": Là giá trị cần tìm. --: Là dấu 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 Enter để hiển thị kết quả.

Kết quả.

Ví dụ 2. Công thức đại diện ký tự cho ngày tháng Công thức SUMPRODUCT đã được thảo luận ở trên hoạt động tốt với các con số, nhưng không thành công với các ngày tháng. Bởi vì trong Excel, ngày tháng được lưu trữ dưới dạng số chuỗi và công thức sẽ xử lý các số đó thay vì ngày tháng được hiển thị trong ô.

Để vượt qua trở ngại 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 các chuỗi này vào hàm SEARCH. Dưới đây là cách thực hiện chi tiết để bạn dễ hình dung.

Bước 1: 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à phạm vi dữ liệu tìm kiếm. mmddyyyy: Là định dạng ngày, tháng, năm trong phạm vi dữ liệu. "4": Là giá trị cần tìm. --: Là dấu 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 Enter để hiển thị kết quả.

Nhấn phím Enter để hiển thị kết quả.

Dưới đây là bài viết về Cách sử dụng Ký tự đại diện trong Excel: Phương pháp áp dụng tìm kiếm, thay thế và lọc dữ liệu. Mong rằng bài viết sẽ giúp bạn thực hiện các tác vụ liên quan đến công việc và học tập. Nếu bạn có bất kỳ ý kiến đóng góp nào, hãy để lại bình luận dưới đây và đừng quên chia sẻ nếu bạn thấy nó hữu ích.