Formula umum
=FILTER(list1,COUNTIF(list2,list1))
Ringkasan
Untuk membandingkan dua daftar dan mengekstrak nilai umum, Anda bisa menggunakan rumus yang didasarkan pada fungsi FILTER dan COUNTIF. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=FILTER(list1,COUNTIF(list2,list1))
di mana list1 (B5: B15) dan list2 (D5: D13) diberi nama rentang. Hasilnya, nilai yang muncul di kedua daftar, tumpah ke kisaran F5: F11.
Penjelasan
Fungsi FILTER menerima larik nilai dan argumen "sertakan" yang memfilter larik berdasarkan ekspresi atau nilai logis.
Dalam kasus ini, larik disediakan sebagai rentang bernama "list1", yang berisi semua nilai dalam B5: B15. The termasuk argumen yang disampaikan oleh fungsi COUNTIF, yang bersarang di dalam FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF disiapkan dengan list2 sebagai rentang , dan list1 sebagai kriteria . Karena kami memberikan COUNTIF sebelas nilai kriteria, COUNTIF mengembalikan sebelas hasil dalam larik seperti ini:
(1;1;0;1;0;1;0;1;0;1;1)
Perhatikan 1 sesuai dengan item di list2 yang muncul di list1.
Larik ini dikirim langsung ke fungsi FILTER sebagai argumen "sertakan":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Fungsi FILTER memfilter list1 menggunakan nilai yang disediakan oleh COUNTIF. Nilai yang terkait dengan nol dihapus; nilai-nilai lain dipertahankan.
Hasil akhirnya adalah larik nilai yang ada di kedua daftar, yang mengalir ke kisaran F5: F11.
Logika yang diperluas
Dalam rumus di atas, kami menggunakan hasil mentah dari COUNTIF sebagai filter. Ini berfungsi karena Excel mengevaluasi nilai bukan nol sebagai TRUE, dan nol sebagai FALSE. Jika COUNTIF mengembalikan hitungan lebih besar dari 1, filter akan tetap bekerja dengan benar.
Untuk memaksakan hasil TRUE dan FALSE secara eksplisit, Anda dapat menggunakan "> 0" seperti ini:
=FILTER(list1,COUNTIF(list2,list1)>0)
Hapus duplikat atau urutkan
Untuk menghapus duplikat, cukup susun rumus di dalam fungsi UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Untuk mengurutkan hasil, tingkatkan di fungsi SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Daftar nilai yang hilang dari list2
Untuk mengeluarkan nilai dalam list1 yang hilang dari list2, Anda dapat membalikkan logika seperti ini:
=FILTER(list1,COUNTIF(list2,list1)=0)
