Công thức chung
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Tóm lược
Một phạm vi có chứa các giá trị trùng lặp không? Nếu bạn muốn kiểm tra một phạm vi (hoặc danh sách) để tìm các bản sao, bạn có thể làm như vậy bằng công thức sử dụng COUNTIF cùng với SUMPRODUCT.
Trong ví dụ, có một danh sách các tên trong phạm vi B3: B11. Nếu bạn muốn kiểm tra danh sách này để xem có tên trùng lặp hay không, bạn có thể sử dụng:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Giải trình
Làm việc từ trong ra ngoài, COUNTIF đầu tiên nhận được số lượng mọi giá trị trong B3: B11 trong phạm vi B3: B11. Vì chúng tôi cung cấp một phạm vi (mảng) ô cho tiêu chí, nên kết quả là COUNTIF trả về một mảng số lượng. Trong ví dụ được hiển thị, mảng này trông giống như sau:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Tiếp theo 1 bị trừ, tạo ra một mảng như thế này:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Lưu ý rằng mỗi 1 trong mảng (tức là các mục chỉ xuất hiện một lần) đã được chuyển thành số không.
Tiếp theo, SUMPRODUCT thêm các phần tử trong mảng này và trả về kết quả, trong trường hợp này là số 2, sau đó được kiểm tra giá trị> 0.
Bất kỳ khi nào một danh sách chứa các bản sao, sẽ có ít nhất hai chữ 1 trong mảng được tính bằng SUMPRODUCT, vì vậy kết quả cuối cùng là TRUE có nghĩa là danh sách chứa các bản sao.
Xử lý các ô trống
Các ô trống trong phạm vi sẽ khiến công thức ở trên tạo ra kết quả không chính xác. Để lọc ra các ô trống hoặc ô trống, bạn có thể sử dụng phương pháp thay thế sau:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Ở đây chúng tôi sử dụng danh sách biểu thức logic "" để buộc tất cả các giá trị được liên kết với các ô trống về không.
