Trong bài này, chúng ta sẽ xem cách dùng ký tự đại diện trong Excel.
Các ký tự đại diện như dấu sao "*", dấu hỏi "?" và dấu ngã "~" có thể mở rộng lớn khả năng tìm kiếm, đếm và chức năng bổ sung trong Excel. Ký tự đại diện Excel dùng được với các hàm như AVERAGEIF, SUMIF, COUNTIF, hàm cơ sở dữ liệu Excel, và khác.
Dấu sao "*" dùng thay thế bất kỳ số lượng ký tự nào, trong khi dấu hỏi "?" dùng đại diện hoặc thay thế một ký tự. Dấu ngã "~" dùng xác định dấu hỏi hoặc dấu sao thực tế trong chuỗi văn bản.
Tôi muốn nói gì với tất cả điều này? Hãy bắt đầu với ví dụ đơn giản.
Vui lòng tải file thực hành để tự làm.
4 Phương Pháp Phù Hợp Dùng Ký Tự Đại Diện Trong Excel
Mọi cha mẹ mới đón con đều chào đón suy nghĩ chọn tên cho bé. Có nhiều cơ sở dữ liệu trình diễn tên phổ biến ở các quốc gia. Theo khảo sát của Baby center, khảo sát gần 400.000 phụ huynh, tên phổ biến nhất cho bé gái 2016 là Sophia, trong khi tên phổ biến nhất cho bé trai 2016 là Jackson.
Trong ví dụ, hai phụ huynh giả định đã biên soạn danh sách tên dựa trên sở thích và ý kiến từ bạn bè, người thân. Họ mong bé gái. Gần đến sinh, họ quyết định xem lại danh sách và dùng ký tự đại diện nâng cao tìm kiếm.
Dữ liệu nguồn như sau:
1. Dùng Hàm COUNTIF Tìm Tên Kết Thúc Bằng Từ Cụ Thể
Trong phương pháp này, tôi dùng hàm COUNTIF tìm tên kết thúc bằng từ "anne". Hàm COUNTIF cho thấy số kết quả tìm được. Nhìn hình thứ hai của phương pháp. Số 3 chỉ số kết quả tìm được.
Các bước:
- Mẹ thích âm thanh tên kết thúc bằng "anne" như Joanne và muốn xem hiện có bao nhiêu tên trong danh sách. Dấu sao đại diện dùng được vì bà không chắc có bao nhiêu ký tự khác trước "anne".
Đọc Thêm: Cách Tạo Số Ngẫu Nhiên Trong Excel (Hướng Dẫn Tối Thượng)
- Vì vậy, trong ô E5, nhập công thức sau dùng dấu sao:
=COUNTIF(B5:B22,"*anne")
- Nhấn CTRL – ENTER.
Đọc Thêm: Cách Dùng COUNTIF Với Ký Tự Đại Diện Trong Excel (7 Cách Dễ)
- Ba tên đáp ứng tiêu chí và sở thích của mẹ.
2. Áp Dụng Định Dạng Có Điều Kiện Dùng Ký Tự Đại Diện
Để dùng ký tự đại diện trong Excel, tôi áp dụng định dạng có điều kiện tìm tên cụ thể. Định dạng có điều kiện tìm kết quả bằng cách thêm quy tắc mới. Hãy xem quy trình từng bước.
Các bước:
- Để xem tên thực khớp tiêu chí, dùng định dạng có điều kiện, kết hợp ký tự đại diện để làm nổi tên đó.
- Chọn phạm vi rồi vào Trang Chủ> Kiểu> Định Dạng Có Điều Kiện như hình.
- Nhấp vào mũi tên thả xuống bên cạnh Định Dạng Có Điều Kiện chọn Quy Tắc Làm Nổi Ô> Văn Bản Chứa.
- Trong ô Định Dạng ô chứa văn bản: hộp văn bản, nhập *anne như dưới.
- Nhấp OK xem tất cả tên kết thúc bằng anne được định dạng đỏ như dưới.
Đọc Tương Tự
- VLOOKUP Với Ký Tự Đại Diện Trong Excel (3 Phương Pháp)
- Excel VBA: Xóa File Với Ký Tự Đại Diện (4 Phương Pháp)
- Excel Match Ký Tự Đại Diện Trong Mảng Tra Cứu (3 Công Thức)
- Bố thích Isabelle hoặc Isabella, chỉ khác một chữ cái, để tìm ta làm sau.
- Thứ nhất, xóa định dạng có điều kiện hiện tại bằng cách chọn Trang Chủ> Kiểu> Định Dạng Có Điều Kiện> Xóa Quy Tắc> Xóa Quy Tắc Toàn Trang Tính như dưới, để xóa định dạng trước.
- Chọn phạm vi lại. Vào Trang Chủ> Kiểu> Định D격 Có Điều Kiện> Quy Tắc Làm Nổi Ô> Văn Bản Chứa nhập Isabell? để thấy Isabelle và Isabella được làm nổi. Chúng chỉ khác một ký tự. Do đó dùng dấu hỏi đại diện. Nhấp OK.
Đọc Thêm: Tìm Dấu Hỏi Trong Excel (4 Phương Pháp Phù Hợp)
- Bây giờ chương trình chỉ làm nổi Isabelle và Isabella, khác nhau một ký tự.
3. Tìm Tên Có Dấu Sao Bằng Hàm COUNTIF
Trong phần này, tôi dùng hàm COUNTIF tìm tên cụ thể có dấu sao. Hàm hoạt động như quy trình trước. Ở đây, tôi trình bày toàn bộ phương pháp từng bước.
Các bước:
- Ở trang tiếp theo, có tên ưa thích ông bà ngoại chọn, ký hiệu bằng dấu sao như dưới.
Đọc Thêm: Cách Xóa Dấu Sao Trong Excel (5 Phương Pháp Đơn Giản)
- Chúng ta có thể đếm bao nhiêu tên trong danh sách ông bà ưa thích. Vì vậy, ô D5, nhập công thức:
=COUNTIF(B5:B22,"*~*")
Dấu ngã, trường hợp này, chỉ dấu sao thứ hai là phần thực của chuỗi. Dấu sao đầu, trường hợp này, là ký tự đại diện nói với Excel tìm bất kỳ ký tự nào trước dấu sao thực.
- Sau khi nhấn CTRL-ENTER ta được 3. Vậy có ba tên trong danh sách ông bà ưa thích.
4. Áp Dụng Định Dạng Có Điều Kiện Tìm Tên Có Dấu Sao
Dùng Định Dạng Có Điều Kiện, tôi tìm tên có dấu sao bằng thêm quy tắc mới. Hãy xem các bước phương pháp.
Các bước:
- Để xem tên thực dùng định dạng có điều kiện, chọn phạm vi lại. Vào Trang Chủ>Kiểu>Định Dạng Có Điều Kiện>Quy Tắc Làm Nổi Ô>Văn Bản Chứa và nhập *~* rồi Nhấp OK.
- Ông bà ưa thích và ký hiệu ba tên, dùng dấu sao thực trong chuỗi như dưới
Đọc Thêm: Cách Tìm Ký Tự * Không Phải Đại Diện Trong Excel (2 Phương Pháp)
Lọc Ký Tự Đại Diện Excel
Trong phần này, tôi trình bày quy trình lọc ký tự đại diện. Đây là quy trình dễ dàng. Bằng cách lọc toàn bộ tập dữ liệu, bạn tìm kết quả mong muốn. Hãy xem cách lọc ký tự đại diện Excel.
Các bước:
- Thứ nhất Chọn toàn bộ tập dữ liệu.
- Đến tab Dữ Liệu của Thanh Công Cụ.
- Trong phần Sắp Xếp & Lọc, tìm tùy chọn Lọc. Nhấp vào.
- Bạn sẽ thấy biểu tượng ở góc trên phải nhãn tập dữ liệu. Nhấp vào.
- Cửa sổ như hình sau sẽ bật lên.
- Nhập từ cần lọc vào ô tìm kiếm. Nhấp nút OK.
- Cuối cùng, bạn tìm được kết quả.
Thay Thế Dữ Liệu Với Ký Tự Đại Diện Excel
Bạn có thể tìm và thay thế dữ liệu ký tự đại diện Excel nhanh và dễ. Hãy xem cách thay thế dữ liệu trong ký tự đại diện Excel.
Các bước:
- Thứ nhất, nhấn Ctrl+H. Cửa sổ bật lên như dưới.
- Để thay Olivia thành Watson, ghi tên Olivia vào ô Tìm Kiếm.
- Sau đó, ghi tên Watson vào ô Thay Thế Bằng.
- Sau đó, Nhấp nút thay thế.
- Bạn sẽ thấy tên Olivia thay bằng Watson.
Ký Tự Đại Diện Excel Không Hoạt Động
Đảm bảo không có ký tự sai trong dữ liệu. Kiểm tra dữ liệu không chứa khoảng trắng đầu/cuối, dùng ngoặc đơn/nhọn không đều, ký tự không in, hoặc lỗi khác khi đếm giá trị văn bản. COUNTIF có thể cho kết quả bất ngờ.
Kết Luận
Ký tự đại diện nâng cao lớn hàm Excel. Chúng cho phép tìm kiếm khớp một phần và đáp ứng tiêu chí một phần.
Vui lòng bình luận cho chúng tôi biết bạn dùng ký tự đại diện như thế nào, tổ hợp nào nâng cao tìm kiếm, và sở thích tên bé ở vùng/quốc gia bạn.
Đọc Thêm Liên Quan
- Cách Tìm Và Thay Giá Trị Dùng Ký Tự Đại Diện Trong Excel
- INDEX MATCH Nhiều Tiêu Chí Với Ký Tự Đại Diện Trong Excel (Hướng Dẫn Hoàn Chỉnh)
- Bộ Lọc Nâng Cao Excel [Nhiều Cột & Tiêu Chí, Dùng Công Thức & Ký Tự Đại Diện]