Formula umum
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Ringkasan
Untuk mengkategorikan teks menggunakan kata kunci dengan kecocokan "berisi", Anda dapat menggunakan fungsi SEARCH, dengan bantuan dari INDEX dan MATCH. Pada contoh yang ditunjukkan, rumus di C5 adalah:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
di mana kata kunci adalah rentang bernama E5: E14, dan kategori adalah rentang bernama F5: F14.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Pada intinya, ini adalah fungsi INDEX dan MATCH.
Di dalam fungsi MATCH, kita menggunakan fungsi SEARCH untuk sel dalam kolom B mencari setiap kata kunci yang tercatat di kisaran bernama kata kunci (E5: E14):
SEARCH(keywords,B5)
Karena kami mencari beberapa item (dalam kata kunci rentang bernama ), kami akan mendapatkan kembali beberapa hasil seperti ini:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Nilai! kesalahan terjadi ketika SEARCH tidak dapat menemukan teks. Saat SEARCH menemukan kecocokan, ia mengembalikan angka yang sesuai dengan posisi teks di dalam sel.
Untuk mengubah hasil ini menjadi format yang lebih bisa digunakan, kami menggunakan fungsi ISNUMBER, yang mengonversi semua nilai menjadi TRUE / FALSE seperti:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Larik ini masuk ke fungsi MATCH sebagai lookup_array, dengan lookup_value disetel TRUE. MATCH mengembalikan posisi TRUE pertama yang ditemukannya dalam larik (7 dalam kasus ini) yang diberikan ke fungsi INDEX sebagai row_num:
=INDEX(categories,7)
INDEX mengembalikan item ke-7 dalam kategori , "Otomatis", sebagai hasil akhir.
Dengan XLOOKUP
Dengan fungsi XLOOKUP, rumus ini dapat disederhanakan. XLOOKUP dapat menggunakan logika yang sama yang digunakan di dalam fungsi MATCH di atas, jadi rumus ekuivalennya adalah:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP menempatkan TRUE pertama dalam larik, dan mengembalikan nilai yang sesuai dari kategori .
Mencegah kecocokan palsu
Satu masalah dengan pendekatan ini adalah Anda mungkin mendapatkan kecocokan palsu dari substring yang muncul di dalam kata yang lebih panjang. Misalnya, jika Anda mencoba mencocokkan "dr", Anda juga dapat menemukan "Andrea", "drink", "dry", dll. Karena "dr" muncul di dalam kata ini. Ini terjadi karena SEARCH secara otomatis melakukan kecocokan "berisi".
Untuk peretasan cepat, Anda dapat menambahkan spasi di sekitar kata pencarian (yaitu "dr", atau "dr") untuk menghindari menangkap "dr" di kata lain. Tapi ini akan gagal jika "dr" muncul pertama atau terakhir dalam sel, atau muncul dengan tanda baca, dll.
Jika Anda membutuhkan solusi yang lebih akurat, salah satu opsinya adalah menormalkan teks terlebih dahulu di kolom pembantu, berhati-hatilah untuk juga menambahkan spasi di depan dan di belakang. Kemudian Anda dapat mencari seluruh kata yang dikelilingi oleh spasi.
