Formula umum
=SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1
Ringkasan
Excel berisi preset bawaan untuk menyorot nilai duplikat dengan pemformatan bersyarat, tetapi hanya berfungsi di tingkat sel. Jika Anda ingin menemukan dan menyorot kolom duplikat, Anda harus menggunakan rumus Anda sendiri, seperti yang dijelaskan di bawah ini.
Untuk menyorot kolom duplikat, Anda dapat menggunakan rumus berdasarkan fungsi SUMPRODUCT. Dalam contoh yang diperlihatkan, rumus yang digunakan untuk menyorot kolom duplikat adalah:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
Penjelasan
Pendekatan ini menggunakan SUMPRODUCT untuk menghitung kemunculan setiap nilai dalam tabel, satu baris dalam satu waktu. Hanya jika nilai yang sama muncul di lokasi yang sama di ketiga baris barulah dihitung. Untuk sel B4, rumus diselesaikan seperti ini:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1 =SUMPRODUCT(((1,1,1,1))*((1,0,1,0))*((1,0,1,0)))>1 =SUMPRODUCT((1,0,1,0))>1 =2>1 =TRUE
Perhatikan bahwa referensi baris sepenuhnya mutlak, sedangkan referensi sel dicampur, dengan hanya baris yang terkunci.
Dengan baris pembantu
Jika Anda tidak keberatan menambahkan baris pembantu ke data Anda, Anda dapat sedikit menyederhanakan rumus pemformatan bersyarat. Di baris pembantu, gabungkan semua nilai di kolom. Kemudian Anda bisa menggunakan COUNTIF pada satu baris itu untuk menghitung nilai yang muncul lebih dari sekali, dan menggunakan hasilnya untuk memicu pemformatan bersyarat di seluruh kolom.
