Tìm hiểu cách ký tự đại diện của Excel tăng tính linh hoạt cho tìm kiếm, thay thế và logic công thức. Khám phá các ví dụ thực tiễn dùng *, ?, và ~ trong lọc, VLOOKUP() và hơn thế nữa.
Ký tự đại diện (wildcard) trong Excel là các ký hiệu đặc biệt giúp bạn thực hiện đối sánh một phần và thao tác dữ liệu linh hoạt trên các tập dữ liệu lớn. Dù Excel không hỗ trợ đầy đủ regex, bạn vẫn có thể dùng ký tự đại diện với cách cấu trúc công thức linh hoạt (các hàm như SEARCH(), TEXT() và ISNUMBER()).
Việc thành thạo ký tự đại diện sẽ dễ dàng hơn khi bạn nắm vững các kiến thức nền tảng Excel. Khóa học Introduction to Excel của chúng tôi hướng dẫn bạn các kỹ năng thiết yếu như điều hướng giao diện, tham chiếu ô và công thức cơ bản—tạo nền tảng vững chắc để bạn áp dụng hiệu quả các kỹ thuật dùng ký tự đại diện trong hướng dẫn này.
Ký tự đại diện trong Excel là gì?
Ký tự đại diện là các ký hiệu đặc biệt thay thế cho ký tự trong chuỗi văn bản, giúp bạn tìm kiếm theo mẫu thay vì khớp chính xác. Excel nhận diện ba ký tự đại diện, mỗi ký tự có mục đích riêng trong đối sánh mẫu và thao tác văn bản.
Dấu hoa thị (*)
Dấu hoa thị (*) khớp với mọi số lượng ký tự, kể cả không có ký tự nào. Ví dụ, App* sẽ khớp với "Apple", "Application", "App" và "Approach".
Ký tự này hữu ích khi bạn biết phần đầu của chuỗi văn bản nhưng muốn bao quát mọi biến thể theo sau.
Dấu hỏi (?)
Dấu hỏi (?) khớp chính xác một ký tự. Dùng B?t sẽ khớp với "Bat", "Bet", "Bit" và "But", nhưng không khớp với "Boat" hoặc "Boot."
Độ chính xác này lý tưởng khi bạn biết độ dài và phần lớn ký tự của chuỗi mục tiêu.
Dấu ngã (~)
Dấu ngã (~) đóng vai trò ký tự thoát (escape), cho phép bạn tìm dấu hoa thị hoặc dấu hỏi thực sự trong dữ liệu. Khi cần tìm văn bản chứa ký tự * hoặc ? theo nghĩa đen, đặt dấu ngã phía trước sẽ báo cho Excel coi chúng là ký tự thường thay vì ký tự đại diện.
Ví dụ, ~*Sale sẽ khớp với cụm từ đúng “*Sale” thay vì coi dấu hoa thị là ký tự đại diện.
Dùng ký tự đại diện trong công thức Excel
Nhiều hàm Excel hỗ trợ ký tự đại diện, rất hữu ích cho phép tính có điều kiện và tra cứu. Các hàm thường dùng chấp nhận ký tự đại diện gồm COUNTIF(), COUNTIFS(), SUMIF(), SUMIFS(), AVERAGEIF(), AVERAGEIFS(), SEARCH(), VLOOKUP(), HLOOKUP(), XLOOKUP(), MATCH() và XMATCH().
Mỗi hàm này có thể tận dụng ký tự đại diện để đối sánh mẫu linh hoạt, cho phép bạn làm việc với đối sánh một phần và xử lý các biến thể trong tập dữ liệu hiệu quả.
COUNTIF() với ký tự đại diện
Hàm COUNTIF() đặc biệt hữu ích khi kết hợp với ký tự đại diện để đếm các ô khớp với mẫu cụ thể. Ví dụ cơ bản:
Công thức này đếm tất cả ô trong phạm vi A2:A12 có chứa "AA" ở bất kỳ vị trí nào trong văn bản. Dấu hoa thị trước và sau "AA" báo với Excel khớp với mọi ký tự đứng trước hoặc sau chuỗi này.
Với tiêu chí động, bạn có thể tham chiếu đến một ô chứa mẫu ký tự đại diện:
Trong trường hợp này, ô E1 có thể chứa "*AA*" hoặc bất kỳ mẫu ký tự đại diện nào khác. Cách này cho phép bạn thay đổi tiêu chí tìm kiếm mà không cần sửa công thức, giúp bảng tính linh hoạt và thân thiện hơn.
VLOOKUP() và XLOOKUP() với ký tự đại diện
Các hàm tra cứu có thể dùng ký tự đại diện để đối sánh một phần, hữu ích khi giá trị tra cứu không khớp chính xác. Dưới đây là ví dụ cho cả hai cách tiếp cận:
Công thức VLOOKUP() này tìm mọi mục trong cột A bắt đầu bằng "John" và trả về giá trị tương ứng từ cột 2. Tham số FALSE đảm bảo hàm dùng chế độ khớp chính xác, vốn là yêu cầu để ký tự đại diện hoạt động.
Tham số chế độ đối sánh (2) báo cho XLOOKUP() dùng đối sánh ký tự đại diện. Công thức này tìm mọi mục kết thúc bằng "Adams" và trả về giá trị tương ứng từ cột C. Nếu không có kết quả, hàm sẽ trả về “Not found”.
IF() với cách xử lý thay thế ký tự đại diện
Mặc dù hàm IF() không trực tiếp hỗ trợ ký tự đại diện, bạn có thể đạt chức năng tương tự bằng cách kết hợp IF() với COUNTIF():
Công thức này kiểm tra ô A1 có chứa văn bản bắt đầu bằng "Amy" hay không và trả về "Yes" nếu có, "No" nếu không. Hàm COUNTIF() xử lý phần đối sánh ký tự đại diện, còn IF() xử lý kết quả.
SUMIF() với ký tự đại diện
Hàm SUMIF() có thể cộng các giá trị dựa trên tiêu chí dùng ký tự đại diện, rất hữu ích để tổng hợp dữ liệu theo các mẫu tương tự:
Công thức này cộng tất cả giá trị ở cột C nơi ô tương ứng ở cột A chứa chữ "a" ở bất kỳ vị trí nào. Mẫu ký tự đại diện "a" bao quát mọi văn bản có chứa "a" bất kể vị trí.
Dùng ký tự đại diện trong các công cụ Excel
Ký tự đại diện không chỉ dùng trong công thức mà còn khả dụng trong nhiều công cụ Excel để tìm kiếm, đối sánh và lọc dữ liệu. Các tính năng tích hợp này giúp bạn tiếp cận ký tự đại diện ngay cả khi không viết công thức phức tạp.
Ký tự đại diện trong Tìm kiếm và Thay thế
Hộp thoại Findand Replace của Excel hỗ trợ ký tự đại diện để tìm các mẫu biến đổi trong dữ liệu. Để bật chức năng ký tự đại diện, bạn cần chọn tùy chọn "Use wildcards" trong hộp thoại FindandReplace.
Bạn có thể dùng dấu hoa thị (*) để tìm các mẫu có độ dài khác nhau. Ví dụ, tìm "Product*X" sẽ phát hiện "Product-A-X", "Product-Premium-X" và "Product-X." Tương tự, dấu hỏi (?) giúp tìm các mẫu theo vị trí ký tự cố định, như "20??" để tìm mọi số có bốn chữ số bắt đầu bằng "20."
Khi cần tìm dấu hoa thị hoặc dấu hỏi theo nghĩa đen trong dữ liệu, hãy dùng dấu ngã (~) làm ký tự thoát. Tìm "~*Sale" sẽ phát hiện các ô chứa văn bản đúng "*Sale" thay vì coi dấu hoa thị là ký tự đại diện.
Ký tự đại diện trong tính năng lọc và tìm kiếm
Khả năng lọc của Excel hỗ trợ ký tự đại diện, đặc biệt hữu ích trong bộ lọc văn bản với các tùy chọn như "contains", "begins with" và "ends with." Các bộ lọc này sẽ tự động hiểu ký tự đại diện khi bạn đưa chúng vào tiêu chí.
Ví dụ, áp dụng bộ lọc với "Jo*" trong tùy chọn "begins with" sẽ hiển thị tất cả tên bắt đầu bằng "Jo" - bao gồm "John", "Joseph", "Joan" và "Jonathan." Cách tiếp cận này phù hợp cho việc khám phá dữ liệu nhanh mà không cần viết công thức.
Tuy nhiên, tính năng tìm kiếm tích hợp có một số hạn chế so với các tùy chọn lọc nâng cao. Mặc dù các mẫu ký tự đại diện cơ bản hoạt động hiệu quả, các yêu cầu lọc đa tiêu chí phức tạp có thể cần kết hợp ký tự đại diện với công thức tùy chỉnh hoặc dùng tính năng Advanced Filter của Excel để đối sánh mẫu tinh vi hơn.
Làm việc với số và ký tự đại diện
Dùng ký tự đại diện với số cần lưu ý đặc biệt vì Excel xử lý ký tự đại diện như các thao tác dựa trên văn bản. Khi áp dụng mẫu ký tự đại diện cho dữ liệu số, Excel sẽ tự động chuyển số thành văn bản, có thể dẫn đến hành vi khớp không như mong đợi.
Để đối sánh số hiệu quả với ký tự đại diện, bạn có vài cách xử lý thay thế. Bạn có thể chuyển số thành văn bản bằng hàm TEXT(), tính năng Text to Columns, hoặc các công thức chuyên biệt cho mục đích này.
Một cách thực tiễn là kết hợp các hàm SEARCH(), ISNUMBER() và SUMPRODUCT():
=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))Được hỗ trợ bởi
Công thức này đếm số ô trong phạm vi B2:B12 có chứa chữ số "4" ở bất kỳ vị trí nào. Để cộng các giá trị nơi số chứa mẫu cụ thể, bạn có thể mở rộng cách tiếp cận này:
=SUMPRODUCT(--(ISNUMBER(SEARCH("4*", TEXT(B2:B12,"0")))), C2:C12)Được hỗ trợ bởi
Công thức này cộng các giá trị ở cột C nơi các số tương ứng ở cột B chứa “4” theo sau bởi bất kỳ chữ số nào.
Với việc đối sánh ngày, hãy dùng hàm TEXT() để chuyển ngày thành các mẫu văn bản có thể tìm kiếm:
Cách này chuyển ngày sang định dạng văn bản nhất quán để ký tự đại diện xử lý hiệu quả. Sau đó bạn có thể áp dụng mẫu ký tự đại diện để tìm các thành phần ngày cụ thể:
Công thức đếm số ngày có chứa “0525” (ngày 25 tháng 5 của bất kỳ năm nào).
Một số trường hợp có số được lưu dưới dạng văn bản, như mã ZIP, mã sản phẩm hoặc số tham chiếu. Khi đó, ký tự đại diện có thể khớp trực tiếp như chuỗi văn bản. Ví dụ, lọc các mã theo mẫu "123*" sẽ khớp với "12345", "123-AB" và "123XYZ", trong khi "??-45" sẽ khớp với "AB-45", "12-45" và "XY-45."
Hạn chế và những điểm dễ nhầm của ký tự đại diện
Không phải mọi hàm Excel đều hỗ trợ ký tự đại diện, điều này có thể gây nhầm lẫn khi bạn kỳ vọng công thức sẽ hoạt động với đối sánh mẫu. Ví dụ, hàm IF() không chấp nhận ký tự đại diện trực tiếp, cần các cách xử lý như kết hợp với COUNTIF() như đã minh họa.
Ký tự đại diện chỉ hoạt động với văn bản trong công thức, nghĩa là giá trị số phải được chuyển sang văn bản trước khi thao tác ký tự đại diện có thể xử lý hiệu quả. Yêu cầu chuyển đổi này có thể dẫn đến kết quả bất ngờ nếu bạn không lưu ý.
Hãy cẩn trọng với đối sánh sai (false positive) trong các công thức tra cứu khi dùng mẫu ký tự đại diện quá rộng. Chẳng hạn, VLOOKUP("*Smith*", ...) có thể trả về các kết quả ngoài ý như "Smithson", "Blacksmith" hoặc "Goldsmith" khi bạn chỉ muốn "Smith." Hành vi này đòi hỏi thiết kế mẫu cẩn thận để tránh lấy phải kết quả không mong muốn.
Một điểm thường gây bất ngờ khác là khoảng trắng ở đầu hoặc cuối trong dữ liệu. Ký tự đại diện coi khoảng trắng là ký tự, nên "John*" sẽ không khớp với " John" (có khoảng trắng đầu dòng). Việc làm sạch dữ liệu trở nên quan trọng khi làm việc với ký tự đại diện để đảm bảo kết quả nhất quán.
Ngoài ra, đối sánh ký tự đại diện mặc định không phân biệt chữ hoa/thường. Điều này có nghĩa là các mẫu như "apple*" sẽ khớp với "Apple", "APPLE" và "ApPlE." Cách hoạt động này thuận tiện cho tìm kiếm chung nhưng có thể gây khớp ngoài ý nếu bạn cần phân biệt chữ hoa/thường. Để đạt đối sánh có phân biệt chữ, hãy cân nhắc dùng các hàm như FIND() kết hợp với ISNUMBER().
Mẹo khi làm việc với ký tự đại diện trong Excel
Ký tự đại diện rất hiệu quả trong việc hợp nhất dữ liệu khớp mơ hồ (fuzzy match), đặc biệt hữu ích khi có lỗi chính tả hoặc sai khác trong nhập liệu. Ví dụ, bạn có thể có "Homel" và "Gomel" trong tập dữ liệu cùng biểu thị một địa điểm. Dùng các mẫu như "*omel" có thể giúp nhận diện và hợp nhất các mục tương tự.
Định dạng số điện thoại thường không nhất quán giữa các tập dữ liệu, với tiền tố hoặc mã quốc gia khác nhau. Bạn có thể dùng ký tự đại diện để chuẩn hóa các điểm không nhất quán này. Chẳng hạn, bạn có thể dùng FindandReplace để chuyển số UK dạng quốc tế về dạng nội địa:
Mẫu này khớp mọi số UK bắt đầu bằng "44" theo sau là mười chữ số, hiệu quả chuyển các số như "447123456789" thành "07123456789" để có định dạng nội địa nhất quán.
Khi cần loại trừ một số kết quả khỏi đối sánh ký tự đại diện, hãy kết hợp nhiều hàm SUMIF() hoặc COUNTIF(). Kỹ thuật này hiệu quả để trừ đi các đối sánh không mong muốn:
=SUMIF(..., "00123H*") - SUMIF(..., "00123H*-SP")Được hỗ trợ bởi
Công thức này cộng tất cả giá trị khớp với "00123H*" nhưng loại trừ những giá trị kết thúc bằng "-SP", giúp bạn kiểm soát chính xác hơn việc đối sánh bằng ký tự đại diện.
Các thay đổi định dạng hoạt động hiệu quả cùng với ký tự đại diện khi dùng các hàm như REPLACE(), SUBSTITUTE() hoặc TEXT(). Các hàm này có thể làm sạch và chuẩn hóa dữ liệu trước hoặc sau khi áp dụng ký tự đại diện, đảm bảo kết quả phân tích nhất quán.
Khi làm với mã sản phẩm hoặc số tham chiếu, ký tự đại diện giúp nhận diện mẫu và ngoại lệ. Bạn có thể dùng *-TEMP để tìm tất cả mã tạm thời, hoặc ??-??-???? để xác định các mục theo một cấu trúc định dạng cụ thể, giúp việc kiểm tra và dọn dẹp dữ liệu hiệu quả hơn.
Kết luận
Dù bạn đang hợp nhất các mục tương tự, chuẩn hóa định dạng số điện thoại hay lọc tập dữ liệu lớn theo mẫu cụ thể, ký tự đại diện mang đến một lựa chọn thiết thực thay cho đối sánh chính xác. Chúng lấp khoảng trống giữa tìm kiếm văn bản cơ bản và chức năng regex phức tạp hơn, giúp đối sánh mẫu trở nên dễ tiếp cận trong môi trường quen thuộc của Excel.
Để mở rộng khả năng Excel vượt ra ngoài thao tác với ký tự đại diện, lộ trình kỹ năng Excel Fundamentals của chúng tôi cung cấp một hành trình toàn diện bao gồm chuẩn bị dữ liệu, phân tích và trực quan hóa. Nếu bạn sẵn sàng áp dụng những kỹ năng này trong bối cảnh phân tích, khóa học Data Analysis in Excel mang đến trải nghiệm thực hành với PivotTable và các hàm logic nâng cao, bổ trợ cho kỹ năng đối sánh mẫu bạn đã học ở đây.
Vinod Chugani bắt đầu sự nghiệp tại Tokyo với vai trò Trưởng bàn giao dịch bán hàng Quỹ phòng hộ trẻ nhất của JPMorgan, sau đó lập kỷ lục doanh số cá nhân tại Lehman Brothers, rồi xây dựng một doanh nghiệp phân phối điện tử tại 30 quốc gia vượt mốc doanh thu 100 triệu đô la Singapore trước khi chuyển hướng sang dữ liệu. Tốt nghiệp Kinh tế Duke và là cựu học viên NYC Data Science Academy, anh là một trong ba người nhận học bổng trong hơn 100 ứng viên cho khóa học Building AI Applications của Hugo Bowne-Anderson trên Maven. Hiện nay, anh viết cho DataCamp, KDnuggets, Machine Learning Mastery và Statology về các chủ đề từ thống kê đến AI hành động, và cố vấn cho các chuyên gia dữ liệu tại NYC Data Science Academy với hơn 1.000 buổi kèm 1-1 đã thực hiện.
FAQs
Ba ký tự đại diện trong Excel là gì?⌄
Excel dùng ba ký tự đại diện: * (dấu hoa thị) khớp với mọi số lượng ký tự, ? (dấu hỏi) khớp chính xác một ký tự, và ~ (dấu ngã) dùng để thoát ký tự đại diện, coi chúng như văn bản thường.
Những hàm Excel nào hỗ trợ ký tự đại diện?⌄
Các hàm phổ biến hỗ trợ ký tự đại diện gồm COUNTIF(), SUMIF(), VLOOKUP(), XLOOKUP(), SEARCH(), MATCH() và các biến thể như COUNTIFS() và SUMIFS().
Vì sao ký tự đại diện không hoạt động với số trong công thức của tôi?⌄
Ký tự đại diện chỉ hoạt động với văn bản, nên Excel sẽ tự động chuyển số sang văn bản khi dùng mẫu ký tự đại diện. Hãy dùng hàm TEXT() để chuyển số trước khi áp dụng ký tự đại diện nhằm có kết quả nhất quán.
Tôi có thể dùng ký tự đại diện trong tính năng Find and Replace của Excel không?⌄
Có, ký tự đại diện hoạt động trong FindandReplace, nhưng bạn phải chọn tùy chọn Usewildcards trong hộp thoại. Dùng ~ trước * hoặc ? để tìm dấu hoa thị hoặc dấu hỏi theo nghĩa đen.
Tôi có thể dùng ký tự đại diện với hàm IF() trong Excel không?⌄
Hàm IF() không trực tiếp hỗ trợ ký tự đại diện, nhưng bạn có thể kết hợp với COUNTIF() như sau: =IF(COUNTIF(A1,"Amy*"),"Yes","No") để đạt chức năng tương tự.
Vì sao mẫu ký tự đại diện của tôi không khớp tất cả kết quả mong đợi?⌄
Hãy kiểm tra khoảng trắng ở đầu/cuối dữ liệu, đảm bảo định dạng văn bản nhất quán và xác minh bạn dùng đúng ký hiệu ký tự đại diện. Ký tự đại diện không phân biệt chữ hoa/thường nhưng nhạy với khoảng trắng thừa và khác biệt định dạng.
Nắm vững các kiến thức cơ bản về Excel một cách nhanh chóng: điều hướng bảng tính, áp dụng công thức, phân tích dữ liệu và tạo biểu đồ đầu tiên của bạn!
%22%3E%3Cpath%20d%3D%22m-369%200h408v79h-408z%22%20fill%3D%22%237933ff%22%2F%3E%3Cpath%20d%3D%22m-148%200h306v45h-306z%22%20fill%3D%22%23974dff%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%2F%3E%3C%2FclipPath%3E%3Cg%20clip-path%3D%22url(%23a)%22%3E%3Cpath%20d%3D%22m76%200h484v36h-484z%22%20fill%3D%22%23974dff%22%2F%3E%3Cpath%20d%3D%22m0%200h384v24h-384z%22%20fill%3D%22%2306bdfc%22%20transform%3D%22matrix(1%200%200%20-1%200%2060)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%3E%0A%20%20%20%20%20%20%20%20%3Crect%20fill%3D%22%23EFEBE4%22%20height%3D%22180%22%20width%3D%22336%22%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Crect%0A%20%20%20%20%20%20%20%20%20%20fill%3D%22%23DD3400%22%0A%20%20%20%20%20%20%20%20%20%20height%3D%22106.6%22%0A%20%20%20%20%20%20%20%20%20%20transform%3D%22rotate(-30%20-208.588%20220.18)%22%0A%20%20%20%20%20%20%20%20%20%20width%3D%22456.125%22%0A%20%20%20%20%20%20%20%20%20%20x%3D%22-208.588%22%0A%20%20%20%20%20%20%20%20%20%20y%3D%22220.18%22%0A%20%20%20%20%20%20%20%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Crect%0A%20%20%20%20%20%20%20%20%20%20fill%3D%22%23FFEC3C%22%0A%20%20%20%20%20%20%20%20%20%20height%3D%2235.875%22%0A%20%20%20%20%20%20%20%20%20%20transform%3D%22rotate(-30%20-208.588%20220.18)%22%0A%20%20%20%20%20%20%20%20%20%20width%3D%22347.576%22%0A%20%20%20%20%20%20%20%20%20%20x%3D%22-208.588%22%0A%20%20%20%20%20%20%20%20%20%20y%3D%22220.18%22%0A%20%20%20%20%20%20%20%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Crect%0A%20%20%20%20%20%20%20%20%20%20fill%3D%22%23FF931E%22%0A%20%20%20%20%20%20%20%20%20%20height%3D%2235.875%22%0A%20%20%20%20%20%20%20%20%20%20transform%3D%22rotate(-30%20-226.525%20189.111)%22%0A%20%20%20%20%20%20%20%20%20%20width%3D%22347.576%22%0A%20%20%20%20%20%20%20%20%20%20x%3D%22-226.525%22%0A%20%20%20%20%20%20%20%20%20%20y%3D%22189.111%22%0A%20%20%20%20%20%20%20%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Crect%0A%20%20%20%20%20%20%20%20%20%20fill%3D%22%23DD3400%22%0A%20%20%20%20%20%20%20%20%20%20height%3D%22106.6%22%0A%20%20%20%20%20%20%20%20%20%20transform%3D%22rotate(-30%2085.5879%20256.055)%22%0A%20%20%20%20%20%20%20%20%20%20width%3D%22456.125%22%0A%20%20%20%20%20%20%20%20%20%20x%3D%2285.5879%22%0A%20%20%20%20%20%20%20%20%20%20y%3D%22256.055%22%0A%20%20%20%20%20%20%20%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Crect%0A%20%20%20%20%20%20%20%20%20%20fill%3D%22%23FFEC3C%22%0A%20%20%20%20%20%20%20%20%20%20height%3D%2235.875%22%0A%20%20%20%20%20%20%20%20%20%20transform%3D%22rotate(-30%2085.5879%20256.055)%22%0A%20%20%20%20%20%20%20%20%20%20width%3D%22184.5%22%0A%20%20%20%20%20%20%20%20%20%20x%3D%2285.5879%22%0A%20%20%20%20%20%20%20%20%20%20y%3D%22256.055%22%0A%20%20%20%20%20%20%20%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Crect%0A%20%20%20%20%20%20%20%20%20%20fill%3D%22%23FF931E%22%0A%20%20%20%20%20%20%20%20%20%20height%3D%2235.875%22%0A%20%20%20%20%20%20%20%20%20%20transform%3D%22rotate(-30%20227.432%20132.736)%22%0A%20%20%20%20%20%20%20%20%20%20width%3D%22271.625%22%0A%20%20%20%20%20%20%20%20%20%20x%3D%22227.432%22%0A%20%20%20%20%20%20%20%20%20%20y%3D%22132.736%22%0A%20%20%20%20%20%20%20%20%2F%3E%0A%20%20%20%20%20%20%3C%2Fg%3E%0A%20%20%20%20%20%20%3Cdefs%3E%0A%20%20%20%20%20%20%20%20%3CclipPath%20id%3D%22clip%22%3E%0A%20%20%20%20%20%20%20%20%20%20%3Crect%20fill%3D%22white%22%20height%3D%22180%22%20width%3D%22336%22%20%2F%3E%0A%20%20%20%20%20%20%20%20%3C%2FclipPath%3E%0A%20%20%20%20%20%20%3C%2Fdefs%3E%0A%20%20%20%20%3C%2Fsvg%3E)
Ký tự đại diện (wildcard) trong Excel là các ký hiệu đặc biệt giúp bạn thực hiện đối sánh một phần và thao tác dữ liệu linh hoạt trên các tập dữ liệu lớn. Dù Excel không hỗ trợ đầy đủ regex, bạn vẫn có thể dùng ký tự đại diện với cách cấu trúc công thức linh hoạt (các hàm như
SEARCH(),TEXT()vàISNUMBER()).Việc thành thạo ký tự đại diện sẽ dễ dàng hơn khi bạn nắm vững các kiến thức nền tảng Excel. Khóa học Introduction to Excel của chúng tôi hướng dẫn bạn các kỹ năng thiết yếu như điều hướng giao diện, tham chiếu ô và công thức cơ bản—tạo nền tảng vững chắc để bạn áp dụng hiệu quả các kỹ thuật dùng ký tự đại diện trong hướng dẫn này.
Ký tự đại diện trong Excel là gì?
Ký tự đại diện là các ký hiệu đặc biệt thay thế cho ký tự trong chuỗi văn bản, giúp bạn tìm kiếm theo mẫu thay vì khớp chính xác. Excel nhận diện ba ký tự đại diện, mỗi ký tự có mục đích riêng trong đối sánh mẫu và thao tác văn bản.
Dấu hoa thị (*)
Dấu hoa thị (
*) khớp với mọi số lượng ký tự, kể cả không có ký tự nào. Ví dụ,App*sẽ khớp với "Apple", "Application", "App" và "Approach".Ký tự này hữu ích khi bạn biết phần đầu của chuỗi văn bản nhưng muốn bao quát mọi biến thể theo sau.
Dấu hỏi (?)
Dấu hỏi (
?) khớp chính xác một ký tự. DùngB?tsẽ khớp với "Bat", "Bet", "Bit" và "But", nhưng không khớp với "Boat" hoặc "Boot."Độ chính xác này lý tưởng khi bạn biết độ dài và phần lớn ký tự của chuỗi mục tiêu.
Dấu ngã (~)
Dấu ngã (
~) đóng vai trò ký tự thoát (escape), cho phép bạn tìm dấu hoa thị hoặc dấu hỏi thực sự trong dữ liệu. Khi cần tìm văn bản chứa ký tự*hoặc?theo nghĩa đen, đặt dấu ngã phía trước sẽ báo cho Excel coi chúng là ký tự thường thay vì ký tự đại diện.Ví dụ,
~*Salesẽ khớp với cụm từ đúng “*Sale” thay vì coi dấu hoa thị là ký tự đại diện.Dùng ký tự đại diện trong công thức Excel
Nhiều hàm Excel hỗ trợ ký tự đại diện, rất hữu ích cho phép tính có điều kiện và tra cứu. Các hàm thường dùng chấp nhận ký tự đại diện gồm
COUNTIF(),COUNTIFS(),SUMIF(),SUMIFS(),AVERAGEIF(),AVERAGEIFS(),SEARCH(),VLOOKUP(),HLOOKUP(),XLOOKUP(),MATCH()vàXMATCH().Mỗi hàm này có thể tận dụng ký tự đại diện để đối sánh mẫu linh hoạt, cho phép bạn làm việc với đối sánh một phần và xử lý các biến thể trong tập dữ liệu hiệu quả.
COUNTIF() với ký tự đại diện
Hàm
COUNTIF()đặc biệt hữu ích khi kết hợp với ký tự đại diện để đếm các ô khớp với mẫu cụ thể. Ví dụ cơ bản:Công thức này đếm tất cả ô trong phạm vi
A2:A12có chứa "AA" ở bất kỳ vị trí nào trong văn bản. Dấu hoa thị trước và sau "AA" báo với Excel khớp với mọi ký tự đứng trước hoặc sau chuỗi này.Với tiêu chí động, bạn có thể tham chiếu đến một ô chứa mẫu ký tự đại diện:
Trong trường hợp này, ô E1 có thể chứa "*AA*" hoặc bất kỳ mẫu ký tự đại diện nào khác. Cách này cho phép bạn thay đổi tiêu chí tìm kiếm mà không cần sửa công thức, giúp bảng tính linh hoạt và thân thiện hơn.
VLOOKUP() và XLOOKUP() với ký tự đại diện
Các hàm tra cứu có thể dùng ký tự đại diện để đối sánh một phần, hữu ích khi giá trị tra cứu không khớp chính xác. Dưới đây là ví dụ cho cả hai cách tiếp cận:
Công thức
VLOOKUP()này tìm mọi mục trong cột A bắt đầu bằng "John" và trả về giá trị tương ứng từ cột 2. Tham sốFALSEđảm bảo hàm dùng chế độ khớp chính xác, vốn là yêu cầu để ký tự đại diện hoạt động.Với
XLOOKUP(), cú pháp hơi khác:Tham số chế độ đối sánh (
2) báo choXLOOKUP()dùng đối sánh ký tự đại diện. Công thức này tìm mọi mục kết thúc bằng "Adams" và trả về giá trị tương ứng từ cột C. Nếu không có kết quả, hàm sẽ trả về “Not found”.IF() với cách xử lý thay thế ký tự đại diện
Mặc dù hàm
IF()không trực tiếp hỗ trợ ký tự đại diện, bạn có thể đạt chức năng tương tự bằng cách kết hợpIF()vớiCOUNTIF():Công thức này kiểm tra ô A1 có chứa văn bản bắt đầu bằng "Amy" hay không và trả về "Yes" nếu có, "No" nếu không. Hàm
COUNTIF()xử lý phần đối sánh ký tự đại diện, cònIF()xử lý kết quả.SUMIF() với ký tự đại diện
Hàm
SUMIF()có thể cộng các giá trị dựa trên tiêu chí dùng ký tự đại diện, rất hữu ích để tổng hợp dữ liệu theo các mẫu tương tự:Công thức này cộng tất cả giá trị ở cột C nơi ô tương ứng ở cột A chứa chữ "a" ở bất kỳ vị trí nào. Mẫu ký tự đại diện "a" bao quát mọi văn bản có chứa "a" bất kể vị trí.
Dùng ký tự đại diện trong các công cụ Excel
Ký tự đại diện không chỉ dùng trong công thức mà còn khả dụng trong nhiều công cụ Excel để tìm kiếm, đối sánh và lọc dữ liệu. Các tính năng tích hợp này giúp bạn tiếp cận ký tự đại diện ngay cả khi không viết công thức phức tạp.
Ký tự đại diện trong Tìm kiếm và Thay thế
Hộp thoại Find and Replace của Excel hỗ trợ ký tự đại diện để tìm các mẫu biến đổi trong dữ liệu. Để bật chức năng ký tự đại diện, bạn cần chọn tùy chọn "Use wildcards" trong hộp thoại Find and Replace.
Bạn có thể dùng dấu hoa thị (*) để tìm các mẫu có độ dài khác nhau. Ví dụ, tìm "Product*X" sẽ phát hiện "Product-A-X", "Product-Premium-X" và "Product-X." Tương tự, dấu hỏi (?) giúp tìm các mẫu theo vị trí ký tự cố định, như "20??" để tìm mọi số có bốn chữ số bắt đầu bằng "20."
Khi cần tìm dấu hoa thị hoặc dấu hỏi theo nghĩa đen trong dữ liệu, hãy dùng dấu ngã (~) làm ký tự thoát. Tìm "~*Sale" sẽ phát hiện các ô chứa văn bản đúng "*Sale" thay vì coi dấu hoa thị là ký tự đại diện.
Ký tự đại diện trong tính năng lọc và tìm kiếm
Khả năng lọc của Excel hỗ trợ ký tự đại diện, đặc biệt hữu ích trong bộ lọc văn bản với các tùy chọn như "contains", "begins with" và "ends with." Các bộ lọc này sẽ tự động hiểu ký tự đại diện khi bạn đưa chúng vào tiêu chí.
Ví dụ, áp dụng bộ lọc với "Jo*" trong tùy chọn "begins with" sẽ hiển thị tất cả tên bắt đầu bằng "Jo" - bao gồm "John", "Joseph", "Joan" và "Jonathan." Cách tiếp cận này phù hợp cho việc khám phá dữ liệu nhanh mà không cần viết công thức.
Tuy nhiên, tính năng tìm kiếm tích hợp có một số hạn chế so với các tùy chọn lọc nâng cao. Mặc dù các mẫu ký tự đại diện cơ bản hoạt động hiệu quả, các yêu cầu lọc đa tiêu chí phức tạp có thể cần kết hợp ký tự đại diện với công thức tùy chỉnh hoặc dùng tính năng Advanced Filter của Excel để đối sánh mẫu tinh vi hơn.
Làm việc với số và ký tự đại diện
Dùng ký tự đại diện với số cần lưu ý đặc biệt vì Excel xử lý ký tự đại diện như các thao tác dựa trên văn bản. Khi áp dụng mẫu ký tự đại diện cho dữ liệu số, Excel sẽ tự động chuyển số thành văn bản, có thể dẫn đến hành vi khớp không như mong đợi.
Để đối sánh số hiệu quả với ký tự đại diện, bạn có vài cách xử lý thay thế. Bạn có thể chuyển số thành văn bản bằng hàm
TEXT(), tính năng Text to Columns, hoặc các công thức chuyên biệt cho mục đích này.Một cách thực tiễn là kết hợp các hàm
SEARCH(),ISNUMBER()vàSUMPRODUCT():Công thức này đếm số ô trong phạm vi B2:B12 có chứa chữ số "4" ở bất kỳ vị trí nào. Để cộng các giá trị nơi số chứa mẫu cụ thể, bạn có thể mở rộng cách tiếp cận này:
Công thức này cộng các giá trị ở cột C nơi các số tương ứng ở cột B chứa “4” theo sau bởi bất kỳ chữ số nào.
Với việc đối sánh ngày, hãy dùng hàm
TEXT()để chuyển ngày thành các mẫu văn bản có thể tìm kiếm:Cách này chuyển ngày sang định dạng văn bản nhất quán để ký tự đại diện xử lý hiệu quả. Sau đó bạn có thể áp dụng mẫu ký tự đại diện để tìm các thành phần ngày cụ thể:
Công thức đếm số ngày có chứa “0525” (ngày 25 tháng 5 của bất kỳ năm nào).
Một số trường hợp có số được lưu dưới dạng văn bản, như mã ZIP, mã sản phẩm hoặc số tham chiếu. Khi đó, ký tự đại diện có thể khớp trực tiếp như chuỗi văn bản. Ví dụ, lọc các mã theo mẫu "123*" sẽ khớp với "12345", "123-AB" và "123XYZ", trong khi "??-45" sẽ khớp với "AB-45", "12-45" và "XY-45."
Hạn chế và những điểm dễ nhầm của ký tự đại diện
Không phải mọi hàm Excel đều hỗ trợ ký tự đại diện, điều này có thể gây nhầm lẫn khi bạn kỳ vọng công thức sẽ hoạt động với đối sánh mẫu. Ví dụ, hàm
IF()không chấp nhận ký tự đại diện trực tiếp, cần các cách xử lý như kết hợp vớiCOUNTIF()như đã minh họa.Ký tự đại diện chỉ hoạt động với văn bản trong công thức, nghĩa là giá trị số phải được chuyển sang văn bản trước khi thao tác ký tự đại diện có thể xử lý hiệu quả. Yêu cầu chuyển đổi này có thể dẫn đến kết quả bất ngờ nếu bạn không lưu ý.
Hãy cẩn trọng với đối sánh sai (false positive) trong các công thức tra cứu khi dùng mẫu ký tự đại diện quá rộng. Chẳng hạn,
VLOOKUP("*Smith*", ...)có thể trả về các kết quả ngoài ý như "Smithson", "Blacksmith" hoặc "Goldsmith" khi bạn chỉ muốn "Smith." Hành vi này đòi hỏi thiết kế mẫu cẩn thận để tránh lấy phải kết quả không mong muốn.Một điểm thường gây bất ngờ khác là khoảng trắng ở đầu hoặc cuối trong dữ liệu. Ký tự đại diện coi khoảng trắng là ký tự, nên "John*" sẽ không khớp với " John" (có khoảng trắng đầu dòng). Việc làm sạch dữ liệu trở nên quan trọng khi làm việc với ký tự đại diện để đảm bảo kết quả nhất quán.
Ngoài ra, đối sánh ký tự đại diện mặc định không phân biệt chữ hoa/thường. Điều này có nghĩa là các mẫu như "apple*" sẽ khớp với "Apple", "APPLE" và "ApPlE." Cách hoạt động này thuận tiện cho tìm kiếm chung nhưng có thể gây khớp ngoài ý nếu bạn cần phân biệt chữ hoa/thường. Để đạt đối sánh có phân biệt chữ, hãy cân nhắc dùng các hàm như
FIND()kết hợp vớiISNUMBER().Mẹo khi làm việc với ký tự đại diện trong Excel
Ký tự đại diện rất hiệu quả trong việc hợp nhất dữ liệu khớp mơ hồ (fuzzy match), đặc biệt hữu ích khi có lỗi chính tả hoặc sai khác trong nhập liệu. Ví dụ, bạn có thể có "Homel" và "Gomel" trong tập dữ liệu cùng biểu thị một địa điểm. Dùng các mẫu như "*omel" có thể giúp nhận diện và hợp nhất các mục tương tự.
Định dạng số điện thoại thường không nhất quán giữa các tập dữ liệu, với tiền tố hoặc mã quốc gia khác nhau. Bạn có thể dùng ký tự đại diện để chuẩn hóa các điểm không nhất quán này. Chẳng hạn, bạn có thể dùng Find and Replace để chuyển số UK dạng quốc tế về dạng nội địa:
Mẫu này khớp mọi số UK bắt đầu bằng "44" theo sau là mười chữ số, hiệu quả chuyển các số như "447123456789" thành "07123456789" để có định dạng nội địa nhất quán.
Khi cần loại trừ một số kết quả khỏi đối sánh ký tự đại diện, hãy kết hợp nhiều hàm
SUMIF()hoặcCOUNTIF(). Kỹ thuật này hiệu quả để trừ đi các đối sánh không mong muốn:Công thức này cộng tất cả giá trị khớp với "00123H*" nhưng loại trừ những giá trị kết thúc bằng "-SP", giúp bạn kiểm soát chính xác hơn việc đối sánh bằng ký tự đại diện.
Các thay đổi định dạng hoạt động hiệu quả cùng với ký tự đại diện khi dùng các hàm như
REPLACE(),SUBSTITUTE()hoặcTEXT(). Các hàm này có thể làm sạch và chuẩn hóa dữ liệu trước hoặc sau khi áp dụng ký tự đại diện, đảm bảo kết quả phân tích nhất quán.Khi làm với mã sản phẩm hoặc số tham chiếu, ký tự đại diện giúp nhận diện mẫu và ngoại lệ. Bạn có thể dùng
*-TEMPđể tìm tất cả mã tạm thời, hoặc??-??-????để xác định các mục theo một cấu trúc định dạng cụ thể, giúp việc kiểm tra và dọn dẹp dữ liệu hiệu quả hơn.Kết luận
Dù bạn đang hợp nhất các mục tương tự, chuẩn hóa định dạng số điện thoại hay lọc tập dữ liệu lớn theo mẫu cụ thể, ký tự đại diện mang đến một lựa chọn thiết thực thay cho đối sánh chính xác. Chúng lấp khoảng trống giữa tìm kiếm văn bản cơ bản và chức năng regex phức tạp hơn, giúp đối sánh mẫu trở nên dễ tiếp cận trong môi trường quen thuộc của Excel.
Để mở rộng khả năng Excel vượt ra ngoài thao tác với ký tự đại diện, lộ trình kỹ năng Excel Fundamentals của chúng tôi cung cấp một hành trình toàn diện bao gồm chuẩn bị dữ liệu, phân tích và trực quan hóa. Nếu bạn sẵn sàng áp dụng những kỹ năng này trong bối cảnh phân tích, khóa học Data Analysis in Excel mang đến trải nghiệm thực hành với PivotTable và các hàm logic nâng cao, bổ trợ cho kỹ năng đối sánh mẫu bạn đã học ở đây.