Ringkasan
Untuk mengekstrak beberapa padanan untuk memisahkan sel, dalam baris yang berasingan, anda boleh menggunakan formula larik berdasarkan INDEX dan KECIL. Dalam contoh yang ditunjukkan, formula di E5 adalah:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Setelah memasukkan formula di sel pertama, seret ke bawah dan seberang untuk mengisi sel lain.
Penjelasan
Catatan: formula ini menggunakan dua julat bernama: "names" merujuk kepada C4: C11, dan "group" merujuk kepada B4: B11. Nama-nama ini ditentukan dalam tangkapan skrin di atas juga.
Inti formula ini adalah: kami menggunakan fungsi KECIL untuk mendapatkan nombor baris yang sesuai dengan "nth match". Setelah kita mempunyai nombor baris, kita memasukkannya ke fungsi fungsi INDEX, yang mengembalikan nilai pada baris itu.
Caranya ialah SMALL bekerja dengan array yang dibina secara dinamik oleh IF dalam bit ini:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Coretan ini menguji julat "kumpulan" yang dinamakan untuk nilai di E4. Sekiranya dijumpai, ia mengembalikan nombor baris "dinormalisasi" dari pelbagai nombor baris yang dibuat dengan bahagian formula ini:
ROW(names)-MIN(ROW(names))+1
Hasilnya adalah susunan yang mengandungi nombor baris di mana terdapat padanan, dan SALAH di mana tidak. Susunan kelihatan seperti ini:
(1; SALAH; SALAH; SALAH; SALAH; 6; SALAH)
Susunan ini menjadi KECIL. Nilai k untuk KECIL (nth) berasal dari julat yang berkembang:
ROWS($E$5:E5)
Semasa menyalin jadual hasil, julat mengembang, menyebabkan k (nth) bertambah. Fungsi KECIL mengembalikan setiap nombor baris yang sepadan, yang dibekalkan ke fungsi INDEX sebagai baris_num, dengan julat "nama" yang dinamakan sebagai array.
Kesalahan pengendalian
Apabila ROWS mengembalikan nilai untuk k yang tidak ada, KECIL melemparkan ralat #NUM. Ini berlaku setelah semua perlawanan berlaku. Untuk menekan ralat, kami menggunakan IFERROR untuk menangkap ralat dan mengembalikan rentetan kosong ("").
