Công thức chung
SUMPRODUCT(--(A:A=A1))
Tóm lược
Lời nói đầu
Đây là một phần giới thiệu dài và khó chịu, nhưng bối cảnh là quan trọng, xin lỗi!
Nếu bạn cố gắng đếm các số rất dài (hơn 16 chữ số) trong một dải với COUNTIF, bạn có thể thấy kết quả không chính xác, do lỗi trong cách một số hàm xử lý các số dài, ngay cả khi những số đó được lưu trữ dưới dạng văn bản. Hãy xem xét màn hình bên dưới. Tất cả số đếm trong cột D đều không chính xác - mặc dù mỗi số trong cột B là duy nhất, số đếm được trả về bởi COUNTIF cho thấy những số này là trùng lặp.
=COUNTIF(data,B5)
Vấn đề này liên quan đến cách Excel xử lý số. Excel chỉ có thể xử lý 15 chữ số có nghĩa và nếu bạn nhập một số có nhiều hơn 15 chữ số trong Excel, bạn sẽ thấy các chữ số ở cuối được chuyển đổi âm thầm thành số không. Vấn đề đếm được đề cập ở trên phát sinh từ giới hạn này.
Thông thường, bạn có thể tránh giới hạn này bằng cách nhập các số dài dưới dạng văn bản, bằng cách bắt đầu số bằng một dấu nháy đơn ('999999999999999999) hoặc bằng cách định dạng (các) ô dưới dạng Văn bản trước khi nhập. Miễn là bạn không cần phải thực hiện các phép toán trên một số, đây là một giải pháp tốt và nó cho phép bạn nhập các số dài thêm cho những thứ như số thẻ tín dụng và số sê-ri mà không bị mất bất kỳ số nào.
Tuy nhiên, nếu bạn cố gắng sử dụng COUNTIF để đếm một số có hơn 15 chữ số (ngay cả khi được lưu trữ dưới dạng văn bản), bạn có thể thấy kết quả không đáng tin cậy. Điều này xảy ra vì COUNTIF chuyển đổi nội bộ giá trị dài trở lại một số tại một thời điểm nào đó trong quá trình xử lý, kích hoạt giới hạn 15 chữ số được mô tả ở trên. Nếu không có tất cả các chữ số, một số số có thể được tính là trùng lặp khi được đếm bằng COUNTIF.
Giải pháp
Một giải pháp là thay thế công thức COUNTIF bằng công thức sử dụng SUM hoặc SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong E5 trông giống như sau:
=SUMPRODUCT(--(data=B5))
Công thức sử dụng phạm vi được đặt tên là "dữ liệu" (B5: B9) và tạo ra số lượng chính xác cho mỗi số với SUMPRODUCT.
Giải trình
Đầu tiên, biểu thức bên trong SUMPRODUCT so sánh tất cả các giá trị trong phạm vi được đặt tên là "dữ liệu" với giá trị từ cột B trong hàng hiện tại. Điều này dẫn đến một mảng kết quả TRUE / FALSE.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Tiếp theo, âm kép ép các giá trị TRUE / FALSE thành 1/0 giá trị.
=SUMPRODUCT((1;0;0;0;0))
Cuối cùng, SUMPRODUCT chỉ đơn giản là tính tổng các mục trong mảng và trả về kết quả.
Biến thể công thức mảng
Bạn cũng có thể sử dụng hàm SUM thay vì hàm SUMPRODUCT, nhưng đây là công thức mảng và phải được nhập bằng control + shift + enter:
(=SUM(--(B:B=B5)))
Các chức năng khác với sự cố này
Tôi chưa tự xác minh điều này, nhưng có vẻ như một số hàm có cùng vấn đề, bao gồm SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF và AVERAGEIFS.
