Formula umum
=FILTER(list1,COUNTIF(list2,list1))
Ringkasan
Untuk memfilter data untuk mengekstrak nilai yang cocok dalam dua daftar, Anda dapat menggunakan fungsi FILTER dan fungsi COUNTIF atau COUNTIFS. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=FILTER(list1,COUNTIF(list2,list1))
di mana list1 (B5: B16) dan list2 (D5: D14) diberi nama rentang. Hasil yang dikembalikan oleh FILTER hanya menyertakan nilai di list1 yang muncul di list2 .
Catatan: FILTER adalah fungsi array dinamis baru di Excel 365.
Penjelasan
Rumus ini bergantung pada fungsi FILTER untuk mengambil data berdasarkan tes logika yang dibuat dengan fungsi COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
bekerja dari dalam ke luar, fungsi COUNTIF digunakan untuk membuat filter yang sebenarnya:
COUNTIF(list2,list1)
Perhatikan kita menggunakan list2 sebagai argumen rentang, dan list1 sebagai argumen kriteria. Dengan kata lain, kami meminta COUNTIF untuk menghitung semua nilai di list1 yang muncul di list2. Karena kami memberikan COUNTIF beberapa nilai untuk kriteria, kami mendapatkan kembali sebuah larik dengan beberapa hasil:
(1;1;0;1;0;1;0;0;1;0;1;1)
Perhatikan bahwa array berisi 12 hitungan, satu untuk setiap nilai dalam list1 . Nilai nol menunjukkan nilai di list1 yang tidak ditemukan di list2 . Bilangan positif lainnya menunjukkan nilai dalam list1 yang ditemukan di list2 . Array ini dikembalikan langsung ke fungsi FILTER sebagai argumen include:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Fungsi filter menggunakan array sebagai filter. Nilai apa pun dalam list1 yang terkait dengan nol akan dihapus, sementara nilai apa pun yang terkait dengan angka positif tetap ada.
Hasilnya adalah larik 7 nilai yang cocok yang tumpah ke dalam rentang F5: F11. Jika data berubah, FILTER akan menghitung ulang dan mengembalikan daftar baru nilai yang cocok berdasarkan data baru.
Nilai yang tidak cocok
Untuk mengekstrak nilai yang tidak cocok dari list1 (yaitu nilai dalam list1 yang tidak muncul di list2 ) Anda dapat menambahkan fungsi NOT ke rumus seperti ini:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Fungsi NOT secara efektif membalikkan hasil dari COUNTIF - angka bukan nol menjadi FALSE, dan nilai nol menjadi TRUE. Hasilnya adalah daftar nilai di list1 yang tidak ada di list2 .
Dengan INDEX
Dimungkinkan untuk membuat rumus untuk mengekstrak nilai yang cocok tanpa fungsi FILTER, tetapi rumusnya lebih kompleks. Salah satu opsinya adalah menggunakan fungsi INDEX dalam rumus seperti ini:
Rumus di G5, 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 rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Inti dari rumus ini adalah fungsi INDEX, yang menerima list1 sebagai argumen array. Sebagian besar rumus yang tersisa hanya menghitung nomor baris yang akan digunakan untuk nilai yang cocok. Ekspresi ini menghasilkan daftar nomor baris relatif:
ROW(list1)-ROW(INDEX(list1,1,1))+1
yang mengembalikan larik 12 angka yang mewakili baris dalam list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Ini difilter dengan fungsi IF dan logika yang sama digunakan di atas dalam FILTER, berdasarkan fungsi COUNTIF:
COUNTIF(list2,list1) // find matching values
Array yang dihasilkan terlihat seperti ini:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Array ini dikirim langsung ke fungsi SMALL, yang digunakan untuk mengambil nomor baris berikutnya yang cocok saat rumus disalin ke bawah kolom. Nilai k untuk SMALL (pikirkan n) dihitung dengan rentang yang meluas:
ROWS($G$5:G5) // incrementing value for k
Fungsi IFERROR digunakan untuk menjebak kesalahan yang terjadi saat rumus disalin dan kehabisan nilai yang cocok. Untuk contoh lain dari ide ini, lihat rumus ini.
