Formula umum
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Ringkasan
Untuk mengekstrak semua kecocokan berdasarkan kecocokan parsial, Anda dapat menggunakan rumus larik berdasarkan fungsi INDEX dan AGGREGATE, dengan dukungan dari ISNUMBER dan SEARCH. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
dengan rentang bernama berikut: "search" = D5, "ct" = D8, "data" = B5: B55.
Catatan: ini adalah rumus array, tetapi tidak memerlukan control + shift + enter, karena AGGREGATE dapat menangani array secara native.
Penjelasan
Inti dari rumus ini adalah fungsi INDEX, dengan AGGREGATE digunakan untuk mengetahui "kecocokan n" untuk setiap baris di area ekstrak:
INDEX(data,nth_match_formula)
Hampir semua pekerjaan adalah mencari tahu dan melaporkan baris mana dalam "data" yang cocok dengan string penelusuran, dan melaporkan posisi setiap nilai yang cocok ke 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, memberi tahu AGGREGATE untuk berperilaku seperti SMALL, dan mengembalikan nilai terkecil ke-n. Argumen kedua, 6, adalah opsi untuk mengabaikan kesalahan. Argumen ketiga adalah ekspresi yang menghasilkan larik hasil yang cocok (dijelaskan di bawah). Argumen keempat, F5, bertindak seperti "k" di SMALL untuk menentukan nilai "n".
AGGREGATE beroperasi pada array, dan ekspresi di bawah ini membangun array untuk argumen ketiga di dalam AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Di sini, fungsi ROW digunakan untuk menghasilkan larik nomor baris relatif, dan ISNUMBER serta SEARCH digunakan bersama untuk mencocokkan string penelusuran dengan nilai dalam data, yang menghasilkan larik nilai TRUE dan FALSE.
Cara cerdasnya adalah membagi nomor baris dengan hasil pencarian. Dalam operasi matematika seperti ini, TRUE berperilaku seperti 1, dan FALSE berperilaku seperti nol. Hasilnya adalah nomor baris yang terkait dengan kecocokan positif dibagi dengan 1 dan bertahan dalam operasi, sementara nomor baris yang terkait dengan nilai yang tidak cocok dihancurkan dan menjadi kesalahan # DIV / 0. Karena AGGREGATE disetel untuk mengabaikan kesalahan, ia mengabaikan kesalahan # DIV / 0, dan mengembalikan angka terkecil "n" dalam nilai yang tersisa, menggunakan angka di kolom F untuk "n".
Mengelola kinerja
Seperti semua rumus array, rumus ini "mahal" dalam hal sumber daya dengan kumpulan data yang besar. Untuk meminimalkan dampak kinerja, seluruh rumus INDEX dan MATCH dibungkus dalam IF seperti ini:
=IF(F5>ct,"",formula)
di mana rentang bernama "ct" (D8) memiliki rumus ini:
=COUNTIF(data,"*"&search&"*")
Pemeriksaan ini menghentikan bagian INDEX dan AGREGATE dari rumus agar tidak berjalan setelah semua nilai yang cocok telah diekstraksi.
Rumus array dengan SMALL
Jika versi Excel Anda tidak memiliki fungsi AGGREGATE, Anda bisa menggunakan rumus alternatif berdasarkan SMALL dan IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
