Formula generik
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Ringkasan
Untuk memadankan teks yang melebihi 255 aksara dengan fungsi MATCH, anda dapat menggunakan fungsi LEFT, MID, dan EXACT untuk menguraikan dan membandingkan teks, seperti yang dijelaskan di bawah. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
di mana data adalah julat bernama B5: B15.
Catatan: formula ini melakukan perbandingan peka huruf besar-kecil.
Penjelasan
Fungsi MATCH mempunyai had 255 aksara untuk nilai carian. Sekiranya anda cuba menggunakan teks yang lebih panjang, MATCH akan mengembalikan ralat #VALUE.
Untuk mengatasi had ini, anda boleh menggunakan logik boolean dan fungsi LEFT, MID, dan EXACT untuk menguraikan dan membandingkan teks.
String yang kami uji dalam sel E5 adalah 373 aksara seperti berikut:
Lorem ipsum dolor amet meletakkan burung di atasnya tabung amanah saham, hak basikal vaporware unicorn yang mungkin anda belum pernah dengar tentang mereka misai. Semiotik berkerak helvetica sebenarnya pusaka. Tumblr poutine unicorn godard berusaha keras sebelum mereka menjual meditasi narwhal kitsch sehelai pinggang fixie twee secara harfiah hoodie retro. Utusan beg neraka jus hijau berkerak.
Pada intinya, ini hanyalah formula MATCH, disiapkan untuk mencari 1 dalam mod padanan tepat:
=MATCH(1,array,0)
Susunan dalam formula di atas hanya mengandungi 1s dan 0s, dan 1s mewakili teks yang sepadan. Susunan ini dibina dengan ungkapan berikut:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Ungkapan ini sendiri mempunyai dua bahagian. Di sebelah kiri kami mempunyai:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Di sini, fungsi LEFT mengekstrak 255 aksara pertama dari E5, dan dari semua sel dalam data julat yang dinamakan (B5: B15). Kerana data mengandungi 11 rentetan teks, KIRI akan menghasilkan 11 hasil.
Fungsi EXACT kemudian membandingkan rentetan tunggal dari E5 dengan semua 11 rentetan yang dikembalikan oleh LEFT. EXACT mengembalikan 11 hasil dalam array seperti ini:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Di sebelah kanan, kita mempunyai ungkapan lain:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Ini adalah pendekatan yang sama seperti yang digunakan dengan LEFT, tetapi di sini kita menggunakan fungsi MID untuk mengekstrak 255 aksara teks seterusnya. Fungsi EXACT kembali menghasilkan 11 hasil:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Apabila dua tatasusunan di atas dikalikan satu sama lain, operasi matematik membentuk nilai BENAR BENAR menjadi 1s dan 0s. Mengikuti peraturan aritmetik boolean, hasilnya adalah susunan seperti ini:
(0;0;0;0;0;0;0;0;0;1;0)
yang dikembalikan terus ke MATCH sebagai tatasusunan pencarian. Formula kini dapat diselesaikan untuk:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
Fungsi MATCH melakukan pencocokan tepat, dan mengembalikan hasil akhir 10, yang mewakili rentetan teks kesepuluh dalam B5: B15.
Catatan: panjang teks yang ditunjukkan dalam contoh dikira dengan fungsi LEN. Ia muncul untuk rujukan sahaja.
Pilihan tidak peka huruf besar kecil
Fungsi EXACT peka huruf besar kecil, jadi formula di atas akan menghormati kes.
Untuk melakukan padanan huruf besar dengan huruf besar, anda menggunakan fungsi ISNUMBER dan SEARCH seperti berikut:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
Struktur keseluruhan formula ini sama dengan contoh di atas, tetapi fungsi SEARCH digunakan dan bukannya EXACT untuk membandingkan teks (dijelaskan secara terperinci di sini).
Tidak seperti EXACT, fungsi SEARCH juga menyokong wildcard.
Dengan XMATCH
Fungsi XMATCH tidak mempunyai had 255 aksara yang sama dengan MATCH. Untuk melakukan padanan serupa pada teks panjang dengan XMATCH, anda boleh menggunakan formula yang lebih mudah di bawah:
=XMATCH(E5,data)
Catatan: XMATCH menyokong kad liar, tetapi tidak peka huruf besar kecil.
