Formula generik
=FILTER(list1,COUNTIF(list2,list1))
Ringkasan
Untuk menyaring data untuk mengekstrak nilai pencocokan dalam dua senarai, anda boleh menggunakan fungsi FILTER dan fungsi COUNTIF atau COUNTIFS. Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
=FILTER(list1,COUNTIF(list2,list1))
di mana senarai1 (B5: B16) dan senarai2 (D5: D14) dinamakan julat. Hasil yang dikembalikan oleh FILTER hanya merangkumi nilai dalam senarai1 yang muncul dalam senarai2 .
Catatan: FILTER adalah fungsi array dinamik baru di Excel 365.
Penjelasan
Formula ini bergantung pada fungsi FILTER untuk mengambil data berdasarkan ujian logik yang dibina dengan fungsi COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
berfungsi dari dalam ke luar, fungsi COUNTIF digunakan untuk membuat penapis sebenarnya:
COUNTIF(list2,list1)
Perhatikan kita menggunakan list2 sebagai argumen range, dan list1 sebagai argumen kriteria. Dengan kata lain, kami meminta COUNTIF mengira semua nilai dalam senarai1 yang muncul dalam senarai2. Kerana kami memberikan COUNTIF beberapa nilai untuk kriteria, kami mendapatkan kembali array dengan pelbagai hasil:
(1;1;0;1;0;1;0;0;1;0;1;1)
Perhatikan array mengandungi 12 kiraan, satu untuk setiap nilai dalam senarai1 . Nilai sifar menunjukkan nilai dalam senarai1 yang tidak terdapat dalam senarai2 . Nombor positif lain menunjukkan nilai dalam senarai1 yang terdapat dalam senarai2 . Susunan ini dikembalikan terus ke fungsi FILTER sebagai argumen termasuk:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Fungsi penapis menggunakan tatasusunan sebagai penapis. Sebarang nilai dalam senarai1 yang dikaitkan dengan sifar akan dihapus, sementara nilai yang dikaitkan dengan nombor positif masih ada.
Hasilnya adalah susunan 7 nilai pencocokan yang tumpah ke dalam julat F5: F11. Sekiranya data berubah, FILTER akan mengira semula dan mengembalikan senarai nilai pencocokan baru berdasarkan data baru.
Nilai yang tidak sepadan
Untuk mengekstrak nilai yang tidak sepadan dari list1 (iaitu nilai dalam senarai1 yang tidak muncul dalam senarai2 ) anda boleh menambahkan fungsi NOT pada formula seperti ini:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Fungsi NOT secara berkesan membalikkan hasil dari COUNTIF - sebarang nombor bukan sifar menjadi SALAH, dan sebarang nilai sifar menjadi BENAR. Hasilnya adalah senarai nilai dalam senarai1 yang tidak terdapat dalam senarai2 .
Dengan INDEX
Adalah mungkin untuk membuat formula untuk mengekstrak nilai yang sepadan tanpa fungsi FILTER, tetapi rumusnya lebih kompleks. Salah satu pilihan adalah menggunakan fungsi INDEX dalam formula seperti ini:
Formula dalam G5, yang disalin adalah:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Inti formula ini adalah fungsi INDEX, yang menerima list1 sebagai argumen array. Sebilangan besar formula yang tinggal hanya mengira nombor baris yang akan digunakan untuk memadankan nilai. Ungkapan ini menghasilkan senarai nombor baris relatif:
ROW(list1)-ROW(INDEX(list1,1,1))+1
yang mengembalikan susunan 12 nombor yang mewakili baris dalam senarai1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Ini disaring dengan fungsi IF dan logik yang sama yang digunakan di atas dalam FILTER, berdasarkan fungsi COUNTIF:
COUNTIF(list2,list1) // find matching values
Susunan yang dihasilkan kelihatan seperti ini:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Array ini dihantar terus ke fungsi KECIL, yang digunakan untuk mengambil nombor baris pencocokan berikutnya ketika formula disalin ke bawah lajur. Nilai k untuk KECIL (think nth) dikira dengan julat yang berkembang:
ROWS($G$5:G5) // incrementing value for k
Fungsi IFERROR digunakan untuk memerangkap kesalahan yang berlaku ketika formula disalin dan kehabisan nilai yang sepadan. Untuk contoh idea lain, lihat formula ini.
