Excel có ba ký tự đại diện (wildcard) dùng trong điều kiện và tra cứu: * khớp với số ký tự bất kỳ (kể cả không có ký tự nào), ? khớp với đúng một ký tự, và ~ biến * hoặc ? đứng sau nó trở lại thành ký tự thường. =COUNTIF(A2:A7,"*apple*") đếm các ô có chứa apple ở bất kỳ vị trí nào.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Pattern | Count | |
| 2 | Apple juice | *apple* | 4 | |
| 3 | Green apple | apple* | 2 | |
| 4 | Pineapple | *juice | 2 | |
| 5 | Orange juice | ????? | 0 | |
| 6 | Pear | *e | 4 | |
| 7 | Apples |
*apple*là chứaapple: 4 kết quả, vìPineapplecũng được tính.apple*là bắt đầu bằngapple: chỉ cóApple juicevàApples. COUNTIF bỏ qua chữ hoa chữ thường.*juicelà kết thúc bằngjuice.?????là dài đúng năm ký tự. Không sản phẩm nào ở đây có năm ký tự, nên kết quả là 0; gõPeachvào A6 thì đếm được 1.*elà kết thúc bằnge.
Gõ mẫu của riêng bạn vào cột C, như *an* hoặc P*, và kết quả đếm sẽ cập nhật.
Khớp một phần trong VLOOKUP và XLOOKUP
VLOOKUP chấp nhận ký tự đại diện ở chế độ so khớp chính xác (FALSE ở đối số cuối). Hãy nối ký tự đại diện vào giá trị ngay trong công thức, để D2 chỉ cần chứa vài chữ cái đầu:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | Starts with | Price | |
| 2 | Apple juice | 3.5 | Pin | 4 | |
| 3 | Green apple | 1.2 | 4 | ||
| 4 | Pineapple | 4 | |||
| 5 | Orange juice | 3.2 | |||
| 6 | Pear | 0.9 |
Cả hai công thức đều tìm thấy Pineapple. Đổi D2 thành juice: mẫu juice* của VLOOKUP cần văn bản bắt đầu bằng juice nên trả về #N/A, còn mẫu *juice* của XLOOKUP tìm sản phẩm đầu tiên có chứa nó, là Apple juice. Như mọi phép so khớp chính xác, tra cứu bằng ký tự đại diện trả về hàng đầu tiên phù hợp, nên hãy viết mẫu đủ cụ thể.
XLOOKUP chỉ coi * và ? là ký tự đại diện khi đối số thứ năm, match_mode, bằng 2. Nếu không, hàm tìm đúng dấu sao theo nghĩa đen. MATCH chấp nhận ký tự đại diện với match_type bằng 0, còn XMATCH với match_mode bằng 2, giống XLOOKUP. Xem VLOOKUP để biết các đối số còn lại.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Phone products | ||
| 2 | Smartphone | |||
| 3 | Headphones | |||
| 4 | Phone stand | |||
| 5 | Laptop bag | |||
| 6 | Charger |
Đến lượt bạn: Trong D2, đếm các sản phẩm có tên chứa phone ở bất kỳ vị trí nào.
Tính tổng và trung bình với ký tự đại diện
Mọi hàm nhận điều kiện đều đọc điều kiện theo cùng một cách, nên các mẫu này cũng dùng được trong SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS và MINIFS:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Pattern | Total | |
| 2 | North-East | 120 | North* | 285 | |
| 3 | North-West | 95 | *West | 155 | |
| 4 | South | 80 | ????? | 150 | |
| 5 | South-West | 60 | |||
| 6 | East | 110 | |||
| 7 | North | 70 |
North* cộng mọi khu vực bắt đầu bằng North, kể cả chính North, vì * cũng khớp với chuỗi rỗng. ????? cộng các khu vực có đúng năm ký tự: South và North.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | West total | ||
| 2 | North-East | 120 | |||
| 3 | North-West | 95 | |||
| 4 | South | 80 | |||
| 5 | South-West | 60 | |||
| 6 | East | 110 | |||
| 7 | North | 70 |
Đến lượt bạn: Trong E2, tính tổng doanh số của mọi khu vực có tên kết thúc bằng West.
Tìm dấu sao hoặc dấu hỏi thật bằng ~
Để đếm văn bản có chứa một dấu * hoặc ? thật, hãy đặt dấu ngã phía trước nó. Bản thân dấu ngã được viết là ~~.
| A | B | C | |
|---|---|---|---|
| 1 | Note | Count | |
| 2 | Rated 5* | 1 | |
| 3 | Why? | 1 | |
| 4 | Done | 4 | |
| 5 | 5 stars |
C2 đếm các ô chứa một dấu sao thật (chỉ có A2), C3 đếm các ô chứa dấu hỏi. C4 cho thấy mặt còn lại: chỉ riêng "*" khớp với mọi văn bản, nên nó đếm mọi ô văn bản, ở đây là 4. Mẫu này bỏ qua số và ô trống, vì vậy COUNTIF(range,"*") là cách thường dùng để đếm ô văn bản.
Những hàm nào chấp nhận ký tự đại diện
Chấp nhận *, ?, ~ | Không chấp nhận |
|---|---|
| COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS | =, <> và các phép so sánh khác |
| VLOOKUP và HLOOKUP với FALSE | IF khi dùng riêng |
| MATCH với 0 | FIND |
| XLOOKUP và XMATCH với match_mode 2 | FILTER, UNIQUE, SORT |
| SEARCH | SUBSTITUTE, TEXTBEFORE, TEXTAFTER |
| Find and Replace (Ctrl+H), ô lọc dữ liệu |
SEARCH nhận ký tự đại diện ngay trong công thức: =SEARCH("b?d","a bad day") trả về 3 trong Excel (FIND và SEARCH có chi tiết). Với FILTER, hãy dùng ISNUMBER(SEARCH(...)) làm điều kiện thay vì một mẫu.
Lỗi thường gặp: ký tự đại diện sau dấu =
Toán tử = không bao giờ đọc ký tự đại diện: =A2="*apple*" hỏi xem A2 có chứa đúng bảy ký tự *apple* hay không. Trong Excel, với Green apple trong A2:
=A2="*apple*" FALSE
=IF(A2="*apple*","yes","no") no
Thay vào đó, hãy đặt phép kiểm tra trong COUNTIF, hàm này trả về 1 hoặc 0 cho một ô đơn, hoặc dùng SEARCH:
| A | B | C | |
|---|---|---|---|
| 1 | Product | COUNTIF test | SEARCH test |
| 2 | Green apple | contains apple | contains apple |
| 3 | Pear | no | no |
IF coi số 1 từ COUNTIF là TRUE và số 0 là FALSE. Phiên bản dùng SEARCH không cần ký tự đại diện nào, vì SEARCH vốn đã tìm văn bản ở bất kỳ vị trí nào trong ô.
Câu hỏi thường gặp
Các ký tự đại diện trong Excel là gì?
* khớp với số ký tự bất kỳ, kể cả không có ký tự nào; ? khớp với đúng một ký tự; ~ đặt trước *, ? hoặc ~ biến nó thành ký tự thường. "*apple*" nghĩa là chứa apple, "A*" là bắt đầu bằng A, "???" là đúng ba ký tự.
Làm sao dùng ký tự đại diện trong VLOOKUP?
Nối ký tự đại diện vào giá trị cần tìm và dùng so khớp chính xác: =VLOOKUP(E2&"*",A2:B6,2,FALSE) tìm mục đầu tiên bắt đầu bằng E2. Trong XLOOKUP, đặt match_mode là 2: =XLOOKUP("*"&E2&"*",A2:A6,B2:B6,"none",2).
Vì sao ký tự đại diện không chạy trong công thức IF?
Phép so sánh = không hiểu ký tự đại diện, nên =IF(A2="*apple*",...) chỉ khớp với đúng văn bản *apple*. Hãy dùng =IF(COUNTIF(A2,"*apple*"),"Yes","No") hoặc =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No").
Làm sao đếm các ô có chứa dấu sao?
Đặt dấu ngã trước nó: =COUNTIF(A2:A10,"*~**"). Dấu * đầu và cuối là ký tự đại diện, còn ~* là một dấu sao thật.
