Bạn đang xem bài viết Cách Sử Dụng Toán Tử Chuỗi Và Ký Tự Đại Diện được cập nhật mới nhất tháng 12 năm 2023 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Ghép nối các chuỗi hoặc nội dung của ô
Một số ví dụ về việc sử dụng toán tử ghép nối gồm:
“Abc”&”Def” trả về “AbcDef”.
“Abc”&A1 trả về “Abc2” nếu ô A1 chứa 2.
A1&A2 trả về “12” nếu ô A1 chứa 1 và ô A2 chứa 2.
=B2&”, “&E2 trả về “Last, First” nếu B2 chứa “Last” và E2 chứa “First”.
Sử dụng ký tự đại diện để khớp với ký tự duy nhất bất kỳ
Một số ví dụ về việc sử dụng ? trong các mô hình khớp gồm:
“Ea?” sẽ khớp với bất kỳ chuỗi nào bắt đầu bằng “Ea” và chứa chính xác một ký tự bổ sung, chẳng hạn như “Ea2” hoặc “Eac”.
“Th??” sẽ khớp với bất kỳ chuỗi nào bắt đầu bằng “Th” và chứa chính xác hai ký tự bổ sung, chẳng hạn như “Then” và “That”.
=COUNTIF(B2:E7,”?ip”) trả về số lượng các ô trong dãy B2:E7 chứa một giá trị bắt đầu bằng một ký tự đứng trước “ip” chẳng hạn như “rip” và “tip”. Biểu thức này không khớp với “drip” hoặc “trip”.
Sử dụng ký tự đại diện để khớp với bất kỳ số lượng ký tự nào
Một số ví dụ về việc sử dụng * trong các mô hình khớp gồm:
“*ed” sẽ khớp với chuỗi có độ dài bất kỳ kết thúc bằng “ed”, chẳng hạn như “Ted” hoặc “Treed”.
=COUNTIF(B2:E7,”*it”) trả về số lượng các ô trong vùng B2:E7 chứa một giá trị kết thúc bằng “it” chẳng hạn như “bit” và “mit”. Giá trị “mitt” không khớp.
Khớp ký tự đại diện
Một số ví dụ về việc sử dụng ký tự ~ trong các mô hình khớp gồm:
“~?” sẽ khớp dấu chấm hỏi, thay vì sử dụng dấu chấm hỏi để khớp với bất kỳ ký tự duy nhất nào.
=COUNTIF(E,”~*”) trả về số lượng các ô trong cột E chứa ký tự dấu hoa thị.
=SEARCH(“~?”,B2) trả về 19 nếu ô B2 chứa “That is a question? Yes it is!” vì dấu chấm hỏi là ký tự thứ 19 trong chuỗi.
Sử dụng nhiều ký tự đại diện trong điều kiện
Các ký tự đại diện (? * ~) có thể được sử dụng cùng nhau trong các biểu thức cho phép các điều kiện. Một số ví dụ gồm:
“*a?” sẽ khớp với bất kỳ biểu thức nào chứa ký tự “a” đứng trước bất kỳ ký tự duy nhất nào khác, chẳng hạn như “That”, “Cap” và”Irregular”.
=COUNTIF(B2:E7,”*on?”) trả về số đếm số lượng các ô trong dãy B2:E7 chứa giá trị bắt đầu bằng bất kỳ số lượng ký tự nào (bao gồm không có) đứng trước “on” và sau đó là một ký tự duy nhất. Biểu thức này khớp với các từ chẳng hạn như “alone”, “bone”, “one” và “none”. Biểu thức này không khớp với “only” (có hai ký tự sau “on”) hoặc “eon” (không có ký tự nào sau “on”).
Cách Sử Dụng Toán Tử Chuỗi Và Ký Tự Đại Diện, Hỗ Trợ Numbers
Toán tử chuỗi (&) có thể được sử dụng trong công thức và ký tự đại diện (*,?,~) có thể được sử dụng trong điều kiện.
Ghép nối các chuỗi hoặc nội dung của ô
Một số ví dụ về việc sử dụng toán tử ghép nối gồm:
“Abc”&”Def” trả về “AbcDef”.
“Abc”&A1 trả về “Abc2” nếu ô A1 chứa 2.
A1&A2 trả về “12” nếu ô A1 chứa 1 và ô A2 chứa 2.
=B2&”, “&E2 trả về “Last, First” nếu B2 chứa “Last” và E2 chứa “First”.
Sử dụng ký tự đại diện để khớp với ký tự duy nhất bất kỳ
Một số ví dụ về việc sử dụng ? trong các mô hình khớp gồm:
“Ea?” sẽ khớp với bất kỳ chuỗi nào bắt đầu bằng “Ea” và chứa chính xác một ký tự bổ sung, chẳng hạn như “Ea2” hoặc “Eac”.
“Th??” sẽ khớp với bất kỳ chuỗi nào bắt đầu bằng “Th” và chứa chính xác hai ký tự bổ sung, chẳng hạn như “Then” và “That”.
=COUNTIF(B2:E7,“?ip”) trả về số lượng các ô trong dãy B2:E7 chứa một giá trị bắt đầu bằng một ký tự đứng trước “ip” chẳng hạn như “rip” và “tip”. Biểu thức này không khớp với “drip” hoặc “trip”.
Sử dụng ký tự đại diện để khớp với bất kỳ số lượng ký tự nào
Một số ví dụ về việc sử dụng * trong các mô hình khớp gồm:
“*ed” sẽ khớp với chuỗi có độ dài bất kỳ kết thúc bằng “ed”, chẳng hạn như “Ted” hoặc “Treed”.
=COUNTIF(B2:E7,“*it”) trả về số lượng các ô trong vùng B2:E7 chứa một giá trị kết thúc bằng “it” chẳng hạn như “bit” và “mit”. Giá trị “mitt” không khớp.
Khớp ký tự đại diện
Một số ví dụ về việc sử dụng ký tự ~ trong các mô hình khớp gồm:
“~?” sẽ khớp dấu chấm hỏi, thay vì sử dụng dấu chấm hỏi để khớp với bất kỳ ký tự duy nhất nào.
=COUNTIF(E,“~*”) trả về số lượng các ô trong cột E chứa ký tự dấu hoa thị.
=SEARCH(“~?”,B2) trả về 19 nếu ô B2 chứa “That is a question? Yes it is!” vì dấu chấm hỏi là ký tự thứ 19 trong chuỗi.
Sử dụng nhiều ký tự đại diện trong điều kiện
Các ký tự đại diện (? * ~) có thể được sử dụng cùng nhau trong các biểu thức cho phép các điều kiện. Một số ví dụ gồm:
“*a?” sẽ khớp với bất kỳ biểu thức nào chứa ký tự “a” đứng trước bất kỳ ký tự duy nhất nào khác, chẳng hạn như “That”, “Cap” và“Irregular”.
=COUNTIF(B2:E7,“*on?”) trả về số đếm số lượng các ô trong dãy B2:E7 chứa giá trị bắt đầu bằng bất kỳ số lượng ký tự nào (bao gồm không có) đứng trước “on” và sau đó là một ký tự duy nhất. Biểu thức này khớp với các từ chẳng hạn như “alone”, “bone”, “one” và “none”. Biểu thức này không khớp với “only” (có hai ký tự sau “on”) hoặc “eon” (không có ký tự nào sau “on”).
Cách Dùng Các Hàm Xử Lý Chuỗi Ký Tự Và Các Ký Tự Đại Diện Trong Excel
– Cú pháp: = LEFT(text, num_chars).
– Chức năng: Trả về một hay nhiều ký tự đầu tiên bên trái của một chuỗi, theo số lượng được chỉ định.
– Ví dụ: = Left(HayHocHoi,6) kết quả thu được là chuỗi: “HayHoc”
Cú pháp: =RIGHT(text, num_chars).
Chức năng: Trả về một hay nhiều ký tự tính từ bên phải của một chuỗi, theo số lượng được chỉ định.
Ví dụ: =Right(HayHocHoi,6) kết quả thu được là chuỗi: “HocHoi”
Cú pháp: = MID(text, start_num, num_chars).
Chức năng: Trả về một hoặc nhiều ký tự liên tiếp bên trong một chuỗi, bắt đầu tại một vị trí cho trước.
Ví dụ: =Mid(HayHocHoi,4,3) kết quả thu được là chuỗi: “Học”
Cú pháp: CONCATENATE(text1, text2…).
Chức năng: Nối nhiều chuỗi thành một chuỗi.
Ví dụ: =CONCATENATE(“Hay”,”Hoc”,”Hoi”) kết quả thu được chuỗi:”HayHocHoi”
Cú pháp: = FIND(find_text, within_text, start_num).
Chức năng: Tìm vị trí bắt đầu của một chuỗi con (find_text) trong một chuỗi (within_text), tính theo ký tự đầu tiên.
Ví dụ: =FIND(“Hoc”,”Hay Hoc Hoi”) kết quả thu được vị trí bắt đầu của chuỗi “Hoc” trong chuỗi “Hay Hoc Hoi” là: 5
Cú pháp: PROPER(text).
Chức năng: Đổi ký tự đầu tiên trong chuỗi thành chữ in hoa, và đổi các ký tự còn lại thành chữ in thường.
Cú pháp: REPLACE(old_text, start_num, num_chars, new_text).
Chức năng: Thay thế một phần của chuỗi bằng một chuỗi khác, với số lượng các ký tự được chỉ định.
Ví dụ: =REPLACE(“HayHocHoi.VN”,1,12,”Hay Học Hỏi”) kết quả thu được là chuỗi: “Hay Học Hỏi”
Cú pháp: SEARCH(find_text, within_text, start_num).
Chức năng: Tìm vị trí bắt đầu của một chuỗi con (find_text) trong một chuỗi (within_text), tính theo ký tự đầu tiên.
Ví dụ: =SEARCH(“Học”,”Hay Học Hỏi”) kết quả thu được bằng: 5 là vị trí bắt đầu chuỗi “Học” trong chuỗi “Hay Học Hỏi”
Cú pháp: SUBSTITUTE(text, old_text, new_text, instance_num).
Chức năng: Thay thế chuỗi này bằng một chuỗi khác.
Ví dụ: =SUBSTITUTE(“HayHocHoi.VN”,”.VN”,”@gmail”,1) kết quả hàm trả về là: HayHocHoi@gmail; hàm thay thế chuỗi “.VN” bằng chuỗi “@gmail” tại vị trí tìm thấy đầu tiên.
Cú pháp: = TRIM(text).
Chức năng: Xóa tất cả những khoảng trắng bị dư (vô ích) trong chuỗi văn bản, chỉ để lại 1 khoảng trắng dùng làm dấu cách giữa hai chữ.
Ví dụ: =TRIM(“Hay Học Hỏi”) kết quả trả về là chuỗi “Hay Học Hỏi”, chuỗi ban đầu có 2 khoảng trắng ở giữa các chữ, qua hàm Trim chỉ còn lại 1 khoảng trắng.
Cú pháp: = UPPER(text).
Chức năng: Đổi tất cả các ký tự trong chuỗi thành chữ in hoa.
Ví dụ: =UPPER(“hay học hỏi”) kết quả hàm trả về là: “HAY HỌC HỎI”
Cú pháp : = LOWER(text)
Chức năng: Để chuyển tất cả ký tự trong ô chứa, chuỗi ký tự thành in thường thì ta có hàm Lower.
Ví dụ: =LOWER(“HAY HỌC HỎI”) kết quả hàm trả về là chuỗi: “hay học hỏi”
– Toán tử chuỗi (&) có thể được sử dụng trong các công thức của Excel – Các ký tự đại diện *, ?, ~ có thể được sử dụng trong điều kiện. * Ghép nối các chuỗi hoặc nội dung của ô
II. Cách sử dụng toán tử chuỗi và ký tự đại diện * ? trong Excel
+ Một số ví dụ về việc sử dụng toán tử ghép nối “&” gồm:
“Hay”&”Hoc”&”Hoi” trả về “Hay Hoc Hoi”.
“HayHocHoi”&A1 trả về “HayHocHoi.VN nếu ô A1 chứa “.VN”.
A1&A2 trả về “12” nếu ô A1 chứa 1 và ô A2 chứa 2.
=B2&”, “&E2 trả về “Last, First” nếu B2 chứa “Last” và E2 chứa “First”.
+ Một số ví dụ về việc sử dụng ? trong các mô hình khớp gồm:
* Sử dụng ký tự đại diện “?” để khớp với ký tự DUY NHẤT bất kỳ
“Ea?” sẽ khớp với bất kỳ chuỗi nào bắt đầu bằng “Ea” và chứa chính xác một ký tự bổ sung, chẳng hạn như “Ea2” hoặc “Eac”.
“Th??” sẽ khớp với bất kỳ chuỗi nào bắt đầu bằng “Th” và chứa chính xác hai ký tự bổ sung, chẳng hạn như “Then” và “That”.
=COUNTIF(B2:E7,”?ip”) trả về số lượng các ô trong dãy B2:E7 chứa một giá trị bắt đầu bằng một ký tự đứng trước “ip” chẳng hạn như “rip” và “tip”. Biểu thức này không khớp với “drip” hoặc “trip”.
+ Một số ví dụ về việc sử dụng * trong các mô hình khớp gồm:
* Sử dụng ký tự đại diện “*” trong excel để khớp với BẤT KỲ CHUỖI ký tự nào
“*ed” sẽ khớp với chuỗi có độ dài bất kỳ kết thúc bằng “ed”, chẳng hạn như “speed” hoặc “need”.
=COUNTIF(B2:E7,”*it”) trả về số lượng các ô trong vùng B2:E7 chứa một giá trị kết thúc bằng “it” chẳng hạn như “bit” và “mit”. Giá trị “mitt” không khớp.
+ Một số ví dụ về việc sử dụng ký tự ~ trong các mô hình khớp gồm:
* Sử dụng ký tự “~” Khớp ký tự đại diện
“~?” sẽ khớp dấu chấm hỏi, thay vì sử dụng dấu chấm hỏi để khớp với bất kỳ ký tự duy nhất nào.
=COUNTIF(E,”~*”) trả về số lượng các ô trong cột E chứa ký tự dấu hoa thị.
=SEARCH(“~?”,B2) trả về 19 nếu ô B2 chứa “That is a question? Yes it is!” vì dấu chấm hỏi là ký tự thứ 19 trong chuỗi.
* Sử dụng nhiều ký tự đại diện (* ? ~) trong điều kiện
Các ký tự đại diện (? * ~) có thể được sử dụng cùng nhau trong các biểu thức cho phép các điều kiện. Một số ví dụ gồm:
“*a?” sẽ khớp với bất kỳ biểu thức nào chứa ký tự “a” đứng trước bất kỳ ký tự duy nhất nào khác, chẳng hạn như “That”, “Cap” và “Irregular”.
=COUNTIF(B2:E7,”*on?“) trả về số đếm số lượng các ô trong dãy B2:E7 chứa giá trị bắt đầu bằng bất kỳ số lượng ký tự nào (bao gồm không có) đứng trước “on” và sau đó là một ký tự duy nhất. Biểu thức này khớp với các từ chẳng hạn như “alone”, “bone”, “one” và “none”. Biểu thức này không khớp với “only” (có hai ký tự sau “on”) hoặc “eon” (không có ký tự nào sau “on”).
Ký Tự Đại Diện Trong Excel: Công Thức Sử Dụng Tìm, Thay Thế Và Lọc
Mọi thứ bạn cần biết về các ký tự đại diện trên một trang: chúng là gì, cách sử dụng chúng tốt nhất trong Excel và tại sao ký tự đại diện không hoạt động với số.
Khi bạn đang tìm kiếm thứ gì đó nhưng không chắc chắn chính xác là gì, ký tự đại diện là một giải pháp hoàn hảo. Bạn có thể nghĩ về một ký tự đại diện như một trò đùa có thể nhận bất kỳ giá trị nào. Chỉ có 3 ký tự đại diện trong Excel (dấu hoa thị, dấu hỏi và dấu ngã), nhưng chúng có thể làm được rất nhiều điều hữu ích!
Các ký tự đại diện trong ExcelTrong Microsoft Excel, ký tự đại diện là một loại ký tự đặc biệt có thể thay thế bất kỳ ký tự nào khác. Nói cách khác, khi bạn không biết một ký tự chính xác, bạn có thể sử dụng ký tự đại diện ở vị trí đó.
Các ký tự đại diện có ích trong mọi tình huống khi bạn cần đối sánh một phần. Bạn có thể sử dụng chúng làm tiêu chí so sánh để lọc dữ liệu, để tìm các mục nhập có một số phần chung hoặc để thực hiện đối sánh mờ trong công thức.
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 . Ví dụ:
ch * – khớp với bất kỳ từ nào bắt đầu bằng “ch” chẳng hạn như Charles , séc , cờ vua , v.v.
* ch – thay thế bất kỳ chuỗi văn bản nào kết thúc bằng “ch”, chẳng hạn như March , inch , fetch , v.v.
* ch * – đại diện cho bất kỳ từ nào có chứa “ch” ở bất kỳ vị trí nào như Chad , nhức đầu , vòm , v.v.
Dấu chấm hỏi dưới dạng ký tự đại diệnDấu chấm hỏi (?) Đại diện cho bất kỳ ký tự đơn nào . Nó có thể giúp bạn cụ thể hơn khi tìm kiếm kết quả phù hợp từng phần. Ví dụ:
? – khớp với bất kỳ mục nhập nào có chứa một ký tự, ví dụ: “a”, “1”, “-“, v.v.
?? – thay thế hai ký tự bất kỳ, ví dụ: “ab”, “11”, “a *”, v.v.
??? – ??? – đại diện cho bất kỳ chuỗi nào có chứa 2 nhóm 3 ký tự được phân tách bằng dấu gạch ngang như ABC-DEF , ABC-123 , 111-222 , v.v.
pri? e – phù hợp với giá cả , niềm tự hào , giải thưởng và những thứ tương tự.
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 nó 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 (~~). Ví dụ:
* ~? – tìm bất kỳ mục nhập nào kết thúc bằng dấu chấm hỏi, ví dụ: Cái gì? , Có ai ở đó không? , Vân vân.
* ~ ** – tìm thấy bất kỳ dữ liệu chứa một dấu hoa thị, ví dụ như * 1 , * 11 * , 1-Mar-2023 * , vv Trong trường hợp này, 1 st và 3 thứ dấu hoa thị là ký tự đại diện, trong khi cái thứ hai biểu thị một chữ ký tự dấu hoa thị.
Tìm và thay thế các ký tự đại diện trong Excel Cách tìm kiếm bằng ký tự đại diệnTheo mặc định, hộp thoại Tìm và Thay thế được định cấu hình để tìm kiếm các tiêu chí được chỉ định ở bất kỳ đâu trong ô, không khớp với toàn bộ nội dung ô. Ví dụ: nếu bạn sử dụng “AA” làm tiêu chí tìm kiếm, Excel sẽ trả về tất cả các mục nhập chứa nó như AA-01 , 01-AA , 01-AA-02 , v.v. Điều đó hoạt động tốt trong hầu hết các tình huống, nhưng trong một số trường hợp nhất định có thể là một sự phức tạp.
Về mặt kỹ thuật, các chuỗi như AAB-01 hoặc BB-002 cũng phù hợp với tiêu chí vì chúng chứa dấu ?? – ?? chuỗi con. Để loại trừ những thứ này khỏi kết quả, hãy nhấp vào nút Tùy chọn và chọn hộp Đối sánh toàn bộ nội dung ô. Bây giờ, Excel sẽ giới hạn kết quả chỉ có dấu ?? – ?? dây:
Cách thay thế bằng ký tự đại diệnTrong trường hợp dữ liệu của bạn chứa một số kết hợp mờ, các ký tự đại diện có thể giúp bạn nhanh chóng xác định vị trí và thống nhất chúng.
Trong ảnh chụp màn hình bên dưới, bạn có thể thấy hai biến thể chính tả của cùng một thành phố Homel và Gomel . Chúng tôi muốn thay thế cả hai bằng một phiên bản khác – Homyel . (Và vâng, cả ba cách viết của thành phố quê hương của tôi đều đúng và được chấp nhận chung 🙂
Để thay thế các kết quả phù hợp từng phần, đây là những gì bạn cần làm:
nhấn Ctrl + H để mở tab Thay thế của hộp thoại Tìm và Thay thế .
Trong hộp Tìm gì , hãy nhập biểu thức ký tự đại diện 😕 Omel
Trong hộp Thay thế bằng , nhập văn bản thay thế: Homyel
Nhấp vào nút Thay thế Tất cả .
Và quan sát kết quả:
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, tức là dấu hoa thị hoặc dấu chấm hỏi theo nghĩa đen, hãy thêm dấu ngã (~) vào tiêu chí tìm kiếm của bạn. Ví dụ: để tìm tất cả các mục có chứa dấu hoa thị, hãy nhập ~ * vào hộp Tìm gì:
Nếu bạn muốn thay thế các dấu hoa thị bằng một cái gì đó khác, hãy chuyển sang tab Thay thế và nhập ký tự quan tâm vào hộp Thay thế bằng . Để loại bỏ tất cả các ký tự dấu hoa thị đã tìm thấy, hãy để trống hộp Thay thế bằng và nhấp vào Thay thế tất cả .
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.
Trong tập dữ liệu mẫu của chúng tôi, giả sử bạn muốn lọc các ID bắt đầu bằng “B”. Đối với điều này, hãy làm như sau:
Thêm bộ lọc vào các ô tiêu đề. Cách nhanh nhất là nhấn Ctrl + Shift + L đường tắt.
Trong cột mục tiêu, hãy nhấp vào mũi tên thả xuống của bộ lọc.
Trong hộp Tìm kiếm , nhập tiêu chí của bạn, B * trong trường hợp của chúng tôi.
Điều này sẽ ngay lập tức lọc dữ liệu dựa trên tiêu chí ký tự đại diện của bạn như hiển thị bên dưới:
Các ký tự đại diện cũng có thể được sử dụng với Bộ lọc nâng cao , có thể làm cho nó trở thành một lựa chọn thay thế tốt cho các biểu thức chính quy (còn được gọi là regexes bởi các chuyên gia công nghệ) mà Excel không hỗ trợ. Để biết thêm thông tin, vui lòng xem Bộ lọc nâng cao của Excel với các ký tự đại diện .
Công thức Excel với ký tự đại diệnAVERAGEIF với các ký tự đại diện – tìm giá trị trung bình (trung bình cộng) của các ô đáp ứng điều kiện được chỉ định.
AVERAGEIFS – trả về giá trị trung bình của các ô đáp ứng nhiều tiêu chí. Giống như AVERAGEIF trong ví dụ trên cho phép các ký tự đại diện.
COUNTIF với các ký tự đại diện – đếm số lượng ô dựa trên một tiêu chí.
COUNTIFS với ký tự đại diện – đếm số ô dựa trên nhiều tiêu chí.
SUMIF với ký tự đại diện – tính tổng các ô có điều kiện.
SUMIFS – thêm các ô có nhiều tiêu chí. Giống như SUMIF trong ví dụ trên chấp nhận các ký tự đại diện.
VLOOKUP với các ký tự đại diện – thực hiện tra cứu theo chiều dọc với khớp một phần.
HLOOKUP với ký tự đại diện – thực hiện tra cứu theo chiều ngang với khớp một phần.
XLOOKUP với các ký tự đại diện – thực hiện tra cứu đối sánh từng phần cả trong một cột và một hàng.
Công thức MATCH với các ký tự đại diện – tìm một phần đối sánh và trả về vị trí tương đối của nó.
XMATCH với các ký tự đại diện – một kế thừa hiện đại của chức năng MATCH cũng hỗ trợ khớp ký tự đại diện.
TÌM KIẾM với các ký tự đại diện – không giống như chức năng FIND phân biệt chữ hoa chữ thường, TÌM KIẾM không phân biệt chữ hoa chữ thường hiểu các ký tự đại diện.
Nếu bạn cần thực hiện đối sánh từng phần với các hàm khác không hỗ trợ ký tự đại diện, bạn sẽ phải tìm ra giải pháp thay thế như công thức ký tự đại diện IF trong Excel .
Các ví dụ sau đây trình bày một số cách tiếp cận chung để sử dụng ký tự đại diện trong công thức Excel.
Công thức ký tự đại diện COUNTIF trong ExcelGiả sử bạn muốn đếm số ô chứa văn bản “AA” trong phạm vi A2: A12. Có ba cách để thực hiện điều này.
Cách dễ nhất là đưa các ký tự đại diện trực tiếp vào đối số tiêu chí :
Trong thực tế, “mã hóa cứng” như vậy không phải là giải pháp tốt nhất. Nếu sau này tiêu chí thay đổi, bạn sẽ phải chỉnh sửa công thức của mình mọi lúc.
Thay vì nhập tiêu chí trong công thức, bạn có thể nhập tiêu chí đó vào một số ô, chẳng hạn như E1 và nối tham chiếu ô với các ký tự đại diện. Công thức hoàn chỉnh của bạn sẽ là:
=COUNTIF(A2:A12, "*AA*")
Ngoài ra, bạn có thể nhập một chuỗi ký tự đại diện (* AA * trong ví dụ của chúng tôi) trong ô tiêu chí (E1) và chỉ bao gồm tham chiếu ô trong công thức:
=COUNTIF(A2:A12,"*"&E1&"*")
Cả ba công thức sẽ tạo ra cùng một kết quả, vì vậy việc sử dụng công thức nào là tùy thuộc vào sở thích cá nhân của bạn.
Công thức VLOOKUP ký tự đại diện trong ExcelKhi bạn cần tìm kiếm một giá trị không khớp chính xác trong dữ liệu nguồn, bạn có thể sử dụng các ký tự đại diện để tìm một phần khớp.
=COUNTIF(A2:A12, E1)
Trong ví dụ này, chúng tôi sẽ tìm kiếm các ID bắt đầu bằng các ký tự cụ thể và trả về giá của chúng từ cột B. Để hoàn thành, hãy nhập các phần duy nhất của ID mục tiêu vào các ô D2, D3 và D4 và sử dụng công thức này để nhận được kết quả:
Công thức trên chuyển đến E1, và do việc sử dụng khéo léo các ô tương đối và tuyệt đối, nó sao chép chính xác đến các ô bên dưới.
Ký tự đại diện Excel cho các sốĐôi khi người ta nói rằng các ký tự đại diện trong Excel chỉ hoạt động cho các giá trị văn bản chứ không phải số. Tuy nhiên, điều này không hoàn toàn đúng. Với tính năng Tìm và Thay thế cũng như Bộ lọc , các ký tự đại diện hoạt động tốt cho cả văn bản và số.
Tìm và thay thế bằng số ký tự đại diệnTrong ảnh chụp màn hình bên dưới, chúng tôi đang sử dụng * 4 * cho tiêu chí tìm kiếm để tìm kiếm các ô chứa chữ số 4 và Excel tìm cả chuỗi văn bản và số:
=VLOOKUP(D2&"*", $A$2:$B$12, 2, FALSE)
Lọc với số ký tự đại diệnTương tự như vậy, 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”:
Tại sao ký tự đại diện Excel không hoạt động với các số trong công thứcCác ký tự đại diện với các số trong công thức là một câu chuyện khác. Việ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ố.
Nhưng cả hai đều không thể xác định chữ số 4 trong một số:
Cách làm cho các ký tự đại diện hoạt động cho các sốGiải pháp đơn giản nhất là chuyển đổi số thành văn bản (ví dụ: bằng cách sử dụng tính năng Văn bản thành Cột) và sau đó thực hiện hàm VLOOKUP, COUNTIF, MATCH, v.v.
Ví dụ, để có được số lượng ô bắt đầu bằng số trong E1, công thức là:
Ví dụ 1. Công thức ký tự đại diện trong Excel cho các sốVí dụ này cho thấy cách đếm các số có chứa một chữ số cụ thể. 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 B2: B12 chứa “4”. Đây là công thức để sử dụng:
Làm việc từ trong ra ngoài, đây là những gì công thức thực hiện:
=COUNTIF(B2:B12, E1&"*" )
Hàm SEARCH tìm kiếm chữ số được chỉ định trong mọi ô của phạm vi và trả về vị trí của nó, nếu không tìm thấy lỗi #VALUE. Đầu ra của nó là mảng sau:
Hàm ISNUMBER lấy nó từ đó và thay đổi bất kỳ số nào thành TRUE và lỗi thành FALSE:
Toán tử một ngôi kép (-) buộc TRUE và FALSE thành 1 và 0, tương ứng:
=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))
Cuối cùng, hàm SUMPRODUCT cộng số 1 và trả về số lượng.
Công thức này hoạt động như thế nào
Để chỉ đếm những ngày có chứa “4” bỏ qua tháng và năm, hãy sử dụng định dạng văn bản “dd” :
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE} {0;1;0;0;1;0;0;1;0;0;0} =SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "mmddyyyy"))))) =SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "dd")))))
Giới Thiệu Về Ký Tự Đại Diện Trong Tìm Kiếm (Wildcard) Và Cách Ứng Dụng Hiệu Quả
Trong bài viết lần này, Học Excel Online sẽ hướng dẫn các bạn về những ký tự đại diện “quyền lực” dùng làm tiêu chí khi tìm kiếm văn bản (hay còn được gọi là wildcard).
Ký tự đại diện (Wildcard) là gì?Wildcard là những kí tự đại diện được dùng làm tiêu chí khi tìm kiếm văn bản, đại diện cho một hoặc nhiều kí tự khác. Những kí tự này vô cùng có ích khi bạn muốn tìm kiếm theo cách tương đối, không chính xác hoàn toàn.
3 kí tự đại diện trong ExcelTrong Excel, có 3 kí tự đại diện được sử dụng bao gồm:
Dù cho tác dụng có phần khác nhau thì trong một chuỗi, các kí tự đại diện này có thể đại diện cho bất cứ giá trị nào trong chuỗi.
Wildcard đầu tiên ta sẽ tìm hiểu là dấu sao. Dấu sao có thể thay thế một hoặc một nhóm các kí tự trong chuỗi không kể chữ hay số(ở dạng Text).
Ví dụ, nếu ta có một cụm “Na*”, nó có thể đại diện cho các chuỗi: “Nam”, “Nammmmmm”, “Nam01234”, “Nam98man89″…
Ta có thể đặt dấu sao ở đầu chuỗi, ví dụ như “*98”. Nó đại diện cho tất cả các chuỗi kết thúc bằng “98” mà không cần biết có bao nhiêu chữ hoặc số trước đó.
Bạn đã bắt đầu mường tượng được tác dụng của kí tự đại diện rồi, phải không nào?
Dấu hỏi được sử dụng khi bạn muốn tìm kiếm một cách cụ thể hơn, nhưng vẫn không phải là chính xác hoàn toàn.
Trong ví dụ phía dưới này, ta sẽ kết hợp với những kiến thức đã biết về dấu sao ở phía trên.
Yêu cầu: Lọc tất cả những người có tên đệm là “Thị” hoặc “Thu”
Hướng làm: Sử dụng dấu sao kết hợp dấu cách để lọc ra phần tên đệm ở giữa.
Sử dụng dấu hỏi để đại diện cho kí tự cuối sau “Th”.
Kết quả:
Sử dụng wildcard để tìm kiếm và thay thế (Find & Replace)Hướng làm:
Khoanh vùng muốn tìm kiếm. Sử dụng phím Ctrl+F để mở hộp thoại tìm kiếm.
Tại ô tìm kiếm, gõ * TH* *
Ấn Find.
Kết quả tìm kiếm sẽ được hiển thị bên dưới:
Sử dụng wildcard với hàm COUNTIFCách Sử Dụng Hàm Mid Lấy Chuỗi Ký Tự Trong Excel
Trong Excel, 3 hàm LEFT, hàm RIGHT và hàm MID là những hàm Excel quan trọng và cơ bản để tiến hành xử lý ký tự có trong chuỗi ký tự ở trên bảng dữ liệu. Với hàm LEFT, khi tiến hành nhập hàm thì Excel sẽ lấy các ký tự ở bên trái. Hàm RIGHT sẽ lấy những ký tự ở bên phải. Còn lại là hàm MID sẽ tiến hành lấy chuỗi ký tự ở giữa tương đương với yêu cầu của người dùng. Cách thực hiện của các hàm này tương đối giống nhau, bạn cần nhập lệnh, sau đó lựa chọn số ký tự muốn lấy ở trong bảng số liệu là xong. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm MID trên Excel để lấy ký tự ở giữa.
Chúng ta sẽ tiến hành thực hiện lấy chuỗi ký tự với bảng thống kê dưới đây. Yêu cầu của bảng đó là lọc ra mã ngành từ tên lớp của sinh viên. Tương ứng là 2 ký tự bắt đầu từ ký tự thứ 5 trong tên lớp.
Hàm MID sẽ cắt ra n ký tự trong chuỗi ký tự từ vị trí ban đầu m.
Bước 1:Cú pháp thực hiện của hàm MID sẽ là =MID(text,m,n).
Trong đó:
text: chuỗi ký tự cần cắt.
m: Vị trí bắt đầu cần cắt của chuỗi ký tự.
n: Số ký tự cần cắt ra từ chuỗi ký tự.
Tại ô D3, chúng ta sẽ nhập công thức =MID(C3,5,2) rồi nhấn Enter. Nghĩa là sẽ lấy 2 ký tự bắt đầu từ ký tự thứ 5 trong ô C3 và điền ký tự kết quả vào ô D3.
Bước 2:Kết quả sẽ được như hình dưới đây. Bạn đã lọc được mã ngành với 2 ký tự trong tên lớp của sinh viên.
Để thực hiện với những ô còn lại, chúng ta chỉ cần kéo xuống các ô còn lại để cho ra kết quả tương tự.
Ngoài ra, cách thực hiện hàm LEFT và RIGHT cũng là hai hàm lấy ký tự tương ứng với bên trái và bên phải trong chuỗi ký tự. Bạn thực hiện tương tự như với hàm MID.
Cú pháp của hàm LEFT là =LEFT(text,n).
Trong đó text là chuỗi ký tự cần lấy và n là số ký tự cần cắt. Nếu không có tham số n, Excel sẽ tự động lấy từ giá trị đầu tiên trong chuỗi.
Chẳng hạn tôi cần số hiệu lớp trong tên lớp với 4 ký tự tính từ ký tự đầu tiên. Bạn sẽ nhập hàm =LEFT(C3,4).
Cuối cùng kết quả sẽ như hình dưới đây.
Cú pháp hàm RIGHT là =RIGHT(text,n).
Chẳng hạn tôi cần lấy 5 ký tự trong mã ngành ở tên lớp. Công thức nhập sẽ là =RIGHT(C3,5). Kết quả cũng cho ra như hình dưới đây.
Trên đây là bài hướng dẫn cách sử dụng hàm MID, hàm lấy chuỗi ký tự ở giữa theo yêu cầu người dùng trên Excel. Theo đó, hàm MID sẽ tiến hành lấy những ký tự ở giữa trong chuỗi ký tự của bảng dữ liệu. Ngoài ra, cách sử dụng hàm LEFT lấy chuỗi ký tự bên trái và hàm RIGHT lấy chuỗi ký tự bên phải trên Excel cũng thực hiện theo hướng dẫn bên trên.
Chúc các bạn thực hiện thành công!
Cập nhật thông tin chi tiết về Cách Sử Dụng Toán Tử Chuỗi Và Ký Tự Đại Diện trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!