Microsoft Excel là một công cụ mạnh mẽ để quản lý và phân tích dữ liệu, nhưng đôi khi việc tìm kiếm hoặc xử lý các chuỗi văn bản không chính xác (fuzzy match) có thể trở thành thách thức. Đây là lúc các ký tự đại diện (wildcards) trong Excel phát huy tác dụng. Wildcards cho phép bạn tìm kiếm các kết quả khớp một phần, mở rộng bộ lọc và xây dựng các công thức tham chiếu đến các ô chứa chuỗi ký tự cụ thể mà không cần biết chính xác toàn bộ chuỗi đó. Chúng đại diện cho các ký tự không được chỉ định, giúp bạn định vị các giá trị văn bản với những “khớp mờ” một cách linh hoạt và hiệu quả, nâng cao đáng kể khả năng làm việc với dữ liệu trên bảng tính của bạn.
Các Ký Tự Đại Diện (Wildcards) Cơ Bản Trong Excel
Để nắm vững cách sử dụng wildcards, việc hiểu rõ mục đích và chức năng của từng loại là vô cùng quan trọng. Excel cung cấp ba ký tự đại diện chính, mỗi loại phục vụ một mục đích riêng biệt.
Ký Tự Đại Diện 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à phổ biến nhất trong Microsoft Excel. Nó đạ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. Điều này biến dấu sao thành một công cụ cực kỳ linh hoạt cho các tìm kiếm mở rộng.
Ví dụ:
*OK*khớp với bất kỳ ô nào 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ự nào).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ự nào), nhưng không có ký tự nào đứng trước.*OKkhớ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ự nào).
| 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 |
Ký Tự Đại Diện Dấu Hỏi Chấm (?): Đại Diện Cho Một Ký Tự Đơn Lẻ
Ký tự đại diện thứ hai của Microsoft Excel là dấu hỏi chấm (?), nó thay thế cho bất kỳ một ký tự đơn lẻ nào. Dấu hỏi chấm rất hữu ích khi bạn biết vị trí của các ký tự nhưng không biết chính xác chúng là gì.
Ví dụ:
?OK?khớp với bất kỳ ô nào chứa một ký tự đơn lẻ đứng trước “OK” và một ký tự đơn lẻ đứng sau.OK?khớp với bất kỳ ô nào chứa một ký tự đơn lẻ đứng sau “OK”, nhưng không có ký tự nào đứng trước.?OKkhớp với bất kỳ ô nào chứa một ký tự đơn lẻ đứng trước “OK”, nhưng 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 Ký Tự Dấu Hỏi Chấm Và Dấu Sao
Bạn có thể kết hợp dấu hỏi chấm và dấu sao để tìm kiếm các kết quả có số lượng ký tự hữu hạn ở một số vị trí, nhưng lại có bất kỳ số lượng ký tự nào ở những vị trí khác. Sự kết hợp này mang lại khả năng tìm kiếm phức tạp và chính xác hơn cho các chuỗi dữ liệu trong Excel.
Ví dụ:
??OK*khớp với bất kỳ ô nào bắt đầu bằng hai ký tự, sau đó chứa “OK”, với 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 đó chứa “OK”, và kết thúc bằng một ký tự khác.?OK*khớp với bất kỳ ô nào bắt đầu bằng một ký tự đơn lẻ, sau đó chứa “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ỏ Chức Nă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 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, chứ không phải chức năng đại diện của chúng. Đâ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 không muốn chúng được coi là ký tự đại diện.
Ví dụ:
*~?khớp với bất kỳ ô nào chứa bất kỳ số lượng ký tự nào (bao gồm cả không có) ở đầu và một dấu hỏi chấm ở cuối.*~?*khớp với bất kỳ ô nào chứa bất kỳ số lượng ký tự nào (bao gồm cả không có) ở hai bên của một dấu hỏi chấm.*~*?khớp với bất kỳ ô nào bắt đầu bằng bất kỳ số lượng ký tự nào, theo sau là một dấu sao, 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 Wildcards Trong Tìm Kiếm Và Thay Thế (Find And Replace)
Một trong những ứng dụng phổ biến nhất của các ký tự đại diện trong Microsoft Excel là tìm kiếm các ký tự trong sổ làm việc và, nếu cần, thay thế chúng bằng các lựa chọn khác. Công cụ Tìm và Thay thế là một tính năng mạnh mẽ và thường bị bỏ qua trong Excel, giúp bạn thao tác với dữ liệu một cách linh hoạt.
Biểu tượng Excel cùng các ký tự đại diện (*), (?) minh họa tính năng tìm kiếm linh hoạt.
Tìm Kiếm Chuỗi Ký Tự Cụ Thể
Hãy xem xét danh sách các mã sản phẩm sau. Các chữ cái ở đầu đạ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 đại diện cho 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), và 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 mã sản phẩm và giá trong Excel, chuẩn bị cho việc tìm kiếm bằng ký tự đại diện.
Giả sử 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, và 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, theo sau là số 2. Bạn cần sử dụng dấu sao ở đây, vì một số quốc gia được đại diện bởi hai chữ cái trong khi những quốc gia khác được đại diện bởi ba chữ cái.
- Hai dấu hỏi chấm theo sau 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.
Sau đó, khi bạn nhấp vào “Find All”, bạn sẽ thấy một danh sách ở 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 các tiêu chí này.
Sử dụng ký tự đại diện (*), (?) trong hộp thoại Tìm và Thay thế của Excel để lọc mã sản phẩm.
Nhấp vào bất kỳ kết quả nào trong hộp thoại Find And Replace để chuyển đến ô tương ứng trong bảng tính của bạn.
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ị hạn chế bán hàng trong nước hoặc quốc tế (nói cách khác, các mã chứa quốc gia và một số có ba chữ số bắt đầu bằng 1, nhưng không kết thúc bằng một 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 lạ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ả tìm kiếm không mong muốn khi chưa chọn "Match Entire Cell Contents" trong Excel.
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 bạn đã nhập cho truy vấn. Vì vậy, để loại trừ những mã này và chỉ trả về các mã hoàn toàn khớp với truy vấn của bạn, hãy nhấp vào “Options”, đánh dấu chọn “Match Entire Cell Contents”, và nhấp vào “Find All” một lần nữa.
Tùy chọn "Match Entire Cell Contents" được chọn để tối ưu kết quả tìm kiếm với Wildcards trong Excel.
Lần này, chỉ những kết quả mong muốn được hiển thị.
Tìm Và Thay Thế Chuỗi Ký Tự
Danh sách Excel này hiển thị những cầu thủ bóng đá yêu thích nhất mọi thời đại của những người trả lời. Tuy nhiên, tên Maradona đã bị viết sai theo ba cách khác nhau, và bạn muốn sửa lại những lỗi sai đó.
Danh sách tên cầu thủ yêu thích với nhiều cách viết sai "Maradona" trong Excel.
Đầ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 lỗi chính tả đề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.
Sau đó, trong trường Replace With, nhập cách viết đúng:
Maradonavà nhấp vào “Replace All” để xác nhận các sửa đổi.
Hộp thoại "Tìm và Thay thế" trong Excel với Wildcard Mar* để sửa lỗi chính tả tên cầu thủ.
Bây giờ, tất cả các trường hợp tên cầu thủ này đều được viết đúng chính tả và nhất quán.
Danh sách tên cầu thủ đã được chuẩn hóa sau khi sử dụng tính năng Tìm và Thay thế với Wildcards trong Excel.
Sử Dụng Wildcards Trong Các Công Thức Excel
Ngoài việc sử dụng wildcards trong hộp thoại Find And Replace của Excel, bạn có thể sử dụng chúng làm đối số cho nhiều hàm khác nhau, chẳng hạn như XLOOKUP để tạo tra cứu 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ó nguồn gốc từ Vương quốc Anh.
Danh sách mã sản phẩm trong Excel, các mã có chứa "UK" được tô sáng, chuẩn bị cho hàm SUMIF.
Biểu tượng Excel cùng ý tưởng sáng tạo, đại diện cho việc sử dụng các hàm như SUMIF để xử lý dữ liệu.
Một cách để thực hiện điều này là nhập:
=SUMIF(A2:A18,"UK*",B2:B18)vào một ô trống, trong đó:
- SUMIF cộng các giá trị dựa trên tiêu chí đã đặt.
- A2:A18 là 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”, theo sau là bất kỳ số lượng ký tự nào.
- B2:B18 là mảng để tính tổng.
Các ký tự đại diện và giá trị văn bản phải luôn được đặt trong dấu ngoặc kép trong các công thức Excel.
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.
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 trong Excel sử dụng tham chiếu ô và ký tự đại diện để tính tổng giá sản phẩm theo điều kiện.
Bây giờ, khi bạn thay đổi mã quốc gia, tổng số sẽ tự động cập nhật.
Bạn có thể sử dụng công cụ Data Validation của Excel để tạo 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.
Áp Dụng Wildcards Trong Bộ Lọc (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. Tính năng lọc dữ liệu là một trong những công cụ cơ bản và hữu ích nhất để phân tích thông tin trên bảng tính của bạn.
Một máy tính xách tay hiển thị ứng dụng Microsoft Excel, thể hiện môi trường làm việc với dữ liệu.
Cho 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.
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ữ A.
Để thực hiện điều này, nhấp vào nút lọc của cột Product_Code, và 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 (như AUS458) hoặc ở giữa (như USA320B).
Tìm kiếm bằng bộ lọc Excel với ký tự đại diện dấu sao (*A) để hiển thị các mã sản phẩm 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.
Những Lưu Ý Quan Trọng Khi Sử Dụng Wildcards
Trước khi bạn bắt đầu tận dụng tối đa các ký tự đại diện trong bảng tính Excel của mình, hãy dành một chút thời gian để đọc những lưu ý cuối cùng sau đây:
- Hạn chế với số thuần túy trong công thức: Khi sử dụng wildcards 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 wildcards tự động chuyển đổi số thành văn bản. Tuy nhiên, việc tìm kiếm các chữ số trong một phạm vi số bằng công cụ Tìm và Thay thế hoặc bộ lọc của Excel sẽ hoạt động tốt, cũng như việc tìm số trong các ô có chứa cả giá trị văn bản.
- Không phải tất cả các hàm đều hỗ trợ: Mặc dù nhiều hàm hỗ trợ việc sử dụng wildcards, 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 nếu bạn không chắc chắn.
- Độ phức tạp và nguy cơ lỗi: Việc thêm wildcards vào công thức làm cho chúng trở nên phức tạp hơn, có khả năng tăng nguy cơ lỗi nếu bạn không cẩn thận. Luôn nhớ nhúng wildcards vào trong dấu ngoặc kép trong các công thức!
- Khác biệt so với Word: Ngoài việc sử dụng wildcards để mở rộng khả năng tìm kiếm 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 chính giữa hai phần mềm 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 wildcards để khớp với nhiều mục hơn.
Kết Luận
Ký tự đại diện (wildcards) là một bộ công cụ vô giá trong Microsoft Excel, cho phép bạn thực hiện các thao tác tìm kiếm, lọc và xử lý dữ liệu phức tạp mà các phương pháp thông thường không thể làm được. Từ việc tìm kiếm các chuỗi văn bản không chính xác, chuẩn hóa dữ liệu bị lỗi, đến việc xây dựng các công thức động và lọc bảng tính của bạn một cách linh hoạt, wildcards mở ra cánh cửa cho hiệu quả công việc vượt trội.
Việc nắm vững cách sử dụng dấu sao (*), dấu hỏi chấm (?) và dấu ngã (~) sẽ giúp bạn tiết kiệm đáng kể thời gian và công sức khi làm việc với lượng lớn dữ liệu. Hãy thực hành các ví dụ trong bài viết và thử nghiệm với dữ liệu của riêng bạn để khai thác tối đa tiềm năng của những ký tự đặc biệt này. Việc áp dụng thành thạo wildcards không chỉ nâng cao kỹ năng Excel của bạn mà còn giúp bạn trở thành một chuyên gia xử lý dữ liệu thực thụ. Hãy tiếp tục khám phá và đừng ngần ngại chia sẻ những mẹo và thủ thuật Excel của bạn với cộng đồng công nghệ trên thoibaocongnghe.net nhé!
