Công thức chung
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Tóm lược
Excel chứa một giá trị đặt trước tích hợp để làm nổi bật các giá trị trùng lặp với định dạng có điều kiện, nhưng nó chỉ hoạt động ở cấp độ ô. Nếu bạn muốn đánh dấu toàn bộ các hàng trùng lặp, bạn sẽ cần sử dụng công thức của riêng mình, như được giải thích bên dưới.
Nếu bạn muốn đánh dấu các hàng trùng lặp trong tập dữ liệu chưa được sắp xếp và bạn không muốn thêm cột trợ giúp, bạn có thể sử dụng công thức sử dụng hàm COUNTIFS để đếm các giá trị trùng lặp trong mỗi cột dữ liệu.
Ví dụ: nếu bạn có giá trị trong các ô B4: D11 và muốn đánh dấu toàn bộ các hàng trùng lặp, bạn có thể sử dụng công thức khá xấu:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Các dải ô được đặt tên cho cú pháp rõ ràng hơn
Lý do công thức trên quá xấu là chúng ta cần phải khóa hoàn toàn từng phạm vi cột, sau đó sử dụng tham chiếu hỗn hợp để kiểm tra từng ô trong mỗi cột. Nếu bạn tạo phạm vi đã đặt tên cho mỗi cột trong dữ liệu: col_a, col_b và col_c, thì công thức có thể được viết bằng cú pháp gọn gàng hơn nhiều:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Giải trình
Trong công thức, COUNTIFS đếm số lần mỗi giá trị trong ô xuất hiện trong cột "mẹ" của nó. Theo định nghĩa, mỗi giá trị phải xuất hiện ít nhất một lần, vì vậy khi số lượng> 1, giá trị phải là một bản sao. Các tham chiếu được khóa cẩn thận nên công thức sẽ chỉ trả về true khi cả 3 ô trong một hàng xuất hiện nhiều hơn một lần trong các cột tương ứng của chúng.
Tùy chọn cột trợ giúp "gian lận" bằng cách kết hợp tất cả các giá trị trong một hàng với nhau trong một ô duy nhất bằng cách sử dụng nối. Sau đó, COUNTIF chỉ cần đếm số lần giá trị nối này xuất hiện trong cột D.
Cột trợ giúp + nối
Nếu bạn không phiền khi thêm cột trợ giúp vào dữ liệu của mình, bạn có thể đơn giản hóa công thức định dạng có điều kiện một chút. Trong cột trợ giúp, hãy nối các giá trị từ tất cả các cột. Ví dụ: thêm một công thức trong cột E trông giống như sau:
=B4&C4&D4
Sau đó, sử dụng công thức sau trong quy tắc định dạng có điều kiện:
=COUNTIF($E$4:$E$11,$E4)>1
Đây là một quy tắc đơn giản hơn nhiều và bạn có thể ẩn cột trợ giúp nếu muốn.
Nếu bạn có số lượng cột thực sự lớn, bạn có thể sử dụng hàm TEXTJOIN (Excel 2016 365) để thực hiện nối bằng một phạm vi:
=TEXTJOIN(",",TRUE,A1:Z1)
Sau đó, bạn có thể sử dụng COUNTIF như trên.
GIỚI THIỆU
Nếu bạn đang sử dụng phiên bản Excel trước năm 2007, bạn có thể sử dụng SUMPRODUCT như sau:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
