Formula generik
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Ringkasan
Untuk menguji sel untuk salah satu daripada beberapa rentetan, dan mengembalikan hasil khusus untuk pertandingan pertama yang dijumpai, anda boleh menggunakan formula INDEX / MATCH berdasarkan fungsi SEARCH. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
di mana perkara (E5: E8) dan hasil (F5: F8) dinamakan julat.
Ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Penjelasan
Formula ini menggunakan dua julat bernama: perkara , dan hasil . Sekiranya anda menggunakan formula ini secara langsung, pastikan untuk menggunakan julat bernama dengan nama yang sama (ditentukan berdasarkan data anda). Sekiranya anda tidak mahu menggunakan julat bernama, gunakan rujukan mutlak.
Inti formula ini adalah coretan ini:
ISNUMBER(SEARCH(things,B5)
Ini berdasarkan formula lain (dijelaskan secara terperinci di sini) yang memeriksa sel untuk satu substring. Sekiranya sel mengandungi substring, formula mengembalikan BENAR. Sekiranya tidak, formula mengembalikan PALSU.
Kerana kami memberikan fungsi SEARCH yang lebih daripada satu perkara untuk mencari, dalam julat bernama perkara , ia akan memberi kita lebih banyak hasil yang satu, dalam array yang kelihatan seperti ini:
(#VALUE!;9;#VALUE!;#VALUE!)
Nombor mewakili pertandingan dalam sesuatu , kesalahan mewakili item yang tidak dijumpai.
Untuk mempermudah susunan, kami menggunakan fungsi ISNUMBER untuk menukar semua item dalam array menjadi BENAR atau SALAH. Sebarang nombor yang sah menjadi BENAR, dan sebarang kesalahan (iaitu perkara yang tidak dijumpai) menjadi SALAH. Hasilnya adalah susunan seperti ini:
(FALSE;TRUE;FALSE;FALSE)
yang masuk ke fungsi MATCH sebagai argumen lookup_array, dengan lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH kemudian mengembalikan kedudukan TRUE pertama yang dijumpai, 2 dalam kes ini.
Akhir sekali, kami menggunakan fungsi INDEX untuk mendapatkan hasil dari julat bernama keputusan pada kedudukan yang sama:
=INDEX(results,2) // returns "found red"
Anda boleh menyesuaikan rentang hasil dengan nilai apa pun yang masuk akal dalam kes penggunaan anda.
Mencegah pertandingan palsu
Satu masalah dengan pendekatan ini dengan pendekatan PENCARIAN ISNUMBER + adalah anda mungkin mendapat padanan palsu dari separa padanan dengan kata-kata yang lebih panjang. Contohnya, jika anda mencocokkan "dr" anda mungkin juga menemui "Andrea", "minum", "tetes", dan lain-lain kerana "dr" muncul di dalam kata-kata ini. Ini berlaku kerana SEARCH secara automatik melakukan pertandingan "berisi-jenis".
Untuk memperbaiki cepat, anda boleh membungkus kata carian dengan aksara ruang (iaitu "dr", atau "dr") untuk mengelakkan mencari "dr" dengan kata lain. Tetapi ini akan gagal sekiranya "dr" muncul pertama atau terakhir dalam sel.
Sekiranya anda memerlukan penyelesaian yang lebih mantap, satu pilihan adalah menormalkan teks terlebih dahulu di ruangan penolong, dan menambahkan ruang depan dan belakang. Kemudian gunakan formula di halaman ini pada teks di lajur pembantu, bukannya teks asalnya.
