Formula umum
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Ringkasan
Apakah rentang berisi nilai duplikat? Jika Anda ingin menguji rentang (atau daftar) untuk duplikat, Anda bisa melakukannya dengan rumus yang menggunakan COUNTIF bersama-sama dengan SUMPRODUCT.
Dalam contoh, ada daftar nama dalam rentang B3: B11. Jika Anda ingin menguji daftar ini untuk melihat apakah ada nama duplikat, Anda dapat menggunakan:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Penjelasan
Bekerja dari dalam ke luar, COUNTIF pertama-tama menghitung setiap nilai di B3: B11 dalam rentang B3: B11. Karena kami menyediakan rentang (larik) sel untuk kriteria, COUNTIF mengembalikan larik hitungan sebagai hasilnya. Dalam contoh yang ditampilkan, array ini terlihat seperti ini:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
1 berikutnya dikurangi, yang menghasilkan larik seperti ini:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Perhatikan bahwa setiap 1 dalam larik (yaitu item yang muncul hanya sekali) telah diubah menjadi nol.
Selanjutnya, SUMPRODUCT menambahkan elemen dalam larik ini dan mengembalikan hasilnya, yang dalam hal ini adalah angka 2, yang kemudian diuji untuk nilai> 0.
Setiap kali daftar berisi duplikat, akan ada setidaknya dua 1 dalam larik yang dijumlahkan oleh SUMPRODUCT, jadi hasil akhir dari TRUE berarti daftar tersebut berisi duplikat.
Menangani sel kosong
Sel kosong dalam rentang akan menyebabkan rumus di atas memberikan hasil yang salah. Untuk memfilter sel kosong atau kosong, Anda bisa menggunakan alternatif berikut:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Di sini kami menggunakan daftar ekspresi logis "" untuk memaksa semua nilai yang terkait dengan sel kosong menjadi nol.
