Công thức Excel: Khớp văn bản dài -

Công thức chung

=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)

Tóm lược

Để so khớp văn bản dài hơn 255 ký tự với hàm MATCH, bạn có thể sử dụng các hàm LEFT, MID và EXACT để phân tích cú pháp và so sánh văn bản, như được giải thích bên dưới. Trong ví dụ được hiển thị, công thức trong G5 là:

=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)

trong đó dữ liệu là dải ô được đặt tên B5: B15.

Lưu ý: công thức này thực hiện so sánh phân biệt chữ hoa chữ thường.

Giải trình

Hàm MATCH có giới hạn 255 ký tự cho giá trị tra cứu. Nếu bạn cố gắng sử dụng văn bản dài hơn, MATCH sẽ trả về lỗi #VALUE.

Để giải quyết giới hạn này, bạn có thể sử dụng logic boolean và các hàm LEFT, MID và EXACT để phân tích cú pháp và so sánh văn bản.

Chuỗi chúng tôi đang kiểm tra trong ô E5 là 373 ký tự như sau:

Lorem ipsum dolor amet đặt một con chim trên đó quỹ ủy thác listicle, quyền xe đạp hơi kỳ lân mà bạn có thể chưa nghe nói về chúng ria mép. Forage helvetica ký hiệu học lớp vỏ thực sự gia truyền. Tumblr poutine kỳ lân đỡ đầu cố gắng hết sức trước khi họ bán hết kỳ lân biển thiền định kitsch áo ghi lê fixie twee đúng nghĩa là áo hoodie retro. Sứ giả túi địa ngục của nghệ nhân nước ép xanh giòn.

Về cơ bản, đây chỉ là một công thức TRẬN ĐẤU, được thiết lập để tìm kiếm 1 trong chế độ đối sánh chính xác:

=MATCH(1,array,0)

Mảng trong công thức ở trên chỉ chứa 1s và 0s và 1s đại diện cho văn bản phù hợp. Mảng này được xây dựng bởi biểu thức sau:

EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))

Biểu thức này tự nó có hai phần. Ở bên trái chúng ta có:

EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars

Ở đây, hàm LEFT trích xuất 255 ký tự đầu tiên từ E5 và từ tất cả các ô trong dữ liệu phạm vi được đặt tên (B5: B15). Vì dữ liệu chứa 11 chuỗi văn bản, LEFT sẽ tạo ra 11 kết quả.

Sau đó, hàm EXACT so sánh chuỗi đơn từ E5 với tất cả 11 chuỗi được trả về bởi LEFT. EXACT trả về 11 kết quả trong một mảng như thế này:

(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)

Ở bên phải, chúng ta có một biểu thức khác:

EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars

Đây chính xác là cách tiếp cận giống như được sử dụng với LEFT, nhưng ở đây chúng tôi sử dụng hàm MID để trích xuất 255 ký tự tiếp theo của văn bản. Hàm EXACT lại trả về 11 kết quả:

(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)

Khi hai mảng trên được nhân với nhau, phép toán sẽ ép các giá trị TRUE FALSE thành 1s và 0s. Tuân theo các quy tắc của số học boolean, kết quả là một mảng như sau:

(0;0;0;0;0;0;0;0;0;1;0)

được trả về trực tiếp MATCH dưới dạng mảng tra cứu. Công thức bây giờ có thể được giải quyết thành:

=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)

Hàm MATCH thực hiện so khớp chính xác và trả về kết quả cuối cùng là 10, đại diện cho chuỗi văn bản thứ mười trong B5: B15.

Lưu ý: độ dài văn bản hiển thị trong ví dụ được tính bằng hàm LEN. Nó chỉ xuất hiện để tham khảo.

Tùy chọn phân biệt chữ hoa chữ thường

Hàm EXACT phân biệt chữ hoa chữ thường, vì vậy công thức trên sẽ tôn trọng chữ hoa chữ thường.

Để thực hiện đối sánh không phân biệt chữ hoa chữ thường với văn bản dài, bạn sử dụng hàm ISNUMBER và SEARCH như sau:

=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)

Cấu trúc tổng thể của công thức này giống với ví dụ trên, nhưng hàm SEARCH được sử dụng thay vì EXACT để so sánh văn bản (giải thích chi tiết tại đây).

Không giống như EXACT, hàm SEARCH cũng hỗ trợ các ký tự đại diện.

Với XMATCH

Hàm XMATCH không có giới hạn 255 ký tự giống như hàm MATCH. Để thực hiện đối sánh tương tự trên văn bản dài với XMATCH, bạn có thể sử dụng công thức đơn giản hơn nhiều bên dưới: