Viết các công thức bằng ký hiệu trong Excel
Ký tự đại diện trong Excel Công thức sử dụng tìm, thay thế và lọcBùi Phạm Ngọc Hân 01/11/2021 Show
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é.Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2016. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2016, 2109 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác. I. Các ký tự đại diện trong ExcelKý 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:
1. Dấu hoa thị dưới dạng ký tự đại diệnDấ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 .
2. Dấu chấm hỏi dưới dạng ký tự đại diệnDấ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.
3. Dấu ngã làm ký tự đại diện nullifierDấ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 (~~).
II. Tìm và thay thế các ký tự đại diện trong Excel1. Cách tìm kiếm bằng ký tự đại diệnLưu ý: Cách này bạn có thể dùng cho tất cả các trường hợp. Ở đây mình ví dụ theo ký tự đại diện dấu (?) 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. 2. Cách thay thế bằng ký tự đại diệnVí dụ ban đầu mình có bảng dữ liệu như hình. 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ũ. Bước 2: Kết quả sau khi nhấn chọn Replace All. 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. III. Lọc dữ liệu bằng các ký tự đại diện trong ExcelCá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. Để hiểu rõ hơn về cách tạo cũng như cách khắc phục bạn hãy tham khảo bài viết: Cách khắc phục lỗi bộ lọc trong Excel IV. Công thức Excel với ký tự đại diệnLưu ý: Có một số lượng hạn chế các hàm Excel hỗ trợ ký tự đại diện và đây là danh sách các hàm phổ biến nhất.
1. Công thức ký tự đại diện COUNTIF trong ExcelĐể hiểu rõ hơn về hàm COUNTIF trong Excel bạn tham khảo: Cách sử dụng hàm 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. 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. Bước 2: Nhấn phím Enter để hiển thị kết quả. 2. Công thức VLOOKUP ký tự đại diện trong ExcelĐể hiểu rõ hơn về hàm VLOOKUP trong Excel bạn tham khảo: Cách sử dụng hàm VLOOKUP 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-. 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. Bước 2: Nhấn phím Enter để hiển thị 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ệnTrong 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. 2. Lọc với số ký tự đại diệnTươ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". 3. Tại sao ký tự đại diện Excel không hoạt động với các số trong công thứcViệ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. Bước 2: Nhấn phím Enter để hiển thị 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:
Bước 2: Nhấn phím Enter để hiển thị kết quả. Ví dụ 2. Công thức ký tự đại diện cho ngày thángCô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:
Bước 2: Nhấn phím Enter để hiển thị kết quả. Xem thêm:
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é.
3.258 lượt xem |