Formula generik
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Ringkasan
Untuk mengekstrak semua perlawanan berdasarkan padanan separa, anda boleh menggunakan formula formula berdasarkan fungsi INDEX dan AGGREGATE, dengan sokongan dari ISNUMBER dan SEARCH. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
dengan julat bernama berikut: "search" = D5, "ct" = D8, "data" = B5: B55.
Catatan: ini adalah formula larik, tetapi tidak memerlukan kawalan + shift + enter, kerana AGGREGATE dapat menangani susunan secara semula jadi.
Penjelasan
Inti formula ini adalah fungsi INDEX, dengan AGGREGATE digunakan untuk mengetahui "nth match" untuk setiap baris di kawasan ekstrak:
INDEX(data,nth_match_formula)
Hampir semua kerja adalah untuk mencari dan melaporkan baris mana dalam "data" yang sesuai dengan rentetan carian, dan melaporkan kedudukan setiap nilai yang sesuai dengan INDEX. Ini dilakukan dengan fungsi AGGREGATE yang dikonfigurasi seperti ini:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Argumen pertama, 15, memberitahu AGGREGATE untuk berkelakuan seperti KECIL, dan mengembalikan nilai terkecil. Argumen kedua, 6, adalah pilihan untuk mengabaikan kesalahan. Argumen ketiga adalah ungkapan yang menghasilkan pelbagai hasil yang sepadan (dijelaskan di bawah). Argumen keempat, F5, bertindak seperti "k" dalam KECIL untuk menentukan nilai "nth".
AGGREGATE beroperasi pada tatasusunan, dan ungkapan di bawah membina susunan untuk argumen ketiga di dalam AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Di sini, fungsi ROW digunakan untuk menghasilkan susunan nombor baris relatif, dan ISNUMBER dan SEARCH digunakan bersama untuk mencocokkan rentetan carian dengan nilai dalam data, yang menghasilkan array nilai BENAR dan SALAH.
Bit pintar adalah membahagikan nombor baris dengan hasil carian. Dalam operasi matematik seperti ini, TRUE berkelakuan seperti 1, dan FALSE berkelakuan seperti sifar. Hasilnya adalah nombor baris yang dikaitkan dengan padanan positif dibahagi dengan 1 dan bertahan dalam operasi, sementara nombor baris yang dikaitkan dengan nilai yang tidak sepadan dihancurkan dan menjadi kesalahan # DIV / 0. Kerana AGGREGATE diatur untuk mengabaikan kesalahan, ia mengabaikan kesalahan # DIV / 0, dan mengembalikan nombor terkecil "nth" dalam nilai yang tersisa, menggunakan nombor di lajur F untuk "nth".
Menguruskan prestasi
Seperti semua formula array, formula ini "mahal" dari segi sumber dengan set data yang besar. Untuk mengurangkan kesan prestasi, keseluruhan formula INDEX dan MATCH dibungkus dalam JIKA seperti ini:
=IF(F5>ct,"",formula)
di mana julat bernama "ct" (D8) memegang formula ini:
=COUNTIF(data,"*"&search&"*")
Pemeriksaan ini menghentikan bahagian formula INDEX dan AGGREGATE daripada dijalankan setelah semua nilai padanan telah diekstrak.
Susun atur formula dengan KECIL
Sekiranya versi Excel anda tidak mempunyai fungsi AGGREGATE, anda boleh menggunakan formula alternatif berdasarkan KECIL dan JIKA:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
