Formula umum
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Ringkasan
Excel berisi preset bawaan untuk menyorot nilai duplikat dengan pemformatan bersyarat, tetapi hanya berfungsi di tingkat sel. Jika Anda ingin menyorot seluruh baris yang merupakan duplikat, Anda harus menggunakan rumus Anda sendiri, seperti yang dijelaskan di bawah ini.
Jika Anda ingin menyorot baris duplikat dalam kumpulan data yang tidak diurutkan, dan Anda tidak ingin menambahkan kolom pembantu, Anda bisa menggunakan rumus yang menggunakan fungsi COUNTIFS untuk menghitung nilai duplikat di setiap kolom data.
Misalnya, jika Anda memiliki nilai di sel B4: D11, dan ingin menyorot seluruh baris duplikat, Anda dapat menggunakan rumus yang agak jelek:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Rentang bernama untuk sintaks yang lebih bersih
Alasan mengapa rumus di atas sangat jelek adalah karena kita perlu mengunci sepenuhnya setiap rentang kolom, kemudian menggunakan referensi campuran untuk menguji setiap sel di setiap kolom. Jika Anda membuat rentang bernama untuk setiap kolom dalam data: col_a, col_b, dan col_c, rumusnya dapat ditulis dengan sintaks yang lebih rapi:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Penjelasan
Dalam rumusnya, COUNTIFS menghitung berapa kali setiap nilai dalam sel muncul di kolom "induk". Menurut definisi, setiap nilai harus muncul setidaknya satu kali, jadi jika hitungan> 1, nilainya harus duplikat. Referensi dikunci dengan hati-hati sehingga rumus akan kembali benar hanya ketika ketiga sel dalam satu baris muncul lebih dari sekali di kolomnya masing-masing.
Opsi kolom pembantu "menipu" dengan menggabungkan semua nilai dalam satu baris bersama-sama dalam satu sel menggunakan penggabungan. Kemudian COUNTIF hanya menghitung berapa kali nilai gabungan ini muncul di kolom D.
Kolom pembantu + penggabungan
Jika Anda tidak keberatan menambahkan kolom pembantu ke data Anda, Anda bisa sedikit menyederhanakan rumus pemformatan bersyarat. Di kolom pembantu, gabungkan nilai dari semua kolom. Misalnya, tambahkan rumus di kolom E yang terlihat seperti ini:
=B4&C4&D4
Kemudian gunakan rumus berikut dalam aturan pemformatan bersyarat:
=COUNTIF($E$4:$E$11,$E4)>1
Ini adalah aturan yang jauh lebih sederhana, dan Anda dapat menyembunyikan kolom pembantu jika Anda mau.
Jika Anda memiliki jumlah kolom yang sangat besar, Anda dapat menggunakan fungsi TEXTJOIN (Excel 2016 365) untuk melakukan penggabungan menggunakan rentang:
=TEXTJOIN(",",TRUE,A1:Z1)
Anda kemudian dapat menggunakan COUNTIF seperti di atas.
SUMPRODUCT
Jika Anda menggunakan versi Excel sebelum 2007, Anda dapat menggunakan SUMPRODUCT seperti ini:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
