Formula generik
=FILTER(list1,COUNTIF(list2,list1))
Ringkasan
Untuk membandingkan dua senarai dan mengekstrak nilai sepunya, anda boleh menggunakan formula berdasarkan fungsi FILTER dan COUNTIF. Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
=FILTER(list1,COUNTIF(list2,list1))
di mana senarai1 (B5: B15) dan senarai2 (D5: D13) dinamakan julat. Hasilnya, nilai-nilai yang muncul dalam kedua-dua senarai, tumpah ke dalam julat F5: F11.
Penjelasan
Fungsi FILTER menerima array nilai dan argumen "include" yang menyaring array berdasarkan ungkapan atau nilai logik.
Dalam kes ini, array disediakan sebagai julat bernama "list1", yang mengandungi semua nilai dalam B5: B15. Yang termasuk hujah disampaikan oleh fungsi COUNTIF, yang bersarang di dalam FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF disiapkan dengan list2 sebagai range , dan list1 sebagai kriteria . Kerana kami memberikan COUNTIF sebelas nilai kriteria, COUNTIF mengembalikan sebelas hasil dalam array seperti ini:
(1;1;0;1;0;1;0;1;0;1;1)
Perhatikan 1 sesuai dengan item dalam senarai2 yang muncul dalam senarai1.
Array ini dihantar terus ke fungsi FILTER sebagai argumen "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Fungsi FILTER menyaring senarai1 menggunakan nilai yang diberikan oleh COUNTIF. Nilai yang berkaitan dengan sifar dikeluarkan; nilai-nilai lain dipelihara.
Hasil akhirnya adalah pelbagai nilai yang terdapat dalam kedua-dua senarai, yang masuk ke dalam julat F5: F11.
Logik lanjutan
Dalam formula di atas, kami menggunakan hasil mentah dari COUNTIF sebagai penapis. Ini berfungsi kerana Excel menilai sebarang nilai bukan sifar sebagai BENAR, dan sifar sebagai SALAH. Sekiranya COUNTIF mengembalikan kiraan yang lebih besar daripada 1, penapis akan tetap berfungsi dengan baik.
Untuk memaksa keputusan yang BENAR dan SALAH secara eksplisit, anda boleh menggunakan "> 0" seperti ini:
=FILTER(list1,COUNTIF(list2,list1)>0)
Keluarkan pendua atau urutkan
Untuk membuang pendua, hanya masukkan formula di dalam fungsi UNIK:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Untuk menyusun hasil, bersarang dalam fungsi SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Nilai senarai hilang dari senarai2
Untuk mengeluarkan nilai dalam senarai1 yang hilang dari senarai2, anda boleh membalikkan logik seperti ini:
=FILTER(list1,COUNTIF(list2,list1)=0)
