Trong kỷ nguyên dữ liệu số, việc quản lý và phân tích thông tin một cách hiệu quả là chìa khóa thành công cho mọi cá nhân và doanh nghiệp. Microsoft Excel, với vai trò là công cụ bảng tính hàng đầu, cung cấp vô số tính năng mạnh mẽ để xử lý dữ liệu. Tuy nhiên, không phải lúc nào dữ liệu của chúng ta cũng được sắp xếp gọn gàng theo một khuôn mẫu nhất định. Đôi khi, chúng ta cần tìm kiếm, lọc hoặc áp dụng công thức cho các chuỗi văn bản không hoàn toàn trùng khớp, mà chỉ chứa một phần thông tin nhất định. Đây chính là lúc “ký tự đại diện” (Wildcards) phát huy sức mạnh vượt trội của mình.
Ký tự đại diện là những biểu tượng đặc biệt cho phép bạn tìm kiếm các kết quả khớp một phần, mở rộng phạm vi lọc dữ liệu và tạo ra các công thức tham chiếu đến các ô chứa những chuỗi ký tự nhất định. Chúng đóng vai trò như “người đại diện” cho các ký tự không xác định, giúp bạn định vị các giá trị văn bản với các kết quả “khớp mờ” một cách linh hoạt và hiệu quả hơn. Với khả năng này, ký tự đại diện không chỉ giúp tiết kiệm thời gian mà còn nâng cao đáng kể độ chính xác và khả năng tùy biến trong việc thao tác với dữ liệu trên Excel. Bài viết này của tinhoccongnghe.net sẽ hướng dẫn bạn từ A đến Z về cách sử dụng các ký tự đại diện, từ những khái niệm cơ bản đến các ứng dụng nâng cao trong tìm kiếm, thay thế, công thức và bộ lọc, giúp bạn trở thành chuyên gia Excel thực thụ.
Ký Tự Đại Diện Cơ Bản Trong Excel: Dấu Sao (*) Và Dấu Hỏi Chấm (?)
Trong Excel, có hai loại ký tự đại diện chính mà bạn cần nắm vững, và việc hiểu rõ mục đích của chúng là nền tảng để sử dụng Wildcards một cách hiệu quả.
Dấu Sao (*): Đại Diện Cho Bất Kỳ Số Lượng Ký Tự Nào
Dấu sao (*) là ký tự đại diện đầu tiên và có ý nghĩa là đại diện cho bất kỳ số lượng ký tự nào, bao gồm cả trường hợp không có ký tự nào.
Ví dụ minh họa:
*OK*: Khớp với bất kỳ ô nào có chứa chuỗi “OK”, với bất kỳ số lượng ký tự nào đứng trước hoặc đứng sau (bao gồm cả không có ký tự).OK*: Khớp với bất kỳ ô nào bắt đầu bằng “OK”, với bất kỳ số lượng ký tự nào đứng sau (bao gồm cả không có ký tự), và không có ký tự nào đứng trước.*OK: Khớp với bất kỳ ô nào kết thúc bằng “OK”, với bất kỳ số lượng ký tự nào đứng trước (bao gồm cả không có ký tự), và không có ký tự nào đứng sau.
| Giá Trị Kiểm Tra | Tiêu Chí: *OK* |
Tiêu Chí: OK* |
Tiêu Chí: *OK |
|---|---|---|---|
| OK | Khớp | Khớp | Khớp |
| Oklahoma | Khớp | Khớp | Không khớp |
| Shook | Khớp | Không khớp | Khớp |
| Look | Khớp | Không khớp | Không khớp |
| Looking | Khớp | Không khớp | Không khớp |
Dấu Hỏi Chấm (?): Đại Diện Cho Bất Kỳ Một Ký Tự Nào
Dấu hỏi chấm (?) là ký tự đại diện thứ hai của Excel, có tác dụng thay thế cho bất kỳ một ký tự đơn lẻ nào.
Ví dụ minh họa:
?OK?: Khớp với bất kỳ ô nào chứa chuỗi “OK”, với một ký tự đứng trước và một ký tự đứng sau.OK?: Khớp với bất kỳ ô nào chứa chuỗi “OK”, với một ký tự đứng sau, và không có ký tự nào đứng trước.?OK: Khớp với bất kỳ ô nào chứa chuỗi “OK”, với một ký tự đứng trước, và không có ký tự nào đứng sau.
| Giá Trị Kiểm Tra | Tiêu Chí: ?OK? |
Tiêu Chí: OK? |
Tiêu Chí: ?OK |
|---|---|---|---|
| Joke | Khớp | Không khớp | Không khớp |
| Oka | Không khớp | Khớp | Không khớp |
| Wok | Không khớp | Không khớp | Khớp |
Kết Hợp Dấu Hỏi Chấm Và Dấu Sao Để Tìm Kiếm Linh Hoạt Hơn
Bạn hoàn toàn có thể kết hợp dấu hỏi chấm và dấu sao để tạo ra các tiêu chí tìm kiếm phức tạp hơn, cho phép bạn định rõ số lượng ký tự trong một số vị trí nhất định, nhưng lại linh hoạt với bất kỳ số lượng ký tự nào ở các vị trí khác.
Ví dụ:
??OK*: Khớp với bất kỳ ô nào bắt đầu bằng hai ký tự, sau đó là “OK”, và có bất kỳ số lượng ký tự nào ở cuối (bao gồm cả không có).*OK?: Khớp với bất kỳ ô nào bắt đầu bằng bất kỳ số lượng ký tự nào (bao gồm cả không có), sau đó là “OK”, và kết thúc bằng một ký tự đơn lẻ.?OK*: Khớp với bất kỳ ô nào bắt đầu bằng một ký tự đơn lẻ, sau đó là “OK”, và có bất kỳ số lượng ký tự nào sau đó (bao gồm cả không có).
| Giá Trị Kiểm Tra | Tiêu Chí: ??OK* |
Tiêu Chí: *OK? |
Tiêu Chí: ?OK* |
|---|---|---|---|
| Took | Khớp | Không khớp | Không khớp |
| Books | Khớp | Khớp | Không khớp |
| Spooky | Không khớp | Khớp | Không khớp |
| Poky | Không khớp | Khớp | Khớp |
| Jokes | Không khớp | Không khớp | Khớp |
Hủy Bỏ Tác Dụng Của Ký Tự Đại Diện: Dấu Ngã (~)
Đôi khi, bạn có thể muốn tìm kiếm chính xác các ký tự dấu hỏi chấm (?) hoặc dấu sao (*) trong bảng tính Excel của mình, thay vì sử dụng chúng làm ký tự đại diện. Đây là lúc ký tự đại diện thứ ba – dấu ngã (~) – phát huy tác dụng. Bạn chỉ cần đặt dấu ngã trước dấu hỏi chấm hoặc dấu sao để báo cho Excel biết rằng bạn muốn coi chúng là các ký tự thông thường.
Ví dụ:
*~?: Khớp với bất kỳ ô nào bắt đầu bằng bất kỳ số lượng ký tự nào (bao gồm cả không có), và kết thúc bằng một dấu hỏi chấm.*~?*: Khớp với bất kỳ ô nào chứa một dấu hỏi chấm, với bất kỳ số lượng ký tự nào ở hai bên (bao gồm cả không có).*~**?: Khớp với bất kỳ ô nào bắt đầu bằng bất kỳ số lượng ký tự nào, tiếp theo là một dấu sao, và sau đó là một ký tự đơn lẻ.
| Giá Trị Kiểm Tra | Tiêu Chí: *~? |
Tiêu Chí: *~?* |
Tiêu Chí: *~**? |
|---|---|---|---|
| Dinner? | Khớp | Khớp | Không khớp |
| ???k | Không khớp | Khớp | Không khớp |
| D?*y | Không khớp | Khớp | Khớp |
Ứng Dụng Thực Tiễn Của Ký Tự Đại Diện Trong Excel
Một trong những công dụng phổ biến nhất của ký tự đại diện trong Microsoft Excel là tìm kiếm các chuỗi ký tự trong một bảng tính và, nếu cần, thay thế chúng bằng các lựa chọn khác.
Sử Dụng Wildcards Trong Chức Năng Tìm Kiếm (Find)
Hãy xem xét một ví dụ thực tế. Giả sử bạn có một danh sách các mã sản phẩm. Các chữ cái ở đầu mã đại diện cho nơi sản xuất (AUS cho Úc, UK cho Vương quốc Anh, USA cho Hoa Kỳ và CAN cho Canada). Chữ số đầu tiên sau đó thể hiện phòng ban cửa hàng liên quan (1 là quần áo, 2 là đồ gia dụng, 3 là thể thao và 4 là vườn). Cuối cùng, các mã có chữ cái ở cuối chỉ dành cho bán hàng trong nước (A) hoặc bán hàng quốc tế (B).
Danh sách các mã sản phẩm và giá trong Microsoft Excel.
Tìm Chuỗi Ký Tự Cụ Thể
Bạn muốn tìm tất cả các sản phẩm đồ gia dụng chỉ dành cho bán hàng trong nước.
- Nhấn Ctrl+F để mở tab Find của hộp thoại Find And Replace.
- Trong trường Find What, nhập:
*2??ATrong đó:
- Dấu sao (*) và số 2 báo cho Excel tìm kiếm các ô bắt đầu bằng bất kỳ số lượng ký tự nào, sau đó là số 2. Bạn cần sử dụng dấu sao ở đây vì một số quốc gia được biểu thị bằng hai chữ cái, trong khi một số khác lại là ba.
- Hai dấu hỏi chấm (??) tiếp theo là chữ A báo cho Excel rằng phần còn lại của chuỗi phải bao gồm hai ký tự và chữ A.
Khi bạn nhấp vào “Find All”, bạn sẽ thấy danh sách kết quả ở dưới cùng của hộp thoại Find And Replace hiển thị bất kỳ kết quả nào khớp với tiêu chí này.
Sử dụng Wildcards trong hộp thoại Find and Replace của Excel để tìm các mã sản phẩm cụ thể.
Nhấp vào bất kỳ kết quả nào trong hộp thoại Find And Replace để nhảy đến ô tương ứng trong bảng tính của bạn.
Tìm Chuỗi Ký Tự Với Điều Kiện Chính Xác (Match Entire Cell Contents)
Bây giờ, giả sử mục tiêu của bạn là trả về các mã cho tất cả các sản phẩm quần áo không bị giới hạn cho bán hàng trong nước hoặc quốc tế (nói cách khác, các mã chứa một quốc gia và một số ba chữ số bắt đầu bằng 1, nhưng không kết thúc bằng chữ cái).
- Trong trường Find What, nhập:
*1?? - Tuy nhiên, khi bạn nhấp vào “Find All”, một sản phẩm kết thúc bằng một chữ cái xuất hiện trong kết quả, cho thấy có điều gì đó còn thiếu trong tiêu chí.
Kết quả Find trong Excel trả về một mã sản phẩm không mong muốn.
Trên thực tế, điều này xảy ra vì một phần của mã không chính xác chứa chuỗi con mà bạn đã nhập cho truy vấn. Vì vậy, để loại trừ các mã này và chỉ trả về các mã khớp hoàn toàn với truy vấn của bạn, hãy nhấp vào “Options”, chọn “Match Entire Cell Contents”, và nhấp vào “Find All” một lần nữa.
Hộp kiểm Match Entire Cell Contents trong Excel.
Lần này, chỉ những kết quả mong muốn được hiển thị.
Sử Dụng Wildcards Trong Chức Năng Tìm Và Thay Thế (Find and Replace)
Danh sách này trong Excel hiển thị những cầu thủ bóng đá yêu thích mọi thời đại của những người trả lời khảo sát. Tuy nhiên, tên của Maradona đã được viết theo ba cách khác nhau, và bạn muốn sửa lại những tên không chính xác.
Danh sách cầu thủ bóng đá yêu thích của người được hỏi trong Excel với nhiều cách viết sai tên Maradona được tô sáng.
- Đầu tiên, nhấn Ctrl+H để mở tab Replace của hộp thoại Find And Replace.
- Sau đó, trong trường Find What, nhập:
Mar*Vì tất cả các cách viết sai đều bắt đầu bằng ba chữ cái này, và đây là tên duy nhất trong danh sách bắt đầu bằng chuỗi ký tự này.
- Tiếp theo, trong trường Replace With, nhập cách viết đúng:
Maradona - Và nhấp vào “Replace All” để xác nhận các chỉnh sửa.
Hộp thoại Find and Replace của Excel với "Mar*" trong trường Find What và "Maradona" trong trường Replace With.
Bây giờ, tất cả các trường hợp tên của cầu thủ này đều được viết đúng và nhất quán.
Danh sách cầu thủ bóng đá yêu thích của người được hỏi trong Excel với tên Maradona được viết nhất quán.
Sử Dụng Wildcards Trong Công Thức Excel
Ngoài việc sử dụng ký tự đại diện trong hộp thoại Find And Replace của Excel, bạn có thể sử dụng chúng trong các đối số cho nhiều hàm khác nhau, chẳng hạn như XLOOKUP để tạo tìm kiếm khớp một phần, COUNTIFS để đếm số ô khớp với nhiều tiêu chí, và nhiều hàm khác nữa.
Trong ví dụ này, bạn muốn sử dụng hàm SUMIF để tính tổng giá của các sản phẩm có xuất xứ từ Vương quốc Anh (UK).
Danh sách mã sản phẩm trong Excel, với tất cả các mã chứa chữ UK được tô sáng.
Một cách để làm điều này là nhập công thức sau vào một ô trống:
=SUMIF(A2:A18,"UK*",B2:B18)Trong đó:
SUMIFcộng các giá trị lại với nhau dựa trên các tiêu chí đã đặt.A2:A18là phạm vi chứa các giá trị cần kiểm tra."UK*"báo cho Excel tìm bất kỳ ô nào trong A2 đến A18 bắt đầu bằng “UK”, sau đó là bất kỳ số lượng ký tự nào.B2:B18là mảng cần tính tổng.
Lưu ý quan trọng: Ký tự đại diện và các giá trị văn bản luôn phải được đặt trong dấu ngoặc kép trong các công thức Excel.
Công thức SUMIF với Wildcards trong Excel để trả về tổng giá của tất cả các mã sản phẩm bắt đầu bằng "UK".
Tuy nhiên, giả sử bạn muốn tìm tổng giá của các sản phẩm từ một quốc gia khác. Trong ví dụ trên, bạn sẽ cần thay đổi công thức thủ công, một phương pháp tốn thời gian và dễ dẫn đến lỗi hơn.
Thay vào đó, hãy nhập tiêu chí (trong trường hợp này là “UK”) vào một ô riêng biệt. Sau đó, tham chiếu ô đó trong công thức, sử dụng ký hiệu dấu và (&) để phân tách tham chiếu ô khỏi ký tự đại diện được nhúng trong dấu ngoặc kép:
=SUMIF(A2:A18,D2&"*",B2:B18)
Công thức SUMIF với tham chiếu ô được nối với Wildcard trong Excel để trả về tổng giá của tất cả các mã sản phẩm bắt đầu bằng "UK".
Bây giờ, khi bạn thay đổi mã quốc gia trong ô D2, tổng sẽ tự động cập nhật.
Công thức SUMIF với tham chiếu ô được nối với Wildcard trong Excel để trả về tổng giá của tất cả các mã sản phẩm bắt đầu bằng "AUS".
Bạn có thể sử dụng công cụ Data Validation của Excel để tạo một danh sách thả xuống các quốc gia, giúp việc thay đổi tiêu chí trở nên dễ dàng hơn.
Sử Dụng Wildcards Trong Tính Năng Lọc Dữ Liệu (Filters)
Một ứng dụng khác của ký tự đại diện trong Microsoft Excel là trong các bộ lọc.
Dù bạn đang làm việc trong một bảng Excel đã định dạng hay một phạm vi chưa định dạng, hãy chọn bất kỳ ô nào trong mảng và nhấn Ctrl+Shift+L để thêm các nút lọc.
Một bảng Excel chưa định dạng với các nút lọc ở hàng tiêu đề.
Bây giờ, giả sử bạn muốn lọc các mã sản phẩm để chỉ bao gồm những mã kết thúc bằng chữ cái A.
- Để làm điều này, nhấp vào nút lọc của cột “Product_Code”.
- Trong trường Search, nhập:
*AĐể bao gồm tất cả các mã kết thúc bằng chữ A. Vì ký tự đại diện dấu sao được đặt trước chữ A trong tìm kiếm, nó sẽ không chọn bất kỳ mã sản phẩm nào chứa A ở đầu (chẳng hạn như AUS458) hoặc ở giữa (chẳng hạn như USA320B).
Tìm kiếm bộ lọc trong Microsoft Excel bao gồm ký tự đại diện dấu sao để chỉ trả về các giá trị kết thúc bằng chữ A.
Sau khi bạn đã xem trước kết quả trong cửa sổ bật lên của bộ lọc, nhấp vào “OK” và xem dữ liệu của bạn được tinh chỉnh để khớp với tiêu chí của bạn.
Dữ liệu Excel được lọc bằng ký tự đại diện để chỉ bao gồm các giá trị kết thúc bằng chữ A.
Những Lưu Ý Quan Trọng Khi Sử Dụng Ký Tự Đại Diện Trong Excel
Trước khi bạn bắt đầu áp dụng các ký tự đại diện vào bảng tính Excel của mình, hãy dành một chút thời gian để đọc những điểm lưu ý cuối cùng sau đây:
- Tìm kiếm số trong phạm vi số: Khi sử dụng ký tự đại diện trong công thức, bạn sẽ gặp trở ngại nếu cố gắng tìm một chữ số trong một phạm vi số. Điều này là do ký tự đại diện tự động chuyển đổi số thành văn bản. Tuy nhiên, việc tìm kiếm chữ số trong một phạm vi số bằng công cụ Find And Replace hoặc bộ lọc của Excel sẽ hoạt động tốt, tương tự như tìm kiếm số trong các ô cũng chứa giá trị văn bản.
- Hàm không hỗ trợ Wildcards: Mặc dù nhiều hàm hỗ trợ sử dụng ký tự đại diện, nhưng một số hàm – bao gồm cả hàm IF – thì không. Hãy kiểm tra tài liệu của hàm cụ thể nếu bạn không chắc chắn.
- Sự phức tạp của công thức: Việc thêm ký tự đại diện vào công thức khiến chúng trở nên phức tạp hơn, có khả năng làm tăng nguy cơ xảy ra lỗi nếu bạn không cẩn thận. Luôn nhớ nhúng các ký tự đại diện trong dấu ngoặc kép khi sử dụng trong công thức!
Ngoài việc sử dụng ký tự đại diện để mở rộng khả năng tìm kiếm của bạn trong Excel, bạn cũng có thể sử dụng chúng để tìm và thay thế văn bản trong Microsoft Word. Điểm khác biệt lớn giữa hai ứng dụng là, bên cạnh dấu sao và dấu hỏi chấm, Word còn cho phép bạn sử dụng dấu ngoặc vuông ([ ]) làm ký tự đại diện để khớp với nhiều mục hơn.
Tóm lại, việc nắm vững cách sử dụng các ký tự đại diện trong Excel là một kỹ năng vô cùng giá trị, giúp bạn làm chủ dữ liệu của mình một cách linh hoạt và mạnh mẽ hơn. Từ việc tìm kiếm nhanh chóng các chuỗi ký tự khớp một phần, lọc dữ liệu theo các tiêu chí phức tạp, đến việc xây dựng các công thức động và thông minh, Wildcards sẽ mở ra một thế giới khả năng mới cho công việc của bạn. Hãy thử áp dụng những kiến thức này vào các bảng tính của mình và cảm nhận sự khác biệt mà chúng mang lại. Đừng ngần ngại để lại bình luận bên dưới nếu bạn có bất kỳ câu hỏi nào hoặc muốn chia sẻ mẹo hay khi sử dụng ký tự đại diện trong Excel nhé!
