Công thức chung
=SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1
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 tìm và đánh dấu các cột 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.
Để đánh dấu các cột trùng lặp, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức được sử dụng để đánh dấu các cột trùng lặp là:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
Giải trình
Cách tiếp cận này sử dụng SUMPRODUCT để đếm sự xuất hiện của mọi giá trị trong bảng, một hàng tại một thời điểm. Chỉ khi cùng một giá trị xuất hiện ở cùng một vị trí trong cả ba hàng thì số lượng mới được tạo. Đối với ô B4, công thức được giải như sau:
=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
Lưu ý rằng tham chiếu hàng là hoàn toàn tuyệt đối, trong khi tham chiếu ô được trộn lẫn, chỉ có hàng bị khóa.
Với một hàng trợ giúp
Nếu bạn không phiền khi thêm hàng 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 một hàng trợ giúp, hãy nối tất cả các giá trị trong cột. Sau đó, bạn có thể sử dụng COUNTIF trên một hàng đó để đếm các giá trị xuất hiện nhiều lần và sử dụng kết quả để kích hoạt định dạng có điều kiện trong toàn bộ cột.
