Formula generik
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Ringkasan
Untuk mengkategorikan teks menggunakan kata kunci dengan padanan "mengandung", anda boleh menggunakan fungsi SEARCH, dengan bantuan dari INDEX dan MATCH. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
di mana kata kunci adalah julat bernama E5: E14, dan kategori adalah rentang bernama F5: F14.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Pada intinya, ini adalah fungsi INDEX dan MATCH.
Dalam fungsi MATCH, kita menggunakan fungsi SEARCH untuk mencari sel dalam lajur B untuk setiap kata kunci yang disenaraikan dalam lingkungan dinamakan kata kunci (E5: E14):
SEARCH(keywords,B5)
Kerana kami mencari banyak item (dalam kata kunci julat yang dinamakan ), kami akan mendapat banyak hasil seperti ini:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Nilai! ralat berlaku apabila SEARCH tidak dapat mencari teks. Apabila SEARCH menemui padanan, ia mengembalikan nombor yang sesuai dengan kedudukan teks di dalam sel.
Untuk mengubah hasil ini menjadi format yang lebih berguna, kami menggunakan fungsi ISNUMBER, yang mengubah semua nilai menjadi BENAR / SALAH seperti:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Susunan ini masuk ke fungsi MATCH sebagai lookup_array, dengan lookup_value ditetapkan sebagai BENAR. MATCH mengembalikan kedudukan TRUE pertama yang dijumpai dalam array (7 dalam kes ini) yang diberikan kepada fungsi INDEX sebagai baris_num:
=INDEX(categories,7)
INDEX mengembalikan item ke-7 dalam kategori , "Auto", sebagai hasil akhir.
Dengan XLOOKUP
Dengan fungsi XLOOKUP, formula ini dapat dipermudahkan. XLOOKUP dapat menggunakan logik yang sama yang digunakan dalam fungsi MATCH di atas, jadi formula yang setara adalah:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP mencari TRUE pertama dalam array, dan mengembalikan nilai yang sesuai dari kategori .
Mencegah pertandingan palsu
Satu masalah dengan pendekatan ini ialah anda mungkin mendapat padanan palsu dari substring yang muncul dalam perkataan yang lebih panjang. Contohnya, jika anda mencocokkan "dr", anda mungkin juga menemui "Andrea", "minuman", "kering", dan lain-lain kerana "dr" terdapat di dalam kata-kata ini. Ini berlaku kerana SEARCH secara automatik melakukan pertandingan "mengandung".
Untuk penggodaman cepat, anda boleh menambahkan ruang di sekitar kata carian (iaitu "dr", atau "dr") untuk mengelakkan menangkap "dr" dengan kata lain. Tetapi ini akan gagal jika "dr" muncul pertama atau terakhir di sel, atau muncul dengan tanda baca, dll.
Sekiranya anda memerlukan penyelesaian yang lebih tepat, satu pilihan adalah menormalkan teks terlebih dahulu di kolom penolong, berhati-hati juga menambah ruang depan dan ketinggalan. Kemudian anda boleh mencari keseluruhan perkataan yang dikelilingi oleh spasi.
