Ký tự đại diện trong Excel là giải pháp giúp bạn tìm thấy điều gì đó không chắc chắn trong tệp dữ liệu và nếu bạn không 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ề các ký tự đại diện cũng như cách thực hiện nhé.
I. Các ký tự đại diện trong Excel
Các ký tự đại diện là các ký tự đặc biệt có thể được thay thế cho bất kỳ ký tự nào trong Excel. Có 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 loại ký tự phổ biến:
Dấu hoa thị
Dấu 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.
Nhân vật
Mô tả nội dung
ch *
So khớp 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à một 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 các kết quả phù hợp từng phần.
Nhân vật
Mô tả nội dung
?
Khớp với bất kỳ mục nhập nào có chứa 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
Giá cả phù hợp, 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 ký tự đại diện sẽ hủy ký tự đại diện và biến nó thành dấu hoa thị theo nghĩa đen (~ *), dấu hỏi nghĩa đen (~?) Hoặc dấu ngã. theo nghĩa đen (~~).
Nhân vật
Mô tả nội dung
* ~?
Tìm bất kỳ đầu vào 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 nào có chứa 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
Trong bảng tính Excel, nhấn Ctrl + F để xuất hiện bảng Tìm và Thay thế> Sau đó nhập ký tự tìm kiếm ?? – ?? > Nhấn Tìm tất cả và bạn sẽ thấy kết quả tìm kiếm với các ký tự đại diện như hình.
Cách tìm kiếm bằng ký tự đại diện
Cách tìm kiếm bằng ký tự đại diện
Ví dụ ban đầu tôi có một bảng dữ liệu như hình.
Bảng ví dụ minh họa.
Bước 1: Nhấn Ctrl + H và trong mục Tìm gì> Nhập biểu thức ký tự đại diện cần tìm để thay thế, trong mục Thay thế bằng> Nhập nội dung thay thế> Nhấn Thay thế Tất cả để 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 bấm Replace All.
Kết quả.
Kết quả.
3. Cách tìm và thay thế các ký tự đại diện
Bạn nhấn Ctrl + F để hiện ra bảng Find and Replace> Nhập ký tự ~ * vào Find What> Nhấn Find All, kết quả sẽ hiển thị như hình.
Cách tìm và thay thế các ký tự đại diện
Cách tìm và thay thế các ký tự đại diện
III. Lọc dữ liệu bằng 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 một điều kiện. Cách tốt nhất và nhanh nhất trong trường hợp này là sử dụng bộ lọc, bằng cách nhấn Ctrl + Shift + L.
Để hiểu rõ hơn về cách tạo cũng như cách khắc phục, mời các bạn 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 các ký tự đại diện
STT
Tên chức năng
Mô tả nội dung
Đầu tiên
AVERAGEIF
Hàm giúp tìm giá trị trung bình (trung bình) của các ô đáp ứng điều kiện xác đị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ố lượng ô 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
Chức năng này giúp thực hiện tra cứu theo chiều dọc với các kết quả phù hợp từng phần.
số 8
HLOOKUP
Chức năng giúp thực hiện tra cứu theo chiều ngang với các kết quả khớp từng 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.
mười
CHỈ TRẬN ĐẤU
Hàm tìm một kết quả phù hợp một phần và trả về vị trí tương đối.
11
CHỈ XMATCH
Là một kế thừa hiện đại của hàm MATCH cũng hỗ trợ đối sánh ký tự đại diện.
1. Công thức ký tự đại diện COUNTIF trong Excel
Ví dụ, tôi có một bảng dữ liệu được minh họa như trong hình và cần tìm kiếm ô chứa ký tự B.
Hình 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ả.
Kết quả.
2. Công thức VLOOKUP ký tự đại diện trong Excel
Ví dụ, tôi có một bảng dữ liệu minh họa như trong hình và tôi cần tìm kiếm ô chứa ký tự A-.
Hình 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ả.
Kết quả.
V. Ký tự đại diện Excel cho các số
Với Tìm và Thay thế cũng như sử dụng Bộ lọc, các ký tự đại diện hoạt động tốt cho cả dữ liệu văn bản và dữ liệu số.
Trong bảng tính Excel, nhấn Ctrl + F để xuất hiện bảng Find and Replace> Sau đó, nhập ký tự tìm kiếm * 4 *> Nhấn Find all bạn sẽ thấy kết quả tìm kiếm có ký tự đại diện như trong hình. hình minh họa.
Tìm và thay thế bằng số ký tự đại diện
Tìm và thay thế bằng số ký tự đại diện
Giống như trên. Bộ lọc tự động của Excel không có vấn đề gì khi lọc ra các số có chứa “4”.
Lọc với số ký tự đại diện
Lọc với số ký tự đại diện
3. Tại sao các ký tự đại diện trong 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ố (cho dù 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 dạng một chuỗi trong một dải 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 các ký tự đại diện trong 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ả.
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 chúng thành 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ế, 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ể. Sau đây mình sẽ có 2 ví dụ minh họa bên dưới để các 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 chức năng:
SUMPRODUCT, ISNUMBER, SEARCH: Đây là các lệnh chức năng.
A3: A7: Là vùng dữ liệu tìm kiếm.
-: Là dấu toán tử để chuyển TRUE, FALSE lần lượt thành 1 và 0.
Công thức ký tự đại diện trong Excel cho số
Bước 2: Nhấn phím Enter để hiển thị kết quả.
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 đối với các số nhưng sẽ không thành công đối với 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 xử lý các số đó chứ không phải ngày tháng được hiển thị trong ô.
Để vượt qua trở ngại này, hãy sử dụng hàm TEXT để chuyển ngày thành chuỗi văn bản. Sau đó, nạp các chuỗi vào hàm TÌM KIẾM. Tôi sẽ nói 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 chức năng:
SUMPRODUCT, ISNUMBER, SEARCH, TEXT: Đây là các lệnh chức năng.
C3: C7: Là vùng dữ liệu tìm kiếm.
mmddyyyy: Đếm ngày và tháng trong vùng dữ liệu.
-: Là dấu toán tử để chuyển TRUE, FALSE lần lượt thành 1 và 0.
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ả.
Nhấn phím Enter để hiển thị kết quả.
Nhấn phím Enter để hiển thị 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 ích cho các bạn thực hiện trong công việc cũng như học tập và nếu 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 nhé.
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments