Công thức Excel: Khớp một phần với các số có ký tự đại diện -

Mục lục

Công thức chung

(=MATCH("*"&number&"*",TEXT(range,"0"),0))

Tóm lược

Để thực hiện đối sánh một phần (ký tự đại diện) với các số, bạn có thể sử dụng công thức mảng dựa trên hàm MATCH và hàm TEXT. Trong ví dụ được hiển thị, công thức trong E6 là:

(=MATCH("*"&E5&"*",TEXT(B5:B10,"0"),0))

Đây là công thức mảng và phải được nhập bằng Control + Shift + Enter, ngoại trừ trong Excel 365.

Giải trình

Excel hỗ trợ các ký tự đại diện "*" và "?", Và các ký tự đại diện này có thể được sử dụng để thực hiện đối sánh từng phần (chuỗi con) trong các công thức tra cứu khác nhau.

Tuy nhiên, nếu bạn sử dụng các ký tự đại diện cùng với một số, bạn sẽ chuyển đổi giá trị số thành giá trị văn bản. Nói cách khác, "*" & 99 & "*" = "* 99 *" (một chuỗi văn bản). Và nếu bạn cố gắng tìm một giá trị văn bản trong một dãy số, kết quả khớp sẽ không thành công.

Một giải pháp là chuyển đổi các giá trị số thành văn bản bên trong công thức bằng cách sử dụng hàm TEXT hoặc bằng cách nối chuỗi rỗng và chuỗi trống vào dải ô. Để làm điều này, công thức trong E6 là:

(=MATCH("*"&E5&"*",TEXT(B5:B10,"0"),0))

Đây là công thức mảng và phải được nhập bằng Control + Shift + Enter

Công thức này sử dụng hàm TEXT để chuyển các số trong B5: B10 thành văn bản có định dạng số "0". Bởi vì chúng tôi cung cấp toàn bộ phạm vi cho TEXT, chúng tôi nhận lại tất cả các giá trị được chuyển đổi thành văn bản trong một mảng, được trả về trực tiếp cho hàm MATCH dưới dạng đối số mảng. Với các số được chuyển đổi thành văn bản, hàm MATCH có thể tìm thấy một phần khớp như bình thường.

Lưu ý rằng MATCH phải được định cấu hình để đối sánh chính xác để sử dụng ký tự đại diện, bằng cách đặt đối số thứ 3 thành 0 hoặc FALSE.