Formula generik
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Ringkasan
Untuk memeriksa sel untuk salah satu daripada beberapa perkara, dan mengembalikan pertandingan pertama yang terdapat dalam senarai, anda boleh menggunakan formula INDEX / MATCH yang menggunakan SEARCH atau FIND untuk mencari perlawanan. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
di mana "benda" adalah julat bernama E5: E9.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Penjelasan
Dalam contoh ini, kita mempunyai senarai warna dalam julat bernama yang disebut "benda" (E5: E9). Kami ingin memeriksa teks di lajur B untuk melihat apakah ia mengandungi warna-warna ini. Sekiranya demikian, kami ingin mengembalikan nama warna pertama yang dijumpai.
Berfungsi dari dalam ke luar, formula ini menggunakan fungsi ISNUMBER dan fungsi SEARCH untuk mencari teks dalam B5 untuk setiap warna yang disenaraikan dalam "perkara" seperti ini:
ISNUMBER(SEARCH(things,B5)
Ungkapan ini didasarkan pada formula (dijelaskan secara terperinci di sini) yang memeriksa sel untuk satu substring. Sekiranya sel mengandungi substring, ungkapan mengembalikan BENAR. Sekiranya tidak, ungkapan mengembalikan SALAH.
Apabila kita memberikan senarai perkara ini (bukan satu perkara) dalam CARIAN ini, kita akan memberikan pelbagai hasil. Setiap warna yang dijumpai akan menghasilkan kedudukan berangka, dan warna yang tidak dijumpai akan menghasilkan kesalahan:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Fungsi ISNUMBER kemudian menukar hasil menjadi nilai BENAR / SALAH. Sebarang nombor menjadi BENAR, dan sebarang kesalahan (tidak dijumpai) menjadi SALAH. Hasilnya adalah susunan seperti ini:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Array ini dikembalikan ke fungsi MATCH sebagai argumen array. Nilai carian adalah BENAR dan jenis padanan ditetapkan ke sifar untuk memaksa padanan tepat. Apabila terdapat warna yang sepadan, MATCH mengembalikan kedudukan pertama yang BENAR dijumpai. Nilai ini dimasukkan ke dalam fungsi INDEX sebagai nombor baris, dengan julat "benda" bernama sebagai array. Apabila terdapat sekurang-kurangnya satu perlawanan, INDEX mengembalikan warna pada kedudukan tersebut. Apabila tidak dijumpai, formula ini mengembalikan ralat # N / A.
Dengan nilai keras
Sekiranya anda tidak mahu menetapkan julat bernama luaran seperti "perkara" dalam contoh ini, anda boleh memasukkan nilai kod keras ke dalam formula sebagai "pemalar larik" seperti ini:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Dapatkan perlawanan pertama di sel
Bahasa di sini agak membingungkan, tetapi formula di atas akan mengembalikan pertandingan pertama yang terdapat dalam senarai perkara yang perlu dicari. Sekiranya anda ingin mengembalikan pertandingan pertama yang terdapat di sel yang sedang diuji, anda boleh mencuba formula seperti ini:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Dalam versi formula ini, fungsi MATCH disiapkan untuk mencari hasil coretan ini:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
yang menggunakan fungsi AGGREGATE untuk mendapatkan nilai minimum dalam hasil yang dikembalikan oleh SEARCH. Kami memerlukan AGGREGATE di sini, kerana array yang masuk kemungkinan akan mengandungi kesalahan (dikembalikan oleh SEARCH ketika perkara tidak dijumpai), dan kami memerlukan fungsi yang akan mengabaikan kesalahan tersebut dan masih memberi kami nilai angka minimum.
Hasil dari AGGREGATE dikembalikan terus ke MATCH sebagai nilai pencarian, bersama dengan array yang sama yang dikembalikan oleh SEARCH. Hasil akhirnya adalah perlawanan pertama yang terdapat di dalam sel, bukan perlawanan pertama yang terdapat dalam senarai perkara.
