Formula generik
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Ringkasan
Excel mengandungi pratetap terbina dalam untuk menonjolkan nilai pendua dengan pemformatan bersyarat, tetapi ia hanya berfungsi di peringkat sel. Sekiranya anda ingin menyerlahkan keseluruhan baris yang merupakan pendua, anda perlu menggunakan formula anda sendiri, seperti yang dijelaskan di bawah.
Sekiranya anda ingin menonjolkan baris pendua dalam sekumpulan data yang tidak disusun, dan anda tidak mahu menambahkan lajur pembantu, anda boleh menggunakan formula yang menggunakan fungsi COUNTIFS untuk menghitung nilai pendua di setiap lajur data.
Sebagai contoh, jika anda mempunyai nilai dalam sel B4: D11, dan ingin menonjolkan keseluruhan baris pendua, anda boleh menggunakan formula yang agak jelek:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Julat dinamakan untuk sintaks yang lebih bersih
Sebab formula di atas sangat jelek ialah kita perlu mengunci sepenuhnya setiap lajur lajur, kemudian menggunakan rujukan campuran untuk menguji setiap sel di setiap lajur. Sekiranya anda membuat julat bernama untuk setiap lajur dalam data: col_a, col_b, dan col_c, rumus dapat ditulis dengan sintaks yang jauh lebih bersih:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Penjelasan
Dalam formula, COUNTIFS mengira berapa kali setiap nilai dalam sel muncul di lajur "induk". Secara definisi, setiap nilai mesti muncul sekurang-kurangnya sekali, jadi apabila kiraan> 1, nilainya mestilah pendua. Rujukan dikunci dengan teliti sehingga formula akan kembali benar hanya apabila ketiga-tiga sel berturut-turut muncul lebih dari sekali di lajur masing-masing.
Pilihan kolum penolong "menipu" dengan menggabungkan semua nilai berturut-turut dalam satu sel menggunakan gabungan. Kemudian COUNTIF hanya mengira berapa kali nilai gabungan ini muncul di lajur D.
Lajur penolong + gabungan
Sekiranya anda tidak keberatan menambahkan lajur pembantu pada data anda, anda boleh sedikit sebanyak mempermudah formula pemformatan bersyarat. Dalam lajur pembantu, gabungkan nilai dari semua lajur. Sebagai contoh, tambahkan formula di lajur E yang kelihatan seperti ini:
=B4&C4&D4
Kemudian gunakan formula berikut dalam peraturan pemformatan bersyarat:
=COUNTIF($E$4:$E$11,$E4)>1
Ini adalah peraturan yang lebih mudah, dan anda boleh menyembunyikan ruangan penolong jika anda mahu.
Sekiranya anda mempunyai jumlah lajur yang sangat banyak, anda boleh menggunakan fungsi TEXTJOIN (Excel 2016 365) untuk melakukan gabungan menggunakan julat:
=TEXTJOIN(",",TRUE,A1:Z1)
Anda kemudian boleh menggunakan COUNTIF seperti di atas.
RINGKASAN
Sekiranya anda menggunakan versi Excel sebelum 2007, anda boleh menggunakan SUMPRODUCT seperti ini:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
