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 Excel
Trong 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í đó.
Hai ký tự đại diện phổ biến mà Excel nhận ra là dấu hoa thị (*) và dấu chấm hỏi (?). Dấu ngã (~) buộc Excel phải coi các chủ đề là các ký tự thông thường, không phải ký tự đại diện.
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ện
Dấ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ện
Dấ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 nullifier
Dấ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-2020 * , 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
Việc sử dụng các ký tự đại diện với tính năng Tìm và Thay thế của Excel khá linh hoạt. Các ví dụ sau đây sẽ thảo luận về một số trường hợp phổ biến và cảnh báo bạn về một số lưu ý.
Cách tìm kiếm bằng ký tự đại diện
Theo 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.
Trong tập dữ liệu dưới đây, giả sử bạn muốn tìm các ID bao gồm 4 ký tự được phân tách bằng dấu gạch ngang. Vì vậy, bạn mở hộp thoại Tìm và Thay thế(Ctrl + F), loại ?? – ?? trong hộp Tìm gì và nhấn Tìm Tất cả . Kết quả có vẻ hơi rắc rối, phải không?
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ện
Trong 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 Excel
Cá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.
- Bấm OK .
Đ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ện
Trước hết, cần lưu ý rằng có một số lượng hạn chế các hàm Excel hỗ trợ ký tự đại diện. Dưới đây là danh sách các hàm phổ biến nhất làm với các ví dụ về công thức:
AVERAGEIF 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 Excel
Giả 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í :
=COUNTIF(A2:A12, "*AA*")
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,"*"&E1&"*")
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 Excel
Khi 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.
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ả:
=VLOOKUP(D2&"*", $A$2:$B$12, 2, FALSE)
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ện
Trong ả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ố:
Lọc với số ký tự đại diện
Tươ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ức
Cá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ố.
Ví dụ: cả hai công thức dưới đây đều đếm số chuỗi có chứa “4” một cách hoàn hảo:
=COUNTIF(A2:A12, "*4*" )
=COUNTIF(A2:A12, "*"&E1&"*" )
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à:
=COUNTIF(B2:B12, E1&"*" )
Trong trường hợp phương pháp này không được chấp nhận trên thực tế, bạn sẽ phải đưa ra công thức riêng cho từng trường hợp cụ thể. Than ôi, một giải pháp chung không tồn tại 🙁 Dưới đây, bạn sẽ tìm thấy một vài ví dụ.
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:
=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))
Công thức này hoạt động như thế nào
Làm việc từ trong ra ngoài, đây là những gì công thức thực hiện:
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:
{#VALUE!;1;#VALUE!;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}
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:
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
Toán tử một ngôi kép (-) buộc TRUE và FALSE thành 1 và 0, tương ứng:
{0;1;0;0;1;0;0;1;0;0;0}
Cuối cùng, hàm SUMPRODUCT cộng số 1 và trả về số lượng.
Ví dụ 2. Công thức ký tự đại diện cho ngày tháng
Công thức SUMPRODUCT được thảo luận ở trên hoạt động tốt cho các con số nhưng sẽ không thành công cho các ngày. Tại sao? Bởi vì bên trong Excel lưu trữ ngày tháng dưới dạng số sê-ri và công thức sẽ xử lý những số đó chứ không phải ngày tháng được hiển thị trong ô.
Để vượt qua trở ngại này, hãy sử dụng hàm TEXT để chuyển đổi ngày tháng thành chuỗi văn bản, sau đó nạp các chuỗi vào hàm SEARCH.
Tùy thuộc vào chính xác những gì bạn muốn đếm, định dạng văn bản có thể khác nhau.
Để đếm tất cả các ngày trong C2: C12 có “4” trong ngày, tháng hoặc năm, hãy sử dụng ” mmddyyyy” :
=SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "mmddyyyy")))))
Để 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” :
=SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "dd")))))
Đó là cách sử dụng ký tự đại diện trong Excel. Tôi hy vọng thông tin này sẽ hữu ích trong công việc của bạn. Dù sao, tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Ký tự đại diện trong Excel: Công thức sử dụng tìm, thay thế và lọc appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/ky-tu-dai-dien-trong-excel.html
Không có nhận xét nào:
Đăng nhận xét